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

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.

Solution

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.