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:
Python3
import psycopg2def 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.
Explanation of auto increment primary key
Using python code:
Python3
import psycopg2try: 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 :
