Python Psycopg2 – Getting ID of row just inserted
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:
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’, ‘firstname.lastname@example.org’);
INSERT INTO public.USER(user_name, email) VALUES (‘Ashish Mysterio’, ‘email@example.com’);
INSERT INTO public.USER(user_name, email) VALUES (‘Priyanka Pandey’, ‘firstname.lastname@example.org’);
After executing the above queries, the `user` table looks like this:
Below is the Implementation
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 –
Please Login to comment...