Python and Psycopg2 Example
Introduction
If you’re planning to write Python scripts that interact with a PostgreSQL database, you’ll need to install and import the right adapter for the job. The psycopg2 driver is a popular PostgreSQL driver developed for use with the Python programming language. In this example, we’ll present a complete Python psycopg2 example, showing you how to install the adapter and use it in a simple Python script.
Prerequisites
Before we dive into our Python psycopg2 example, let’s go over a few prerequisites that are essential for this project:
You’ll need to have Python installed on your machine. Make sure that you’re using Python 3 since Python 2.7 is now deprecated.
You’ll also need to Install the Python driver for PostgreSQL in order to access the database from a Python script. We’ll be covering this process in the next section.
A PostgreSQL database cluster needs to be installed on your machine. You can verify that it’s installed by using the command
psql -V, which will return the version of Postgres that exists on your machine.
Installing Psycopg2
There are several ways to install psycopg2 on your machine. You can download many Python packages by clicking this site.
Install using pip command
The easiest way to install psycopg2 is to use the pip command. This command will install packages from the Python Package Index, or ‘pypi’.
The first step is to activate the virtual environment where you’d like to install your Python package:
1 | source ~/projects/orkb/venv/bin/activate |
You can then use the pip command to install the psycopg2 package:
1 | pip install psycopg2 |
NOTE: This command will install the latest version of psycopg2. If you want a particular version, just add a version number after the word psycopg2, like this: =2.7.5.
Connect PostgreSQL using psycopg2
If you want to perform SQL queries from a Python script, you’ll need to connect to a PostgreSQL database cluster in your code.
You can create a Python file in any editor or IDE that supports Python extensions; alternatively, you can use a virtual environment (venv) to run your Python 3 code.
In your venv, enter the following command:
1 | python3 |
This will take you to the Python programming interface or the interactive Python console.
Next, create a file and add the following code to it:
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 | #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") |
When you run this file, the output should look like this:
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 |
Psycopg2 insert data into PostgreSQL
Now that we confirmed we were able to connect to PostgreSQL using psycopg2, let’s try something a bit more complex. Our next Python psycopg2 example will insert data into a PostgreSQL table using the Python driver for Postgres.
If you’re following along with these examples, be sure to indicate the appropriate user, database and table information for your own instance of PostgreSQL.
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 | 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, 'Layla Nowiztki', '789-1-46-268414-1', 'How to become a professional programmer', 'January 25 2011') cursor.execute(pg_insert, inserted_values) #Commit transaction and prints the result successfully connection.commit() count = cursor.rowcount print (count, "Successfully inserted") #Error handling for psycopg2 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") |
The output of this code will look like his:
1 2 | 1 Successfully inserted The PostgreSQL connection is now closed |
After you run the code, go to the psql console to see if data was actually inserted into the PostgreSQL table.
To confirm whether the insert operation was successful, use a SELECT statement:
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
In order to harness the power of PostgreSQL from your Python scripts, you’ll need to use the correct adapter. The psycopg2 adapter for PostgreSQL makes it easy to get connected to a database with just a few lines of code. In this article, we explained how to install psycopg2 and provided sample code that demonstrated how to use the adapter. With this Python psycopg2 example to use as a guide, you’ll have no trouble connecting to PostgreSQL in your own Python scripts.
Just the Code
If you’d like to test out the Python script from this tutorial on your own machine, here’s the code in its entirety:
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 | #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") #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, 'Layla Nowiztki', '789-1-46-268414-1', 'How to become a professional programmer', 'January 25 2011') #Execute the pg_insert SQL string cursor.execute(pg_insert, inserted_values) #Commit transaction and prints the result successfully connection.commit() count = cursor.rowcount print (count, "Successfully inserted") #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") |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started