Python and PostgreSQL Integration: Step-by-Step Guide

In this article I will show you how to connect your python program to a PostgreSQL database.You will need  a working python installation and PostgreSQL running in your machine. If you don't have python installed, please check python installation guide  before moving to the next part.

installations

We will use psql  the PostgreSQL command line tool to create our demo database in this tutorial , you can as well use the graphical interface, pgadmin, just make sure that your database is active and you have the necessary permissions.

run the following to check whether PostgreSQL  is accessible through the command line

psql --version

if you get the version of PostgreSQL installed, congratulations you have PostgreSQL running in your system.

If you get an error like 'psql is not recognized ....', it means that you don't have PostgreSQL installed, head to  postgresql.org and download the latest version.

Besides python and PostgreSQL you will need a python adapter for PostgreSQL, in our case we will use psycopg2 which is one of the best options.

run the following command on the command interface.

pip install psycopg2

Creating demo database

Now that we are done with installations, we will need to create a demo database, which we will use to connect to our python script.

createdb -U your_username demo

You will be prompted to enter the password for the user you have specified , Enter your password to complete creating the database. If no password error occurred , the database you have just created should be accessible both from psql or pgadmin. To check whether the database was created successfully run 

psql -d demo -U your_username

The -d  followed by  demo  tells psql that we want to open a database called demo while the -U  followed by the username specifies which user we are going to open the database with.

Once again you will be prompted to enter your password, if successful your command line will open psql  and point to the database you've just created .

Creating a connection class

We are now ready to connect our python code to the database we've just created , open a python script  or create one and name it demo.py. You can use the command line to create one using the following command which will create a file named demo.py in your current directory.

type con > demo.py

Open demo.py in your favorite text-editor and type the following lines of code and make sure to set user to your PostgreSQL username

import psycopg2
conn = psycopg2.connect(
      """dbname=demo
         user=your_username
         password=your@secret
      """)

In the first line   we are importing  psycopg2 and  in the second line we are initializing a connection class.

dbname  is used to specify the database that we want to start a connection with. Make sure that the password you specify is correct for the user pointed to by your username.

If you get no error after running the above code,  the connection was successful  and you are now ready to start making queries to your database.

Querying demo database.

To start making queries , you will need  to  create a cursor object, a direct way to do that is

cursor = conn.cursor() #using the conn object we created above to create our cursor

Let us see some queries in action

Creating a table 

query = """
     CREATE TABLE person (
            name varchar(200),
            age int,
            country varchar(100)
           )
"""
cursor.execute(query)
conn.commit()
cursor.close()
conn.close()

The first line above is an SQL command for creating a person  relation in our database, we specify that the  table should contain three fields that is name , age , and the country the person comes from.

execute() method

In our code above we request for an SQL CREATE TABLE operation by calling cursor.execute(query).

The execute function returns None so to use  the values returned by the executed query you will need to use retrieval   methods  such as fetchall we will look at data retrieval in a short while. 

The execute method by itself does not do changes to the database so if you execute a query that will make changes to the database like in our case above you will need to call the commit() method of the connection instance.

You can avoid the overhead of calling conn.commit() every time you execute  queries  that are likely to alter the database by setting auto commit  to True, default is False.

add the following line of  code in your script and remove the conn.commit()

conn.autocommit = True

Your full script should now look like

import psycopg2
conn = psycopg2.connect(
      """dbname=demo
         user=your_username
         password=your@secret
      """)
conn.automomplete=True
cursor = conn.cursor() #using the conn object we created above to create our cursor

query = """
     CREATE TABLE person (
            name varchar(200),
            age int,
            country varchar(100)
           )
"""
cursor.execute(query)

cursor.close()
conn.close()

note the missing conn.commit() below the execute statement as it is unnecessary because auto commit is set to True.

The cursor class supports using it with the python's with keyword , this way you can use the cursor without needing to call cursor.close() because it is done for you by python in the background.

In your code remove the cursor  = conn.cursor()  and  put all the cursor operations inside a with block like shown below

with conn.cursor() as cursor:
    query = """
          CREATE TABLE person (
            name varchar(200),
            age int,
            country varchar(100)
           )"""

    cursor.execute(query)
  

     

The with block is  helpful  where the developer might forget to terminate a process, for example  the one shown above where you might forget to close the cursor thus leaving some of it's data suspended in memory.

Inserting data 

To perform an  SQL operations on bulk entries , the executemany method  helps perform all the entries operations under one session.So if you ever need to execute bulky SQL operations you can use executemany , though the documentation says that performing execute method in a loop is a little bit faster than using executemany. The following code shows how we can use the executemany method to populate our person table.

 

#...
with conn.cursor() as cursor:
     #....
     people = (("Smith",24,"America"),("Eric",18,'Canada'),("Leisel",14,"Germany"),("John",20,"Kenya"))
     cursor.executemany("iNSERT INTO person (name,age,country) VALUES (%s, %s, %s)",people)

Make sure to remove the query for creating table person , otherwise you will get a duplicate error since the table is already created.

If you run the above script your database will be populated with the data  we specified in the people tuple, let's prove that. Open the command line and move to demo database by running,

psql -d demo -U username

Enter your password and when the name of the database appears , run the following SQL command to get all the entries in table person.

SELECT * FROM person;

 

The above image shows that our execute many function worked successfully.

data retrieval

To use data from a PostgreSQL database in our python program , we will need to use a SELECT query to get data and the fetch methods of our cursor class to turn  the data into their equivalent python data types.

For example if we want to get people present in the person table of the demo database we will do it like:

#This assumes you have already initiated the connection class and set autocommit to True
with conn.cursor() as cursor:
   cursor.execute(" SELECT * FROM demo")
   person1 = cursor.fetchone()
   print(person1)
   people = cursor.fetchall()
   print(people)

If you call the retrieval methods such as fetchone , fetchmany or fetchall  and the leading execute  command did not return any data , a ProgrammingError  will be raised.

The fetchone method returns the first item returned by the SELECT query while fetchall as name suggests returns a tuple of all the elements.

You can as well use fetchmany  which takes the number of elements you want as it's argument.

You learned how to integrate Python with PostgreSQL, hope you found the article helpful .