Re: [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.close()


        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?

f = open("data.tsv", "r") textlines = f.readlines() p = re.compile('^(\d+)\t(\d+)$') for line in textlines:

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

smime.p7s
Description: S/MIME Cryptographic Signature

--
http://mail.python.org/mailman/listinfo/python-list