Create a PostgreSQL Database Using The Psycopg2 Python Library

Introduction to the psycopg2 Python adapter for PostgreSQL

When you connect to PostgreSQL from a Python script, you can do much more than just query the database. You can insert rows into a table, create a new table and even create a new database. The psycopg2 adapter is a library that simplifies making SQL queries and statements in Python. This tutorial will show you how to create a PostgreSQL database using the psycopg2 Python library.

Prerequisites to using psycopg2 to create a PostgreSQL database

Before we can delve into the details of our Python script, it’s important to review the prerequisites that are needed for this tutorial:

  • Python 3 and the PIP package manager will need to be installed. Use the pip3 -V and python3 -V commands to get the version numbers for the PIP3 and Python 3 installations.

  • You can install the psycopg2 library using pip3 as shown below:

pip3 install psycopg2

Screenshot of Python 3 and PIP3 versions in terminal and installation of psycopg2

  • You’ll need to have a PostgreSQL user with the CREATEDB role enabled before proceeding with the code shown in this article. The next section will explain how to accomplish this in psql if you don’t have a user set up already.

Creating a PostgreSQL database inside the ‘psql’ command-line interface

In this section, we’ll show how you can manually create a database using the psql command-line interface. First, we’ll use the psql command to connect to PostgreSQL:

psql

NOTE: You’ll need to use the sudo -u postgres psql command to take advantage of elevated sudo privileges if you encounter any permission errors.

Once you’re connected to PostgreSQL, you can create a database and grant privileges to a user using the following SQL statements:

CREATE DATABASE python_test;
CREATE USER objectrocket with encrypted password 'mypass';
GRANT ALL PRIVILEGES ON DATABASE python_test TO objectrocket;

NOTE: Make sure that each of your SQL commands ends with a semi-colon (;).

Alter the PostgreSQL user’s role to allow for creating databases

The psycopg2 Python adapter will raise a ProgrammingError exception if the user specified in your Python script doesn’t have a CREATEDB role created for it.

We’ll use the following SQL statement to ALTER the user’s role so that it can create PostgreSQL databases:

ALTER USER {USER_NAME} CREATEDB;

This statement should return the response ALTER ROLE if the user exists.

psycopg2 returning permission denied error before altering user to create database

Create a new Python script and import the psycopg2 libraries

At this point, we’re ready to turn our attention to Python. Let’s create a new Python script using an IDE or text editor that has support for Python syntax and indentation. You can also use a terminal-based editor like vim, nano or gedit to create a new Python file. Be sure your file has the .py file extension.

Import the ‘connect’ and ‘extensions’ libraries from the psycopg2 Python package

Make sure to import your packages at the beginning of the script. Use Python’s from keyword to import just the two libraries needed to create a PostgreSQL database:

# import the psycopg2 database adapter for PostgreSQL
from psycopg2 import connect, extensions, sql

Instantiate a PostgreSQL connection object in the Python script

After the import statements, create a connection to the PostgreSQL server using your own host parameters, database name, and password:

# declare a new PostgreSQL connection object
conn = connect(
dbname = "python_test",
user = "objectrocket",
host = "localhost",
password = "mypass"
)

# object type: psycopg2.extensions.connection
print ("\ntype(conn):", type(conn))

NOTE: A pre-existing database is required in order to create a connection to PostgreSQL.

Create a global string object for the new database’s name

Next, we’ll declare a string for the new database name that will get passed:

# string for the new database name to be created
DB_NAME = "some_new_database"

Set the isolation level for the PostgreSQL transaction using psycopg2

The psycopg2 adapter will raise an ActiveSqlTransaction exception if you don’t set the connection object’s set_isolation_level attribute. This is because the CREATE DATABASE statement won’t work unless AUTOCOMMIT is set to ON.

Screenshot of psycopg2 Python adapter for PostgreSQL raising ActiveSqlTransaction error while creating database

PostgreSQL isolation levels for the psycopg2 adapter

The connection object’s set_isolation_level attribute accepts 5 different isolation levels with integer values of 0 through 4, and they are defined as follows:

0 = READ UNCOMMITTED 1 = READ COMMITTED 2 = REPEATABLE READ 3 = SERIALIZABLE 4 = DEFAULT

Use the ‘ISOLATION_LEVEL_AUTOCOMMIT’ attribute to set the isolation level

Rather than hard coding the integer value for the isolation level, you can use the extensions library’s ISOLATION_LEVEL_AUTOCOMMIT attribute to handle this for you.

The following code has psycopg2 return the integer and then sets the isolation level using that value:

# get the isolation leve for autocommit
autocommit = extensions.ISOLATION_LEVEL_AUTOCOMMIT
print ("ISOLATION_LEVEL_AUTOCOMMIT:", extensions.ISOLATION_LEVEL_AUTOCOMMIT)

# set the isolation level for the connection's cursors
# will raise ActiveSqlTransaction exception otherwise
conn.set_isolation_level( autocommit )

Instantiate a psycopg2 cursor and execute the ‘CREATE DATABASE’ SQL statement

In the next code segment, we instantiate a cursor from the PostgreSQL connection and execute the CREATE DATABASE statement:

# instantiate a cursor object from the connection
cursor = conn.cursor()

# use the execute() method to make a SQL request
cursor.execute('CREATE DATABASE ' + str(DB_NAME))

Use the psycopg2 module to execute SQL string instead

The other way to create a database is by utilizing the psycopg2 library’s sql module, which concatenates the string for you:

# use the sql module to avoid SQL injection attacks
cursor.execute(sql.SQL(
"CREATE DATABASE {}"
).format(sql.Identifier( DB_NAME )))

NOTE: It’s best to pass SQL queries and requests to the psycopg2 package library’s sql module instead of concatenating SQL strings. This prevents the risk of SQL injection attacks.

Close the psycopg2 cursor and connection objects

Be sure to close out all psycopg2 connections and cursors to prevent your server from running out of memory:

# close the cursor to avoid memory leaks
cursor.close()

# close the connection to avoid memory leaks
conn.close()

Verify the PostgreSQL database was created

If the Python script executed properly and no errors were raised, you can use psql to verify that the new database is there:

sudo -u postgres psql

Once inside the command-line interface, you can use the \l command to have it list all of your PostgreSQL databases. You should see some_new_database listed among the others:

Screenshot of psql terminal command line listing all PostgreSQL databases

Conclusion

There are many database operations that you can execute using a Python script that connects to PostgreSQL; creating a new database is just one example of the PostgreSQL tasks you can perform. In this article, we showed you how to create a PostgreSQL database using the psycopg2 Python library. With the example code provided in this tutorial, you’ll be able to create your own script to connect to PostgreSQL and interact with a database.

Just the Code

Throughout this tutorial, we looked at our Python code one segment at a time. Shown below is the full script needed to create a PostgreSQL database using the psycopg2 library.

#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import the psycopg2 database adapter for PostgreSQL
from psycopg2 import connect, extensions, sql

# declare a new PostgreSQL connection object
conn = connect(
dbname = "python_test",
user = "objectrocket",
host = "localhost",
password = "mypass"
)

# object type: psycopg2.extensions.connection
print ("\ntype(conn):", type(conn))

# string for the new database name to be created
DB_NAME = "some_new_database"

# get the isolation leve for autocommit
autocommit = extensions.ISOLATION_LEVEL_AUTOCOMMIT
print ("ISOLATION_LEVEL_AUTOCOMMIT:", extensions.ISOLATION_LEVEL_AUTOCOMMIT)

"""
ISOLATION LEVELS for psycopg2
0 = READ UNCOMMITTED
1 = READ COMMITTED
2 = REPEATABLE READ
3 = SERIALIZABLE
4 = DEFAULT
"""


# set the isolation level for the connection's cursors
# will raise ActiveSqlTransaction exception otherwise
conn.set_isolation_level( autocommit )

# instantiate a cursor object from the connection
cursor = conn.cursor()

# use the execute() method to make a SQL request
#cursor.execute('CREATE DATABASE ' + str(DB_NAME))

# use the sql module instead to avoid SQL injection attacks
cursor.execute(sql.SQL(
"CREATE DATABASE {}"
).format(sql.Identifier( DB_NAME )))

# close the cursor to avoid memory leaks
cursor.close()

# close the connection to avoid memory leaks
conn.close()

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.