From 302a3b17c5905ad9e1bc9c7c32857729c1dd41aa Mon Sep 17 00:00:00 2001 From: Helmut Grohne Date: Sat, 20 Jul 2013 14:09:30 +0200 Subject: 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. --- webapp.py | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) (limited to 'webapp.py') 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, -- cgit v1.2.3