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
--
http://mail.python.org/mailman/listinfo/python-list
