pi@raspberrypi ~ $ psql pytest
psql (9.1.9)
Type "help" for help.
pytest=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | employees | table | postgres
(1 row)
pytest=>When you enter the database name on the psql command line, the psql program takes you directly to
that database, and you don’t have to use the \c meta-command. Even though the owner of the
employees table is the postgres user account, the pi login role has privileges to interact with
it.
Now you have your table and user account all set. The next step is to install the PostgreSQL module
for Python.
Installing the Python PostgreSQL Module
Very much as in the MySQL environment, Python has several different modules that provide support
for communicating with PostgreSQL databases from your Python scripts. Fortunately, there’s a Python
v3 module for PostgreSQL already in the Raspbian software repository. The oddly named
psycopg2 module provides full support for interacting with the PostgreSQL database from Python
scripts. This is what you’ll use in the following examples.
The psycopg2 module is in the python3-psycopg2 software package. To install it, you just
use the apt-get utility, like this:
Click here to view code image
pi@raspberrypi ~ $ sudo apt-get install python3-psycopg2The psycopg2 module has both Python v2 and Python v3 versions, so make sure you install the
python3-psycopg2 module!
Coding with psycopg2
With the psycopg2 module installed, you’re all set to start coding your Python scripts to access the
PostgreSQL database. You’ll probably notice that many of the methods used are the same ones you’ve
seen in the MySQL/Connector module. For most Python database modules, once you learn how to
use one of them, it’s not too difficult to pick up how to use any others.
The following sections walk through how to connect to the PostgreSQL database, insert new data
records, and retrieve data records.
Connecting to the Database
Before you can interact with the table, your Python script must connect to the PostgreSQL database
you created. You do that with the connect() method, as shown here:
Click here to view code image
>>>import psycopg2
>>> conn = psycopg2.connect('dbname=pytest')