469,929 Members | 1,401 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

[APSW] SELECT COUNT(*) not succesfull?

Hello

I'm trying to use the APSW package to access a SQLite database, but
can't find how to check if a row exists. I just to read a
tab-separated file, extract a key/value from each line, run "SELECT
COUNT(*)" to check whether this tuple exists in the SQLite database,
and if not, run an INSERT.

The problem is that "if not row" isn't run:

==========
import apsw

connection=apsw.Connection("test.sqlite")
cursor=connection.cursor()

data = {}

f = open("data.tsv", "r")
textlines = f.readlines()
f.close()

p = re.compile('^(\d+)\t(\d+)$')
for line in textlines:
m = p.search(line)
if m:
data[m.group(1)] = m.group(2)

for (key,value) in data.items():
sql = "SELECT COUNT(*) FROM mytable WHERE key='%s'" % key
row=cursor.execute(sql)

#Why not run?
if not row:
print "Row doesn't exist : %s" % key
sql = "INSERT INTO mytable (key,value) VALUES ('%s',%u)" %
key,value
cursor.execute(sql)

connection.close(True)
sys.exit()
==========

Any idea what's wrong with the above?

Thank you.
Oct 22 '08 #1
8 3475
On Wed, 22 Oct 2008 18:35:35 +0200, Bruno Desthuilliers
<bd*****************@free.quelquepart.frwrote:
>It is - the problem is that cursor.execute doesn't return what you
think... Truth is that according to the db-api specification, the return
value of cursor.execute is not defined (IOW : can be absolutely
anything).
OK, I'll check if I can find how to get the result from a SELECT
COUNT(*) and if not, use a different wrapper. Thanks a lot for the
embedded comments.
Oct 22 '08 #2
....
Now I don't know what apsw is, but it's common for libraries
to provide their own wrapping of the db-api.
....
From the Debian GNU/Linux package manager ....

APSW (Another Python SQLite Wrapper) is an SQLite 3 wrapper
that provides the thinnest layer over SQLite 3 possible.
Everything you can do from the C API to SQLite 3, you can do
from Python. Although APSW's API looks vaguely similar to Python's
DB-API, it is not compliant with that API and instead works the way
SQLite 3 does.
I've never used apsw myself ....
--
Stanley C. Kitching
Human Being
Phoenix, Arizona

Oct 23 '08 #3
En Wed, 22 Oct 2008 20:14:42 -0200, Gilles Ganault <no****@nospam.com>
escribió:
On Wed, 22 Oct 2008 18:35:35 +0200, Bruno Desthuilliers
<bd*****************@free.quelquepart.frwrote:
>It is - the problem is that cursor.execute doesn't return what you
think... Truth is that according to the db-api specification, the return
value of cursor.execute is not defined (IOW : can be absolutely
anything).

OK, I'll check if I can find how to get the result from a SELECT
COUNT(*) and if not, use a different wrapper. Thanks a lot for the
embedded comments.
In case you didn't notice, B.D. already provided the answer you're after -
reread his 3rd paragraph from the end.

--
Gabriel Genellina

Oct 23 '08 #4
On Thu, 23 Oct 2008 00:24:01 -0200, "Gabriel Genellina"
<ga*******@yahoo.com.arwrote:
>In case you didn't notice, B.D. already provided the answer you're after -
reread his 3rd paragraph from the end.
Yes, but it doesn't work with this wrapper (APSW version 3.5.9-r1):
>The recommended way is to pass the arguments to cursor.execute, ie:
I'm getting an error when doing it this way:

=======
isbn = "123"
sql = "SELECT COUNT(*) FROM books WHERE isbn='%s'"

#Incorrect number of bindings supplied. The current statement uses 0
and there are 1 supplied. Current offset is 0
cursor.execute(sql, (isbn,))
=======

I don't know enough about Python and this wrapper to tell why it
triggers an error.
>you want:
row = cursor.fetchone()
count = row[0]
if not count:
This wrapper doesn't seem to support fetchone():

=====
#AttributeError: 'apsw.Cursor' object has no attribute 'fetchone'
row = cursor.fetchone()
=====

This works, though:
========
cursor.execute(sql)
for row in cursor.execute(sql):
#Record not found -Insert
if not row[0]:

========

Thank you.
Oct 23 '08 #5
Gilles Ganault a écrit :
On Thu, 23 Oct 2008 00:24:01 -0200, "Gabriel Genellina"
<ga*******@yahoo.com.arwrote:
>In case you didn't notice, B.D. already provided the answer you're after -
reread his 3rd paragraph from the end.

Yes, but it doesn't work with this wrapper (APSW version 3.5.9-r1):
>>The recommended way is to pass the arguments to cursor.execute, ie:

I'm getting an error when doing it this way:

=======
isbn = "123"
sql = "SELECT COUNT(*) FROM books WHERE isbn='%s'"

#Incorrect number of bindings supplied. The current statement uses 0
and there are 1 supplied. Current offset is 0
cursor.execute(sql, (isbn,))
There was a notice about checking the correct placeholder for your
db-api implementation - it's not necessarily '%s' !-)
=======

I don't know enough about Python and this wrapper to tell why it
triggers an error.
>>you want:
row = cursor.fetchone()
count = row[0]
if not count:

This wrapper doesn't seem to support fetchone():

=====
#AttributeError: 'apsw.Cursor' object has no attribute 'fetchone'
row = cursor.fetchone()
=====

This works, though:
========
cursor.execute(sql)
for row in cursor.execute(sql):
#Record not found -Insert
if not row[0]:
Ok, so I wrongly assumed this apws stuff was db-api compliant, and you
can as well forget everything I wrote. My fault, I should have
double-checked this before answering.
Oct 23 '08 #6
Dennis Lee Bieber wrote:
On Thu, 23 Oct 2008 09:26:54 +0200, Gilles Ganault <no****@nospam.com>
declaimed the following in comp.lang.python:

>Yes, but it doesn't work with this wrapper (APSW version 3.5.9-r1):
APSW is not, so far as I recall, a "DB-API 2" adapter -- it is a
touch more low-level (closer to the raw C-interface). pysqlite2 IS a
DB-API 2 adapter.

For APSW, one will need to read the specific documentation on all
the calls to determine behavior (even if the same person is now
maintaining both APSW and pysqlite2 <G>)
Maintainership of pysqlite or APSW hasn't changed. pysqlite is still
maintained by me and APSW still by Roger Binns.

-- Gerhard

Oct 23 '08 #7
Gilles Ganault wrote:
On Thu, 23 Oct 2008 00:24:01 -0200, "Gabriel Genellina"
<ga*******@yahoo.com.arwrote:
>In case you didn't notice, B.D. already provided the answer you're after -
reread his 3rd paragraph from the end.

Yes, but it doesn't work with this wrapper (APSW version 3.5.9-r1):
>>The recommended way is to pass the arguments to cursor.execute, ie:

I'm getting an error when doing it this way:

=======
isbn = "123"
sql = "SELECT COUNT(*) FROM books WHERE isbn='%s'"

#Incorrect number of bindings supplied. The current statement uses 0
and there are 1 supplied. Current offset is 0
cursor.execute(sql, (isbn,))
=======

I don't know enough about Python and this wrapper to tell why it
triggers an error.
>>you want:
row = cursor.fetchone()
count = row[0]
if not count:

This wrapper doesn't seem to support fetchone():

=====
#AttributeError: 'apsw.Cursor' object has no attribute 'fetchone'
row = cursor.fetchone() [...]
Directly calling next() should probably do the trick with APSW. Its
cursors support the iterator interface and iterators are implemented by
providing __iter__() and next() methods.

-- Gerhard

Oct 23 '08 #8
On 2008-10-23 09:26, Gilles Ganault wrote:
On Thu, 23 Oct 2008 00:24:01 -0200, "Gabriel Genellina"
<ga*******@yahoo.com.arwrote:
>In case you didn't notice, B.D. already provided the answer you're after -
reread his 3rd paragraph from the end.

Yes, but it doesn't work with this wrapper (APSW version 3.5.9-r1):
APSW doesn't implement the DB-API and as a result, you'll run
into all sorts of problem when trying to use DB-API examples
with it.

I'd suggest that you try pysqlite instead which does implement
the DB-API and also works around a couple of gotchas you find
with SQLite when using APSW that have to do with the way SQLite
manages transactions.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Oct 23 2008)
>>Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
__________________________________________________ ______________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
Oct 23 '08 #9

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.