pysqlite - simple problem

[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