Creating tables in PostgreSQL using Python is a crucial step when building data-driven applications. This tutorial will guide you through the process using the psycopg2 library, which provides a robust interface for working with PostgreSQL databases.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. Setting Up PostgreSQL

4. Installing psycopg2

5. Connecting to a Database

6. Creating a Table in PostgreSQL

7. Full Working Example

8. Tips and Common Pitfalls

1. What You’ll Learn

• How to connect Python to PostgreSQL

• How to define and execute a CREATE TABLE SQL command

• How to handle exceptions and close connections gracefully

✅ 2. Prerequisites

Make sure you have the following ready:

• Python 3.x installed

• PostgreSQL server installed and running

• A PostgreSQL database created (e.g., school)

• psycopg2 installed (explained below)

3. Setting Up PostgreSQL

If you haven’t already created a PostgreSQL database, follow the tutorial on Creating a PostgreSQL Database with Python.
Example: create a database named school.

4. Installing psycopg2

Install the PostgreSQL adapter for Python:

pip install psycopg2-binary

5. Connecting to a Database

To execute SQL commands, connect to an existing database (school in this example):

import psycopg2

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

cur = conn.cursor()

6. Creating a Table in PostgreSQL

Let’s create a table named students with the following fields:

• id: Auto-incrementing primary key

• name: Text field (up to 100 characters)

• age: Integer

• grade: Text field (up to 10 characters)

SQL Query:

CREATE TABLE IF NOT EXISTS students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

Python Code:

cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        age INT,
        grade VARCHAR(10)
    )
""")
conn.commit()

7. Full Working Example

import psycopg2

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

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

    # Create a table
    cur.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100),
            age INT,
            grade VARCHAR(10)
        )
    """)

    # Commit the transaction
    conn.commit()

    print("Table 'students' created successfully.")

    # Close communication
    cur.close()
    conn.close()

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

8. Tips and Common Pitfalls

Conclusion

You’ve successfully created a table in PostgreSQL using Python! This is a foundational step for building any Python application that uses PostgreSQL for data storage. In future tutorials, you can move on to inserting data, querying, and updating records.