Skip to content
Related Articles

Related Articles

Improve Article

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.

 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. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

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

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 :




My Personal Notes arrow_drop_up
Recommended Articles
Page :