<commit_before>from __future__ import with_statement

import base64, sbd # only needed for image processing, move to other module>?
import toolkit, config, sys
import article, sources, user
import re, collections, time
#from oset import OrderedSet # for listeners, replace with proper orderedset whenever python gets it
OrderedSet = set # OrderedSet gaat mis bij pickle, dan maar geen order op de listeners...?
import table3
import threading
import cPickle
from cStringIO import StringIO
import functools
from contextlib import contextmanager


_encoding = {
    0 : 'utf-8',
    1 : 'UTF-7',
    2 : 'ascii',
    3 : 'latin-1',
}

_MAXTEXTCHARS = 8000

class SQLException(Exception):
    def __init__(self, sql=None, exception=None):
        self.sql = sql
        self.exception = exception
    def __str__(self):
        return"SQLException: Error on executing %r: %s" % (self.sql, self.exception)

def reportDB():
    import MySQLdb
    conf = config.Configuration('app', 'eno=hoty', 'localhost', 'report', MySQLdb)
    db = amcatDB(conf)
    db.conn.select_db('report')
    db.conn.autocommit(False)
    return db


class amcatDB(object):
    """
    Wrapper around a connection to the anoko SQL Server database with a number
    of general and specialized methods to facilitate easy retrieval
    and storage of data
    """

    def __init__(self, configuration=None, auto_commit=0, profile=False, **configargs):
        """
        Initialise the connection to the anoko (SQL Server) database using
        either the given configuration object or config.default
        """
        self.conn = self.connect(configuration, auto_commit, **configargs)
        self.init(profile)
        

    def connect(self, configuration=None, auto_commit=0, **configargs):
        if not configuration: configuration=config.default(**configargs)
        self.dbType = configuration.drivername    
        self.mysql = False #????
        if self.dbType == "MySQLdb":
            import MySQLdb.converters
            conv_dict = MySQLdb.converters.conversions
            conv_dict[0] = float
            conv_dict[246] = float
            conn = configuration.connect(conv=conv_dict)
            conn.select_db('report')
            conn.autocommit(False)
        else:
            conn = configuration.connect()
        self.database = configuration.database
        return conn

    def init(self, profile=False):
        self.DB_LOCK = threading.Lock()        
        self._articlecache = {}
        
        # should be function(string SQL): None or string.
        # Returning string will cause SQL to be changed
        self.beforeQueryListeners = OrderedSet()
        
        # should be function(string SQL, double time, list-of-lists data): None
        self.afterQueryListeners = OrderedSet()

        if profile:
            self.profiler = ProfilingAfterQueryListener()
            self.afterQueryListeners.add(self.profiler)


    ################ picklability #####################
    def __getstate__(self):
        if 'DB_LOCK' in self.__dict__ and self.DB_LOCK:
            self.DB_LOCK.acquire()
        d = dict(self.__dict__.items())
        for delprop in 'DB_LOCK', 'conn':
            if delprop in d: del d[delprop]
        if 'DB_LOCK' in self.__dict__ and self.DB_LOCK:
            self.DB_LOCK.release()
        return d
    def __setstate__(self, d):
        self.__dict__ = d
        self.DB_LOCK = threading.Lock()
      
    def quote(self, value):
        return "'%s'" % str(value).replace("'", "''")   

    ############### cursor acquisition ###############
    
    @contextmanager
    def cursor(self):
        c = None
        try:
            c = self._getCursor()
            yield c
        finally:
            if c is not None:
                self._releaseCursor(c)
        
    def _getCursor(self):
        if self.conn is None:
            raise Exception("Cannot query without database connection")
        if not self.DB_LOCK.acquire(False):
            raise Exception("Cannot lock database, try again later")
        return self.conn.cursor()
    def _releaseCursor(self, cursor):
        if cursor is not None: 
            try:
                cursor.close()
            except:
                pass
        if self.DB_LOCK.locked():
            self.DB_LOCK.release()
        
    def commit(self):
        self.conn.commit()
    def rollback(self):
        self.conn.rollback()

    def queryDict(self, sql, **kargs):
        res, colnames = self.doQuery(sql, colnames=True, **kargs)
        for row in res:
            yield dict(zip(colnames, row))


    ############## Main query logic #####################
    
    def doQueryOnCursor(self, sql, cursor):
        sql  = self.fireBeforeQuery(sql)
        try:
            cursor.execute(sql)
            return cursor
        except Exception, e:
            raise SQLException(sql, e)
            
    def doQuery(self, sql, colnames = False, select=None):
        """
        Execute the query sql on the database and return the result.
        If cursor is given, use that cursor and return the cursor instead
        Otherwise, pre- and postprocess around a call with a new cursor
        """
        if type(sql) == unicode: sql = sql.encode('latin-1', 'replace')
        if select is None:
            select=sql.lower().strip().startswith("select")
        c = None
        t = time.time()
        with self.cursor() as c:
            self.doQueryOnCursor(sql, c)
            try:
                res = c.fetchall() if select else None
            except Exception, e:
                raise SQLException(sql, e)
            self.fireAfterQuery(sql, time.time() - t, res)
            if select and colnames:
                info = c.description
                colnames = [entry[0] for entry in info]
                return res, colnames            
            return res

    def doCall(self, proc, params):
        """
        calls the procedure with the given params (tuple). Returns
        (paramvalues, result), where paramvalues is the list of
        params with output params updates, while result is the
        result of c.fetchall() after the call
        """
        c = self.cursor()
        values = c.callproc(proc, params)
        res = c.fetchall()
        c.close()
        return values, res

        
    def fireBeforeQuery(self, sql):
        for func in self.beforeQueryListeners:
            s = func(sql)
            if s: sql = s
        return sql
    def fireAfterQuery(self, sql, time, results):
        for func in self.afterQueryListeners:
            func(sql, time, results)

    def getProfiler(self):<commit_msg>Added doSafeQuery to dbtoolkit<commit_after>from __future__ import with_statement

import base64, sbd # only needed for image processing, move to other module>?
import toolkit, config, sys
import article, sources, user
import re, collections, time
#from oset import OrderedSet # for listeners, replace with proper orderedset whenever python gets it
OrderedSet = set # OrderedSet gaat mis bij pickle, dan maar geen order op de listeners...?
import table3
import threading
import cPickle
from cStringIO import StringIO
import functools
from contextlib import contextmanager


_encoding = {
    0 : 'utf-8',
    1 : 'UTF-7',
    2 : 'ascii',
    3 : 'latin-1',
}

_MAXTEXTCHARS = 8000

class SQLException(Exception):
    def __init__(self, sql=None, exception=None):
        self.sql = sql
        self.exception = exception
    def __str__(self):
        return"SQLException: Error on executing %r: %s" % (self.sql, self.exception)

def reportDB():
    import MySQLdb
    conf = config.Configuration('app', 'eno=hoty', 'localhost', 'report', MySQLdb)
    db = amcatDB(conf)
    db.conn.select_db('report')
    db.conn.autocommit(False)
    return db


class amcatDB(object):
    """
    Wrapper around a connection to the anoko SQL Server database with a number
    of general and specialized methods to facilitate easy retrieval
    and storage of data
    """

    def __init__(self, configuration=None, auto_commit=0, profile=False, **configargs):
        """
        Initialise the connection to the anoko (SQL Server) database using
        either the given configuration object or config.default
        """
        self.conn = self.connect(configuration, auto_commit, **configargs)
        self.init(profile)
        

    def connect(self, configuration=None, auto_commit=0, **configargs):
        if not configuration: configuration=config.default(**configargs)
        self.dbType = configuration.drivername    
        self.mysql = False #????
        if self.dbType == "MySQLdb":
            import MySQLdb.converters
            conv_dict = MySQLdb.converters.conversions
            conv_dict[0] = float
            conv_dict[246] = float
            conn = configuration.connect(conv=conv_dict)
            conn.select_db('report')
            conn.autocommit(False)
        else:
            conn = configuration.connect()
        self.database = configuration.database
        return conn

    def init(self, profile=False):
        self.DB_LOCK = threading.Lock()        
        self._articlecache = {}
        
        # should be function(string SQL): None or string.
        # Returning string will cause SQL to be changed
        self.beforeQueryListeners = OrderedSet()
        
        # should be function(string SQL, double time, list-of-lists data): None
        self.afterQueryListeners = OrderedSet()

        if profile:
            self.profiler = ProfilingAfterQueryListener()
            self.afterQueryListeners.add(self.profiler)


    ################ picklability #####################
    def __getstate__(self):
        if 'DB_LOCK' in self.__dict__ and self.DB_LOCK:
            self.DB_LOCK.acquire()
        d = dict(self.__dict__.items())
        for delprop in 'DB_LOCK', 'conn':
            if delprop in d: del d[delprop]
        if 'DB_LOCK' in self.__dict__ and self.DB_LOCK:
            self.DB_LOCK.release()
        return d
    def __setstate__(self, d):
        self.__dict__ = d
        self.DB_LOCK = threading.Lock()
      
    def quote(self, value):
        return "'%s'" % str(value).replace("'", "''")   

    ############### cursor acquisition ###############
    
    @contextmanager
    def cursor(self):
        c = None
        try:
            c = self._getCursor()
            yield c
        finally:
            if c is not None:
                self._releaseCursor(c)
        
    def _getCursor(self):
        if self.conn is None:
            raise Exception("Cannot query without database connection")
        if not self.DB_LOCK.acquire(False):
            raise Exception("Cannot lock database, try again later")
        return self.conn.cursor()
    def _releaseCursor(self, cursor):
        if cursor is not None: 
            try:
                cursor.close()
            except:
                pass
        if self.DB_LOCK.locked():
            self.DB_LOCK.release()
        
    def commit(self):
        self.conn.commit()
    def rollback(self):
        self.conn.rollback()

    def queryDict(self, sql, **kargs):
        res, colnames = self.doQuery(sql, colnames=True, **kargs)
        for row in res:
            yield dict(zip(colnames, row))


    ############## Main query logic #####################
    
    def doQueryOnCursor(self, sql, cursor):
        sql  = self.fireBeforeQuery(sql)
        try:
            cursor.execute(sql)
            return cursor
        except Exception, e:
            raise SQLException(sql, e)
            
    def doQuery(self, sql, colnames = False, select=None):
        """
        Execute the query sql on the database and return the result.
        If cursor is given, use that cursor and return the cursor instead
        Otherwise, pre- and postprocess around a call with a new cursor
        """
        if type(sql) == unicode: sql = sql.encode('latin-1', 'replace')
        if select is None:
            select=sql.lower().strip().startswith("select")
        c = None
        t = time.time()
        with self.cursor() as c:
            self.doQueryOnCursor(sql, c)
            try:
                res = c.fetchall() if select else None
            except Exception, e:
                raise SQLException(sql, e)
            self.fireAfterQuery(sql, time.time() - t, res)
            if select and colnames:
                info = c.description
                colnames = [entry[0] for entry in info]
                return res, colnames            
            return res
        
    def doSafeQuery(self, sql, *args):
        e_args = [quotesql(a) for a in args]
        sql = sql % (toolkit.makeList(*e_args))
        self.doQuery(sql)
        

    def doCall(self, proc, params):
        """
        calls the procedure with the given params (tuple). Returns
        (paramvalues, result), where paramvalues is the list of
        params with output params updates, while result is the
        result of c.fetchall() after the call
        """
        c = self.cursor()
        values = c.callproc(proc, params)
        res = c.fetchall()
        c.close()
        return values, res

        
    def fireBeforeQuery(self, sql):
        for func in self.beforeQueryListeners:
            s = func(sql)
            if s: sql = s
        return sql
    def fireAfterQuery(self, sql, time, results):
        for func in self.afterQueryListeners:
            func(sql, time, results)

    def getProfiler(self):
{'fraction_non_alphanumeric': 0.10488941489755159, 'fraction_numerical': 0.0032546786004882017, 'mean_word_length': 2.1030525591002984, 'pattern_counts': {'":': 2, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6382795', 'n_tokens_mistral': 3726, 'n_tokens_neox': 3651, 'n_words': 1144}