def FillRandomValues(count=31):
def MakeRandomRow(cols):
global fillUniqId
ret = []
for col in cols:
# if isinstance(col,(str,unicode)):
# name=col
# colType=cols[col]['type']
# else:
name = col['field']
colType = col['type']
val = ''
if 'enum' in colType:
val = random.choice(colType.strip('enum()').split(',')).strip(
"'").encode('utf-8')
elif colType.startswith('varchar') or colType.startswith(
'char') or colType.startswith('text'):
val = name + str(fillUniqId)
elif 'date' in colType:
val = str(random.randint(2007, 2012)) + '-0' + str(
random.randint(1, 9)) + '-' + str(random.randint(10, 28))
else:
# if name.lower()=='id':
# val=fillUniqId
# else:
val = random.randint(1, 3)
ret.append(val)
fillUniqId += 1
return ret
def MakeRandomRows(cols, count=31):
global fillUniqId
fillUniqId = 1
return [MakeRandomRow(cols) for x in xrange(count)]
# if not defs:
tabs = ExecSql('SHOW TABLES')
for rec in tabs:
tab = OnMakeTabName(rec[0])
cols = ExecSql("SHOW COLUMNS FROM %s WHERE Field<>'id'" % tab,
flAsDict=1)
vals = list(MakeRandomRows(cols, count))
ExecSql("SET FOREIGN_KEY_CHECKS = 0")
sql = genInsert(tab, [x['field'] for x in cols], vals, OnMakeTabName)
app.Log(str(vals))
app.Log('FillRandomValues : ' + sql)
ExecSql(sql)
ExecSql("SET FOREIGN_KEY_CHECKS = 1")
return None
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 __init__(self):
self.error=traceback.format_exc()
self.exception=sys.exc_info()[1]
# print 'Exception : ', str(self)
# SimpleLog(str(self))
if flOneThreadMode:
app.Log(str(self))
def xNodes(Node, xPath, CheckCount=0):
ret = SmartXpath(Node, xPath)
if (CheckCount > 0) and (len(ret) != CheckCount):
err = 'CorrectCount=%s;\nMatches.Count=%s;\nXPath=%s;\nurl=%s' % (
CheckCount, len(ret), xPath, GetDocUrl(Node))
app.Log(err, 'Wrong_matches_count.log')
if NoErrorsMode:
ret = [None] * CheckCount
else:
raise BaseException(err)
return ret
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 GetCursor(db, statement, flRecursiveCnt=0, cursor=None):
ret = None
try:
rCur = cursor or db.cursor()
ret = rCur.execute(statement)
except (AttributeError, MySQLdb.OperationalError) as e:
# if flRecursiveCnt:
# app.Log('Still get mysql Error (programm execution is stopped): '+str(e))
# raise e
# else:
app.Log('exception generated during sql connection: ' + str(e))
raise e
# InitDb(db.name,db.host,db.user,db.UserUniqDbId,db.OnInit)
# return GetCursor(statement,flRecursiveCnt+1)
return [rCur, ret]
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 getdb(raise_err=1):
threadId = thread.get_ident()
err = ''
if threadId not in dbs:
if flScriptMode:
threadId = dbs.keys()[0]
else:
err = 'ERROR: NO OPEN MYSQL CONNECTION for thread %d' % threadId
else:
if len(dbs[threadId]) > 1:
err = 'ERROR: YOU HAVE FEW MYSQL CONNECTION for thread %d' % threadId
if err != '':
app.out(err)
app.Log(err)
if raise_err:
raise Exception(err)
return None
return dbs[threadId][0]
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
#print app.CurDir
tmpLoadPrefix = ''
##db=None
flFastLoad = 1
ScriptModeFile = 1
ScriptModeArr = 2
ScriptArr = []
flScriptMode = 0
ScriptLogFile = app.AbsPath('script.sql')
#ScriptCommands=[]
if flScriptMode:
# clear script.sql
#fsys.Save2File()
app.Log('', ScriptLogFile, 0, 1)
defEnc = 'utf8'
DefEngine = 'MyISAM'
dEncSet = {
'cp1251': 'CHARACTER SET utf8 COLLATE utf8_general_ci',
'utf8': 'CHARACTER SET utf8 COLLATE utf8_unicode_ci'
}
s = '''
SET group_concat_max_len = 4000000;
#delete from xld_70a39f9a537b4eba94bc291051027b3a where sheet between 0 and 3;
delete from xld_70a39f9a537b4eba94bc291051027b3a; where col_width<10 and col_to=0 and row_to=0;
ALTER TABLE xld_70a39f9a537b4eba94bc291051027b3a ADD UNIQUE (sheet ,col,row, lb, tb, rb, bb);
'''