[APSW] SELECT COUNT(*) not succesfull?

[Gilles Ganault]

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.

[Gilles Ganault]

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.

[Cousin Stanley]

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

[Gabriel Genellina]

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

[Gilles Ganault]

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.

[Bruno Desthuilliers]

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.

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

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

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

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

[M.-A. Lemburg]

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