Python PostgreSQL Create Table

FILE: create_table.py

import psycopg2
conn = psycopg2.connect(database = "kwikl3arn", user = "postgres", password = "post123", host = "localhost", port = "5432")
conn.autocommit = True
print ("Opened database successfully")

cur = conn.cursor()

cur.execute('CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))')

print ("created table successfully")
conn.close()

RUN:

C:\Users\Your Name>python create_table.py

Opened database successfully
created table successfully

Show list of tables in current database

 FILE: table_list.py

import psycopg2
conn = psycopg2.connect(database = "kwikl3arn", user = "postgres", password = "post123", host = "localhost", port = "5432")
print ("Opened database successfully")

cur = conn.cursor()

cur.execute("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'")

rows = cur.fetchall()
for row in rows:
   print (row[0])
   
conn.close()

RUN:

C:\Users\Your Name>python table_list.py

Opened database successfully
customers

ALTER TABLE

If the table already exists, use the ALTER TABLE keyword

• When creating a table, you should also create a column with a unique key for each record.
• This can be done by defining a PRIMARY KEY.
• We use the statement " SERIAL PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.

 FILE: alter_table.py

import psycopg2
conn = psycopg2.connect(database = "kwikl3arn", user = "postgres", password = "post123", host = "localhost", port = "5432")
conn.autocommit = True
print ("Opened database successfully")

cur = conn.cursor()

cur.execute("ALTER TABLE customers ADD COLUMN id SERIAL  PRIMARY KEY ")

conn.close()

RUN:

C:\Users\Your Name>python alter_table.py

Opened database successfully