sqlite autoincrement of primary key

[Alan Meyer]

On 11/29/2010 1:12 PM, tinauser wrote:
> Dear List
> I'm writing an application that has to create and populate an SQLite
> database.
> I'm doing pretty well, but now I'm facing a problem I can not solve.
>
> I create a table with a primary key autoincrement, something like
>
> sqlcmd="CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, name
> TEXT)"
> cur.execute(sqlcmd)
>
> Now comes the time of populating the database.
> I perfectly know that if I do something like:
>
> sqlcmd="INSERT INTO foo (name) VALUES (?))"
> cur.execute(sqlcmd, ('xxx',))
> The table will automatically insert the value of id.
>
>
> However, for readibility problem, I need to use the sqlite insert
> command giving all the entries. I want, however, to let sqlite to
> handle the primary key.
> Normally, the sqlite command that works would be
>
> INSERT INTO 'foo' VALUES (NULL, 'yyy' )
>
> however, if in python i try to execute a script like:
>
> cur.execute(
> '''
> INSERT INTO 'foo' VALUES (?,?)
> '''
> ,('NULL','yyy'))
>
> I get a datatype mismatch error.
>
> Has anyone a workaround ?

There are two red flags popping up for me here.

The first is your switch from:

    "INSERT INTO foo ..."
to
    "INSERT INTO 'foo' ..."

I don't know sqllite, however, quotes around the foo is not standard SQL 
and should cause an error.  "datatype mismatch" is not exactly the 
message I'd expect, but it could be appropriate.

The second red flag is your desire to increase readability by inserting 
something into an auto-increment field.  That might just confuse me if I 
were reading it and knew that NULL (or None) is an invalid and 
inappropriate value for that column.  To me at least, readability is 
reduced by that, not increased.  I'm a little surprised that sqllite 
would accept it no matter how you did it.

You could do something like this:

    INSERT INTO foo (name) VALUES ('whatever')

as another poster suggested.  That seems to me more readable than 
leaving out the column name list but including an auto-increment field 
in the values list.  It gives more, and more valid, information to the 
programmer who reads your code.

     Alan