Create a 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().
The create_tables() function creates four tables in the suppliers database: vendors, parts, vendor_parts, and part_drawings.
Python3
#!/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() |
PostgreSQL – Create Tables in Python
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:
- First, construct CREATE TABLE statements.
- Next, connect to the PostgreSQL database by calling the connect() function. The connect() function returns a connection object.
- Then, create a cursor object by calling the cursor() method of the connection object.
- After that, execute the CREATE TABLE by calling the execute() method of the cursor object.
- Finally, close the communication with the PostgreSQL database server by calling the close() methods of the cursor and connection objects.