Herzlichen Dank
@Sirius3 - nein, es wird immer ein neuer cursor instanziiert.
die passiert in Zeile 71 erster Beispielcode
cursor = self._connect.cursor()
hier die Stackdaten vom ersten Durchlauf - def _flag_by_fwg(self, week_db, item_db)
fwg = {tuple} ('875001',)
fwg_count_selected = {list} [('875001', 8673), ('875002', 1232), ('875003', 11942)]
item_db = {DatabaseService} <services.database.DatabaseService object at 0x7fd5db732ef0>
ratio = {int} 31
selected_count = {int} 8673
self = {ItemFilter} <core.itemfilter.ItemFilter object at 0x7fd5db716390>
tmp_fwg = {int} 875001
week_count = {int} 28421
week_db = {DatabaseService} <services.database.DatabaseService object at 0x7fd5e8de8b38>
Stackdaten von def fetch_many(self, query, data=None, limit=10000)
cursor = {Cursor} <sqlite3.Cursor object at 0x7fd5e859b420>
data = {tuple} ('875001',)
limit = {int} 10000
query = {str} 'select * from preqc where selected is NULL and fwg=?'
results = {list} [(14576, 1845, '0000040295015', '0000103347291', '875001', 'JPS RED XL-BOX 25ST. 6,00 1ST REEMTSMA CIGARETTENFABRIKEN GMBH ', None, None, None, None, '0', None, None, None, None, None, None, None, None, 6.0, 3600.0, None, 600.0, 1.0, None,
self = {DatabaseService} <services.database.DatabaseService object at 0x7fd5e8de8b38>
hier der 2. Durchlauf, der kracht
def fetch_many(self, query, data=None, limit=10000):
fwg = {tuple} ('875002',)
fwg_count_selected = {list} [('875001', 8673), ('875002', 1232), ('875003', 11942)]
item_db = {DatabaseService} <services.database.DatabaseService object at 0x7fd5db732ef0>
ratio = {int} 47
selected = {list} [(3557642, 1845, '0000042048589', '0000102104411', '875001', 'JPS JUST BLUE OHNE ZUSAETZE 19 S 1ST REEMTSMA CIGARETTENFABRIKEN GMBH ', None, None, None, None, '0', None, None, None, None, None, None, None, None, 7.0, 3500.0, None, 500.0, 1.0, Non
selected_count = {int} 1232
self = {ItemFilter} <core.itemfilter.ItemFilter object at 0x7fd5db716390>
tmp_fwg = {int} 875002
week_count = {int} 2633
week_db = {DatabaseService} <services.database.DatabaseService object at 0x7fd5e8de8b38>
stackdaten methode fetch_many:
cursor = {Cursor} <sqlite3.Cursor object at 0x7fd5e859b420>
data = {tuple} ('875002',)
limit = {int} 10000
query = {str} 'select * from preqc where selected is NULL and fwg=?'
self = {DatabaseService} <services.database.DatabaseService object at 0x7fd5e8de8b38>
der stackdaten innerhalb der exception von fetch_many
cursor = {Cursor} <sqlite3.Cursor object at 0x7fd5e859b420>
data = {tuple} ('875002',)
e = {InterfaceError} Error binding parameter 0 - probably unsupported type.
args = {tuple} ('Error binding parameter 0 - probably unsupported type.',)
__len__ = {int} 1
0 = {str} 'Error binding parameter 0 - probably unsupported type.'
limit = {int} 10000
query = {str} 'select * from preqc where selected is NULL and fwg=?'
self = {DatabaseService} <services.database.DatabaseService object at 0x7fd5e8de8b38>
Der DatabaseService.. (ignoriert die altfragmente)
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
dankeschön
chris
