Login

get next mysql autoincrement value

Author:
bernie2004
Posted:
April 4, 2009
Language:
SQL
Version:
Not specified
Score:
1 (after 3 ratings)

i use this to get the pk of a record before creation, in my scenario to name an uploaded image: 

def UPLOADTO( i,n ):
    if not i.id:
        id = get_nextautoincrement( i.__class__ )
    else:
        id = i.id
    return str(id)+'.jpg'

1
2
3
4
5
6
7
8
    def get_nextautoincrement( mymodel ):
        from django.db import connection
        cursor = connection.cursor()
        cursor.execute( "SELECT Auto_increment FROM information_schema.tables WHERE table_name='%s';" % \
                        mymodel._meta.db_table)
        row = cursor.fetchone()
        cursor.close()
        return row[0]

More like this

  1. create_template_postgis-ubuntu_lucid by clawlor 14 years, 3 months ago
  2. PostgreSQL fulltext with language translations by diverman 14 years, 4 months ago
  3. Drop all tables in MySQL database by mpasternacki 14 years, 10 months ago
  4. grep and delete sqlite tables by kifkif 15 years ago
  5. Substitute hyphens with spaces to enale URLs to reference to multi-word tags by miernik 15 years, 1 month ago

Comments

ranedk (on April 4, 2009):

I think this might have some problems if there are multiple requests at the same time. You will get the next increment id, but before you do an insert, another requests inserts the data.

#

ludvig.ericson (on April 12, 2009):

MySQL can't do this. The only sensible thing to do here is something like PostgreSQL's series, where you have to reserve the next item to get its value (or well, it's how things are done.)

This code, as the previous poster says, is dangerous because it's up for race.

Beyond that, it doesn't use the database's own quoting mechanisms, so very exotic cases might end up making this code fail.

Further, it doesn't really handle the case where there is no auto_increment set at all. It'll result in an IndexError on line 8.

So, I'm sorry, but I think this needs to be reworked on a fundamental level.

#

Please login first before commenting.