How to Define an Auto Increment Primary Key in PostgreSQL using Python?
Prerequisite: PostgreSQL
Python has various database drivers for PostgreSQL. Currently, most used version is psycopg2 because it fully implements the Python DB-API 2.0 specification. The psycopg2 provides many useful features such as client-side and server-side cursors, asynchronous notification and communication, COPY command support, etc.
Installation
psycopg2 can be downloaded like any other module using the following command:
pip install psycopg2
Approach
PostgreSQL's way of creating Primary key with auto increment feature :
A column has to be defined with SERIAL PRIMARY KEY. Here SERIAL is not a true data type, but is simply shorthand notation that tells Postgres to create an auto incremented, unique identifier for the specified column. By simply setting a column as SERIAL with PRIMARY KEY attached, Postgres will handle all the complicated behind-the-scenes work and automatically increment our the specified column with a unique, primary key value for every INSERT.
Database Information
Database name: testdb
Table name: EMPLOYEE
In the EMPLOYEE TABLE, column named EMPLOYEE_ID will be implemented as an auto-incremented Primary key column.
Syntax:
CREATE TABLE <table_name>(
<column1_name> SERIAL NOT NULL PRIMARY KEY,
.
.
);
The implementation of creating a table with such specification is given below:
import psycopg2
def create_table():
conn = None
try:
# connect to the PostgreSQL server
conn = psycopg2.connect(database="testdb", user="postgres",
password="password", host="127.0.0.1", port="5432")
print("Opened database successfully")
# create a cursor
cursor = conn.cursor()
# Dropping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# Creating table as per requirement, let us have EMPLOYEE table
# and in order to have auto increment primary key, EMPLOYEE_ID SERIAL PRIMARY KEY
# is used and it is explained before code
sql = '''CREATE TABLE EMPLOYEE(
EMPLOYEE_ID SERIAL PRIMARY KEY,
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
)'''
cursor.execute(sql)
print("Table created successfully........")
# close communication with the PostgreSQL database server
cursor.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_table()
We can see the table created using pgadmin tool
Now, Insertion needs to done to see if our auto-increment feature works or not. This can be done either directly through pgadmin or using python code.
pgadmin way :
Below is the screenshot that shows execution of insert queries and resultant result-set.
Using python code:
import psycopg2
try:
connection = psycopg2.connect(user="postgres",
password="password",
host="127.0.0.1",
port="5432",
database="testdb")
cursor = connection.cursor()
# As Employee table is having auto incremented primary id column(employee_id), no need to specify about that value here
postgres_insert_query = ''' INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE,SEX,INCOME) VALUES (%s,%s,%s,%s,%s)'''
record_to_insert = ('asd', 'wer', 19, 'f', 5000)
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into Employee table")
except (Exception, psycopg2.Error) as error:
if(connection):
print("Failed to insert record into Employee table", error)
finally:
# closing database connection.
if(connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
Output of employee table after executing above program :
