Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Psycopg2 – Return dictionary like values

  • Last Updated : 28 Nov, 2021

In this article, we will discuss how to use psycopg2 to return dictionary-like values.

We can return dictionary-like values by using the psycopg2 PostgreSQL driver with and without using the extras module and both will be discussed in this article. psycopg.extras provide Miscellaneous goodies for Psycopg2. It holds helper functions and classes until a better place in distribution is found.

Without using psycopg. extras module:

First import the required packages and form a connection to the PostgreSQL database using the psycopg2.connect() method. and a cursor with the use of the cursor() class, after that we execute the select SQL statement to retrieve rows from the table. The cursor.fetchall() method gives the output as tuples, we use slicing to fetch values and print them out in the form of a dictionary.

Example: Use psycopg2 to return dictionary like values

Python3




import psycopg2
  
conn = psycopg2.connect(
    database="codes", user='postgres', password='pass',
    host='127.0.0.1', port='5432'
)
  
conn.autocommit = True
cursor = conn.cursor()
  
sql = '''CREATE TABLE continent_codes(code varchar(3), name char(20),
concatenated_column varchar(30));'''
  
  
cursor.execute(sql)
  
sql2 = '''COPY continent_codes(code,name,
concatenated_column)
FROM '/private/tmp/continent_codes.csv'
DELIMITER ','
CSV HEADER;'''
  
cursor.execute(sql2)
  
sql3 = '''select * from continent_codes;'''
cursor.execute(sql3)
results = cursor.fetchall()
for row in results:
    print("code: {}".format(row[0]))
    print("name: {}".format(row[1]))
    print("concatenated_column: {}".format(row[2]))
conn.commit()
conn.close()

Output :

code: AF
name: Africa
concatenated_column: AFAfrica
code: NA
name: North America
concatenated_column: NANorth America
code: OC
name: Oceania
concatenated_column: OCOceania
code: AN
name: Antartica
concatenated_column: ANAntartica
code: AS
name: Asia
concatenated_column: ASAsia
code: EU
name: Europe
concatenated_column: EUEurope
code: SA
name: South America
concatenated_column: SASouth America

Using psycopg.extras module

RealDictCursor is a cursor that uses a real dict as the base type for rows. This cursor doesn’t allow normal indexing to fetch data. data can be fetched only with the help of keys of column names of the table.

Syntax:

cursor = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)

Cursor subclasses are passed as cursor_factory argument to connect() so that the connection’s cursor() method will generate objects of this class. cursor_factory is an argument in the conn.cursor() class.

Syntax:

psycopg2.extras.RealDictCursor(*args, **kwargs)

This cursor used realdict as the base type for rows.

Example: Use psycopg2 to return dictionary like values

Python3




import psycopg2.extras
  
conn = psycopg2.connect(
    database="codes", user='postgres', password='pass',
    host='127.0.0.1', port='5432'
)
  
conn.autocommit = True
cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
sql = '''CREATE TABLE continent_codes(code varchar(3), name char(20),
concatenated_column varchar(30));'''
  
  
cursor.execute(sql)
  
sql2 = '''COPY continent_codes(code,name,
concatenated_column)
FROM '/private/tmp/continent_codes.csv'
DELIMITER ','
CSV HEADER;'''
  
cursor.execute(sql2)
  
sql3 = '''select * from continent_codes;'''
cursor.execute(sql3)
results = cursor.fetchall()
for row in results:
    print("code: {}".format(row['code']))
    print("name: {}".format(row['name']))
    print("concatenated_column: {}".format(row['concatenated_column']))
conn.commit()
conn.close()

Output:

code: AF
name: Africa
concatenated_column: AFAfrica
code: NA
name: North America
concatenated_column: NANorth America
code: OC
name: Oceania
concatenated_column: OCOceania
code: AN
name: Antartica
concatenated_column: ANAntartica
code: AS
name: Asia
concatenated_column: ASAsia
code: EU
name: Europe
concatenated_column: EUEurope
code: SA
name: South America
concatenated_column: SASouth America

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!