Python Psycopg2 - Getting ID of row just inserted

Last Updated : 28 Mar, 2022

In this article, we are going to see how to get the ID of the row just inserted using pyscopg2 in Python

Establishing a connection to the PostgreSQL server

In order to establish a connection to the PostgreSQL server, we will make use of the pscopg2 library in python. You can install psycopg2 using the following command:

pip install psycopg2

After installing the library, the following code can be used to create a connection to the database server:

Python3

import psycopg2


def get_connection():
    try:
        return psycopg2.connect(
            database="postgres",
            user="postgres",
            password="password",
            host="127.0.0.1",
            port=5432,
        )
    except:
        return False


conn = get_connection()

if conn:
    print("Connection to the PostgreSQL established successfully.")
else:
    print("Connection to the PostgreSQL encountered and error.")

Running the above code will produce the following result if the database credentials provided is correct and the connection is successfully established:

Connection to the PostgreSQL established successfully.

Creating Table for demonstration

We will create a demo table `user` for the understanding of this article. You can use the below query to generate the sample `user` table.

CREATE TABLE public.user (

user_id SERIAL PRIMARY KEY,

user_name VARCHAR(50) NOT NULL,

email VARCHAR(50) NOT NULL

);

INSERT INTO public.USER(user_name, email) VALUES ('Amit Pathak', '[email protected]');

INSERT INTO public.USER(user_name, email) VALUES ('Ashish Mysterio', '[email protected]');

INSERT INTO public.USER(user_name, email) VALUES ('Priyanka Pandey', '[email protected]');

After executing the above queries, the `user` table looks like this:

Below is the Implementation

Python3

# This program uses fetchone()
# to get the row ID of the latest
# entry in the table

import psycopg2

def get_connection():
    try:
        return psycopg2.connect(
            database="postgres",
            user="postgres",
            password="password",
            host="127.0.0.1",
            port=5432,
        )
    except:
        return False

# GET THE CONNECTION OBJECT
conn = get_connection()

# CREATE A CURSOR USING THE CONNECTION OBJECT
curr = conn.cursor()

# EXECUTE THE SQL QUERY
curr.execute('''
    INSERT INTO public.USER(user_name, email)
    VALUES ('Sudha Tiwari', '[email protected]')
    RETURNING user_id;
''')

# FETCH THE LATEST USER ID USING THE CURSOR
data = curr.fetchone()
print("User ID of latest entry:", data[0])

# CLOSE THE CONNECTION
conn.close()

Output:

User ID of latest entry: 4

In the above example, we first created a psycopg2 connection object, and using this object, we created a cursor to commit our query. We already have 3 entries in the table with `user_id` as 1, 2, and 3 respectively. For any new entries in the table, the `user_id` field will be incremented since it is an auto-increment field by default. So, the new record should get a `user_id` as 4. This is what we can see from the output. The query uses an additional RETURNING clause which returns the desired column value working just like any callback. We then use the fetchone() method to fetch the response of the latest query executed which gives the required row ID. The `user` table now has the following records -