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.

2 files changed, 31 insertions, 28 deletions

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__":
diff --git a/webapp.py b/webapp.py
index 18a65be..9883caf 100755
--- a/webapp.py
+++ b/webapp.py
@@ -248,13 +248,13 @@ class Application(object):
 
     def get_details(self, package):
         with self.db.begin() as conn:
-            row = conn.execute("SELECT version, architecture FROM package WHERE package = ?;",
-                               (package,)).fetchone()
+            row = conn.execute("SELECT version, architecture FROM package WHERE package = :package;",
+                               package=package).fetchone()
             if not row:
                 raise NotFound()
             version, architecture = row
-            row = conn.execute("SELECT count(filename), sum(size) FROM content WHERE package = ?;",
-                               (package,)).fetchone()
+            row = conn.execute("SELECT count(filename), sum(size) FROM content WHERE package = :package;",
+                               package=package).fetchone()
             num_files, total_size = row
         if total_size is None:
             total_size = 0
@@ -266,15 +266,15 @@ class Application(object):
 
     def get_dependencies(self, package):
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT required FROM dependency WHERE package = ?;",
-                               (package,))
+            cur = conn.execute("SELECT required FROM dependency WHERE package = :package;",
+                               package=package)
             return set(row[0] for row in fetchiter(cur))
 
     def cached_sharedstats(self, package):
         sharedstats = {}
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT package2, func1, func2, files, size FROM sharing WHERE package1 = ?;",
-                               (package,))
+            cur = conn.execute("SELECT package2, func1, func2, files, size FROM sharing WHERE package1 = :package;",
+                               package=package)
             for package2, func1, func2, files, size in fetchiter(cur):
                 if (func1, func2) not in hash_functions:
                     continue
@@ -304,8 +304,8 @@ class Application(object):
            hash function pairs to hash values.
         """
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT id, filename, size, hash FROM content JOIN hash ON content.id = hash.cid JOIN duplicate ON content.id = duplicate.cid WHERE package = ? AND function = 'sha512' ORDER BY size DESC;",
-                               (package1,))
+            cur = conn.execute("SELECT id, filename, size, hash FROM content JOIN hash ON content.id = hash.cid JOIN duplicate ON content.id = duplicate.cid WHERE package = :package AND function = 'sha512' ORDER BY size DESC;",
+                               package=package1)
             cursize = -1
             files = dict()
             minmatch = 2 if package1 == package2 else 1
@@ -324,8 +324,8 @@ class Application(object):
                 entry = dict(filenames=set((filename,)), size=size, matches={})
                 files[hashvalue] = entry
 
-                cur = conn.execute("SELECT ha.function, ha.hash, hb.function, filename FROM hash AS ha JOIN hash AS hb ON ha.hash = hb.hash JOIN content ON hb.cid = content.id WHERE ha.cid = ? AND package = ?;",
-                                   (cid, package2))
+                cur = conn.execute("SELECT ha.function, ha.hash, hb.function, filename FROM hash AS ha JOIN hash AS hb ON ha.hash = hb.hash JOIN content ON hb.cid = content.id WHERE ha.cid = :cid AND package = :package;",
+                                   cid=cid, package=package2)
                 for func1, hashvalue, func2, filename in fetchiter(cur):
                     entry["matches"].setdefault(filename, {})[func1, func2] = \
                             hashvalue
@@ -349,8 +349,8 @@ class Application(object):
 
     def show_hash(self, function, hashvalue):
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT content.package, content.filename, content.size, hash.function FROM content JOIN hash ON content.id = hash.cid WHERE hash = ?;",
-                               (hashvalue,))
+            cur = conn.execute("SELECT content.package, content.filename, content.size, hash.function FROM content JOIN hash ON content.id = hash.cid WHERE hash = :hashvalue;",
+                               hashvalue=hashvalue)
             entries = [dict(package=package, filename=filename, size=size,
                             function=otherfunc)
                        for package, filename, size, otherfunc in fetchiter(cur)
@@ -363,13 +363,13 @@ class Application(object):
 
     def show_source(self, package):
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT package FROM package WHERE source = ?;",
-                               (package,))
+            cur = conn.execute("SELECT package FROM package WHERE source = :source;",
+                               source=package)
             binpkgs = dict.fromkeys(pkg for pkg, in fetchiter(cur))
             if not binpkgs:
                 raise NotFound
-            cur = conn.execute("SELECT package.package, sharing.package2, sharing.func1, sharing.func2, sharing.files, sharing.size FROM package JOIN sharing ON package.package = sharing.package1 WHERE package.source = ?;",
-                               (package,))
+            cur = conn.execute("SELECT package.package, sharing.package2, sharing.func1, sharing.func2, sharing.files, sharing.size FROM package JOIN sharing ON package.package = sharing.package1 WHERE package.source = :source;",
+                               source=package)
             for binary, otherbin, func1, func2, files, size in fetchiter(cur):
                 entry = dict(package=otherbin,
                              funccomb=function_combination(func1, func2),