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:

• 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:

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:

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:

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

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:

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:

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:

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.

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:

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:

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:

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:

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:

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:

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.