Python PostgreSQL - Create Database


Advertisements

You can create a database in PostgreSQL using the CREATE DATABASE statement. You can execute this statement in PostgreSQL shell prompt by specifying the name of the database to be created after the command.

Syntax

Following is the syntax of the CREATE DATABASE statement.

CREATE DATABASE dbname;

Example

Following statement creates a database named testdb in PostgreSQL.

postgres=# CREATE DATABASE testdb;
CREATE DATABASE

You can list out the database in PostgreSQL using the \l command. If you verify the list of databases, you can find the newly created database as follows −

postgres=# \l
                                                List of databases
   Name    | Owner    | Encoding |        Collate             |     Ctype   |
-----------+----------+----------+----------------------------+-------------+
mydb       | postgres | UTF8     | English_United States.1252 | ........... |
postgres   | postgres | UTF8     | English_United States.1252 | ........... |
template0  | postgres | UTF8     | English_United States.1252 | ........... |
template1  | postgres | UTF8     | English_United States.1252 | ........... |
testdb     | postgres | UTF8     | English_United States.1252 | ........... |
(5 rows)

You can also create a database in PostgreSQL from command prompt using the command createdb, a wrapper around the SQL statement CREATE DATABASE.

C:\Program Files\PostgreSQL\11\bin> createdb -h localhost -p 5432 -U postgres sampledb
Password:

Creating a Database Using Python

The cursor class of psycopg2 provides various methods execute various PostgreSQL commands, fetch records and copy data. You can create a cursor object using the cursor() method of the Connection class.

The execute() method of this class accepts a PostgreSQL query as a parameter and executes it.

Therefore, to create a database in PostgreSQL, execute the CREATE DATABASE query using this method.

Example

Following python example creates a database named mydb in PostgreSQL database.

import psycopg2

#establishing the connection

conn = psycopg2.connect(
   database="postgres", user='postgres', password='password', 
   host='127.0.0.1', port= '5432'
)
conn.autocommit = True

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Preparing query to create a database
sql = '''CREATE database mydb''';

#Creating a database
cursor.execute(sql)
print("Database created successfully........")

#Closing the connection
conn.close()

Output

Database created successfully........

Useful Video Courses


Video

Python Online Training

Most Popular

187 Lectures 17.5 hours

Malhar Lathkar

Video

Python Essentials Online Training

55 Lectures 8 hours

Arnab Chakraborty

Video

Learn Python Programming in 100 Easy Steps

136 Lectures 11 hours

In28Minutes Official

Video

Python with Data Science

75 Lectures 13 hours

Eduonix Learning Solutions

Video

Python 3 from scratch to become a developer in demand

70 Lectures 8.5 hours

Lets Kode It

Video

Python Data Science basics with Numpy, Pandas and Matplotlib

63 Lectures 6 hours

Abhilash Nelson

Advertisements