Create A Database In PostgreSQL Using Psycopg And Python

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
  • Psycopg2 is the Python client library for PostgreSQL. Using Psycopg2 a Python program can create a database on PostgreSQL server.  

Creating a PostgreSQL database using Python and Psycopg2:

  • Import the Psycopg2 module inside a Python program. If the import fails use the pip command to install Psycopg2.
  • To install Psycopg2 use the command: pip install psycopg2
  • Create a connection object to the PostgreSQL server by providing the logon credentials and calling the function psycopg2.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:

# ----- Example Python program to create a database in PostgreSQL using Psycopg2 -----

# import the PostgreSQL client for Python

import psycopg2

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

 

# Connect to PostgreSQL DBMS

con = psycopg2.connect("user=test password='test'");

con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);

 

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

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)

 


Copyright 2020 © pythontic.com