PostgreSQL - Create table using Python

Last Updated : 13 Aug, 2024

Creating tables in a PostgreSQL database using Python is a common task for developers working with databases. This process involves defining the structure of your data and ensuring that your database is optimized for efficient storage and retrieval.

In this article, we will walk through the steps of creating tables in PostgreSQL using Python.

Prerequisites:

psycopg2 module: A popular PostgreSQL adapter for Python, which allows you to connect to and interact with your PostgreSQL database.
Sample Database: A PostgreSQL database where you can create and manage tables.

Steps to Create a Table in PostgreSQL Using Python

To create a table in the database use the following steps:

First, create a CREATE TABLE statement
Second, establish a connection to the database using the 'connect()' function
Third, construct a cursor object by using the 'cursor()' method.
Now execute the above created CREATE TABLE statement using the 'execute()' function.

Example: Creating Tables in a School Database

To demonstrate the process, let's walk through an example where we create several tables in a PostgreSQL database named 'school'. We'll use a Python script named 'create_table.py', which includes a function called 'create_table()'.

Python

import psycopg2
from config import config


def create_tables():
    """ create tables in the PostgreSQL database"""
    commands = (
        """
        CREATE TABLE student (
            student_id SERIAL PRIMARY KEY,
            student_name VARCHAR(255) NOT NULL
        )
        """,
        """ CREATE TABLE grade (
                grade_id SERIAL PRIMARY KEY,
                grade_name VARCHAR(255) NOT NULL
                )
        """,
        """
        CREATE TABLE student_grade (
                grade_id INTEGER PRIMARY KEY,
                file_extension VARCHAR(5) NOT NULL,
                drawing_data BYTEA NOT NULL,
                FOREIGN KEY (grade_id)
                REFERENCES grade (grade_id)
                ON UPDATE CASCADE ON DELETE CASCADE
        )
        """,
        """
        CREATE TABLE student_detail (
                student_id INTEGER NOT NULL,
                grade_id INTEGER NOT NULL,
                PRIMARY KEY (student_id , grade_id),
                FOREIGN KEY (student_id)
                    REFERENCES student (student_id)
                    ON UPDATE CASCADE ON DELETE CASCADE,
                FOREIGN KEY (grade_id)
                    REFERENCES grade (grade_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()

To verify so use the below command through the client tool of the same database(ie, school):

\dt

Output:

This will successfully create the tables :

'student'
'grade'
'student_grade'
'student_detail'

Best Practices for Creating Tables in PostgreSQL

Use consistent and descriptive names for tables and columns.
Choose appropriate data types for each column to ensure efficient storage and accurate data representation.
Define constraints such as PRIMARY KEY, FOREIGN KEY, and UNIQUE to enforce data integrity.
Consider adding indexes on columns that will be frequently searched or used in join operations.

PostgreSQL - Create table using Python

ddeevviissaavviittaa

Improve

Article Tags :

Practice Tags :

python

PostgreSQL - Create table using Python

Prerequisites:

Steps to Create a Table in PostgreSQL Using Python

Example: Creating Tables in a School Database

Best Practices for Creating Tables in PostgreSQL

Similar Reads