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

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

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

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