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

 

[Bruno Desthuilliers]

Gilles Ganault a écrit :

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:

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). FWIW, sqlite3 returns the cursor object itself and mysqldb
returns (IIRC) the numbor of rows affected (selected, updated,
whatever). Now I don't know what apsw is, but it's common for libraries
to provide their own wrapping of the db-api. Anyway: it doesn't return a
'row' in any case.

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

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

data = {}

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

files are their own iterators, so you could just keep the file opened
and iterate over it - might save you some memory if the file is huge.

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

You do understand that if m.group(1) appears more than one time in
data.tsv, only the last value will be kept, do you ?

for (key,value) in data.items():

You don't need the parens around key, value here.

sql = "SELECT COUNT(*) FROM mytable WHERE key='%s'" % key
row=cursor.execute(sql)

The recommended way is to pass the arguments to cursor.execute, ie:

sql = "SELECT COUNT(*) FROM mytable WHERE key=%s" # cf below
cursor.execute(sql, (key,))

NB : checks that for your implementation of the db-api, the placeholder
is %s (this is implementation-specific).

This will do all appropriate preparation of the arguments (quoting etc)
and will protect you from sql injection. Also, you can now extract the
sql=<statement> from the loop.

Also, you may want to rewrite your query as "SELECT COUNT(key) FROM
mytable (etc...)", which (depending on the database engine, the schema
and a couple other considerations) might be a bit faster

#Why not run?
> if not row:

you want:
row = cursor.fetchone()
count = row[0]
if not count:

print "Row doesn't exist : %s" % key
sql = "INSERT INTO mytable (key,value) VALUES ('%s',%u)" %
key,value

same remark as above.

cursor.execute(sql)

connection.close(True)
sys.exit()

If it's the end of your script, this last statement is useless.

HTH

 

[Gilles Ganault]

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

 

[Gabriel Genellina]

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.

 

[Gilles Ganault]

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

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 :

Yes, but it doesn't work with this wrapper (APSW version 3.5.9-r1):


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.

 

[Gerhard Häring]

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

 

[Gerhard Häring]

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

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]

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)________________________________________________________________________

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