Skip to content
Related Articles

Related Articles

How to Define an Auto Increment Primary Key in PostgreSQL using Python?
  • Last Updated : 11 Dec, 2020

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

filter_none

edit
close

play_arrow

link
brightness_4
code

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()
        # Droping 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()

chevron_right


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

filter_none

edit
close

play_arrow

link
brightness_4
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")

chevron_right


Output of employee table after executing above program :

Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.

My Personal Notes arrow_drop_up
Recommended Articles
Page :