Обсуждение: How to store the PSQL command result into an array or Python dictionary?

Поиск
Список
Период
Сортировка

How to store the PSQL command result into an array or Python dictionary?

От
KhunSanAung
Дата:
Hi All,

I have a postgres database with 'myTable' having three columns as followed.

id

SubContinent

 Country

1

South-East Asia

 Indonesia

2

South-East Asia

 Thailand

3

South-East Asia

 Malaysia

4

South-East Asia

 Singapore

5

South-East Asia

 Philippines

6

South-East Asia

 Vietnam

7

South-East Asia

 Myanmar

8

South-East Asia

 Brunei

9

South-East Asia

 Cambodia

10

South-East Asia

 Laos

11

South-East Asia

 Timor Leste

 

I want to filter & select the table with some condition.


SELECT SubContinent, Country from myTable WHERE Country='Thailand' ;


Accessing to pgdatabase via Python is okay for me.

Using the PSQL command via Python, I would like to select the columns and store the values in an array of dictionary like below.


dict= {'SubContinent': 'South-East Asia', 'Country': 'Thailand'}; 


How can I put the selection result into a Python dictionary?


Any help is highly appreciate.

Many thanks in advanced.


Best regards


--
Have a nice day!
--

Mr. Khun San Aung

Re: How to store the PSQL command result into an array or Python dictionary?

От
Yaser Raja
Дата:
In PG 9.2 and above you can use the function row_to_json to get the result as a JSON. 

SELECT row_to_json(mytable) from myTable WHERE Country='Thailand' ;
                          row_to_json                           
----------------------------------------------------------------
 {"id":2,"subcontinent":"South-East Asia","country":"Thailand"}
(1 row)

You should be able to use this JSON for further processing in Python.

Regards
Yaser



On Tue, Nov 24, 2015 at 10:55 PM, KhunSanAung <khunsanaung.gis@gmail.com> wrote:
Hi All,

I have a postgres database with 'myTable' having three columns as followed.

id

SubContinent

 Country

1

South-East Asia

 Indonesia

2

South-East Asia

 Thailand

3

South-East Asia

 Malaysia

4

South-East Asia

 Singapore

5

South-East Asia

 Philippines

6

South-East Asia

 Vietnam

7

South-East Asia

 Myanmar

8

South-East Asia

 Brunei

9

South-East Asia

 Cambodia

10

South-East Asia

 Laos

11

South-East Asia

 Timor Leste

 

I want to filter & select the table with some condition.


SELECT SubContinent, Country from myTable WHERE Country='Thailand' ;


Accessing to pgdatabase via Python is okay for me.

Using the PSQL command via Python, I would like to select the columns and store the values in an array of dictionary like below.


dict= {'SubContinent': 'South-East Asia', 'Country': 'Thailand'}; 


How can I put the selection result into a Python dictionary?


Any help is highly appreciate.

Many thanks in advanced.


Best regards


--
Have a nice day!
--

Mr. Khun San Aung


Re: How to store the PSQL command result into an array or Python dictionary?

От
Terry Schmitt
Дата:
This does not directly answer your question, but I would seriously consider using psycopg for all Postgres/Python development.
This will give you much functionality in interacting with postgres.

Thanks,
Terry

On Tue, Nov 24, 2015 at 7:55 PM, KhunSanAung <khunsanaung.gis@gmail.com> wrote:
Hi All,

I have a postgres database with 'myTable' having three columns as followed.

id

SubContinent

 Country

1

South-East Asia

 Indonesia

2

South-East Asia

 Thailand

3

South-East Asia

 Malaysia

4

South-East Asia

 Singapore

5

South-East Asia

 Philippines

6

South-East Asia

 Vietnam

7

South-East Asia

 Myanmar

8

South-East Asia

 Brunei

9

South-East Asia

 Cambodia

10

South-East Asia

 Laos

11

South-East Asia

 Timor Leste

 

I want to filter & select the table with some condition.


SELECT SubContinent, Country from myTable WHERE Country='Thailand' ;


Accessing to pgdatabase via Python is okay for me.

Using the PSQL command via Python, I would like to select the columns and store the values in an array of dictionary like below.


dict= {'SubContinent': 'South-East Asia', 'Country': 'Thailand'}; 


How can I put the selection result into a Python dictionary?


Any help is highly appreciate.

Many thanks in advanced.


Best regards


--
Have a nice day!
--

Mr. Khun San Aung


Re: How to store the PSQL command result into an array or Python dictionary?

От
Yaroslav Fedevych
Дата:
psycopg2 has an extension to retrieve rows as dictionaries from a cursor.

See http://initd.org/psycopg/docs/extras.html#dictionary-like-cursor
for details.

You don't need (unless you want to, of course) to deal with JSON and
introduce major overhead for that.

On Wed, Nov 25, 2015 at 9:20 PM, Yaser Raja <yrraja@gmail.com> wrote:
In PG 9.2 and above you can use the function row_to_json to get the result as a JSON. 

SELECT row_to_json(mytable) from myTable WHERE Country='Thailand' ;
                          row_to_json                           
----------------------------------------------------------------
 {"id":2,"subcontinent":"South-East Asia","country":"Thailand"}
(1 row)

You should be able to use this JSON for further processing in Python.

Regards
Yaser



On Tue, Nov 24, 2015 at 10:55 PM, KhunSanAung <khunsanaung.gis@gmail.com> wrote:
Hi All,

I have a postgres database with 'myTable' having three columns as followed.

id

SubContinent

 Country

1

South-East Asia

 Indonesia

2

South-East Asia

 Thailand

3

South-East Asia

 Malaysia

4

South-East Asia

 Singapore

5

South-East Asia

 Philippines

6

South-East Asia

 Vietnam

7

South-East Asia

 Myanmar

8

South-East Asia

 Brunei

9

South-East Asia

 Cambodia

10

South-East Asia

 Laos

11

South-East Asia

 Timor Leste

 

I want to filter & select the table with some condition.


SELECT SubContinent, Country from myTable WHERE Country='Thailand' ;


Accessing to pgdatabase via Python is okay for me.

Using the PSQL command via Python, I would like to select the columns and store the values in an array of dictionary like below.


dict= {'SubContinent': 'South-East Asia', 'Country': 'Thailand'}; 


How can I put the selection result into a Python dictionary?


Any help is highly appreciate.

Many thanks in advanced.


Best regards


--
Have a nice day!
--

Mr. Khun San Aung