This tutorial picks up from where theConnecting to PostgreSQL database server tutorial left off.

Steps for creating tables in PostgreSQL from Python

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

• First,connect to the PostgreSQL server by calling theconnect() function. Theconnect() function returns aconnection object.
• Second, create acursor object by calling thecursor() method of theconnection object.
• Third, execute theCREATE TABLE by calling theexecute() method of thecursor object.
• Finally, close the connection.

If you use thewith statement, you don’t need to explicitly close the connection.

Creating tables in Python example

Let’s take an example of creating tables from Python.

1) Create a Python program

First, create a new file in the project directory calledcreate_tables.py.

Second, define a new function calledcreate_tables() in thecreate_tables.py module:

import psycopg2from configimport load_configdef 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        )        """)    try:        config= load_config()        with psycopg2.connect(**config) as conn:            with conn.cursor() as cur:                # execute the CREATE TABLE statement                for commandin commands:                    cur.execute(command)    except (psycopg2.DatabaseError, Exception)as error:        print(error)if __name__ == '__main__':    create_tables()

How it works.

First, initialize a list ofCREATE TABLE statements:

commands= ...

Next, read the connection parameters using theload_config() function of theconfig module:

config= load_config()

Then, connect to the PostgreSQL server using theconnect() function of thepsycopg2 module. Theconnect() function returns aconnection object:

with psycopg2.connect(**config) as conn:

Thewith statement will close the database connection automatically.

After that, create a newcursor object from theconnection object using thecursor() function:

with conn.cursor() as cur:

Thewith statement will also automatically close the cursor once it is no longer in use.

Finally, iterate over the command in thecommands list and execute each of them using theexecute() method:

for commandin commands:   cur.execute(command)

Thecreate_tables() function will create four tables in thesuppliers database:

• vendors – stores vendor data.
• parts – stores the part data.
• vendor_parts – stores the links between vendors and parts.
• part_drawings – stores the drawing of the parts.

The following diagram illustrates the tables and their relationships:

2) Execute the create_tables.py script

The following command executes thecreate_tables.py module that calls thecreate_tables() function:

python create_table.py

3) Verify the table creation

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the PostgreSQL server using thepsql client tool.

psql-U postgres

It’ll prompt you for a password for thepostgres user.

Second, change the current database tosuppliers:

\c suppliers

Third,show tables in thesuppliers database using the\dt command:

\dt

Output:

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)

The output indicates that thesuppliers database has four tables.

Download the project source code

Summary

• Call theexecute() method of theCursor object to execute aCREATE TABLE statement to create a new table in the database.

Last updated onMay 19, 2024

On this page

• Steps for creating tables in PostgreSQL from Python
• Creating tables in Python example
• Summary