Create a database in PostgreSQL using psycopg and Python

A database can be created in PostgreSQL using psycopg and Python through a database cursor object executing the DDL statement CREATE DATABASE. The example Python program creates a database and lists it through psql - the interactive terminal for PostgreSQL DBMS.

PostgreSQL Updated 2 Jul 2019 2 min read

Overview

  • A simple task often done by any database developer is to create a database and create a table underneath the database. Subsequently, the table is inserted with several rows and PostgreSQL is queried by an application program using a language like Python.
  • A database acts as a container for a variety of SQL objects like tables, stored procedures and functions, indexes and views.
  • CREATE DATABASE is one of the DDL statements supported by the PostgreSQL Database Management System.
  • Remember, only a valid user of the PostgreSQL server does creation of any SQL object on a PostgreSQL server. The user should have sufficient privilege to create a database on PostgreSQL server.
  • Psycopg is the Python client library for PostgreSQL. Using Psycopg a Python program can create a database on PostgreSQL server.

Creating a PostgreSQL database using Python and Psycopg

  • Import the Psycopg module inside a Python program. If the import fails use the pip command to install Psycopg.
  • To install Psycopg and its dependencies use the command: pip install "psycopg[binary,pool]"
  • Create a connection object to the PostgreSQL server by providing the logon credentials and calling the function psycopg.connect().
  • Through the connection object obtain a cursor object by calling the cursor() method on the connection object.
  • Define a Python string, which contains the SQL command CREATE DATABASE.
  • Pass the Python string containing the SQL statement while calling the execute() method of the database cursor.
  • Calling execute() function with CREATE DATABASE creates a database on the PostgreSQL server.
  • To get the list of databases, enter into the interactive terminal of PostgreSQL, by typing psql from command line. From psql type \list or \l to list all the databases.

Example

python
# Example Python program that creates

            # a database in PostgreSQL using Psycopg

# import the PostgreSQL client for Python

            import psycopg

# Connect to PostgreSQL DBMS with

            # autocommit enabled

            con = psycopg.connect("user=test password='test'", autocommit=True)

# Obtain a DB Cursor

            cursor          = con.cursor()

            name_Database   = "SocialMedia"

# Create table statement

            sqlCreateDatabase = "create database "+name_Database+";"

# Create a table in PostgreSQL database

            cursor.execute(sqlCreateDatabase)

Output

Output
List of databases

    Name     |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges

-------------+-------------+----------+-------------+-------------+-----------------------

 postgres    | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 ims         | test        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 socialmedia | test        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0   | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

             |             |          |             |             | postgres=CTc/postgres

 template1   | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

             |             |          |             |             | postgres=CTc/postgres

 test        | test        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/test             +

             |             |          |             |             | test=CTc/test

(6 rows)