PostgreSQL is one of the popular Relational Database Management Systems. It is a simple RDBMS with advanced and enterprise-class features. One of the reasons it is so popular is, it supports both SQL and JSON querying, which means you can perform relational and non-relation queries on a PostgreSQL database. PostgreSQL is supported by many programming languages, and Python is one of those.
In this Python PostgreSQL tutorial, you will learn how to use the Pscopg2 library to connect PostgreSQL with Python. After connecting your Python script to PostgreSQL you can also perform the database queries and it will reflect on your actual PostgreSQL database. By the end of this tutorial, you will have a solid understanding on
- What is the Pscopg2 library?
- How to connect Python with PostgreSQL database.
- How to create a PostgreSQL database using Python
- How to perform PostgreSQL CURD operation with Python.
Prerequisites
Before we get started with the main topic Python connection with PostgreSQL, there are some tools that need to be installed in your system before you start coding.
- PostgreSQL : PostgreSQL must be downloaded on your system as a localhost. To download and install PostgreSQL on your computer click here .
- Python 3: This article primarily focuses on Python3, so it’s a no-brainer that Python must be installed on your system.
What is the Psycopg2 library?
Psycopg2 is one of the most popular Python PostgreSQL database adapters. It is a third-party open-source library, which allows you to connect your Python script with the PostgreSQL server or database. It is a very powerful library cum API that supports heavy multi-threaded applications, which help multiple users to operate on a single database concurrently. Popular Python Web-Frameworks like Flask, Django, and Pyramid are also compatible with Psycopg2 for Postgresql connectivity.
How to Install Python Psycopg2 library
As Psycopg2 is not a Part of Standard Python modules, we need to install it for your Python environment using the pip install command. Run the following command on your Terminal, Bash, or command prompt and it will install the Psycopg2 for your Python environment.
pip install psycopg2
Alternatives of Python Psycopg2 library
There are many alternatives of Psycopg2 which can also connect Python with PostgreSQL, and here is the list of those Psycopg2 alternatives.
Note: To use sqlalchemy one of the above libraries also needs to be installed. This tutorial will only be sticking to the Psycopg2 library because it is the most popular and stable Python library to connect with the PostgreSQL database. psycopg2 also has a binary stand-along package that needs no compiler or external libraries to execute. With the following pip install, you can also download its binary version using the pip command only.
pip install psycopg2-binary
Psycopg2 has multiple versions if you are interested in the older versions, you can install that specific version by specifying it while installing the package. For example, suppose you want to install the psycopg2 version 2.8.5 released back in April 2020, you can install it using the following pip install command.
pip install psycopg2 = 2.8.5
How to connect Python with PostgreSQL
Here are the steps you need to follow to connect Python with PostgreSQL:
Step 1: Create a PostgreSQL Database
With Python and psycopg2 we can only set the connection between Python and a PostgreSQL database. Before setting up the connection we need to create a Database first then we will connect using it with a Python script using psycopg2. To create a database in PostgreSQL, you can either use the PgAdmin or SQL Shell(psql). It’s straightforward to create a PostgreSQL database using SQL Shell, so we will be using that. To create a new database you can use the
CREATE DATABASE databaseName;
commend. Let’s create a PostgreSQL database by name
students
. Open your SQL Shell(psql), log in with the local host, and execute the following command.
CREATE DATABASE students;
The above command will create a new database of
students
for you. To check if the database was created successfully you can list out all the databases using the
\l
command.
Step 2: How to Connect Python with the PostgreSQL database
After successfully creating a PostgreSQL database now you can start writing your Python code and connect your Python script with created PostgreSQL database.
Now let’s discuss the steps that we need to follow just to connect a Python script with the PostgreSQL database.
Step I: Import the psycopg2 library
The first step toward setting up the connection is importing the psycopg2 library into the script. To import the library we can use Python’s import keyword.
#import the psycopg2 library into the Python program
import psycopg2
Step II: Set up the connection using psycopg2.connect() metod and login credentials.
While creating the Database on Shell(psql) you have noticed that it asked you Server, Database, Port, Username, and Password details. This is because of security reasons, even in the command shells, you have to log in first before accessing any PostgreSQL database. The same security goes when we access a PostgreSQL database using Python. We first have to connect to the database using the database credentials. To set the connection, the credentials you required are
- UserName
- Password
- Host
- Port
- DataBaseName
Username: It is your username with which you are working on PostgreSQL. By default, its value is
postgres Password: It is the password that you have created while installing Postgresql.
Host: It is the host IP address or server name on which PostgreSQL is running. In our case, we are running the PostgreSQL locally, which means our host value is localhost.
Port: It is the Port number on which the PostgreSQL is running. By default, PostgreSQL uses Port number 5432.
DataBaseName: It is the database name that you want to set up a connection with.
For this tutorial, we have created
students
database in the above section. So we will be using that database name. You can most of the above details while running the Shell (psql)
Once you have all the credentials you can set a connection to the PostgreSQL database using the
psycopg2.connect(dbname, user, password, port=5432, host)
method
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
The
psycopg2.connect()
method creates a new session of the database, connects our script to the valid database, and returns a new connection object. The
try
block would execute and set the connection successfully if all the credentials are correct, otherwise for the invalid credential the
except
block will execute.
Step 3: Perform SQL operation on PostgreSQL database using Python
After the successful connection setup, now it’s time to perform the SQL operation on the connected database using Python. To execute the SQL operation on the database we have to initialize a cursor object for the connection. To initialize the cursor object we can call the cursor() class on the conn object that we created in the above section. The cursor object allows you to perform the database operations on the selected database.
#initialize cursor object
cursor = conn.cursor()
The cursor object
cursor
,
supports a
execute()
method which accepts the SQL query or command as a string parameter and executes that command on the database. Let’s use the
execute()
method and execute the
SELECT version();
postgreSQL command.
Example
# import the psycopg 2 library into Python program
import psycopg2
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
#initialize cursor object
cursor = conn.cursor()
#execute the query on cursor
cursor.execute("SELECT version();")
result = cursor.fetchone()
print("The version of the PostgreSQL is:", result)
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
#close the connection after all the operations
cursor.close()
Output
The version of the PostgreSQL is: ('PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit',)
The execute() method return None, the method executes the query on the cursor object only. To get the query result we have to use the fetchone(), fetchall(), or fetchmany() methods. The fetchone() returns the first row from the result query set The fetchall() returns a list of all the rows from the result query set. The fetchmany(n) returns specific n numbers of rows from the result query set. The cursor.close() method closes the created connection session between the script and PostgreSQL. It is generally used at the end of the script when we are done with all the operations.
Step 4: How to Create a PostgreSQL table using Python
Now we are connected with our students’ database. Right now the database has no tables, to create a new table with columns, we can execute the
CREATE TABLE
command using the
execute()
method. Let’s create a table of
student_details
with the following column attributes Integer ID, Varchar Name, Integer age, Varchar grade, Real marks, Date dob.
Example
# import the psycopg 2 library into Python program
import psycopg2
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
#initialize cursor object
cursor = conn.cursor()
query = '''
CREATE TABLE IF NOT EXISTS student_details
(id integer PRIMARY KEY, name varchar, age integer, grade varchar, marks real, dob date );
'''
#execute the query on cursor
cursor.execute(query)
#make the changes to the database persistent
conn.commit()
print("The table has been created successfully")
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
#close the connection after all the operations
cursor.close()
Output
The table has been created successfully
The above program will generate a table
student_details
for the
students
database. You can verify whether the table was created or not by typing \dt command on the Shell(psql).
tudents=# \c students
You are now connected to database "students" as user "postgres".
students=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | student_details | table | postgres
(1 row)
The
conn.commit()
method will make sure that the changes also reflect on the actual database. If you forget to execute the commit() method on the connection object, there will be no changes on the database.
Take away points from the above example
- Always use the CREATE TABLE IF NOT EXISTS query when creating a table for a database. The IF NOT EXISTS SQL command makes sure that it only creates a table if it does not exist. If you omit this command from the create table and re-execute the python script it will raise the relation "student_details" already exists error.
- Always bind the query string using triple quotes ''' or use a raw string.
- Make sure to commit the changes after executing all the operations before closing the connection.
Step 5: Insert data into PostgreSQL table using Python
Whether it is a structured programing language or a high-level programming language both have data types to represent different data objects. Both Python and PostgreSQL have a defined set of data types. When we insert data into the database table the psycopg2 map the Python datatype to PostgreSQL data type. Here is the list of the data type mapping performed by the psycopg2
| Python Data types | PostgreSQL Data type |
| None | NULL |
| bool | bool |
| float | real double |
| int | smallint integer bigint |
| str unicode | varchar text |
| buffer memoryview bytearray bytes Buffer protocol | bytea |
| datetime.date | date |
| time | time timetz |
| datetime | timestamp timestamptz |
| timedelta | interval |
| list | ARRAY |
| tuple namedtuple | Composite types IN syntax |
| dict | hstore |
| Psycopg’s Range | range |
| Anything™ | json |
| UUID | uuid |
| ipaddress objects | inet cidr |
Now let’s insert some random data set to the student_details table that we created in the above section. To insert the data into the PostgreSQL database’s table we can use the
SQL INSERT INTO table_name (id, name, age, grade, marks, dob ) VALUES (id_data, name_data, age_data, grade_data, marks_data, dob_data)
Example
# import the psycopg2 library into Python program
import psycopg2
# import datetime module for date
import datetime
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
# initialize cursor object
cursor = conn.cursor()
names = ['Rahul', 'Neha', 'Joe', 'Rose', 'Jack']
ages = [16, 17, 18, 17, 16]
grades = ['11th', '11th', '12th', '12th', '10th']
marks = [998.2, 997.2, 990, 987, 983.4]
dobs = [(2005, 1, 1), (2004, 2, 14), (2003, 4, 15),
(2004, 5, 17), (2005, 7, 25)]
for i in range(5):
query = f'''
INSERT INTO
student_details (id , name, age, grade, marks, dob)
VALUES ({i}, '{names[i]}',{ages[i]}, '{grades[i]}', {marks[i]}, '{datetime.datetime(dobs[i][0], dobs[i][1], dobs[i][2])}')
;
'''
print(f"Inserted {i+1} record to row")
# execute the query on cursor
cursor.execute(query)
# make the changes to the database persistent
conn.commit()
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
# close the connection after all the operations
cursor.close()
Output
Inserted 1 record to row
Inserted 2 record to row
Inserted 3 record to row
Inserted 4 record to row
Inserted 5 record to row
Take Away points for the above example.
- For date data convert the date into a datetime object.
- For the string, and date values use the wrap the data with a single quote, as done in the above example.
That’s it with connecting to a PostgreSQL database using Python, creating tables, and inserting data into the tables. Now Let’s perform some CRUD operations with the student database.
How to Perform the CRUD operations on PostgreSQL database using Python?
CRUD operation means Create, Read/Retrieve, Update and Delete. To perform the CRUD operation on a database table, we can use the following SQL command.
- INSERT to Create new row data.
- SELECT to Read or Retrieve row data.
- UPDATE and SET commands to Update row data, and,
- DELETE command to delete row data.
Create a new data row in a database using Python
To create a new row or a record of data, we can use the SQL
INSERT
command.
Example
Let’s add a new student detail to the
student_details
table, using Python.
# import the psycopg 2 library into Python program
import psycopg2
# import datetime module for date
import datetime
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
# initialize cursor object
cursor = conn.cursor()
new_student = {'id': 5, 'name': 'Rohan', 'age': 17, 'grade': '11th',
'marks': 979.3, 'dob': datetime.datetime(2004, 5, 20)}
query = f'''
INSERT INTO
student_details (id , name, age, grade, marks, dob)
VALUES ({new_student['id']}, '{new_student['name']}',{new_student['age']}, '{new_student['grade']}', {new_student['marks']}, '{new_student['dob']}')
;
'''
print(f"New Record has been inserted into the table")
# execute the query on cursor
cursor.execute(query)
# make the changes to the database persistent
conn.commit()
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
# close the connection after all the operations
cursor.close()
Output
New Record has been inserted into the table
The INSERT command inserts a new row into the table. To add a new column to the table we can use the
ALTER TABLE table_name ADD COLUMN
command. Let’s add a new column
email
into the
student_details
table
# import the psycopg 2 library into Python program
import psycopg2
# import datetime module for date
import datetime
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
# initialize cursor object
cursor = conn.cursor()
query = f'''
ALTER TABLE student_details
ADD COLUMN email VARCHAR
;
'''
print(f"New Column has been added to the table")
# execute the query on cursor
cursor.execute(query)
# make the changes to the database persistent
conn.commit()
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
# close the connection after all the operations
cursor.close()
Output
New Column has been added to the table
To verify the newly added row and column you can run the
SELECT * FROM table_name
command on your Shell(psql)
Read/Retrieve rows data from Postgresql database table using Python
To read the data from the rows we use the
SELECT row_names FROM table_name
SQL command. And to access those rows in Python we can either use
fetchone()
,
fetchall()
, or
fetchmany()
methods.
Example 1
Read the first student detail from the student_details table.
# import the psycopg2 library into Python program
import psycopg2
# import datetime module for date
import datetime
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
# initialize cursor object
cursor = conn.cursor()
# select all the rows from the studnet_details
query = f'''
SELECT * FROM student_details ;
'''
# execute the query on cursor
cursor.execute(query)
# get only first row data
result = cursor.fetchone()
print('ID', "\t", 'NAME', "\t", 'AGE', "\t", 'GRADE',
"\t", 'MARKS', "\t", 'DOB', "\t", 'EMAIL')
print(result[0], "\t", result[1], "\t", result[2], "\t", result[3],
"\t", result[4], "\t", result[5].strftime("%d/%m/%Y"), "\t", result[6])
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
# close the connection after all the operations
cursor.close()
Output
ID NAME AGE GRADE MARKS DOB EMAIL
0 Rahul 16 11th 998.2 01/01/2005 None
The
fetchone()
method returns a single row of data as a tuple. To fetch all the rows we can use the
fetchall()
method, or to fetch a specific number of rows we can use the
fetchmany()
function
Example 2
Read the first 3 students by selecting all the rows.
# import the psycopg 2 library into Python program
import psycopg2
# import datetime module for date
import datetime
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
# initialize cursor object
cursor = conn.cursor()
# select all the rows from the studnet_details
query = f'''
SELECT * FROM student_details ;
'''
# execute the query on cursor
cursor.execute(query)
# get only first 3 rows data
result = cursor.fetchmany(3)
print('ID', "\t", 'NAME', "\t", 'AGE', "\t", 'GRADE',
"\t", 'MARKS', "\t", 'DOB', "\t", 'EMAIL')
for student in result:
print(student[0], "\t", student[1], "\t", student[2], "\t", student[3],
"\t", student[4], "\t", student[5].strftime("%d/%m/%Y"), "\t", student[6])
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
# close the connection after all the operations
cursor.close()
Output
ID NAME AGE GRADE MARKS DOB EMAIL
0 Rahul 16 11th 998.2 01/01/2005 None
1 Neha 17 11th 997.2 14/02/2004 None
2 Joe 18 12th 990.0 15/04/2003 None
Instead of
fetchmany(3)
we could have also limited the query result by using the
LIMIT 3
SQL command. For example
query = f'''
SELECT * FROM student_details LIMIT 3 ;
'''
Update rows data of a Postgresql database table using Python
In the update operation, you can update the old content or data of a single or multiple rows at once. In SQL to update a data of a row we have the
UPDATE
and
SET
commands.
Example
Update the student marks which id is 4 and also set his/her email address.
# import the psycopg 2 library into Python program
import psycopg2
# import datetime module for date
import datetime
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
# initialize cursor object
cursor = conn.cursor()
# update the row
query1 = f'''
UPDATE student_details
SET marks = 986.56,
email = 'jackgmail.com'
WHERE id = 4
;
'''
# execute the query on cursor
cursor.execute(query1)
#commit the changes
conn.commit()
# SELECT all the rows
query2 = f'''
SELECT * FROM student_details
ORDER BY id
;
'''
# execute the query on cursor
cursor.execute(query2)
# get all rows
result = cursor.fetchall()
print('ID', "\t", 'NAME', "\t", 'AGE', "\t", 'GRADE',
"\t", 'MARKS', "\t", 'DOB', "\t", 'EMAIL')
for student in result:
print(student[0], "\t", student[1], "\t", student[2], "\t", student[3],
"\t", student[4], "\t", student[5].strftime("%d/%m/%Y"), "\t", student[6])
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
# close the connection after all the operations
cursor.close()
Output
ID NAME AGE GRADE MARKS DOB EMAIL
0 Rahul 16 11th 998.2 01/01/2005 None
1 Neha 17 11th 997.2 14/02/2004 None
2 Joe 18 12th 990.0 15/04/2003 None
3 Rose 17 12th 987.0 17/05/2004 None
4 Jack 16 10th 986.56 25/07/2005 jackgmail.com
5 Rohan 17 11th 979.3 20/05/2004 None
Delete rows data of a Postgresql database table using Python
To delete a row or record from a PostgreSQL database table we can use the
DELETE FROM
command.
Example
DELETE
the row which id is 5
# import the psycopg 2 library into Python program
import psycopg2
# import datetime module for date
import datetime
# connection credentials
userName = "postgres"
passWord = "secretpass123"
port = 5432
dataBaseName = 'students'
try:
# set the connection
conn = psycopg2.connect(dbname=dataBaseName, user=userName,
password=passWord, port=port, host='localhost')
# initialize cursor object
cursor = conn.cursor()
# delete the row
query1 = f'''
DELETE FROM student_details
WHERE id = 5
;
'''
# execute the query on cursor
cursor.execute(query1)
#commit the changes
conn.commit()
# SELECT all the rows
query2 = f'''
SELECT * FROM student_details
ORDER BY id
;
'''
# execute the query on cursor
cursor.execute(query2)
# get all rows
result = cursor.fetchall()
print('ID', "\t", 'NAME', "\t", 'AGE', "\t", 'GRADE',
"\t", 'MARKS', "\t", 'DOB', "\t", 'EMAIL')
for student in result:
print(student[0], "\t", student[1], "\t", student[2], "\t", student[3],
"\t", student[4], "\t", student[5].strftime("%d/%m/%Y"), "\t", student[6])
except psycopg2.Error as e:
print("Fail to execute due to the error:", e)
# close the connection after all the operations
cursor.close()
Output
ID NAME AGE GRADE MARKS DOB EMAIL
0 Rahul 16 11th 998.2 01/01/2005 None
1 Neha 17 11th 997.2 14/02/2004 None
2 Joe 18 12th 990.0 15/04/2003 None
3 Rose 17 12th 987.0 17/05/2004 None
4 Jack 16 10th 986.56 25/07/2005 jackgmail.com
Conclusion
Now let’s sum up our Python tutorial on how to connect Python with the Postgresql database. To connect a Python program to a PostgreSQL database we need a database adapter like psycopg2. Psycopg2 is an API that acts as a bridge between Python and PostgreSQL databases so we can execute SQL queries using Python only. To set up the connection between Python and Postgresql we use the psycopg2.connect() method, and conn.cursor() method to perform the SQL operation. To execute the SQL query on the database we take the help of the cursor.execute() method. And to save the changes on the real database use the conn.commit() method.
Leave a Comment on this Post