PostgreSQL Python: Querying Data
Summary: in this tutorial, you will learn how to query data from the PostgreSQL tables in Python.
This tutorial picks up from where theHandling BLOB Data Tutorial left off.
The steps for querying data from a 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 server by calling theconnect() function of thepsycopg2 module.
conn = psycopg2.connect(config)If the connection is created successfully, theconnect() function returns a newConnection object; Otherwise, it throws aDatabaseError exception.
Next, create a new cursor by calling thecursor() method of theConnection object. Thecursor object is used to execute aSELECT statement.
cur= conn.cursor()Then, execute aSELECT statement by calling theexecute() method. If you want to pass values to theSELECT statement, you use the placeholder (%s) in theSELECT statement and bind the input values when calling theexecute() method:
cur.execute(sql, (value1,value2))After that, process the result set returned by the SELECT statement using thefetchone(), fetchall(), orfetchmany() 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 are 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 database connection by calling theclose() method of theCursor andConnection objects
cur.close()conn.close()If you use context managers, you don’t need to explicitly call theclose() methods of theCursor andConnection objects.
Querying data using the fetchone() method
For the demonstration purposes, we will use theparts,vendors, andvendor_parts tables in thesuppliers database:
The followingget_vendor() function selects data from thevendors table and fetches the rows using the fetchone() method.
import psycopg2from configimport load_configdef get_vendors(): """ Retrieve data from the vendors table """ config= load_config() try: with psycopg2.connect(**config) as conn: with conn.cursor() as cur: cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name") print("The number of parts: ", cur.rowcount) row= cur.fetchone() while rowis not None: print(row) row= cur.fetchone() except (Exception, psycopg2.DatabaseError)as error: print(error)if __name__ == '__main__': get_vendors()Output:
The number of parts: 8(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.')(8, 'LG')(7, 'Murata Manufacturing Co. Ltd.')Querying data using the fetchall() method
The followingget_parts() function uses thefetchall() method of the cursor object to fetch rows from the result set and display all the parts in theparts table.
import psycopg2from config import load_configdef get_vendors(): """ Retrieve data from the vendors table """ config= load_config() try: with psycopg2.connect(**config)as conn: with conn.cursor()as cur: cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name") rows = cur.fetchall() print("The number of parts: ", cur.rowcount) for row in rows: print(row) except (Exception, psycopg2.DatabaseError)as error: print(error)if __name__== '__main__': get_vendors()The number of parts: 8(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.')(8, 'LG')(7, 'Murata Manufacturing Co. Ltd.')Querying data using the fetchmany() method
The followingget_suppliers() function selects parts and vendor data using thefetchmany() method.
import psycopg2from configimport load_configdef iter_row(cursor, size=10): while True: rows= cursor.fetchmany(size) if not rows: break for rowin rows: yield rowdef get_part_vendors(): """ Retrieve data from the vendors table """ config= load_config() try: with psycopg2.connect(**config) as conn: with conn.cursor() as cur: 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 rowin iter_row(cur,10): print(row) except (Exception, psycopg2.DatabaseError)as error: print(error)if __name__ == '__main__': get_part_vendors()Output:
('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.')Download the project source code
In this tutorial, we have learned how to select data from the PostgreSQL tables in Python using the fetchone(),fetchall(), andfetchmany() methods.
Last updated on