PostgreSQL – Create Tables in Python

Last Updated : 22 Aug, 2024

Creating tables in PostgreSQL using Python is an essential skill for developers working with databases. This article will explore the process of creating new tables in the PostgreSQL database using Python.

Why Create PostgreSQL Tables with Python?

Using Python to create PostgreSQL tables is beneficial because Python offers powerful libraries like ‘psycopg2′ for interacting with databases. Automating table creation through scripts can save time, especially when working with dynamic data structures or multiple databases.

Prerequisites

Before diving in, make sure you have the following:

PostgreSQL Installed: Ensure that PostgreSQL is properly installed and running on your machine.
Python Installed: You should have Python 3.x installed.
‘psycopg2’ Library: This is the most popular PostgreSQL adapter for Python. Install it using pip:

pip install psycopg2

Steps for creating PostgreSQL tables in Python

To create a new table in a PostgreSQL database, you use the following steps:

Construct the CREATE TABLE Statements: First, construct CREATE TABLE statements.
Connect to the PostgreSQL Database: Next, connect to the PostgreSQL database by calling the ‘connect()’ function. The connect() function returns a connection object.
Create a Cursor Object: Then, create a cursor object by calling the ‘cursor()‘ method of the connection object.
Execute the CREATE TABLE Statements: After that, execute the CREATE TABLE by calling the ‘execute()’ method of the cursor object.
Close the Cursor and Connection: Finally, close the communication with the PostgreSQL database server by calling the close() methods of the cursor and connection objects.

Python Program to Create PostgreSQL Tables

Let’s walk through a practical example of how to implement these steps.

Step 1: Create the Python Program

First, create a new file called ‘create_table.py’. Second, inside the ‘create_table.py’ file, define a new function called ‘create_tables()’.

Step 2: Define the ‘create_tables()’ Function

The ‘create_tables()’ function creates four tables in the suppliers database: ‘vendors’, ‘parts’, ‘vendor_parts’, and ‘part_drawings’.

#!/usr / bin / python

import psycopg2
from config import config


def create_tables():
    """ create tables in the PostgreSQL database"""
    commands = (
        """
        CREATE TABLE vendors (
            vendor_id SERIAL PRIMARY KEY,
            vendor_name VARCHAR(255) NOT NULL
        )
        """,
        """ CREATE TABLE parts (
                part_id SERIAL PRIMARY KEY,
                part_name VARCHAR(255) NOT NULL
                )
        """,
        """
        CREATE TABLE part_drawings (
                part_id INTEGER PRIMARY KEY,
                file_extension VARCHAR(5) NOT NULL,
                drawing_data BYTEA NOT NULL,
                FOREIGN KEY (part_id)
                REFERENCES parts (part_id)
                ON UPDATE CASCADE ON DELETE CASCADE
        )
        """,
        """
        CREATE TABLE vendor_parts (
                vendor_id INTEGER NOT NULL,
                part_id INTEGER NOT NULL,
                PRIMARY KEY (vendor_id, part_id),
                FOREIGN KEY (vendor_id)
                    REFERENCES vendors (vendor_id)
                    ON UPDATE CASCADE ON DELETE CASCADE,
                FOREIGN KEY (part_id)
                    REFERENCES parts (part_id)
                    ON UPDATE CASCADE ON DELETE CASCADE
        )
        """)
    conn = None
    try:
        # read the connection parameters
        params = config()
        # connect to the PostgreSQL server
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        # create table one by one
        for command in commands:
            cur.execute(command)
        # close communication with the PostgreSQL database server
        cur.close()
        # commit the changes
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()


if __name__ == '__main__':
    create_tables()

Step 3: Execute the Python program

To execute the Python program, you use the following command:

python create_table.py

Step 4: Verify Table Creation in PostgreSQL

First, log in to the PostgreSQL database server using the psql program.

Second, use the \dt command to display the table list from the suppliers database.

suppliers=# \dt

             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | part_drawings | table | postgres
 public | parts         | table | postgres
 public | vendor_parts  | table | postgres
 public | vendors       | table | postgres
(4 rows)

As you see can see clearly from the output, we have four tables created successfully in the suppliers database.

If you use other client tools like pgAdmin, you can view the tables via the table list under the public schema.

Important Points for Creating Tables in PostgreSQL

Apply necessary constraints like primary keys, foreign keys, and NOT NULL constraints when creating tables.
For large tables, consider indexing columns that will be frequently queried to improve performance.
If you have multiple tables, organize your code into separate functions for better readability and maintainability.
Always use meaningful table and column names that clearly indicate their purpose.