use sqlalchemy.text

Without using this wrapper the sql statements are not munged by
sqlalchemy. Specifically paramstyle is not translated. For sqlite3 this
did not matter, because it allows the changed paramstyle, but for
postgres it fails without sqlalchemy.text wrappers.

2 files changed, 13 insertions, 13 deletions

diff --git a/update_sharing.py b/update_sharing.py
index 664b627..f8300f6 100755
--- a/update_sharing.py
+++ b/update_sharing.py
@@ -7,11 +7,11 @@ from dedup.utils import fetchiter, enable_sqlite_foreign_keys
 def add_values(conn, insert_key, files, size):
     params = dict(files=files, size=size, pid1=insert_key[0],
                   pid2=insert_key[1], func1=insert_key[2], func2=insert_key[3])
-    rows = conn.execute("UPDATE sharing SET files = files + :files, size = size + :size WHERE pid1 = :pid1 AND pid2 = :pid2 AND func1 = :func1 AND func2 = :func2;",
+    rows = conn.execute(sqlalchemy.text("UPDATE sharing SET files = files + :files, size = size + :size WHERE pid1 = :pid1 AND pid2 = :pid2 AND func1 = :func1 AND func2 = :func2;"),
                         **params)
     if rows.rowcount > 0:
         return
-    conn.execute("INSERT INTO sharing (pid1, pid2, func1, func2, files, size) VALUES (:pid1, :pid2, :func1, :func2, :files, :size);",
+    conn.execute(sqlalchemy.text("INSERT INTO sharing (pid1, pid2, func1, func2, files, size) VALUES (:pid1, :pid2, :func1, :func2, :files, :size);"),
                  **params)
 
 def compute_pkgdict(rows):
@@ -47,7 +47,7 @@ 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.pid, content.id, content.filename, content.size, hash.function FROM hash JOIN content ON hash.cid = content.id WHERE hash = :hashvalue;",
+            rows = conn.execute(sqlalchemy.text("SELECT content.pid, 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)
@@ -56,7 +56,7 @@ def main():
                 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(sqlalchemy.text("INSERT INTO duplicate (cid) VALUES (:cid);"),
                                  cid=cid)
             process_pkgdict(conn, pkgdict)
 
diff --git a/webapp.py b/webapp.py
index 2ed84bb..ea25536 100755
--- a/webapp.py
+++ b/webapp.py
@@ -248,12 +248,12 @@ class Application(object):
 
     def get_details(self, package):
         with self.db.begin() as conn:
-            row = conn.execute("SELECT id, version, architecture FROM package WHERE name = :name;",
+            row = conn.execute(sqlalchemy.text("SELECT id, version, architecture FROM package WHERE name = :name;"),
                                name=package).fetchone()
             if not row:
                 raise NotFound()
             pid, version, architecture = row
-            row = conn.execute("SELECT count(filename), sum(size) FROM content WHERE pid = :pid;",
+            row = conn.execute(sqlalchemy.text("SELECT count(filename), sum(size) FROM content WHERE pid = :pid;"),
                                pid=pid).fetchone()
             num_files, total_size = row
         if total_size is None:
@@ -267,14 +267,14 @@ class Application(object):
 
     def get_dependencies(self, pid):
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT required FROM dependency WHERE pid = :pid;",
+            cur = conn.execute(sqlalchemy.text("SELECT required FROM dependency WHERE pid = :pid;"),
                                pid=pid)
             return set(row[0] for row in fetchiter(cur))
 
     def cached_sharedstats(self, pid):
         sharedstats = {}
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT pid2, package.name, func1, func2, files, size FROM sharing JOIN package ON sharing.pid2 = package.id WHERE pid1 = :pid;",
+            cur = conn.execute(sqlalchemy.text("SELECT pid2, package.name, func1, func2, files, size FROM sharing JOIN package ON sharing.pid2 = package.id WHERE pid1 = :pid;"),
                                pid=pid)
             for pid2, package2, func1, func2, files, size in fetchiter(cur):
                 if (func1, func2) not in hash_functions:
@@ -305,7 +305,7 @@ class Application(object):
            from 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 pid = :pid AND function = 'sha512' ORDER BY size DESC;",
+            cur = conn.execute(sqlalchemy.text("SELECT id, filename, size, hash FROM content JOIN hash ON content.id = hash.cid JOIN duplicate ON content.id = duplicate.cid WHERE pid = :pid AND function = 'sha512' ORDER BY size DESC;"),
                                pid=pid1)
             cursize = -1
             files = dict()
@@ -325,7 +325,7 @@ 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 = :cid AND pid = :pid;",
+                cur = conn.execute(sqlalchemy.text("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 pid = :pid;"),
                                    cid=cid, pid=pid2)
                 for func1, hashvalue, func2, filename in fetchiter(cur):
                     entry["matches"].setdefault(filename, {})[func1, func2] = \
@@ -350,7 +350,7 @@ class Application(object):
 
     def show_hash(self, function, hashvalue):
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT package.name, content.filename, content.size, hash.function FROM content JOIN hash ON hash.cid = content.id JOIN package ON content.pid = package.id WHERE hash = :hashvalue;",
+            cur = conn.execute(sqlalchemy.text("SELECT package.name, content.filename, content.size, hash.function FROM content JOIN hash ON hash.cid = content.id JOIN package ON content.pid = package.id WHERE hash = :hashvalue;"),
                                hashvalue=hashvalue)
             entries = [dict(package=package, filename=filename, size=size,
                             function=otherfunc)
@@ -364,12 +364,12 @@ class Application(object):
 
     def show_source(self, package):
         with self.db.begin() as conn:
-            cur = conn.execute("SELECT name FROM package WHERE source = :source;",
+            cur = conn.execute(sqlalchemy.text("SELECT name 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 p1.name, p2.name, sharing.func1, sharing.func2, sharing.files, sharing.size FROM sharing JOIN package AS p1 ON sharing.pid1 = p1.id JOIN package AS p2 ON sharing.pid2 = p2.id WHERE p1.source = :source;",
+            cur = conn.execute(sqlalchemy.text("SELECT p1.name, p2.name, sharing.func1, sharing.func2, sharing.files, sharing.size FROM sharing JOIN package AS p1 ON sharing.pid1 = p1.id JOIN package AS p2 ON sharing.pid2 = p2.id WHERE p1.source = :source;"),
                                source=package)
             for binary, otherbin, func1, func2, files, size in fetchiter(cur):
                 entry = dict(package=otherbin,