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:

Python3

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.

Explanation of auto increment primary key

Using python code:

Python3

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 :