How to use psycopg.extras module In SQL
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 = Truecursor = 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
Psycopg2 – Return dictionary like values
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.
Contact Us