Issue 395 - Filter hits statistics backend

sitescripts/filterhits/db.py

Index: sitescripts/filterhits/db.py
diff --git a/sitescripts/filterhits/db.py b/sitescripts/filterhits/db.py
index f09cd500acb559ba696eeb22df860daf912bf865..91264fc8c1c3565c9003e997c56783279cafa74e 100644
--- a/sitescripts/filterhits/db.py
+++ b/sitescripts/filterhits/db.py
@@ -1,7 +1,7 @@
 # coding: utf-8
 
 # This file is part of the Adblock Plus web scripts,
-# Copyright (C) 2006-2014 Eyeo GmbH
+# Copyright (C) 2006-2015 Eyeo GmbH
 #
 # Adblock Plus is free software: you can redistribute it and/or modify
 # it under the terms of the GNU General Public License version 3 as
@@ -15,71 +15,55 @@
 # You should have received a copy of the GNU General Public License
 # along with Adblock Plus.  If not, see <http://www.gnu.org/licenses/>.
 
-import MySQLdb
-
-db = None
+import itertools, MySQLdb
 
 def connect(user, password, database):
-  global db
-  if not db:
-    db = MySQLdb.connect(
-      user=user,
-      passwd=password,
-      db=database,
-      use_unicode=True, charset="utf8"
-    )
-  return db
-
-def disconnect():
-  global db
-  if db:
-    db.close()
-    db = None
-
-def escape(s):
-  return MySQLdb.escape_string(s)
+  return MySQLdb.connect(
+    user=user,
+    passwd=password,
+    db=database,
+    use_unicode=True, charset="utf8"
+  )
 
-def query(sql, dict_result=False):
+def query(db, sql, *params, **kwargs):
   """
-   Executes the query given by the provided SQL and returns the results.
-   If dict_result keyword argument is provided + True the results will be
-   returned as a tuple of dictionaries, otherwise a tuple of tuples.
+  Executes the query given by the provided SQL and returns the results.
+  If dict_result keyword argument is provided + True the results will be
+  returned as a tuple of dictionaries, otherwise a tuple of tuples.
   """
-  global db
+  dict_result=kwargs.pop('dict_result', False)
+
   try:
     if dict_result:
       cursor = db.cursor(MySQLdb.cursors.DictCursor)
     else:
       cursor = db.cursor()
-    cursor.execute(sql)
+    cursor.execute(sql, params)
     results = cursor.fetchall()
   finally:
     if cursor:
       cursor.close()
   return results
 
-def write(sql):
+def write(db, queries):
   """
-    This writes a given SQL string or iterator of SQL strings to the database.
-    All given SQL will be run as one transaction and rolled back on error.
+  This writes a given SQL string or iterator of tuples containing SQL

[Sebastian Noack, 2015/02/17 14:59:17]

Iterators (in Python) are objects that implement the iterator protocol, i.e. the
next() method. When using for-loops, Python internally calls iter(obj) to create
an iterator, however the object passed to this function isn't an iterator
itself, it's merely an iterable object.

[kzar, 2015/02/24 18:05:11]

Done.

+  strings and any required parameters to the database. All queries will
+  be run as one transaction and rolled back on error.
   """
-  global db
-
-  if isinstance(sql, str):
-    sql = [sql]
+  if isinstance(queries, str):
+    queries = ((queries,),)
 
   try:
-    # Commit the changes
     cursor = db.cursor()
-    for query in sql:
-      if query:
-        [cursor.execute(s) for s in query.split(";") if s]
-    db.commit()
+    try:
+      for query in queries:
+        sql, params = query[0], query[1:]

[Sebastian Noack, 2015/02/17 14:59:17]

I'd prefer to use a two-dimensional tuple here and everywhere else like (sql,
params) rather than prepanding the sql to the params. This makes it easier to
understand the data structure, is more efficient, and reduces complexity.

[kzar, 2015/02/24 18:05:11]

I like doing it this way for db.query and db.write personally as it reduces the
boilerplate when using the functions with zero or one parameters. I originally
did it in a way similar to how you suggested but found lots of calls where I was
passing empty or 1 item tuples - it seemed kind of ugly.

[Sebastian Noack, 2015/02/26 16:39:25]

I don't agree. So I am leaving it up to Wladimir.

+        cursor.execute(sql, params)
+      db.commit()
+    finally:
+      cursor.close()
   except MySQLdb.Error:
-    # On error roll them back
     if db:
       db.rollback()
     raise
-  finally:
-    if cursor:
-      cursor.close()