Summary: in this tutorial, you will learn how 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:
- First, construct a CREATE TABLE statement.
- Next, connect to the PostgreSQL database by calling the
connect()function. Theconnect()function returns aconnectionobject. - Then, create a
cursorobject by calling thecursor()method of theconnectionobject. - After that, execute the
CREATE TABLEby calling theexecute()method of thecursorobject. - Finally, close the communication with the PostgreSQL database server by calling the
close()methods of thecursorandconnectionobjects.
Creating tables in Python example
The following create_tables() function creates four tables in the suppliers database: vendors, parts, vendor_parts, and part_drawings.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | #!/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() |
Let’s run the program and use the \dt command to display table list in the suppliers database.
1 2 3 4 5 6 7 8 9 | 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, we have tables created successfully in the suppliers database.
In this tutorial, we have shown you the steps of creating new PostgreSQL tables in Python using psycopg database adapter.