468,253 Members | 1,284 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,253 developers. It's quick & easy.

pysqlite - simple problem

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

Sep 1 '06 #1
8 3206
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
Sep 1 '06 #2
"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>

Sep 1 '06 #3
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

Sep 1 '06 #4
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>

Sep 1 '06 #5

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

Sep 1 '06 #6
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.

Sep 2 '06 #7
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>

Sep 2 '06 #8
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

Sep 3 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Rob Cowie | last post: by
1 post views Thread by DurumDara | last post: by
1 post views Thread by aldonnelley | last post: by
14 posts views Thread by Nader Emami | last post: by
5 posts views Thread by =?ISO-8859-1?Q?Gerhard_H=E4ring?= | last post: by
4 posts views Thread by Tilman Kispersky | last post: by
4 posts views Thread by Astley Le Jasper | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.