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:
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 |
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:
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) |
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