This article explores the process of creating table in The PostgreSQL database using Python.
Prerequisites:
- psycopg2 module
- sample database
Creating a Table:
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:
In this example we have already created a Database called school. We will be adding tables to it. To do so we created a file called create_table.py and defined a create_table() function as shown below:
Python3
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()
|
This will successfully create the tables :
- student
- grade
- student_grade
- student_detail
To verify so use the below command through the client tool of the same database(ie, school):
\dt
Output: