From a03daac99a237babcd874748d19fc0f809a1dc60 Mon Sep 17 00:00:00 2001 From: Helmut Grohne Date: Wed, 10 Jul 2013 22:01:13 +0200 Subject: use sqlalchemy paramstyle By using the :name syntax inside sql statements, sqlalchemy will replace the contents with whatever paramstyle the underlying dbapi2 module needs. In case of psycopg2 the paramstyle is not qmark for instance. --- update_sharing.py | 23 +++++++++++++---------- 1 file changed, 13 insertions(+), 10 deletions(-) (limited to 'update_sharing.py') diff --git a/update_sharing.py b/update_sharing.py index 0995114..caca445 100755 --- a/update_sharing.py +++ b/update_sharing.py @@ -5,12 +5,15 @@ import sqlalchemy from dedup.utils import fetchiter, enable_sqlite_foreign_keys def add_values(conn, insert_key, files, size): - rows = conn.execute("UPDATE sharing SET files = files + ?, size = size + ? WHERE package1 = ? AND package2 = ? AND func1 = ? AND func2 = ?;", - (files, size) + insert_key) + params = dict(files=files, size=size, package1=insert_key[0], + package2=insert_key[1], func1=insert_key[2], + func2=insert_key[3]) + rows = conn.execute("UPDATE sharing SET files = files + :files, size = size + :size WHERE package1 = :package1 AND package2 = :package2 AND func1 = :func1 AND func2 = :func2;", + **params) if rows.rowcount > 0: return - conn.execute("INSERT INTO sharing (package1, package2, func1, func2, files, size) VALUES (?, ?, ?, ?, ?, ?);", - insert_key + (files, size)) + conn.execute("INSERT INTO sharing (package1, package2, func1, func2, files, size) VALUES (:package1, :package2, :func1, :func2, :files, :size);", + **params) def compute_pkgdict(rows): pkgdict = dict() @@ -45,17 +48,17 @@ def main(): conn.execute("DELETE FROM duplicate;") readcur = conn.execute("SELECT hash FROM hash GROUP BY hash HAVING count(*) > 1;") for hashvalue, in fetchiter(readcur): - rows = conn.execute("SELECT content.package, content.id, content.filename, content.size, hash.function FROM hash JOIN content ON hash.cid = content.id WHERE hash = ?;", - (hashvalue,)).fetchall() + rows = conn.execute("SELECT content.package, content.id, content.filename, content.size, hash.function FROM hash JOIN content ON hash.cid = content.id WHERE hash = :hashvalue;", + hashvalue=hashvalue).fetchall() print("processing hash %s with %d entries" % (hashvalue, len(rows))) pkgdict = compute_pkgdict(rows) for row in rows: cid = row[1] - already = conn.scalar("SELECT cid FROM duplicate WHERE cid = ?;", - (cid,)) + already = conn.scalar("SELECT cid FROM duplicate WHERE cid = :cid;", + cid=cid) if not already: - conn.execute("INSERT INTO duplicate (cid) VALUES (?);", - (cid,)) + conn.execute("INSERT INTO duplicate (cid) VALUES (:cid);", + cid=cid) process_pkgdict(conn, pkgdict) if __name__ == "__main__": -- cgit v1.2.3