Skip to content
Related Articles

Related Articles

PostgreSQL – Create Tables in Python
  • Last Updated : 02 Sep, 2020

This article will explore the process to create new tables in the PostgreSQL database using Python.

Steps for creating PostgreSQL tables in Python

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

  1. First, construct CREATE TABLE statements.
  2. Next, connect to the PostgreSQL database by calling the connect() function. The connect() function returns a connection object.
  3. Then, create a cursor object by calling the cursor() method of the connection object.
  4. After that, execute the CREATE TABLE by calling the execute() method of the cursor object.
  5. Finally, close the communication with the PostgreSQL database server by calling the close() methods of the cursor and connection objects.

Create a Python program –

First, create a new file called

Second, inside the file, define a new function called create_tables().

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)
        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)
                FOREIGN KEY (part_id)
                    REFERENCES parts (part_id)
    conn = None
        # 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:
        # close communication with the PostgreSQL database server
        # commit the changes
    except (Exception, psycopg2.DatabaseError) as error:
        if conn is not None:
if __name__ == '__main__':

Execute the Python program –

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


Verify the table creation –

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.

Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.

My Personal Notes arrow_drop_up
Recommended Articles
Page :