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