def genInsert(table,keys,listofvalues, OnMakeTabName=None, insStatement='insert'):#, fInsertIgnore=1
""" Generate Insert DML"""
if not listofvalues:
raise Exception('genInsert : empty listofvalues')
ins=''
table=utf8(table)
keys=utf8(keys)
if OnMakeTabName:
table=OnMakeTabName(table)
keys=map(OnMakeTabName,keys)
if not isinstance(listofvalues,(tuple,list)):
listofvalues=[[listofvalues]]
if not isinstance(listofvalues[0],(tuple,list)):
listofvalues=[listofvalues]
listofvalues=app.utf8(listofvalues)
try:
sql=(insStatement+' into %s'%table)#.encode('utf-8')
vals=reduce(lambda ret,vals : ret+'(%s),'%makeInsStr(vals,format),listofvalues,'')
ins=sql+((keys and '('+makeInsStr(keys)+')') or '')
ins=ins+' values '+vals[:-1]+';'
except:
print('genInsert-Error:',table,keys,listofvalues)
app.LogError('',1)
#for vals in listofvalues:
# vals
return ins
def GetXDocFromData(Data, Encoding=None):
#auto detect and remove xmlns
#<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="ru" # lang="ru">
Data = str(Data)
if 'xmlns=' in Data:
Data = Data.replace('xmlns=', 'this_xmlns_was_removed_case_libxml2=')
Encoding = Encoding or GetCharset(Data) #GetWebSiteEncoding(None,Data)
try:
#ret=libxml2.htmlParseDoc(Data,Encoding)
parser = etree.HTMLParser()
#(isinstance(Data,unicode) and Data) or Data.decode(Encoding))
#or ' charset="utf' in Data
# if (not isinstance(Data,str)):
# enc=GetCharset(Data)
# Data=Data.decode(enc)
ret = etree.parse(StringIO(Data), parser)
if AutoFixLinks:
FixLinks(ret)
except:
app.LogError(Data, 1, 'libxml_error.log')
return ret
def SmartXpath(Node, xPath):
# DecXPath=DecRus(xPath.replace('->','following-sibling::node()[1]').replace('<-','preceding-sibling::node()[1]'))
DecXPath = xPath.replace('->', 'following-sibling::node()[1]').replace(
'<-', 'preceding-sibling::node()[1]')
try:
# return Node.xpath(DecRus(DecXPath))
return Node.xpath(DecXPath)
except:
app.LogError('SmartXpath.' + DecXPath, 1)
def CloseDb(db=None):
if db is None:
db = getdb()
## global db
try:
if db and db.open:
try:
db.close()
except:
app.LogError('CloseDb. ')
# thId=thread.get_ident()
# dbs[thId]=None
# del dbs[thId]
except:
app.LogError('CloseDb. ')
db = None
def AutoCloseDb():
with dbs_lock:
for k, v in dbs.items():
cDelta = v.lastAccess - datetime.datetime.now()
if cDelta.seconds > AutoCloseDbAfter:
try:
dbn = v.dbn
v.close()
app.Log('autoclosedb ' + dbn)
except:
app.LogError('AutoClose.')
dbs[k] = None
del dbs[k]
def __task(self,index, func,args, kwargs):
global loc
try:
fResult=None
loc.obj=ThreadObj()
loc.obj.parent=self.curThread
rep,flDone=0,0
while (rep<=self.RepeatIfExcept) and (not flDone):
rep+=1
try:
# log all tasks params
if self.flLog==app.flFullLog:
app.Log((';params;'+func.__name__+'('+str(args)+')').replace('\n','\\'),FullLogFileName)
fResult=func(*args,**kwargs)
flDone=1
except (KeyboardInterrupt, SystemExit):
sys.exit(0)
except:
fResult=ThreadExcept()
app.LogError('Threads.__task'+str(func)+'\n'+str(args),str(kwargs))
if self.flNoResult:
fResult=None
DoCleanUp()
## if self.OnError:
## try:
## self.OnError(fResult)
## except:
## pass
finally:
self.MutexAcquire()
self.running -= 1
try:
# log all tasks
if self.flLog==app.flFullLog:
app.Log((';'+((isinstance(fResult,ThreadExcept) and 'error') or 'success')+';'+str(fResult)).replace('\n','\\'),FullLogFileName)
# check for auto error log
if self.flLog and isinstance(fResult,ThreadExcept):
#log all Errors
app.Log(str(fResult.exception)+'\n'+fResult.error+'\nfunction params='+str(args)+str(kwargs))
self.ReturnData[index]=None
else:
self.ReturnData[index]=fResult
DoCleanUp()
finally:
if self.running == 0 and self.IsLocked():
self.done.release()
self.MutexRelease()
def genUpdate(table,IdKey,dKeyVal):
table=utf8(table)
dKeyVal=utf8(dKeyVal)
sql=''
try:
sql='update %s set '%table
sql+=makeUpdStr(dKeyVal,format)
sql+=' where id=%s'%str(IdKey)
except:
print('genUpdate-Error:',table,IdKey,dKeyVal)
app.LogError('',1)
#for vals in listofvalues:
# vals
return sql
def FlushScript():
global flScriptMode
if not flScriptMode:
return
#flScriptMode=not flStopScriptMode
if flScriptMode == 1:
try:
app.Log(
subprocess.check_output(['myexec', lastDbName, ScriptLogFile]))
except:
app.LogError()
elif flScriptMode == 2:
mSave = flScriptMode
flScriptMode = 0
try:
ExecSql(ScriptArr)
finally:
flScriptMode = mSave
def ImportTable(nodes, fMap, table, ukey):
try:
# print '123'
dNodes = dict([[xValue(n, '@' + fMap[ukey], flEncode=0),
GetProps(n)] for n in nodes])
sql = 'select %s from %s where %s in (%s)' % (ukey, table, ukey,
str(dNodes.keys())[1:-1])
# print sql
ret = ExecSql(sql)
# print ret
sql = ''
lvals = []
keys = fMap.keys()
for x in Set(dNodes.keys()).difference(Set(ret)):
ps = dNodes[x]
lvals.append(map(lambda k: ps.get(fMap[k]), keys))
app.Log(genInsert(table, keys, lvals))
ExecSql(genInsert(table, keys, lvals))
except:
app.LogError('ImportTable : ' + table)
def CreateTable(TabName,
fields,
vals=None,
flDropTab=0,
flOnlyIns=0,
flCreateIndexes=1,
MinColumnSize=250,
flNotNull=1,
flExec=1):
# global fs,vs
global tmpLoadPrefix
#check fields names for table creation
try:
if vals and not fields:
fields = ['f%d' % i for i in xrange(len(vals[0]))]
reservedWords = ['key', 'from', 'to']
fldTypes = {}
if isinstance(fields, (SmartTypes.SmartDict, dict)):
fldTypes = fields
fields = fldTypes.keys()
fields = map(lambda x: MySqlName(
(x in reservedWords and x + '1') or x), fields) #.replace('-','_')
# check for duplicate field name
# for now simple for id
if fields.count('id') > 1:
ind = fields.index('id', 1)
fields[ind] = 'id1'
#if len({}.fromkeys(fields))!=len(fields):
# for i in range(len(fields)):
# fields)
# get cols info
# rCols=ExecSql(sqlCols%(lastDbName,TabName)+' and DATA_TYPE="text"',0)
ret = ExecSql(sqlCols % (TabName, ))
app.Log('COLS sql=%s cols=%s : ' % (sqlCols % (TabName), str(ret)))
dAllCols = dict(ret)
colsInfo = None
sqlCreateTab = ''
ret = []
GetCharDef = lambda size: (
(size >= 1000) and 'TEXT') or 'varchar(%d)' % (max(size, 1))
if vals:
fLens = [[
fields[i],
max(
map(lambda x: len(fsys.ToUtf8(x)) + 4,
map(itemgetter(i), vals)))
] for i in xrange(len(fields))]
else:
fLens = zip(fields, [0] * len(fields))
dLens = dict(fLens)
if dAllCols and not flDropTab:
colsInfo = dict(dAllCols)
app.Log('colsInfo=' + str(colsInfo))
for k, v in filter(
lambda x:
(x[0] in colsInfo) and (int(x[1]) > int(colsInfo[x[0]])),
dLens.items()):
if v >= 1000:
ret += [
'ALTER TABLE %s CHANGE %s %s TEXT %s NULL DEFAULT NULL'
% (TabName, k, k, dEncSet[defEnc])
]
else:
ret += [
'ALTER TABLE %s CHANGE %s %s VARCHAR(%s) %s NULL DEFAULT NULL'
% (TabName, k, k, v, dEncSet[defEnc])
]
else:
defFields = map(
lambda (s, maxLen):
('%s ' + ((s in fldTypes and fldTypes[s]) or
(s.lower() == 'id' and 'INT') or GetCharDef(
max(maxLen, MinColumnSize))) + ' %s') % (s, (
(s.lower() == 'id' or flNotNull
) and 'not null') or 'default null'), fLens)
sqlCreateTab = (
not flOnlyIns and
'create table IF NOT EXISTS %s (%s) ENGINE=%s DEFAULT CHARSET=%s'
% (TabName, makeInsStr(defFields), DefEngine, defEnc)) or ''
ret = [(flDropTab and 'drop table IF EXISTS %s' % TabName) or '',
sqlCreateTab]
if 'id' in fldTypes:
ret += [
'ALTER TABLE %s ADD PRIMARY KEY (id)' % TabName,
'ALTER TABLE %s modify id INT(11) NOT NULL AUTO_INCREMENT'
% TabName
]
# if not tmpLoadPrefix:
# tmpLoadPrefix='db_'
if vals:
if tmpLoadPrefix:
fold = tmpLoadPrefix + 'temp'
fsys.CreateDirs(fold)
fold += '\\'
fName = app.AbsPath(fold + '%s.tab' % TabName)
app.Log('CreateTable ' + fName)
# vals=app.ApplyFunc2Vals(vals,lambda x: ((x is None) and 'Null') or x)
# for r in vals:
# for i in xrange(len(r)):
# if r[i] is None:
# r[i]='Null'
# else:
# r[i]=r[i].replace('\n','\\n').replace(';','.')
# import csv
fsys.Save2Csv_old(fName,
vals,
add_repl=lambda x:
((x is not None) and x.replace('\\', '\\\\').
replace('\n', '\\n').replace(';', '.')) or
'') #,'$^','$\r\n^'#.replace('`','')
# fsys.Save2Csv(fName,vals)#,'$^','$\r\n^'
#mysql wants / slashes
fName = fName.replace('\\', '/')
# if we create table then we need always
if sqlCreateTab:
insFields = fields
else:
insFields = map(
lambda f: ((f in dAllCols) and f) or '@dummy', fields)
insFields = fsys.lineSep(insFields, ',')
LoadSql = """
LOAD DATA LOCAL INFILE '%s'
IGNORE
INTO TABLE %s
CHARACTER SET UTF8
FIELDS
TERMINATED BY ';'
ESCAPED BY '\\\\'
LINES
TERMINATED BY '\\r\\n'
IGNORE 0 LINES (%s)""" % (fName, TabName, insFields)
retIns = [LoadSql]
else:
retIns = map(lambda v: genInsert(TabName, fields, [v]), vals)
ret += retIns
if flCreateIndexes and flDropTab:
for x in fields:
if x.lower() != 'id':
ret.append('ALTER TABLE %s ADD INDEX(%s)' % (TabName, x))
if flExec:
fsys.Save2File('new_script.sql', ret)
ExecSql(ret, 0)
return ret
except:
app.LogError(
'Table=%s\nFields=%s\nVals=%s' %
(str(TabName), str(fields), str((vals and vals[:100]) or '')))
raise
def _ExecSql(sql, flRaise=1, flAsDict=0, pars={}, db=None, recursive=0):
if db is None:
db = getdb()
## global db
# print db
# обрабатываем параметры переданные в качестве диктионари.
if pars:
# print 'Before', str(pars)
for k, v in pars.items():
if isinstance(v, basestring):
if isinstance(v, unicode):
v = v.encode('utf-8')
pars[k] = mydb.escape_func(v)
# print 'After', str(pars)
# sql=sql%pars
if isinstance(sql, (unicode, str)) and sql.strip() == '':
return None
# if flAsDict:
# return [{}]
# else:
# return []
if not db:
raise Exception('NO DB')
flErrorProcessed = 0
r = None
try:
if not isinstance(sql, (list, tuple)):
sql = [sql]
sql = ['START TRANSACTION'] + sql
# flSelect=(len(sql)==1) and sql[0].lower().startswith('select')
flDml = 0
flErrorProcessed = 0
cur = None
for sCmd in sql:
try:
if flCloseCur:
if cur:
cur.close()
cur = None
# time.sleep(0.05);
if pars:
sCmd = sCmd % pars
sCmd = app.removeComments(sCmd, 1)
sCmdStrip = sCmd.strip().strip(';')
sCmdStripLow = sCmdStrip.lower()
if sCmdStrip != 'START TRANSACTION':
app.Log(sCmdStrip)
if sCmdStrip and (sCmd != ''):
script = mySqlScriptPattern.split(sCmdStrip) #[1::2]
if len(script) > 1:
flErrorProcessed = 1
r = ExecSql(script, flRaise, flAsDict, pars)
flErrorProcessed = 0
continue
## if not flScriptMode:
## if sCmd<>'START TRANSACTION':
## app.Log('SQL Command : '+ sCmd)
try:
flSelect = sCmdStripLow.startswith(
'select') or sCmdStripLow.startswith('show')
flDml = flDml or isDml(sCmdStripLow)
if flScriptMode == 1:
if not flSelect:
app.Log((sCmdStrip.endswith(';') and sCmdStrip)
or (sCmdStrip + ';'), ScriptLogFile, 0)
continue
elif flScriptMode == 2:
if not flSelect:
ScriptArr.append(sCmdStrip.strip(';'))
continue
else:
##app.Log('create cursor')
cur, ret = GetCursor(
db,
sCmdStrip,
cursor=((not flCloseCur and cur) or None))
if isDml(sCmdStrip) and not r:
r = ret
# try:
# z=input(sCmd)
# except:
# pass
if flSelect:
r = cur.fetchall()
if not r:
return []
app.Log('ExecSql.Fields Description =' +
str(cur.description))
if flAsDict:
fields = map(itemgetter(0), cur.description)
# make field list unique. Add index to name
fldUniq = []
for x in fields:
if x in fldUniq:
# try to find next unique index
for i in xrange(1, 100):
if (x + str(i)) not in fldUniq:
break
fldUniq.append(x + str(i))
else:
fldUniq.append(x)
fields = fldUniq
r1 = []
for x in r:
r1.append(
SmartTypes.SmartDict(zip(fields, x)))
r = r1
flSelect = not flSelect
except:
app.LogError('ExecSql:', 0)
app.LogError(sCmd, flRaise)
#return None
finally:
if cur and flCloseCur:
# app.Log('close cursor')
cur.close()
cur = None
try:
if not flScriptMode:
#if flDml:
# app.Log('db commit')
db.commit()
except:
app.LogError('ExecSql.Commit', 0)
# if len(r)==1:
# if len(r[0])==1:
# return r[0][0]
except:
##OperationalError: (2006, 'MySQL server has gone away')
if 'server has gone away' in traceback.format_exc():
if recursive < 3:
app.LogError(
'Recconnect. We will try to execute sql again : ' +
str(sql))
threadId = thread.get_ident()
#reconnect
dbs[threadId] = []
db = InitDb(db.name, db.host, db.user, db.password)
return _ExecSql(sql, flRaise, flAsDict, pars, db,
recursive + 1)
return None
if not flScriptMode:
db.rollback()
if not flErrorProcessed:
app.LogError('ExecSql:', 0)
app.LogError(sCmd, 0)
if flRaise:
raise
return None
return r
def DoCleanUp():
try:
if CleanUpFunc:
CleanUpFunc()
except:
app.LogError('Threads CleanUpFunc error')