pysqlite - simple problem

[rdrink]

I am just getting into pysqlite (with a fair amount of Python and MySQL
experience behind me) and have coded a simple test case to try to get
the hang of things...
yet have run into a 'stock simple' problem...

I can create a database 'test.db', add a table 'foo' (which BTW I
repeatedly DROP on each run) with one INTGER column 'id', and can
insert data into with:
cur.execute("INSERT INTO foo (id) VALUES (200)")
con.commit()
(and fetch back out)
all with no problem. But...

If I try to expand this to:
num = 200
cur.execute("INSERT INTO foo (id) VALUES (?)", num)
I get the error...
Traceback (most recent call last):
File "/home/rdrink/Programming/Python/Inner_Square/sqlite_test.py",
line 46, in ?
cur.execute("INSERT INTO foo (id) VALUES (?)", num)
File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 255, in
execute
self.rs = self.con.db.execute(SQL % parms)
TypeError: not all arguments converted during string formatting
.... which obviously points to a 'typing' problem.
?? but where ??

>From all the docs I have read Python 'int' and sqlite INTEGER should

pass back and forth seemlessly...
And I have even tried to reduce things to simply:
cur.execute("INSERT INTO foo (id) VALUES (?)", 200)
but still raise the same error.

So this has to be something stupidly simple... but for the life of me I
can't see it.

Advice, suggestions, pointers for the noob?

rd

[Gerold Penz]

rdrink schrieb:

num = 200
cur.execute("INSERT INTO foo (id) VALUES (?)", num)

Hi!

``num`` must be an iterable object (tuple, list, ...).

num = (200,)
cur.execute("INSERT INTO foo (id) VALUES (?)", num)

Regards,
Gerold
:-)

--
__________________________________________________ ______________________
Gerold Penz - bcom - Programmierung
ge*********@tirol.utanet.at | http://gerold.bcom.at | http://sw3.at
Ehrliche, herzliche Begeisterung ist einer der
wirksamsten Erfolgsfaktoren. Dale Carnegie

[Fredrik Lundh]

"rdrink" <rd****@artic.eduwrote:

>I am just getting into pysqlite (with a fair amount of Python and MySQL
experience behind me) and have coded a simple test case to try to get
the hang of things...

yet have run into a 'stock simple' problem...

what does

import sqlite
print sqlite.paramstyle
print sqlite.version

print on your machine ?

(afaik, version 1 of the python bindings use paramstyle=pyformat, version
2 uses qmark. maybe you have a version 1 library ?)

</F>

[John Machin]

rdrink wrote:

I am just getting into pysqlite (with a fair amount of Python and MySQL
experience behind me) and have coded a simple test case to try to get
the hang of things...
yet have run into a 'stock simple' problem...

I can create a database 'test.db', add a table 'foo' (which BTW I
repeatedly DROP on each run) with one INTGER column 'id', and can
insert data into with:
cur.execute("INSERT INTO foo (id) VALUES (200)")
con.commit()
(and fetch back out)
all with no problem. But...

If I try to expand this to:
num = 200
cur.execute("INSERT INTO foo (id) VALUES (?)", num)
I get the error...
Traceback (most recent call last):
File "/home/rdrink/Programming/Python/Inner_Square/sqlite_test.py",
line 46, in ?
cur.execute("INSERT INTO foo (id) VALUES (?)", num)
File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 255, in
execute
self.rs = self.con.db.execute(SQL % parms)
TypeError: not all arguments converted during string formatting
... which obviously points to a 'typing' problem.
?? but where ??

From all the docs I have read Python 'int' and sqlite INTEGER should

pass back and forth seemlessly...
And I have even tried to reduce things to simply:
cur.execute("INSERT INTO foo (id) VALUES (?)", 200)
but still raise the same error.

So this has to be something stupidly simple... but for the life of me I
can't see it.

With the '?' paramstyle, the 2nd arg to cursor.execute() should be a
*sequence* (typically a tuple) of the values that you are inserting.

Tty this:
cur.execute("INSERT INTO foo (id) VALUES (?)", (num, ))

This is standard Python DBAPI stuff - you would probably get a similar
response from other gadgets e.g. mySQLdb -- IOW it's not specific to
pysqlite.

Advice, suggestions, pointers for the noob?

General advice: Read the docs -- both the gadget-specific docs and the
Python DBAPI spec (found at http://www.python.org/dev/peps/pep-0249/).

HTH,
John

[Fredrik Lundh]

John Machin wrote:

>So this has to be something stupidly simple... but for the life of me I
can't see it.

With the '?' paramstyle, the 2nd arg to cursor.execute() should be a
*sequence* (typically a tuple) of the values that you are inserting.

Tty this:
cur.execute("INSERT INTO foo (id) VALUES (?)", (num, ))

This is standard Python DBAPI stuff - you would probably get a similar
response from other gadgets e.g. mySQLdb -- IOW it's not specific to
pysqlite.

that mistake gives an entirely different error message, at least under 2.2.0
(which is the version shipped with 2.5):

>>import sqlite3
db = sqlite3.connect("foo.db")
cur = db.cursor()
cur.execute("CREATE TABLE foo (id INTEGER)")

<pysqlite2.dbapi2.Cursor object at 0x00B7CEF0>

>>cur.execute("INSERT INTO foo (id) VALUES (?)", 200)

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current
statement uses 1, and there are -1 supplied.

>>cur.execute("INSERT INTO foo (id) VALUES (?)", [200])

<pysqlite2.dbapi2.Cursor object at 0x00B7CEF0>

(not sure "-1 arguments supplied" is any less confusing, though)

</F>

[John Machin]

Fredrik Lundh wrote:

John Machin wrote:

So this has to be something stupidly simple... but for the life of me I
can't see it.

With the '?' paramstyle, the 2nd arg to cursor.execute() should be a
*sequence* (typically a tuple) of the values that you are inserting.

Tty this:
cur.execute("INSERT INTO foo (id) VALUES (?)", (num, ))

This is standard Python DBAPI stuff - you would probably get a similar
response from other gadgets e.g. mySQLdb -- IOW it's not specific to
pysqlite.

that mistake gives an entirely different error message, at least under 2.2.0
(which is the version shipped with 2.5):

You're right. I didn't spot that the OP may be using an antique:

File "/usr/lib/python2.4/site-packages/sqlite/main.py"

So the advice has to be augmented:
1. Update to latest pysqlite2 (which BTW is a later version that that
shipped with Python 2.5, just to add some confusion)
2. Pass values as a sequence

Cheers,
John

[rdrink]

Thanks everyone!
But... RTFM? Ouch. It's not like I don't know what I'm doing :-(

.... rather, that I *am* using the older sqlite module

print sqlite.paramstyle = pyformat
print sqlite.version = 1.0.1

..... which does not support the qmark sytax. (and I fell victim of
someone elses tutorial).

And yes I should prolly move to pysqlite2, but for now I was able to
fix it this way...
num = 200
mess = "INSERT INTO foo (id) VALUES (%s)" % num
cur.execute(mess)

.... don't know why I didn't think of that last (oh wait, Yes I do...
because 'last night' was actually 2am this morning, after working all
day!)

But thanks again to all of you for your help.

[Fredrik Lundh]

rdrink wrote:

And yes I should prolly move to pysqlite2, but for now I was able to
fix it this way...
num = 200
mess = "INSERT INTO foo (id) VALUES (%s)" % num
cur.execute(mess)

... don't know why I didn't think of that last (oh wait, Yes I do...
because 'last night' was actually 2am this morning, after working all
day!)

the "pyformat" parameter style means that you're supposed to use "%s"
instead of "?" for the placeholders:

cur.execute("INSERT INTO foo (id) VALUES (%s)", (num,))

while string formatting works, and is safe for simple cases like this,
it can quickly turn into a performance and security problem. better
avoid it for anything other than command-line tinkering and throw-away
scripts.

(I'm sure this is mentioned in the fine manual, btw ;-)

</F>

[rdrink]

Dennis Lee Bieber wrote:

That is probably the worst way to "fix" the problem -- as in the
future, you may end up trying that method for something that may need to
be quoted or escaped.

cur.execute(template, (arg1,) )

allows the DB-API spec to properly convert the argument to the string
format (quoted or escaped) as needed.

Thank you Dennis, point taken.
I will upgrade to pysqlite2 as soon as possible.

>the "pyformat" parameter style means that you're supposed to use "%s"
instead of "?" for the placeholders:

cur.execute("INSERT INTO foo (id) VALUES (%s)", (num,))

Thanks Fredrick, that seems so obvious now!....

(I'm sure this is mentioned in the fine manual, btw ;-)

.... I guess I have must have missed it ;-)

>while string formatting works, and is safe for simple cases like this,
it can quickly turn into a performance and security problem. better
avoid it for anything other than command-line tinkering and throw-away
scripts.

You are both right about the perils of a non-standard approach, which
could easily break. Fortunately in this case this is a private project,
so no worry there.
-----
And while you are both being so helpful, May I ask anyother stupid
question?...
One of the columns of my table contains a rather large list of numbers
e.g. [12345, 76543, 89786, ... ] sometimes up to 500 entries long.
And when I defined my table I set this column to text.
But the problem with that approach is of course then that it gets
returned as a string (which just happens to look like a list!) and I
can't iter over it. However I can use rsplit(','), with the exception
of the leading and trailing '[' ']', and I could fix that too... but
that's not the point... the real question is: Is there a way to have
python interperate the string "[ a,b,c ]" as a list? (and yes I have be
reading up on typing)...
OR
Is there a better way to store this in sqlite, ala a BLOB or encoded?

Thanks
Robb