pi@raspberrypi ~ $ psql pytest
psql (9.1.9)
Type "help" for help.
pytest=> select * from employees;
empid | lastname | firstname | salary
-------+----------+------------+--------
1 | Blum | Katie Jane | 55000
2 | Blum | Jessica | 35000
(2 rows)
pytest=>The data is there! The next step is to write the code to query the table and retrieve the data values.
Querying Data
To query data, you submit a SELECT statement by using the execute() method. However, to
retrieve the query results, you have to use the fetchall() method for the cursor object. Listing
21.4 shows the script2104.py program, which demonstrates how to do this.
LISTING 21.4 The script2104.py Program Code
Click here to view code image
1: #!/usr/bin/python3
2:
3: import psycopg2
4: conn = psycopg2.connect('dbname=pytest')
5: cursor = conn.cursor()
6: cursor.execute('SELECT empid, lastname, firstname, salary FROM
employees')
7: result = cursor.fetchall()
8: for data in result:
9: print(data[0], data[1], data[2], data[3])
10: cursor.close()
11: conn.close()The script2104.py program assigns the output of the fetchall() method to the result
variable (line 7), which then contains a list of the data records in the query results. It iterates through
the list by using a for loop (line 8). The resulting list uses positional index values to reference each
data field in the data record. The order of the values matches the order in which you list the data
fields in the SELECT statement.
When you run the script2104.py program, you should see a list of the data records stored in the
PostgreSQL employees table, as shown here:
Click here to view code image
pi@raspberrypi ~ $ python3 script2104.py
1 Blum Katie Jane 55000.0
2 Blum Jessica 35000.0
pi@raspberrypi ~ $You can use these methods to handle a database of any size. The beauty of using a database server is
that all the data crunching and scaling happens behind the scenes, in the database server. Your Python
scripts just need to interface with the database server to submit SQL statements to handle the data.