def testPymysql(self):
sqlConfig = {'host':'127.0.0.1', 'port':3306, 'user':'******',
'passwd':'200123', 'db':'msg', 'charset':'utf8', 'auto_commit':True}
# {'passwd': u'200123', 'charset': 'utf8', 'db': u'msg', 'host': u'127.0.0.1', 'user': u'root', 'auto_commit': True, 'port': 3306}
conn = SqlConnFactory(PymysqlConn, sqlConfig, 1)
print(conn.executeSql("show tables"))
print(conn.getSql("msg", Sql.select).execute())
def __init__(self):
sqlConfig = {
'host': cprop.getVal("db", "host"),
'port': cprop.getInt("db", "port"),
'user': cprop.getVal("db", "user"),
'passwd': cprop.getVal("db", "passwd"),
'db': cprop.getVal("db", "db"),
'charset': 'utf8'
}
self.sqlConn = SqlConnFactory(MysqldbConn, sqlConfig)
def __getConn(self, dbConfig):
if self.conns.__contains__(dbConfig):
conn = self.conns[dbConfig]
else:
if self.isMysqldb:
from db.mysqldb import MysqldbConn
conn = SqlConnFactory(MysqldbConn, self.dbConfigs[dbConfig], 1)
else:
from db.pysql import PymysqlConn
conn = SqlConnFactory(PymysqlConn, self.dbConfigs[dbConfig], 1)
self.conns[dbConfig] = conn
return conn
class CtestDbOp:
def __init__(self):
sqlConfig = {
'host': cprop.getVal("db", "host"),
'port': cprop.getInt("db", "port"),
'user': cprop.getVal("db", "user"),
'passwd': cprop.getVal("db", "passwd"),
'db': cprop.getVal("db", "db"),
'charset': 'utf8'
}
self.sqlConn = SqlConnFactory(MysqldbConn, sqlConfig)
def saveCtree(self, name, fnid=-1, nid=None):
sql = self.sqlConn.getSql("ctree",
Sql.insert if nid is None else Sql.update,
True)
sql.appendValueByJson({'name': name, "fnid": fnid, "nid": nid})
sql.appendWhere("nid", nid)
return sql.execute()
def getCtree(self, fnid=None, name=None, nid=None):
sql = self.sqlConn.getSql("ctree", Sql.select, True)
sql.appendWhereByJson({'name': name, "fnid": fnid, "nid": nid})
return sql.execute()
def deleteCtree(self, nid):
if Sql.isEmpty(nid):
return 0
sql = self.sqlConn.getSql("ctree", Sql.delete, True)
sql.appendWhere("nid", nid)
return sql.execute()
# test case
def saveCtestcase(self,
scenario=None,
tags=None,
name=None,
ttype=None,
priority=None,
steps=None,
remark=None,
owner=None,
fnid=None,
nid1=None,
nid2=None,
caseid=None):
modifytime = TimmerOperation.getFormatTime(time.time())
sql = self.sqlConn.getSql(
"testcase", Sql.insert if Sql.isEmpty(caseid) else Sql.update,
True)
sql.appendValueByJson({
'scenario': scenario,
'tags': tags,
'name': name,
"ttype": ttype,
"priority": priority,
'steps': steps,
"remark": remark,
"owner": owner,
'modifytime': modifytime,
"fnid": fnid,
"nid1": nid1,
"nid2": nid2,
"caseid": caseid
})
sql.appendWhere("caseid", caseid)
return sql.execute()
def syncTestcaseOindex(self):
return self.sqlConn.executeSql(
'update testcase set testcase.oindex=testcase.caseid where testcase.oindex=0'
)
def changeTestCaseOindex(self, caseid1, caseid2):
sql = self.sqlConn.getSql("testcase", Sql.select, True,
"caseid,oindex")
caseinfo = sql.appendWhere("caseid", (caseid1, caseid2),
"in").execute()
case1, case2 = caseinfo
sql1 = self.sqlConn.getSql("testcase", Sql.update).appendValue(
"oindex", case1['oindex']).appendWhere("caseid", case2['caseid'])
sql2 = self.sqlConn.getSql("testcase", Sql.update).appendValue(
"oindex", case2['oindex']).appendWhere("caseid", case1['caseid'])
return sql1.execute() + sql2.execute()
def getCtestcase(self,
fnid=None,
nid1=None,
nid2=None,
searchKey=None,
ttype=None,
priority=None,
name=None,
owner=None,
planid=None,
caseid=None,
isInplan=True):
sql = self.sqlConn.getSql("testcase", Sql.select, True)
sql.startCondition().appendWhere(
'fnid', fnid,
isAnd=False).appendWhere('nid1', nid1, isAnd=False).appendWhere(
'nid2', nid2, isAnd=False).endCondition()
sql.appendWhereByJson({
'name': name,
"ttype": ttype,
"priority": priority,
"owner": owner,
"caseid": caseid
})
if not Sql.isEmpty(planid):
sql.appendCondition(
"caseid %s in (select caseid from plancase where planid=%s)",
("" if isInplan else "not", planid))
if not Sql.isEmpty(searchKey):
searchKey = '%%%s%%' % searchKey
sql.appendCondition(
"(name like '%s' or tags like '%s'or scenario like '%s')",
(searchKey, searchKey, searchKey))
sql.orderBy("scenario,oindex")
return sql.execute()
def deleteCtestcase(self, caseid):
if Sql.isEmpty(caseid):
return 0
sql = self.sqlConn.getSql("testcase", Sql.delete, True)
sql.appendWhere("caseid", caseid)
return sql.execute()
def _checkDate(self, d):
return None if Sql.isEmpty(d) else d
# test plan
def saveCtestplan(self,
name=None,
owner=None,
version=None,
tags=None,
summary=None,
issues=None,
ptype=None,
priority=None,
status=None,
progress=None,
pstarttime=None,
pendtime=None,
starttime=None,
endtime=None,
mailto=None,
fnid=None,
nid1=None,
nid2=None,
planid=None):
# status: created, executing, finished, paused
pstarttime = self._checkDate(pstarttime)
pendtime = self._checkDate(pendtime)
starttime = self._checkDate(starttime)
endtime = self._checkDate(endtime)
sql = self.sqlConn.getSql(
"testplan", Sql.insert if Sql.isEmpty(planid) else Sql.update,
True)
sql.appendValueByJson({
'name': name,
'owner': owner,
'version': version,
'tags': tags,
'summary': summary,
'issues': issues,
'ptype': ptype,
'priority': priority,
"status": status,
"progress": progress,
'pstarttime': pstarttime,
"pendtime": pendtime,
"starttime": starttime,
'endtime': endtime,
"mailto": mailto,
"fnid": fnid,
"nid1": nid1,
"nid2": nid2,
"planid": planid
})
sql.appendWhere("planid", planid)
return sql.execute()
def saveCtestplanStatus(self,
planid,
status=None,
progress=None,
starttime=None,
endtime=None,
mailto=None,
mailfrom=None,
mailcc=None):
if Sql.isEmpty(planid):
return 0
if Sql.isEmpty(mailto): mailto = None
if Sql.isEmpty(mailfrom): mailfrom = None
if Sql.isEmpty(mailcc): mailcc = None
sql = self.sqlConn.getSql("testplan", Sql.update, True)
sql.appendValueByJson({
'status': status,
'progress': progress,
'starttime': starttime,
'endtime': endtime,
'mailto': mailto,
'mailfrom': mailfrom,
'mailcc': mailcc
})
sql.appendWhere("planid", planid)
return sql.execute()
def _makeTimeBetween(self, sql, cstarttime, cendtime, sqlStart, sqlEnd):
if not Sql.isEmpty(cstarttime):
if not Sql.isEmpty(cendtime):
sql.appendCondition("(({0}<='%s' and '%s'<={1}) or ({0}<='%s' and '%s'<={1}) or ('%s'<={0} and {0}<='%s'))".format(sqlStart, sqlEnd), \
(cstarttime, cstarttime, cendtime, cendtime, cstarttime, cendtime))
else:
sql.appendCondition("(%s>='%s')", (sqlEnd, cstarttime))
elif not Sql.isEmpty(cendtime):
sql.appendCondition("(%s<='%s')", (sqlStart, cendtime))
def getCtestplan(self,
fnid=None,
nid1=None,
nid2=None,
nameOrTags=None,
ptype=None,
priority=None,
inStatus=None,
outStatus=None,
cstarttime=None,
cendtime=None,
owner=None,
name=None,
tags=None,
planid=None):
sql = self.sqlConn.getSql("testplan", Sql.select, True)
sql.startCondition().appendWhere(
'fnid', fnid,
isAnd=False).appendWhere('nid1', nid1, isAnd=False).appendWhere(
'nid2', nid2, isAnd=False).endCondition()
if not Sql.isEmpty(nameOrTags):
nameOrTags = '%%%s%%' % nameOrTags
sql.appendCondition(
"(name like '%s' or tags like '%s' or owner like '%s')",
(nameOrTags, nameOrTags, nameOrTags))
if not Sql.isEmpty(name):
name = '%%%s%%' % name
sql.appendWhere('name', name, 'like')
if not Sql.isEmpty(tags):
tags = '%%%s%%' % tags
sql.appendWhere('tags', tags, 'like')
sql.appendWhereByJson({
"owner": owner,
'priority': priority,
'status': inStatus,
"planid": planid
})
sql.appendWhere('ptype', ptype, 'in')
sql.appendWhere('status', outStatus, '!=')
self._makeTimeBetween(sql, cstarttime, cendtime, "pstarttime",
"pendtime")
sql.orderBy("status,pendtime,priority,ptype,fnid desc,tags,name")
return sql.execute()
def deleteCtestplan(self, planid):
if Sql.isEmpty(planid):
return 0
sql = self.sqlConn.getSql("testplan", Sql.delete, True)
sql.appendWhere("planid", planid)
return sql.execute()
# plan related
def savePlancase(self,
planid=None,
caseid=None,
scenario=None,
tags=None,
name=None,
owner=None,
status=None,
remark=None,
plancaseid=None):
# status: not-start, executing, passed, failed
modifytime = TimmerOperation.getFormatTime(time.time())
sql = self.sqlConn.getSql(
"plancase", Sql.insert if Sql.isEmpty(plancaseid) else Sql.update,
True)
sql.appendValueByJson({
'planid': planid,
"caseid": caseid,
"tags": tags,
"scenario": scenario,
"name": name,
'owner': owner,
'remark': remark,
"status": status,
"modifytime": modifytime
})
sql.appendWhere("plancaseid", plancaseid)
return sql.execute()
def setPlancase(self,
plancaseid,
status,
owner,
planid=None,
curstatus=None):
# status: not-start, executing, passed, failed
if Sql.isEmpty(planid) and Sql.isEmpty(plancaseid): return 0
sql = self.sqlConn.getSql("plancase", Sql.update, True)
sql.appendValueByJson({"status": status, 'owner': owner})
sql.appendWhere("plancaseid", plancaseid).appendWhere(
"planid", planid).appendWhere("status", curstatus)
result = sql.execute()
if result > 0 and not Sql.isEmpty(plancaseid):
self.savePlancase(plancaseid=plancaseid)
return result
def getPlancase(self,
planid,
caseid=None,
owner=None,
status=None,
scenarioName=None,
caseName=None,
fields='*'):
sql = self.sqlConn.getSql("plancase", Sql.select, True, fields)
sql.appendWhereByJson({
'planid': planid,
"caseid": caseid,
"status": status
})
if not Sql.isEmpty(owner):
owner = '%%%s%%' % owner
sql.appendCondition("(owner like '%s')", (owner, ))
if not Sql.isEmpty(scenarioName):
caseTags = '%%%s%%' % scenarioName
sql.appendCondition("(tags like '%s' or scenario like '%s')",
(caseTags, caseTags))
if not Sql.isEmpty(caseName):
caseName = '%%%s%%' % caseName
sql.appendCondition("(name like '%s')", (caseName, ))
sql.orderBy('tags,scenario,caseid')
return sql.execute()
def countPlancase(self, planid=None, status=None):
sql = self.sqlConn.getSql("plancase", Sql.select, True,
'count(*) as count')
sql.appendWhereByJson({'planid': planid, "status": status})
return sql.execute()
def deletePlancase(self, plancaseid):
if Sql.isEmpty(plancaseid):
return 0
sql = self.sqlConn.getSql("plancase", Sql.delete, True)
sql.appendWhere("plancaseid", plancaseid)
return sql.execute()
def savePlandaily(self,
planid,
day,
status,
progress,
caseprogress,
costtime,
costman,
summary,
issues,
dailyId=None):
sql = self.sqlConn.getSql(
"plandaily", Sql.insert if Sql.isEmpty(dailyId) else Sql.update,
True)
sql.appendValueByJson({
'planid': planid,
"day": day,
"status": status,
"progress": progress,
'caseprogress': caseprogress,
'costtime': costtime,
'costman': costman,
'summary': summary,
'issues': issues,
"dailyId": dailyId
})
sql.appendWhere("dailyId", dailyId)
return sql.execute()
def getPlandaily(self,
planid=None,
day=None,
dailyId=None,
limit=None,
status=None):
sql = self.sqlConn.getSql("plandaily", Sql.select, True)
sql.appendWhereByJson({
'planid': planid,
"dailyId": dailyId,
'day': day,
"status": status
})
sql.limit = limit
sql.orderBy("day DESC")
return sql.execute()
# test env
def getTestEnv(self,
envname,
hostip,
vmaccount,
owner,
ownerStatus,
fnid,
nid1,
nid2,
testenvid=None):
sql = self.sqlConn.getSql("testenv", Sql.select, True)
sql.appendWhereByJson({
'hostip': hostip,
'owner': owner,
'ownerStatus': ownerStatus
})
sql.startCondition().appendWhere(
'fnid', fnid,
isAnd=False).appendWhere('nid1', nid1, isAnd=False).appendWhere(
'nid2', nid2, isAnd=False).endCondition()
if not Sql.isEmpty(vmaccount):
vmaccount = '%%%s%%' % vmaccount
sql.appendCondition("(vmaccount like '%s')", (vmaccount, ))
if not Sql.isEmpty(envname):
envname = '%%%s%%' % envname
sql.appendCondition("(envname like '%s' or tags like '%s')",
(envname, envname))
# 'vmammounts': vmammounts
# 'ownerStartTime': ownerStartTime, 'ownerEndTime': ownerEndTime,
sql.appendWhere("testenvid", testenvid)
return sql.execute()
def saveTestEnv(self,
envname,
tags,
hostip,
hostaccount,
hostinfo,
vmaccount,
vmammounts,
vminfo,
owner,
ownerStatus,
ownerInfo,
ownerStartTime,
ownerEndTime,
fnid,
nid1,
nid2,
testenvid=None):
sql = self.sqlConn.getSql(
"testenv", Sql.insert if Sql.isEmpty(testenvid) else Sql.update,
True)
sql.appendValueByJson({
'envname': envname,
'tags': tags,
'hostip': hostip,
'hostaccount': hostaccount,
'hostinfo': hostinfo,
'vmaccount': vmaccount,
'vmammounts': vmammounts,
'vminfo': vminfo,
'owner': owner,
'ownerStatus': ownerStatus,
'ownerInfo': ownerInfo,
'ownerStartTime': ownerStartTime,
'ownerEndTime': ownerEndTime,
'fnid': fnid,
'nid1': nid1,
'nid2': nid2
})
sql.appendWhere("testenvid", testenvid)
return sql.execute()
def deleteTestEnv(self, testenvid):
if Sql.isEmpty(testenvid):
return 0
sql = self.sqlConn.getSql("testenv", Sql.delete, True)
sql.appendWhere("testenvid", testenvid)
return sql.execute()
# test config
def getTestConfig(self,
subject=None,
stype=None,
cname=None,
ckey=None,
fnid=None,
nid1=None,
nid2=None,
status=None,
fields='*',
configid=None):
sql = self.sqlConn.getSql("cconfig", Sql.select, True, fields)
sql.appendWhereByJson({
'configid': configid,
'stype': stype,
'status': status
})
sql.startCondition().appendWhere(
'fnid', fnid,
isAnd=False).appendWhere('nid1', nid1, isAnd=False).appendWhere(
'nid2', nid2, isAnd=False).endCondition()
if not Sql.isEmpty(subject):
subject = '%%%s%%' % subject
sql.appendCondition("(subject like '%s')", (subject, ))
if not Sql.isEmpty(cname):
cname = '%%%s%%' % cname
sql.appendCondition("(cname like '%s' or calias like '%s')",
(cname, cname))
if not Sql.isEmpty(ckey):
ckey = '%%%s%%' % ckey
sql.appendCondition("(ckey like '%s')", (ckey, ))
sql.orderBy("subject,ckey,cname")
return sql.execute()
def saveTestConfig(self,
cname,
calias,
status,
subject,
ckey,
stype,
ccontent,
owner,
fnid,
nid1,
nid2,
configid=None):
modifytime = TimmerOperation.getFormatTime(time.time())
sql = self.sqlConn.getSql(
"cconfig", Sql.insert if Sql.isEmpty(configid) else Sql.update,
True)
sql.appendValueByJson({
'cname': cname,
'calias': calias,
'status': status,
'subject': subject,
'ckey': ckey,
'stype': stype,
'ccontent': ccontent,
"owner": owner,
'modifytime': modifytime,
'fnid': fnid,
'nid1': nid1,
'nid2': nid2
})
sql.appendWhere("configid", configid)
return sql.execute()
def deleteTestConfig(self, configid):
if Sql.isEmpty(configid):
return 0
sql = self.sqlConn.getSql("cconfig", Sql.delete, True)
sql.appendWhere("configid", configid)
return sql.execute()
# deploy
def saveCdeploy(self,
version=None,
procode=None,
proname=None,
protype=None,
branch=None,
brancharg=None,
pendtime=None,
creator=None,
owner=None,
notifyer=None,
remark=None,
attach=None,
fnid=None,
nid1=None,
nid2=None,
phase=None,
status=None,
deployarg=None,
deploytimes=None,
isSetModifytime=False,
deployid=None):
creattime = TimmerOperation.getFormatTime(
time.time()) if deployid is None else None
modifytime = TimmerOperation.getFormatTime(
time.time()) if isSetModifytime else None
creator = creator if deployid is None else None
sql = self.sqlConn.getSql(
"cdeploy", Sql.insert if Sql.isEmpty(deployid) else Sql.update,
True)
sql.appendValueByJson({
'version': version,
'procode': procode,
'proname': proname,
'protype': protype,
'branch': branch,
'brancharg': brancharg,
'pendtime': pendtime,
"creator": creator,
"owner": owner,
"notifyer": notifyer,
"remark": remark,
'attach': attach,
'creattime': creattime,
'modifytime': modifytime,
"phase": phase,
"status": status,
"deployarg": deployarg,
'deploytimes': deploytimes,
'fnid': fnid,
'nid1': nid1,
'nid2': nid2
})
sql.appendWhere("deployid", deployid)
return sql.execute()
def getCdeploy(self,
branchversion=None,
projectremark=None,
phase=None,
creator=None,
fnid=None,
nid1=None,
nid2=None,
cstarttime=None,
cendtime=None,
deployid=None):
sql = self.sqlConn.getSql("cdeploy", Sql.select, True)
sql.appendWhereByJson({'deployid': deployid})
if phase == '9':
sql.appendWhere("phase", 4, "!=")
else:
sql.appendWhere("phase", phase)
sql.startCondition().appendWhere(
'fnid', fnid,
isAnd=False).appendWhere('nid1', nid1, isAnd=False).appendWhere(
'nid2', nid2, isAnd=False).endCondition()
if creator is not None:
sql.appendCondition(
"(creator='%s' or owner='%s' or notifyer like '%%%s%%')",
(creator, creator, creator))
if not Sql.isEmpty(branchversion):
branchversion = '%%%s%%' % branchversion
sql.appendCondition("(branch like '%s' or version like '%s')",
(branchversion, branchversion))
if not Sql.isEmpty(projectremark):
projectremark = '%%%s%%' % projectremark
sql.appendCondition("(procode like '%s' or remark like '%s')",
(projectremark, projectremark))
self._makeTimeBetween(sql, cstarttime, cendtime, "creattime",
"pendtime")
sql.orderBy("version,procode")
return sql.execute()
def deleteCdeploy(self, deployid, creator):
if Sql.isEmpty(deployid):
return 0
sql = self.sqlConn.getSql("cdeploy", Sql.delete, True)
sql.appendWhere("deployid", deployid).appendWhere("creator", creator)
return sql.execute()
sqlStr,
isSelect=True,
isFethall=True,
isCommit=True,
dbName=None):
import base64
return curlCservice(self.host,
None,
urlPath=self.path,
isCheckResp=True,
base64Sql=base64.encodestring(sqlStr),
dbName=dbName,
dbconfig=self.dbconfig)
def __reConnect__(self):
self.host = self.sqlConfig['host']
self.path = self.sqlConfig['path']
self.dbconfig = self.sqlConfig['dbconfig']
if __name__ == '__main__':
from db.sqllib import SqlConnFactory
conn = SqlConnFactory()
conn.setSqlclass(
SqlToolProxy, {
'host': '172.16.12.124:8085',
'path': '/cservice/OnlineSqlTool/onlineExecuteSql',
'dbconfig': "dev"
}, 1)
print conn.executeSql("show tables", dbName='mysql')