sqlite autoincrement of primary key

[tinauser]

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 ?

 

[Mel]

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 ?

Have you tried

'''INSERT INTO foo VALUES (NULL, ?)'''

Mel.

 

[John Bokma]

however, if in python i try to execute a script like:

cur.execute(
'''
INSERT INTO 'foo' VALUES (?,?)
'''
,('NULL','yyy'))

,(None, 'yyy'))

Or use VALUES(NULL, ?)

as suggested in another post.

 

[Tim Harig]

'''
INSERT INTO 'foo' VALUES (?,?)
'''
,('NULL','yyy'))
s/'NULL'/None/

I get a datatype mismatch error.

The sqlite module is smart enough to convert between Python types and
Sqlite types. If you pass it 'NULL' it thinks you are passing it a string.
Python uses None in much the same way that databases use NULL, so the
module converts None to 'NULL' and vise versa.

 

[D'Arcy J.M. Cain]

tinauser wrote:
'''INSERT INTO foo VALUES (NULL, ?)'''

Does this work in SQLite:

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

That's the standard SQL way.

 

[Tim Harig]

Does this work in SQLite:

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

That's the standard SQL way.

Yes, it works; but, the OP asked specifically to be able to enter all of
the field values, including the autoincrement field.

 

[tinauser]

The sqlite module is smart enough to convert between Python types and
Sqlite types.  If you pass it 'NULL' it thinks you are passing it a string.
Python uses None in much the same way that databases use NULL, so the
module converts None to 'NULL' and vise versa.

Thanks all of you for the fast answers!

 

[Alan Meyer]

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

 

[D'Arcy J.M. Cain]

Yes, it works; but, the OP asked specifically to be able to enter all of
the field values, including the autoincrement field.

You're right, I missed that. However reading the OP's message I am
still confused. How does removing the field name and adding a
positional NULL or None improve readability. I now wonder if it was
more of an assignment requirement rather than a real one.

 

[Tim Harig]

You're right, I missed that. However reading the OP's message I am
still confused. How does removing the field name and adding a
positional NULL or None improve readability. I now wonder if it was
more of an assignment requirement rather than a real one.

That, I don't know. I would agree that it seems like a mis-guided
approach; but, it is what he asked for.

 

[tinauser]

You're right, I missed that.  However reading the OP's message I am
still confused.  How does removing the field name and adding a
positional NULL or None improve readability.  I now wonder if it was
more of an assignment requirement rather than a real one.

Was not an assignment.
I created a general table class and several specific tables.
The application I'm going to write is going to be used by scientist
that might not have a strong computational background, but they might
be willing of adding some new tables. Their code knowledge might be so
low that they might not go further a copy-paste.
The way I defined the table, they just have to create lists indicating
the name of the columns, their content and eventually some initial
values for instance.
self.colNames= [ 'id' , 'name' , 'surname',
'age' ]
self.colType = [INTEGER, TEXT , TEXT , INTEGER]
self.init.append([ None , 'john' , 'Lennon' ,
'51' ])
i.e. the code has to resable the structure of the table and be easily
copy and paste (this last thing makes this system better than,
instead, using python dictionary...also because the future user might
not be familiar at all with dictionary).I might think of doing an
automatic substitution of the primarykey with None, that is not a
problem

Anyhow, as said, thanks for all the answers, you solved my question

Regards