sqlite autoincrement of primary key
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 ?
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
tinauser wrote:
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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
tinauser tinau...@libero.it writes:
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.
--
John Bokma j3b
Blog: http://johnbokma.com/Facebook: http://www.facebook.com/j.j.j.bokma
Freelance Perl Python Development: http://castleamber.com/
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
On 2010-11-29, tinauser tinau...@libero.it wrote:
'''
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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
On Mon, 29 Nov 2010 13:19:19 -0500
Mel mwil...@the-wire.com wrote:
tinauser wrote:
'''INSERT INTO foo VALUES (NULL, ?)'''
Does this work in SQLite:
INSERT INTO foo (name) VALUES ('xxx')
That's the standard SQL way.
--
D'Arcy J.M. Cain da...@druid.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
On 2010-11-29, D'Arcy J.M. Cain da...@druid.net wrote:
On Mon, 29 Nov 2010 13:19:19 -0500
Mel mwil...@the-wire.com wrote:
tinauser wrote:
'''INSERT INTO foo VALUES (NULL, ?)'''
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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
On Nov 29, 7:28 pm, Tim Harig user...@ilthio.net wrote:
On 2010-11-29, tinauser tinau...@libero.it wrote:
'''
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.
Thanks all of you for the fast answers!
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
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
Re: sqlite autoincrement of primary key
On Mon, 29 Nov 2010 19:11:18 + (UTC)
Tim Harig user...@ilthio.net wrote:
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.
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.
--
D'Arcy J.M. Cain da...@druid.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
On 2010-11-29, D'Arcy J.M. Cain da...@druid.net wrote:
On Mon, 29 Nov 2010 19:11:18 + (UTC)
Tim Harig user...@ilthio.net wrote:
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.
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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite autoincrement of primary key
On Nov 29, 10:49 pm, D'Arcy J.M. Cain da...@druid.net wrote:
On Mon, 29 Nov 2010 19:11:18 + (UTC)
Tim Harig user...@ilthio.net wrote:
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.
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.
--
D'Arcy J.M. Cain da...@druid.net | Democracy is three
wolveshttp://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
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
--
http://mail.python.org/mailman/listinfo/python-list
