PostgreSQL Python: Create Table

Summary: In this tutorial, you’ll learn how to create a table in PostgreSQL from Python using Psycopg 3.

Prerequisites #

Before you start, make sure you have:

• Python installed
• PostgreSQL running
• A database to connect to, for example: pyinventory.
• A PostgreSQL user such as postgres
• A virtual environment for your project

What the products table stores #

The products table stores basic information:

• product_id — the primary key
• name — the product name
• price — the product price
• safety_stock — the minimum stock level to maintain
• gross_weight — the product’s gross weight

Here’s the statement for creating the products table in PostgreSQL:

CREATE TABLE IF NOT EXISTS products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    safety_stock INTEGER NOT NULL,
    gross_weight NUMERIC(10, 2)
)Code language: SQL (Structured Query Language) (sql)

Python script for creating tables #

First, create the create_table.py file and use the following code to create the products table in the PostgreSQL database:

import psycopg
from config import config

create_table_sql = """
    CREATE TABLE IF NOT EXISTS products (
        product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price NUMERIC(10, 2) NOT NULL,
        safety_stock INTEGER NOT NULL,
        gross_weight NUMERIC(10, 2)
    )
    """

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(create_table_sql)
            print("The products table has been created successfully.")
except Exception as e:
    print(f"Error: {e}")
Code language: Python (python)

How it works:

Step 1. Import psycopg and config module:

import psycopg
from config import config
Code language: Python (python)

Step 2. Define a variable to store the CREATE TABLE statement:

create_table_sql = """
    CREATE TABLE IF NOT EXISTS products (
        product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price NUMERIC(10, 2) NOT NULL,
        safety_stock INTEGER NOT NULL,
        gross_weight NUMERIC(10, 2)
    )
    """Code language: Python (python)

Step 3. Execute the CREATE TABLE statement:

with psycopg.connect(**config) as conn:
    with conn.cursor() as cur:
        cur.execute(create_table_sql)Code language: Python (python)

Second, run the script by executing the create_table.py file:

python create_table.pyCode language: Python (python)

If the script succeeds, you should see:

The products table has been created successfully.
Code language: Python (python)

Verify the table #

Step 1. Open the Command Prompt on Windows or Terminal on macOS:

Step 2. Connect to the pyinventory database on the local PostgreSQL server using psql:

psql -U postgres -d pyinventoryCode language: Python (python)

Step 3. Use the \dt command to show tables in the pyinventory database:

\dtCode language: Python (python)

If everything is fine, you should see the following output:

        List of relations
Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
public | products | table | postgres
(1 row)
Code language: Python (python)

Summary #

In this tutorial, you learned how to:

• Define the products table using the PostgreSQL CREATE TABLE statement.
• Connect to PostgreSQL from Python using psycopg.connect().
• Execute a CREATE TABLE statement with cur.execute().