When working with PostgreSQL in Python, one of the first tasks you might need to perform is creating a new database. This article will walk you through the entire process using the psycopg2 library, a popular PostgreSQL adapter for Python.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. Installing psycopg2

4. Connecting to the PostgreSQL Server

5. Creating a New Database

6. Verifying the Database

7. Complete Working Example

8. Tips and Common Pitfalls

1. What You’ll Learn

• How to connect to PostgreSQL using psycopg2

• How to create a new PostgreSQL database from Python

• How to handle common errors and ensure best practices

✅ 2. Prerequisites

Before getting started, make sure you have:

• Python 3.x installed

• PostgreSQL installed and running

• Access to the postgres superuser or a user with permissions to create databases

• Basic knowledge of Python and SQL

3. Installing psycopg2

To connect Python with PostgreSQL, you need the psycopg2 library. Install it using:

pip install psycopg2-binary

4. Connecting to the PostgreSQL Server

To create a new database, you must first connect to an existing database, typically the default postgres database.

import psycopg2

conn = psycopg2.connect(
    dbname="postgres",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

Replace your_password with your actual PostgreSQL password.

5. Creating a New Database

Use a CREATE DATABASE SQL statement to create a new database.

cur.execute("CREATE DATABASE school")

Call conn.commit() only if you’ve executed a BEGIN transaction block (not required for CREATE DATABASE).

⚠️ Note: PostgreSQL does not allow creating a database inside a transaction block, so avoid using conn.autocommit = False.

✅ Enable autocommit:

conn.autocommit = True
cur.execute("CREATE DATABASE school")

6. Verifying the Database

You can verify that the database was created via a PostgreSQL client or GUI tool like pgAdmin:

\l  -- Run inside psql terminal

Or connect to the new database using Python:

new_conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)
print("Connected to new database successfully.")

7. Complete Working Example

import psycopg2
from psycopg2 import sql

try:
    # Connect to the default database
    conn = psycopg2.connect(
        dbname="postgres",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )

    # Enable autocommit mode
    conn.autocommit = True

    # Create a cursor object
    cur = conn.cursor()

    # Create new database
    cur.execute(sql.SQL("CREATE DATABASE {}").format(
        sql.Identifier("school")
    ))

    print("Database 'school' created successfully.")

    # Close the cursor and connection
    cur.close()
    conn.close()

except psycopg2.Error as e:
    print(f"Error: {e}")

8. Tips and Common Pitfalls

Conclusion

You've now learned how to create a PostgreSQL database using Python! This is often the first step when setting up a backend system, analytics pipeline, or any data-centric application. In upcoming steps, you can proceed to create tables, insert data, and run queries using the newly created database.