Working with PostgreSQL in Python

Introduction

PostgreSQL is one of the most advanced and widely used relational database management systems. It's extremely popular for many reasons, a few of which include it being open source, its extensibility, and its ability to handle many different types of applications and varying loads.

With Python, you can easily establish a connection to your PostgreSQL database. There are many Python drivers for PostgreSQL, with "psycopg" being the most popular one. Its current version is psycopg2.

In this article, we'll be discussing how to access a PostgreSQL database in Python using the psycopg2 driver.

The psycopg2 Module

We can integrate Postgres with Python using the psycopg2 module. psycopg2 is a Postgres database adapter for Python. To use this module, you should first install it. This can be done using the pip command, as shown below:

$ pip3 install psycopg2

Note that I am using Python 3.5, hence I have used pip3 instead of pip.

Once the module has been installed, you can use it to connect to your database in your application.

Connecting to a Database

To connect to your database, you should first create a connection object representing the database. Next, you should create a cursor object to help you in execution of your SQL statements.

The following example demonstrates how to establish a connection to the database named "postgres":

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432")

print("Database opened successfully")

Output:

Database opened successfully

The following is the list of parameters that have been passed to the connect() method:

• database: The name of the database you need to connect to.
• user: The username to be used for authentication.
• password: The database password for the user.
• host: The address of the database server. For example, a domain name, "localhost", or an IP address.
• port: The port number. If you don't provide this, the default one will be used, which is 5432.

Note that the values for the above parameters should be correct for the connection to be successful. If not, an exception will be generated. The output in the above code shows that the connection to the database has been established successfully.

Creating a Table

To create a Postgres table in Python, we use the CREATE TABLE SQL statement. This query should be executed after establishing a connection to the database. We also create a cursor object by calling the cursor() method that belongs to the connection object. This cursor object is used to actually execute your commands.

We then call the execute() method of the cursor object to help us in creating the table. Finally, we need to commit and close the connection. "Committing" the connection tells the driver to send the commands to the database.

The following example demonstrates this:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()
cur.execute('''CREATE TABLE STUDENT
      (ADMISSION INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      COURSE        CHAR(50),
      DEPARTMENT        CHAR(50));''')
print("Table created successfully")

con.commit()
con.close()

Output

Database opened successfully
Table created successfully

The commit() method helps us apply the changes that we have made to the database, and these changes cannot be undone if commit() runs successfully. The close() method will close the connection to the database.

At this point we have created a table with 4 columns, all of which have various data types. The above output shows that the table was created successfully.

Inserting Data

We can insert either a single record or multiple records into a Postgres database table. Again, we must first establish a connection to the database server by calling the connect() function. Next, we should create a cursor object by calling the cursor() method. Lastly, we should run the INSERT statement via the execute() method to add the data into the table.

Here is an example of this in action:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3420, 'John', 18, 'Computer Science', 'ICT')");

con.commit()
print("Record inserted successfully")
con.close()

Output

Database opened successfully
Record inserted successfully

After running this code we have inserted a single record into our database table. This was done by specifying the name of the table as well as the columns into which we need to insert the data into. It is also possible for us to insert multiple records with one command. For example:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3419, 'Abel', 17, 'Computer Science', 'ICT')");
cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3421, 'Joel', 17, 'Computer Science', 'ICT')");
cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3422, 'Antony', 19, 'Electrical Engineering', 'Engineering')");
cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3423, 'Alice', 18, 'Information Technology', 'ICT')");

con.commit()
print("Records inserted successfully")
con.close()

Output

Database opened successfully
Records inserted successfully

Since the commit() method isn't called until after we "execute" all of the INSERT statements, multiple records are inserted with a single call of the commit() method.

Retrieving Data

You can select data from a Postgres database and view the table records. First, you must establish a connection to the database using the connect() function. Next, a new cursor should be created by calling the cursor() method. The created cursor object can then be used to execute the SELECT statement for querying data from the database.

For example:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()
cur.execute("SELECT admission, name, age, course, department from STUDENT")
rows = cur.fetchall()

for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[3])
    print("DEPARTMENT =", row[4], "\n")

print("Operation done successfully")
con.close()

Output

Database opened successfully
ADMISSION = 3420
NAME = John
AGE = 18
COURSE = Computer Science
DEPARTMENT = ICT

ADMISSION = 3419
NAME = Abel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT

ADMISSION = 3421
NAME = Joel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT

ADMISSION = 3422
NAME = Antony
AGE = 19
COURSE = Electrical Engineering
DEPARTMENT = Engineering

ADMISSION = 3423
NAME = Alice
AGE = 18
COURSE = Information Technology
DEPARTMENT = ICT

Operation done successfully

Here we have retrieved data from the database by specifying the table and names of the columns that we need to retrieve from the database table. This data is returned to us as list of tuples, with the "top level" list being the rows of data. Then each row is a tuple of the column data. If no rows are returned for the query then an empty list is returned by fetchall().

Updating Tables

We can update or modify the details of a record that has already been inserted into a database table. First, we have to establish a connection to the database using the connect() method. Next, we call the cursor() function to create a cursor object. Finally, we run the execute() method to execute the UPDATE statement with input values.

For example:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("UPDATE STUDENT set AGE = 20 where ADMISSION = 3420")
con.commit()
print("Total updated rows:", cur.rowcount)

cur.execute("SELECT admission, age, name, course, department from STUDENT")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[2])
    print("DEPARTMENT =", row[3], "\n")

print("Operation done successfully")
con.close()

Output

Database opened successfully
Total updated rows: 1
ADMISSION = 3419
NAME = 17
AGE = Abel
COURSE = Abel
DEPARTMENT = Computer Science

ADMISSION = 3421
NAME = 17
AGE = Joel
COURSE = Joel
DEPARTMENT = Computer Science

ADMISSION = 3422
NAME = 19
AGE = Antony
COURSE = Antony
DEPARTMENT = Electrical Engineering

ADMISSION = 3423
NAME = 18
AGE = Alice
COURSE = Alice
DEPARTMENT = Information Technology

ADMISSION = 3420
NAME = 20
AGE = John
COURSE = John
DEPARTMENT = Computer Science

Operation done successfully

In the above example, we have updated the value of column AGE for all rows where the ADMISSION is 3420. After executing the update we then retrieve this data to verify that the appropriate rows/columns have been updated.

Deleting Rows

To delete a record from a Postgres database table, we should first establish a connection to the database server. Secondly, a cursor object should be created by calling the cursor() function. We then run the DELETE statement to perform the deletion.

For example:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("DELETE from STUDENT where ADMISSION=3420;")
con.commit()
print("Total deleted rows:", cur.rowcount)

cur.execute("SELECT admission, name, age, course, department from STUDENT")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[3])
    print("DEPARTMENT =", row[4], "\n")

print("Deletion successful")
con.close()

Output

Database opened successfully
Total deleted rows: 1
ADMISSION = 3419
NAME = Abel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT

ADMISSION = 3421
NAME = Joel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT

ADMISSION = 3422
NAME = Antony
AGE = 19
COURSE = Electrical Engineering
DEPARTMENT = Engineering

ADMISSION = 3423
NAME = Alice
AGE = 18
COURSE = Information Technology
DEPARTMENT = ICT

Deletion successful

In this example we have deleted all records where the admission number of the student is 3420, which in this case is only one row. After retrieving the data using SELECT, we can see that this record is not part of the above output, confirming that it has been deleted from the database.

Conclusion

With Python, there are various ways we can access a PostgreSQL database. There are many database drivers for Python that we can use for this purpose, but psycopg is the most popular one. In this article we showed how to install the module, establish a connection to your PostgreSQL database, and execute common SQL queries using Python code.