Das deutsche Python-Forum

Code: Alles auswählen

sql.py:
def sql_preqc_get_by_fwg():
    """ returns a select sql statement to select all items by fwg """
    sql = "SELECT * FROM preqc WHERE fwg=? AND selected IS NULL"
    return sql

itemfilter.py:
...
from lib.sql import sql_preqc_get_by_fwg
...
    def filter_items_by_upcs(self):
        """filter items by a list up upcs
        :return:
        """
        item_db = self._item_db
        week_db = DatabaseService('sqlite', self._rd_controller.target.app_preqc_file)
        
        if self._options.del_selected:
            week_db.execute(sql_preqc_clear_filter_tag())
        try:    
            self._flag_by_fwg(week_db, item_db)
        except Exception as e:
            logger.error('%s flag_by_items failed - %s ' % (self._rd_controller.whoiam, e))  
            raise Exception(e)
            
  def _flag_by_fwg(self, week_db, item_db):
        """ """
        fwg_count_selected = week_db.get_data(sql_preqc_get_count_fwg_selected())  
        
        for fwg, selected_count in fwg_count_selected:
            try:
                #ignore FWG Zero - stoe doesnot have FWGs
                tmp_fwg = int(fwg)
                if not tmp_fwg:
                    continue
            except:
                pass

            #TODO remove after recreate all dbs new
            if " " in fwg:
                fwg = "'%s'" % fwg
                
            week_count = week_db.get_data(sql_preqc_get_count_fwg(), (fwg,))
            week_count = week_count[0][0]
            
            ratio = 0
            if week_count:
                ratio = round(selected_count * 100 / week_count)
            else:
                print("-%s-" % fwg) #TODO TEST
                
            if ratio < int(settings.ifilter_fwg_ratio):
                continue
     
            #fix ratio 1 because 1 is the selection flag for selected by item - ratio muss nicht genau sein
            ratio = 2 if ratio == 1 else ratio

           for selected in week_db.fetch_many(sql_preqc_get_by_fwg(), (fwg,)):
               self._update_week_db(week_db, selected, ratio)

database.py:
    def fetch_many(self, query, data=None, limit=10000):
        """
        @query: the sql statement
        @data: a list of values 
        @data: the limit
        :return:
        """
        try:
            cursor = self._connect.cursor()
            if data:
                cursor.execute(query, data)
            else:
                cursor.execute(query)
            while True:
                results = cursor.fetchmany(limit)
                if not results:
                    break
                yield results
        except Exception as e:
            error = 'fetch_many: ' + str(e)
            raise Exception(error) 
        finally:
            cursor.close()

Code: Alles auswählen

try:
    for selected in week_db.fetch_many(sql_preqc_get_by_fwg(), (fwg, )):
        self._update_week_db(week_db, selected, ratio)
except:
    for selected in week_db.fetch_many(sql_preqc_get_by_fwg(), (fwg, )):
        self._update_week_db(week_db, selected, ratio)

Code: Alles auswählen

#!/usr/bin/env python

import logging
import sqlite3
import time
from settings import Settings
import jaydebeapi
import jpype

settings = Settings()
jpype_state = False

logger = logging.getLogger("db")

class DatabaseService(object):
    """ DatabaseService open a connection to a Database
        supported DBs: Mysql, Oracle, sqlite
    """
    
    def __init__(self, *account):
        self._account = account
        self._connect = self.connectdb(account)

            
    def reconnect(self):
        self._connect = self.connectdb(self._account)
        
    def __exit__(self):
        self._connect.close()
        
    def connectdb(self, account):
        """open the db connection 
           @account: <object> the logindata
        """
        dbtype = account[0]

        def oracle(account):
            cs = 'jdbc:oracle:thin:%s/%s@%s:%s:%s' % tuple(account[1:])
            driver = 'oracle.jdbc.driver.OracleDriver'
            try:
                args = '-Djava.class.path=%s' % settings.ojdbc
                if not jpype.isJVMStarted():
                    jpype.startJVM(settings.jvm_path, args)
                return jaydebeapi.connect(driver, cs)
            except Exception as e:
                logger.critical(e)
                logger.critical("connectdb: oracle - Problem connecting to \"%s\"" % cs)
                raise
                
        # def mysql():
        #     try:
        #         return MySQLdb.connect(
        #             host=host, user=user, passwd=passwd, port=port, db=db)
        #     except MySQLdb.Error as e:
        #         error = 'connectdb: mysql - ' + str(e)
        #         raise Exception(error)
        
        def sqlite(account):
            try:
                db = account[1]
                if not db[-3:] == '.db':
                    db += '.db'
                connect = sqlite3.connect(db)
                #connect.text_factory = str
                return connect
            except sqlite3.Error as e:
                error = 'connectdb: sqlite - ' + str(e)
                raise Exception(error) 
          
        dbs = {#'mysql': mysql,
               'oracle': oracle,
               'sqlite': sqlite,}
        
        connected = False
        for i in range(0, 5):
            try:
                connected = dbs[dbtype](account)
                break
            except Exception as e:
                logger.error('%s - try reconnect in 20sec -%s' % (str(e), i))
                time.sleep(20)
            
        return connected or dbs[dbtype](account)
    
    def execute(self, query, data=None, commit=True):
        """@query: the sql statement
           @data: the data list if the statement put data
           @commit: to commit the query immediately
        """
        try:
            cursor = self._connect.cursor() 
            if not data:
                cursor.execute(query)
            else:
                cursor.executemany(query, data)
            if commit:
                self._connect.commit()
        except Exception as e:
            self._connect.rollback()
            raise Exception(e) 
        finally:
            cursor.close()

    def get_data(self, query, data=None):
        """
        :param query: the sql statement
        :return:
        """
        try:
            cursor = self._connect.cursor()
            if data:
                cursor.execute(query, data)
            else:
                cursor.execute(query)
            return cursor.fetchall()
        except Exception as e:
            error = 'get_data: ' + str(e)
            raise Exception(error) 
        finally:
            cursor.close()

    def fetch_many(self, query, data=None, limit=10000):
        """
        @query: the sql statement
        @data: a list of values 
        :return:
        """
        try:
            cursor = self._connect.cursor()
            if data:
                cursor.execute(query, data)
            else:
                cursor.execute(query)
            while True:
                results = cursor.fetchmany(limit)
                if not results:
                    break
                yield results
        except sqlite3.Error as e:
            error = 'fetch_many: ' + str(e)
            raise Exception(error) 
        finally:
            cursor.close()
            
    # def _callproc(self, cursor, proc, param):
    #     """ create the procedure call because
    #         not all supported databases provide stored procedures.
    #
    #         Parameter:
    #             cursor: the open databasecursor
    #             proc: the real procedurename without @
    #             param: procedure in parameter
    #     """
    #     if hasattr(cursor, 'callproc'):
    #         return cursor.callproc(proc, param)
    #     else:
    #         syntax = "call %s %s" % (proc, str(param))
    #         return cursor.execute(syntax)
        
    # def callproc(self, procedurename, param=()):
    #     """call an db procedure without parameter
    #
    #         Parameter:
    #         procedurename: must start with @
    #         param: ()
    #     """
    #
    #     connect = self._connect
    #     cursor = connect.cursor()
    #     param = (param,) if param and type(param) not in (list, tuple) else param
    #
    #     try:
    #         cursor.callproc(procedurename[1:], param)
    #         logid = cursor.fetchall()
    #         if not logid:
    #             return
    #         logid = logid[0]
    #         if len(logid) == 1:
    #             return logid[0]
    #         else:
    #             return logid
    #     except Exception as e:
    #         connect.rollback()
    #         error = 'callprog: ' + str(e)
    #         raise Exception(error)
    #     finally:
    #         cursor.close()
    #         connect.commit()
            
    def autoCommitOff(self):
        self._connect.autocommit(False)
        
    def autoCommitOn(self):
        self._connect.autocommit(True)

    # def store_data(self, dbdata, header=None, commit=True):
    #     """ store data via query or procedure into the database
    #
    #         Parameter:
    #         qkey: if start with @ then callproc
    #               without @ the use the local sqlite3 inorder to
    #               get the query statement
    #         data: data tuple with data to be stored
    #     """
    #     try:
    #
    #         def putmany(*largs):
    #             key, cursor, dbdata = largs
    #
    #             try:
    #                 for values in dbdata:
    #                     cursor.callproc(key, values)
    #             except Exception as e:
    #                 error = '%s - %s - %s' % (key, values, str(e))
    #                 raise Exception(error)
    #
    #
    #         connect = self._connect
    #         cursor = connect.cursor()
    #
    #         query = self.get_statement(qkey)
    #         if len(dbdata) == 0:
    #             cursor.execute(query)
    #         elif len(dbdata) == 1:
    #             cursor.execute(query, dbdata)
    #         else:
    #             cursor.executemany(query, dbdata)
    #
    #     except Exception as e:
    #         connect.rollback()
    #         error = 'store_data: ' + str(e)
    #         raise Exception(error)
    #     finally:
    #         if commit:
    #             cursor.close()
    #             self._connect.commit()
    
    # def get_testdata(self, filename, dml):
    #     """ """
    #     result = []
    #     with open(filename, 'rb') as file:
    #         values = file.readlines()
    #         for i in values:
    #             x = i.replace('\n', '')
    #             x = tuple(x.split(dml))
    #             result.append(x)
    #     return result

Code: Alles auswählen

import sqlite3

week_db = sqlite3.connect('/mnt/tabak1/dilo/sc429meh/preqc/preqc_2015_02.db')

cursor = week_db.cursor()
cursor.execute("SELECT fwg, count(*) from preqc where selected is not null group by fwg")
fwg_count_selected = cursor.fetchall()
cursor.close()


for fwg, selected_count in fwg_count_selected:
    fwg = ("%s" % fwg,)
    cursor = week_db.cursor()

    cursor.execute("SELECT count(*) from preqc where fwg=?", fwg)
    week_count = cursor.fetchall()
    cursor.close()

    week_count = week_count[0][0]
    ratio = 0
    if week_count:
        ratio = round(selected_count * 100 / week_count)

    try:
        cursor = week_db.cursor()
        cursor.execute("select * from preqc where selected is NULL and fwg=?", fwg)

        selected = cursor.fetchmany(10000)
        selected_ids = [(x[0],) for x in selected]
        xcursor = week_db.cursor()
        xcursor.executemany("update preqc set selected=%s where id=?" % ratio, selected_ids)
        week_db.commit()
        xcursor.close()
        cursor.close()

    except Exception as e:
        print(e)