Python Psycopg2 - Getting ID of row just inserted

Python Psycopg2 - Getting ID of row just inserted

In PostgreSQL, when you insert a row into a table that has an auto-incrementing primary key (usually an id column), you often want to retrieve the ID of the newly inserted row. This can be done using the RETURNING clause in your INSERT statement while using Psycopg2, a PostgreSQL adapter for Python.

Here's a basic example to illustrate how you can get the ID of a row just inserted into a PostgreSQL database using Psycopg2.

Step 1: Install Psycopg2

First, ensure you have Psycopg2 installed. If not, you can install it using pip:

pip install psycopg2-binary

Step 2: Establish a Connection and Create a Cursor

You need to establish a connection to your PostgreSQL database and create a cursor object:

import psycopg2

# Connect to your postgres DB
conn = psycopg2.connect("dbname=test user=yourusername password=yourpassword")

# Open a cursor to perform database operations
cur = conn.cursor()

Replace "dbname=test user=yourusername password=yourpassword" with your database name, username, and password.

Step 3: Execute the INSERT Query with RETURNING Clause

When you execute the INSERT statement, include the RETURNING clause to get the ID of the newly inserted row:

# Execute the INSERT query with RETURNING id
cur.execute("INSERT INTO your_table_name (column1, column2) VALUES (%s, %s) RETURNING id", (value1, value2))

# Fetch the id of the newly inserted row
inserted_row_id = cur.fetchone()[0]
print("The ID of the inserted row is:", inserted_row_id)

Replace "your_table_name", "column1", "column2", value1, and value2 with your actual table name and column values.

Step 4: Commit the Transaction and Close the Connection

After executing the query, commit the transaction and close the cursor and connection:

# Commit the transaction
conn.commit()

# Close the cursor and connection
cur.close()
conn.close()

Complete Example

Here's the complete code put together:

import psycopg2

# Connect to your postgres DB
conn = psycopg2.connect("dbname=test user=yourusername password=yourpassword")

# Open a cursor to perform database operations
cur = conn.cursor()

# Execute the INSERT query with RETURNING id
cur.execute("INSERT INTO your_table_name (column1, column2) VALUES (%s, %s) RETURNING id", (value1, value2))

# Fetch the id of the newly inserted row
inserted_row_id = cur.fetchone()[0]
print("The ID of the inserted row is:", inserted_row_id)

# Commit the transaction
conn.commit()

# Close the cursor and connection
cur.close()
conn.close()

This script will insert a new row into the specified table and print the ID of that row.

Notes

  • Ensure that your table has an auto-incrementing primary key, typically named id.
  • Always handle exceptions (like connection errors) and ensure that the database connection is closed properly, even if errors occur. This can be done using try-except blocks and finally blocks or with-statement contexts.
  • This method assumes the use of PostgreSQL. Different databases might have different methods for retrieving the last inserted ID.

More Tags

rxjs5 basic-authentication applicationpoolidentity laravel tcp automationanywhere tf.keras mule square android-hardware

More Programming Guides

Other Guides

More Programming Examples