[APSW] SELECT COUNT(*) not succesfull?

[Tino Wildenhain]

Gilles Ganault wrote:
> 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?

Apart from the other comments, what is obviously wrong is
the way you aliased the variables into SQL.
Please read on parametrized queries, e.g. the following
should work:

sql = "SELECT COUNT(*) FROM mytable WHERE key=%s"

cursor.execute(sql,(key,))

(see also SQL injection)

Regards
Tino
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20081023/95e2e7a8/attachment-0001.bin>