Summary: in this tutorial, you will learn how to query data from the PostgreSQL tables in Python using psycopg database adapter.
The steps for querying data from PostgreSQL table in Python
To query data from one or more PostgreSQL tables in Python, you use the following steps.
First, establish a connection to the PostgreSQL database server by calling the connect() function of the psycopg module.
1 | conn = psycopg2.connect(dsn) |
If the connection was created successfully, the connect() function returns a new connection object, otherwise, it throws a DatabaseError exception.
Next, create a new cursor by calling the cursor() method of the connection object. The cursor object is used to execute SELECT statements.
1 | cur = conn.cursor() |
Then, execute a SELECT statement by calling the execute() method. If you want to pass values to the SELECT statement, you use the placeholder ( %s) in the SELECT statement and bind the input values when you call the execute() method as follows.
1 | cur.execute(sql, (value1,value2)) |
After that, process the result set returned by the stored procedure using the fetchone(), fetchall(), or fetchmany() method.
- The
fetchone()fetches the next row in the result set. It returns a single tuple orNonewhen no more row is available. - The
fetchmany(size=cursor.arraysize)fetches the next set of rows specified by thesizeparameter. If you omit this parameter, thearraysizewill determine the number of rows to be fetched. Thefetchmany()method returns a list of tuples or an empty list if no more rows available. - The
fetchall()fetches all rows in the result set and returns a list of tuples. If there are no rows to fetch, thefetchall()method returns an empty list.
Finally, close the communication with the PostgreSQL by calling the close() method of the cursor and connection objects
1 2 | cur.close() conn.close() |
Querying data using fetchone() method
For the demonstrations, we will use the parts, vendors, and vendor_parts tables in the suppliers database that we created in the creating tables tutorial
The following get_vendor() function selects data from the vendors table and fetches the rows using the fetchone() method.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | def get_vendors(): """ query data from the vendors table """ conn = None try: params = config() conn = psycopg2.connect(**params) cur = conn.cursor() cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name") print("The number of parts: ", cur.rowcount) row = cur.fetchone() while row is not None: print(row) row = cur.fetchone() cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close() |
The following shows the output of the get_vendors() function.
1 2 | if __name__ == '__main__': get_vendors() |
1 2 3 4 5 6 7 8 | The number of parts: 7 (1, '3M Corp') (2, 'AKM Semiconductor Inc.') (3, 'Asahi Glass Co Ltd.') (4, 'Daikin Industries Ltd.') (5, 'Dynacast International Inc.') (6, 'Foster Electric Co. Ltd.') (7, 'Murata Manufacturing Co. Ltd.') |
Querying data using fetchall() method
The following get_parts() function uses the fetchall() method of the cursor object to fetch rows from the result set and displays all the parts in the parts table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | def get_parts(): """ query parts from the parts table """ conn = None try: params = config() conn = psycopg2.connect(**params) cur = conn.cursor() cur.execute("SELECT part_id, part_name FROM parts ORDER BY part_name") rows = cur.fetchall() print("The number of parts: ", cur.rowcount) for row in rows: print(row) cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close() |
1 2 | if __name__ == '__main__': get_parts() |
1 2 3 4 5 6 7 | The number of parts: 6 (4, 'Antenna') (5, 'Home Button') (6, 'LTE Modem') (1, 'SIM Tray') (2, 'Speaker') (3, 'Vibrator') |
Querying data using fetchmany() method
The following get_suppliers() function selects parts and vendors data using the fetchmany() method.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | def iter_row(cursor, size=10): while True: rows = cursor.fetchmany(size) if not rows: break for row in rows: yield row def get_part_vendors(): """ query part and vendor data from multiple tables""" conn = None try: params = config() conn = psycopg2.connect(**params) cur = conn.cursor() cur.execute(""" SELECT part_name, vendor_name FROM parts INNER JOIN vendor_parts ON vendor_parts.part_id = parts.part_id INNER JOIN vendors ON vendors.vendor_id = vendor_parts.vendor_id ORDER BY part_name; """) for row in iter_row(cur, 10): print(row) cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close() |
1 2 | if __name__ == '__main__': get_part_vendors() |
1 2 3 4 5 6 7 8 9 10 11 12 | ('Antenna', 'Foster Electric Co. Ltd.') ('Antenna', 'Murata Manufacturing Co. Ltd.') ('Home Button', 'Dynacast International Inc.') ('Home Button', '3M Corp') ('LTE Modem', 'Dynacast International Inc.') ('LTE Modem', '3M Corp') ('SIM Tray', 'AKM Semiconductor Inc.') ('SIM Tray', '3M Corp') ('Speaker', 'Daikin Industries Ltd.') ('Speaker', 'Asahi Glass Co Ltd.') ('Vibrator', 'Dynacast International Inc.') ('Vibrator', 'Foster Electric Co. Ltd.') |
In this tutorial, we have shown you various ways to select data from the PostgreSQL tables in Python using the fetchone(), fetchall(), and fetchmany() methods.