473,857 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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("IN SERT 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("IN SERT 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("IN SERT INTO foo (id) VALUES (?)", num)
File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 255, in
execute
self.rs = self.con.db.exe cute(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("IN SERT 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 3561
rdrink schrieb:
num = 200
cur.execute("IN SERT INTO foo (id) VALUES (?)", num)
Hi!

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

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

Regards,
Gerold
:-)

--
_______________ _______________ _______________ _______________ ____________
Gerold Penz - bcom - Programmierung
ge*********@tir ol.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.e duwrote:
>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.paramsty le
print sqlite.version

print on your machine ?

(afaik, version 1 of the python bindings use paramstyle=pyfo rmat, 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("IN SERT 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("IN SERT 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("IN SERT INTO foo (id) VALUES (?)", num)
File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 255, in
execute
self.rs = self.con.db.exe cute(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("IN SERT 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("IN SERT 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("IN SERT 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.dbap i2.Cursor object at 0x00B7CEF0>
>>cur.execute(" INSERT INTO foo (id) VALUES (?)", 200)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.Program mingError: Incorrect number of bindings supplied. The current
statement uses 1, and there are -1 supplied.
>>cur.execute(" INSERT INTO foo (id) VALUES (?)", [200])
<pysqlite2.dbap i2.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("IN SERT 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.paramsty le = 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(mes s)

.... 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(mes s)

... 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("IN SERT 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(tem plate, (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("IN SERT 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
4407
by: Rob Cowie | last post by:
Hi all, I'm having difficulty installing pysqlite 2.1.3 on Mac OS X 10.4.4 There are some notes on the pysqlite wiki regarding modification of the setup.py script and I've followed them to no avail. Build and install appear to go smoothly but attempting to run the tests from the python interpreter fails. Likewise any attempt to utilise pysqlite2 in a python script fails.
1
1292
by: DurumDara | last post by:
Hi ! I have this code in my program. Before this I use APSW, but that project's connection object doesn't have close method... .... crs.execute(*'''create table files (*f_id integer not null primary key, f_name varchar(255), f_size long, f_attr integer, f_crtime varchar(20), f_mdtime varchar(20), f_hash long )*''') ... crs=connection.cursor()
1
1531
by: aldonnelley | last post by:
Hi there. I'm a long-time lurker and (I think) first time poster. Only relatively new to python, and I'm trying to get pysqlite to work with binary data, and having a tough time of it. I want to set up a table with: - a URL, - some filenames related to that URL, - and some simple generated HTML.
1
2281
by: Thomas | last post by:
Hi there! Installing TurboGears out-of-the-box (egg-based) on Windows doesn't work because I can't compile the extensions needed for the required pysqlite (also egg- based): Installed c:\programme\python24\lib\site-packages\turbogears-1.0.1- py2.4.egg Processing dependencies for TurboGears>=1.0
14
7165
by: Nader Emami | last post by:
I have installed "TurboGears" and I would install 'pysqlite' also. I am a user on a Linux machine. If I try to install the 'pysqlite' with 'easy_install' tool I get the next error message. The error message is longer than what I send here. % easy_install pysqlite Searching for pysqlite Reading http://cheeseshop.python.org/pypi/pysqlite/ Reading http://pysqlite.org/
5
2336
by: =?ISO-8859-1?Q?Gerhard_H=E4ring?= | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 pysqlite 2.5.0 released ======================= I'm pleased to announce the availability of pysqlite 2.5.0. This is a release with major new features. Go to http://pysqlite.org/ for downloads, online documentation and
4
3289
by: Tilman Kispersky | last post by:
I am trying to install sqlite for use with python on cygwin. I have installed the sqlite packages from cygwin (that is libsqlite3-devel and libsqlite3_0). When attempting to easy_install pysqlite I get: $ easy_install pysqlite Searching for pysqlite Reading http://pypi.python.org/simple/pysqlite/ Reading http://pysqlite.org/ Reading http://initd.org/pub/software/pysqlite/releases/2.3/2.3.3/ Reading http://pysqlite.sourceforge.net/
15
14835
by: Kurda Yon | last post by:
Hi, I try to "build" and "install" pysqlite? After I type "python setup.py build" I get a lot of error messages? The first error is "src/ connection.h:33:21: error: sqlite3.h: No such file or directory". So, I assume that the absence of the "sqlite3.h" is the origin of the problem. I found on the web, that this file should be either in "/usr/local/ include" or in "/usr/local/lib". I check this directories and I really
4
4273
by: Astley Le Jasper | last post by:
I've been getting errors recently when using pysqlite. I've declared the table columns as real numbers to 2 decimal places (I'm dealing with money), but when doing division on two numbers that happen to have no decimal fractions, the results through pysqlite are coming through as integers. The funny thing is that when looking at the database using SQLite Manager or SQLite Pro the results there are displayed correctly. As a temporary fix...
0
9767
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11082
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10394
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9549
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7939
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5967
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4592
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4190
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3215
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.