Open In App

Python Psycopg2 – Getting ID of row just inserted

Improve
Improve
Like Article
Like
Save
Share
Report

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’, ‘amit@xyz.com’);

INSERT INTO public.USER(user_name, email) VALUES (‘Ashish Mysterio’, ‘ashish@xyz.com’);

INSERT INTO public.USER(user_name, email) VALUES (‘Priyanka Pandey’, ‘priyanka@xyz.com’);

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

user table (before code run)

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', 'sudha@xyz.com')
    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 –

user table (after code run)



Last Updated : 28 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads