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]

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
(e-mail address removed) | http://gerold.bcom.at | http://sw3.at
Ehrliche, herzliche Begeisterung ist einer der
wirksamsten Erfolgsfaktoren. Dale Carnegie

 

[Fredrik Lundh]

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]

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 ??
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

 

[Dennis Lee Bieber]

what does

import sqlite
print sqlite.paramstyle
print sqlite.version

print on your machine ?

Can't speak for the original poster, but for me...
ImportError: No module named sqlite
'qmark'

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Fredrik Lundh]

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):
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]

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

 

[Dennis Lee Bieber]

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.

Poor choice of example -- MySQLdb is one that explicitly handles a
scalar "args" when doing the data conversion/escaping, and then relies
upon normal Python string formatting to create the final SQL... As I
recall, it even works with dictionary "args" and %(id)s substitution...


/cursor.py/
def execute(self, query, args=None):

<snip doc string>

try:
if args is None:
r = self._query(query)
else:
r = self._query(query % self.connection.literal(args))
except TypeError, m:

<snip>


/connection.py/
def literal(self, o):
"""

If o is a single object, returns an SQL literal as a string.
If o is a non-string sequence, the items of the sequence are
converted and returned as a sequence.

Non-standard. For internal use; do not use this in your
applications.

"""
return self.escape(o, self.converter)
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[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.

 

[Dennis Lee Bieber]

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)

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.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[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]

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

 

[Dennis Lee Bieber]

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?

Well... Approach 1 is: Don't do that...

A relational database is not supposed to have "multi-values" in a
column.

Normalization would be that you either 1) create many columns (which
requires knowing how many values could be in the list) or 2) create a
separate table of:

ID primary key
Link foreign key (ID of the record in the original table)
Value whatever (ONE value from the list.

Instead of:

32 | data | more | [list, of, stuff]

you create

32 | data | more

AND

1 | 32 | list
2 | 32 | of
3 | 32 | stuff



However, if you trust that no one has booby-trapped the database
contents, you could try:

#this is the item returned by the database interface (with ... replaced)

db_list = "[12345, 76543, 89786, None]"
real_list = eval(db_list)
print real_list [12345, 76543, 89786, None]

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/