Inserting last created auto_increment value into the table using LAST_INSERT_ID()

Question

I have a table of area, which might have many districts. When creating the table I have set the id of the area to auto increment.

I have tried every solution that I could find on the internet using the LAST_INSERT_ID() and it just doesn't work. I either get an error that LAST_INSERT_ID() is not defined, incorrect syntax or out of sync error.

I am using python and mysql

cur.execute('''
            INSERT IGNORE INTO area1_tb (
            id,
            area1            
            ) VALUES (%s, %s)''',
            (None, area1,))


cur.execute('''INSERT IGNORE INTO district_tb (
            id,
            district1,
            area1_id
            ) VALUES (%s, %s, %s)''',
            (None, district1, SELECT LAST_INSERT_ID(),))

I need the id from the area1_tb to be linked to the area1_id from the district_tb but I keep getting the errors.

Possible duplicate of [How do I get the "id" after INSERT into MySQL database with Python?](https://stackoverflow.com/questions/2548493/how-do-i-get-the-id-after-insert-into-mysql-database-with-python) — MatsLindh, Jul 25 '19 at 20:13
When you ask for help with an error, it is worthwhile to include the exact error message. — Bill Karwin, Jul 26 '19 at 15:00

score 2 · Accepted Answer · answered Jul 25 '19 at 20:38

You're very close. Don't SELECT LAST_INSERT_ID(). Just use its value. It's a function in MySQL's dialect of SQL

Try this:

cur.execute('''
        INSERT IGNORE INTO area1_tb (
        id,
        area1            
        ) VALUES (%s, %s)''',
        (None, area1,))

cur.execute('''INSERT IGNORE INTO district_tb (
        id,
        district1,
        area1_id
        ) VALUES (%s, %s, LAST_INSERT_ID())''',
        (None, district1,))

And, if you want to insert multiple districts in a single area, try storing the id from the area insert in a MySQL variable so you can reuse it:

cur.execute('''
        INSERT IGNORE INTO area1_tb (
        id,
        area1            
        ) VALUES (%s, %s)''',
        (None, area1,))

cur.execute('SET @areaId := LAST_INSERT_ID()', Params=None)

cur.execute('''INSERT IGNORE INTO district_tb (
        id,
        district1,
        area1_id
        ) VALUES (%s, %s, @areaId)''',
        (None, district1,))
cur.execute('''INSERT IGNORE INTO district_tb (
        id,
        district1,
        area1_id
        ) VALUES (%s, %s, @areaId)''',
        (None, district2,))
cur.execute('''INSERT IGNORE INTO district_tb (
        id,
        district1,
        area1_id
        ) VALUES (%s, %s, @areaId)''',
        (None, district3,))

Subsequent inserts overwrite LAST_INSERT_ID() so you need to save it to reuse it.

oh my lord, it worked! Thanks a lot! I have spent days trying to make this work and I was so close all the time. Thanks again! — user288690, Jul 25 '19 at 21:34
Yeah, the trouble with programming SQL stuff is all the mixed languages. It's sometimes hard to keep straight which language any given hunk of text is in. I've never seen an IDE that could tell when a string is a fragment of SQL and check it. — O. Jones, Jul 26 '19 at 19:55

Inserting last created auto_increment value into the table using LAST_INSERT_ID()

1 Answers1