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
--
http://mail.python.org/mailman/listinfo/python-list
