PostgreSQL CRUD example in Python with Psycopg2 (Part 1)

Introduction

This is the first part of a tutorial series providing a PostgreSQL crud example in python using the Psycopg2 adapter. Psycopg is a PostgreSQL adapter created for Python programming language and the wrapper for the official PostgreSQL client library. Psycopg is currently the most popular adapter for PostgreSQL and can be used to create, update, read and delete Postgres data.

Prerequisites

  • Python 3 must be properly installed and working on the device; note that Python 3 is required as Python 2.7 has been deprecated.

  • The Python driver for PostgreSQL must be properly installed and working in order for a PostgreSQL crud example in python using the Psycopg2 adapter to work.

  • The PostgreSQL database cluster must be properly installed on the machine. To verify the installation and the current version, execute the psql -V command in the terminal.

Create a test database for Psycopg2

A database must be created for the CRUD examples to work. Connect to the psql command line interface for Postgres using the psql command and name the database demo on the Postgres server.

psql: error

If a user or database for Postgres hasn’t been setup yet, or the correct password was not entered, the following error occurs will occur while attempting to connect to psql:

1
psql: error: could not connect to server: FATAL: role "postgres" does not exist

In this case, try connecting to one of the built-in PostgreSQL databases, like template, with the username by passing it to the -U option as shown here:

1
psql -U objectrocket -d template1

Use the ‘CREATE DATABASE’ SQL keyword

Once inside of psql, use the following CREATE DATABASE syntax to create a database that can be use to perform the CRUD operations with the psycopg2 Python adapter:

1
CREATE DATABASE demo;

The above command should return a CREATE DATABASE response resembling the following screenshot:

Screenshot of PostgreSQL CRUD example in Python creating database in psql

Now type the \q command to exit the psql interface and return to the terminal.

Installing psycopg2

There are multiple ways of installing the Psycopg2 adapter. The first way is to execute the pip3 install command for PIP. The second method is to download the wheel package and install it from the source using the following command:

1
pip3 install /path/to/psycopg2-package.whl

Install Psycopg2 using PIP

However, the easiest way to install psycopg2 is use the pip command used to install the package from the Python package Index or the pypi.

Using the terminal in Linux or mac, activate the needed virtual environment tools used for the Python packages with the following command:

1
source ~/projects/orkb/venv/bin/activate

Now use the pip command to install the psycopg2:

1
pip3 install psycopg2

NOTE: This will install the latest version of psycopg2. If a specific version is desired, add =2.7.5 after the word “psycopg2.”

Connect PostgreSQL using psycopg2

SQL queries can be performed by just connecting to the PostgreSQL database cluster using the Python 3.

A Python file can be created in any code editor or IDE that supports the Python extension. Alternatively, the virtual environment can be used just as easily to run Python 3.

Enter the following command in the virtual environment module:

1
python3

This command will connect to the Python programming interface or the interactive Python console.

Now create a file with the .py Python file extension and put the following code in the file:

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
31
32
33
# Import the Python driver for PostgreSQL
import psycopg2

# Create a connection credentials to the PostgreSQL database
try:
    connection = psycopg2.connect(
        user = "postgres",
        password = "1234",
        host = "localhost",
        port = "5432",
        database = "demo"
    )

    # Create a cursor connection object to a PostgreSQL instance and print the connection properties.
    cursor = connection.cursor()
    print(connection.get_dsn_parameters(),"\n")

    # Display the PostgreSQL version installed
    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print("You are connected into the - " record,"\n")

# Handle the error throws by the command that is useful when using Python while working with PostgreSQL
except(Exception, psycopg2.Error) as error:
    print("Error connecting to PostgreSQL database", error)
    connection = None

# Close the database connection
finally:
    if connection != None:
        cursor.close()
        connection.close()
        print("PostgreSQL connection is now closed")

NOTE: It is good practice to close the cursor and connection objects once finished, to avoid possible memory leaks.

Once the file has been ran using the python3 command, the result should resemble the following:

1
2
3
4
5
{'user': 'postgres', 'dbname': 'demo', 'host': 'localhost', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'}

You are connected into the - ('PostgreSQL 12.1 (Ubuntu 12.1-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit',)

PostgreSQL connection is now closed

Screenshot of PostgreSQL CRUD example in Python Psycopg2 connection script

Postgres ‘CREATE TABLE’ example

A table for the CRUD examples used in this tutorial series must be created. Use the following SQL statement in psql to create a book table on the database:

1
2
3
4
5
6
7
8
CREATE TABLE book
    (
        id INTEGER PRIMARY KEY,
        author VARCHAR(128),
        isbn VARCHAR(128),
        title VARCHAR(128),
        date_published DATE
    );

Alternatively, execute the statement in a psycopg2 cursor object in a Python script using the psycopg2 adapter.

PostgreSQL: Create a table using the psycopg2 adapter

Create the table by first importing the necessary libraries and connecting to PostgreSQL, as done previously, by executing the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# Import the Python driver for PostgreSQL
import psycopg2

# Import the SQL library from the adapter
from psycopg2 import sql, extensions

# Connect to PostgreSQL
connection = psycopg2.connect(
    user = "postgres",
    password = "1234",
    host = "localhost",
    port = "5432",
    database = "demo"
)

The above code uses the extensions and sql libraries from the psycopg2 adapter module.

Now, set the isolation level so that the SQL string can be executed using the following sql.SQL() method:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
autocommit = extensions.ISOLATION_LEVEL_AUTOCOMMIT
connection.set_isolation_level( autocommit )

# Instantiate a cursor object from the connection
cursor = connection.cursor()

table_name = "book"

# Concatenate SQL string
create_table = "CREATE TABLE {} (id INTEGER PRIMARY KEY, author VARCHAR(128), "
create_table += "isbn VARCHAR(128), title VARCHAR(128), date_published DATE);"

# use the sql module instead to avoid SQL injection attacks
cursor.execute(sql.SQL(
    create_table
).format(sql.Identifier( table_name )))

The results should resemble the following: Screenshot of PostgreSQL CRUD example in Python creating a Postgres table using Psycopg2 adapter

NOTE: Using sql.Identifier() to execute SQL statements in psycopg2 is typically considered safer and will help to prevent SQL injection attacks.

When the above code is executed in a Python script using the python3 command, a table called book in the demo database will be created. Execute the \dt command in psql to verify the table has been created.

Use to psycopg2 insert PostgreSQL record data

Now an example in psycopg2 will be created to insert data into a PostgreSQL table using the Python driver for Postgres. However, before proceeding, be certain to specify the user, the database and the table in PostgreSQL that will execute the Python command.

The following CRUD example uses a try-except indentation block to catch any psycopg2 errors:

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
# Import the Python driver for PostgreSQL
import psycopg2

# Create a connection credentials to the PostgreSQL database.
try:
    connection = psycopg2.connect(
        user = "objectrocket",
        password = "1234",
        host = "localhost",
        port = "5432",
        database = "demo"
    )

    cursor = connection.cursor()

    # Get the column name of a table inside the database and put some values
    pg_insert = """ INSERT INTO book (id, author, isbn, title, date_published)
                VALUES (%s,%s,%s,%s,%s)"""


    inserted_values = (
        1, # record ID
        'Layla Nowiztki',
        '789-1-46-268414-1',
        'How to become a professional programmer',
        'January 25 2011'
    )

    # Execute the above SQL string
    cursor.execute(pg_insert, inserted_values)

    # Commit transaction and prints the result successfully
    connection.commit()

    # Get a total of the inserted records
    count = cursor.rowcount
    print ("Successfully inserted", count, "records.")

except(Exception, psycopg2.Error) as error:
    print("Error connecting to PostgreSQL database", error)
    connection = None

finally:
    # Close the connection to the PostgreSQL database
    if connection != None:
        cursor.close()
        connection.close()
        print("The PostgreSQL connection is now closed")

The result displayed in Python terminal should resemble the following:

1
2
1 Successfully inserted
The PostgreSQL connection is now closed

NOTE: If there is an error, it will set the connection object instance to None. Otherwise, the connection in the finally: indentation block will be closed.

Once completed, go to the psql console to monitor the PostgreSQL data. To confirm the data was actually inserted into the table, use the database credentials given inside the configuration of the Python file.

Now use the following SELECT statement example to get data from a PostgreSQL table:

1
2
3
4
5
demo=# SELECT * FROM book;
 id |     author     |       isbn        |                  title                  | date_published
----+----------------+-------------------+-----------------------------------------+----------------
  1 | Layla Nowiztki | 789-1-46-268414-1 | How TO become a professional programmer | 2011-01-25
(1 ROW)

Screenshot of PostgreSQL CRUD example in Python for INSERT INTO using Psycopg2 adapter

Conclusion

This was part one of a tutorial series providing a PostgreSQL crud example in python for inserting data in PostgreSQL using the psycopg2 Python driver. The article explained that a database must be created for the CRUD examples to work and how to create a test database for Psycopg2. The tutorial also covered how to use the ‘CREATE DATABASE’ SQL keyword, install psycopg2 and connect to PostgreSQL using psycopg2. Finally, the tutorial provided a Postgres ‘CREATE TABLE’ example, how to create a table using the psycopg2 adapter and how to use psycopg2 to insert PostgreSQL record data. Remember that it is imperative that the Python driver for PostgreSQL be properly installed in order to be able to connect to the PostgreSQL database and create a crud example.

Just the Code

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
#!/usr/bin/python3

# Import the Python driver for PostgreSQL
import psycopg2

# Import the SQL library from the adapter
from psycopg2 import sql, extensions

try:

    # Connect to PostgreSQL
    connection = psycopg2.connect(
        user = "postgres",
        password = "1234",
        host = "localhost",
        port = "5432",
        database = "demo"
    )

    autocommit = extensions.ISOLATION_LEVEL_AUTOCOMMIT
    connection.set_isolation_level( autocommit )

    # Instantiate a cursor object from the connection
    cursor = connection.cursor()

    table_name = "book"

    # concatenate SQL string
    create_table = "CREATE TABLE {} (id INTEGER PRIMARY KEY, author VARCHAR(128), "
    create_table += "isbn VARCHAR(128), title VARCHAR(128), date_published DATE);"

    # use the sql module instead to avoid SQL injection attacks
    cursor.execute(sql.SQL(
        create_table
    ).format(sql.Identifier( table_name )))


    # Get the column name of a table inside the database and put some values
    pg_insert = """ INSERT INTO book (id, author, isbn, title, date_published)
                VALUES (%s,%s,%s,%s,%s)"""


    inserted_values = (
        1, # record ID
        'Layla Nowiztki',
        '789-1-46-268414-1',
        'How to become a professional programmer',
        'January 25 2011'
    )

    # Execute the above SQL string
    cursor.execute(pg_insert, inserted_values)

    # Commit transaction and prints the result successfully
    connection.commit()

    # Get a total of the inserted records
    count = cursor.rowcount
    print ("Successfully inserted", count, "records.")

except(Exception, psycopg2.Error) as error:
    print("Error connecting to PostgreSQL database", error)
    connection = None

finally:
    # Close the connection to the PostgreSQL database
    if connection != None:
        cursor.close()
        connection.close()
        print("The PostgreSQL connection is now closed")

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.