[APSW] SELECT COUNT(*) not succesfull?

[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