How to get primary_key of last inserted record in database with Python

Question

I'm trying to add some data to database using python. But I'm unable to get the auto increment primary_key of the last inserted record.

I've checked similar questions here and here, but it haven't worked.

My code is as follows:

def insert_vehicles_to_db(vehicle):
    conn = db_connection()
    cur = conn.cursor()    

    if vehicle_not_exists(vehicle, conn, cur):
        try:
            insert_vehicle(vehicle, conn, cur)
        except Exception as e:
            pass    
    else:
        pass
    conn.close()

Then it goes to insert_vehicle function. In that function I want:

to add a new vehicle to the database
to add a new price in vehicle_price table
for previous step I need the last inserted vehicle primary key from vehicles table

The function insert_vehicle is as follows:

def insert_vehicle(vehicle, conn, cur):
    try:
        query = "INSERT INTO vehicles (reference, data, price, reference_url, timestamp) VALUES (%s, %s, %s, %s, %s);"
        cur.execute(query, (vehicle['reference'], "", vehicle['price'], vehicle['reference_url'], datetime.datetime.now()))


        ////////// I tried here vehicle_id = cur.lastrowid, it gives me always 0 //////////


        insert_vehicle_price(vehicle['price'], vehicle_id, conn, cur)
        conn.commit()

    except Exception as e:
        # TODO Handle error
        pass

And insert_vehicle_price looks as follows:

def insert_vehicle_price(price, vehicle_id, conn, cur):
    //// Here I need the correct vehicle_id to be able to insert a new record in `vehicle_price` table
    pass

Any idea how to solve it?

if I were you I've probably finished writing this using SQLAlchemy ORM — Azat Ibrakov
– Azat Ibrakov, Commented Jul 3, 2018 at 11:13
Please run SHOW CREATE TABLE vehicles in the mysql client, and include the result in your question. To confirm that the table actually has an auto-increment PK, and which column is it. — Bill Karwin
– Bill Karwin, Commented Jul 3, 2018 at 13:21
@BillKarwin I get an error ERROR: syntax error at or near "CREATE" LINE 1: SHOW CREATE TABLE vehicles ^ SQL state: 42601 Character: 6 — Boky
– Boky, Commented Jul 3, 2018 at 13:30
Your question is tagged mysql. But that error format indicates you are using PostgreSQL, not MySQL. That could explain why the lastrowid doesn't behave as you think it should. — Bill Karwin
– Bill Karwin, Commented Jul 3, 2018 at 13:35

hondvryer · Accepted Answer · 2018-07-03 10:44:42Z

3

In case your primary_key is the ID then you can use cursor.lastrowid to get the last row ID inserted on the cursor object, or connection.insert_id() to get the ID from the last insert on that connection.

answered Jul 3, 2018 at 10:44

hondvryer

4521 gold badge3 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get primary_key of last inserted record in database with Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related