def ExecuteMany():
da = DataAccess()
args = [('gyb1', 33, 1), ('张三', 50, 0), ('李四', 70, 1)]
cmdText = ""
for each in args:
cmdText += "INSERT INTO Student(name, age,sex) VALUES ('%s',%s,%s);" % each
rowcount = da.ExecuteScalar(cmdText)
print(rowcount)
def Test():
da = DataAccess()
cmdText = "SELECT now();"
# res = da.ExecuteScalar(cmdText)
res = da.ExecuteScalarByConn(cmdText)
print(res)
print(res['effect_row'])
print(res['rows'])
def ExecuteNonQueryByParams():
da = DataAccess()
name = 'gyb'
age = 10
cmdText = "SELECT * FROM Student s WHERE name=%s AND age=%s"
params = (name, age)
rowcount = da.ExecuteNonQuery(cmdText, params)
rowcount = da.ExecuteNonQueryByConn(cmdText, params)
print(rowcount)
def ExecuteProc():
da = DataAccess()
print("--------------------------------------------")
isFlag = True
weight = 2.2
amount = 3.3
args = (1, "雇佣兵", isFlag, dt.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
weight, amount)
cmdText = "proc_test"
res = da.ExecuteStoredProcdure(cmdText, args)
print(res)
print("--------------------------------------------")
cmdText = "p1"
res = da.ExecuteStoredProcdure(cmdText)
print(res)
def getOrg(orgID=None):
da = DataAccess()
if dbType == DBType.MYSQL:
cmdText = '''SELECT o.OrgID,o.OrgCode,o.OrgName,o.ParentOrgID
FROM org o
where o.OrgType=%s'''
elif dbType == DBType.SQLSERVER:
cmdText = '''SELECT o.OrgID,o.OrgCode,o.OrgName,o.ParentID
FROM t_Org o
where o.OrgType=%s '''
params = [3]
if not orgID is None:
cmdText += " AND o.OrgID=%s"
params.append(orgID)
print(params)
res = da.ExecuteNonQuery(cmdText, tuple(params))
return res
def ExecuteOneOrMany():
da = DataAccess()
cmdText = "INSERT INTO Student(name, age,sex) VALUES (%s,%s,%s)"
args = [('gyb', 10, 1), ('zhansan', 20, 0), ('lisi', 30, 1)]
rowcount = da.ExecuteMany(cmdText, args)
rowcount = da.ExecuteScalar(cmdText, ('test', 11, 1))
rowcount = da.ExecuteScalar(cmdText, ('gyb', 10, 1))
rowcount = da.ExecuteManyByConn(cmdText, args)
print(rowcount)
rowcount = da.ExecuteScalarByConn(cmdText, ('测试', 13, 1))
print(rowcount)
rowcount = da.ExecuteScalarByConn(cmdText, ('雇佣兵', 14, 1))
print(rowcount)
def ExecuteScalarByParams():
da = DataAccess()
id = 1
name = 'gyb'
age = 10
cmdText = "UPDATE Student s set age=age+2 WHERE id=%s AND s.name=%s"
params = (id, name)
rowcount = da.ExecuteScalar(cmdText, params)
rowcount = da.ExecuteScalarByConn(cmdText, params)
print(rowcount)
cmdText = "SELECT * FROM Student s WHERE name=%s AND age=%s"
params = (name, age)
rowcount = da.ExecuteScalar(cmdText, params)
rowcount = da.ExecuteScalarByConn(cmdText, params)
print(rowcount)
def getCustomer(orgID):
da = DataAccess()
if dbType == DBType.MYSQL:
cmdText = '''SELECT 'kds3' AS 来源系统, cb.CompBranchCodeSAP AS 户头SAP代码, cb.CompBranchName AS 户头名称,
f.Id AS 客户ID,f.Code AS 客户编码,f.Name AS 客户名称,
REPLACE(SUBSTR(cust.StandardCustAttribute, INSTR(cust.StandardCustAttribute, '"CustChannelTypeName":') + 22,
INSTR(cust.StandardCustAttribute, '"CustChannel":') - INSTR(cust.StandardCustAttribute, '"CustChannelTypeName":') - 23), '"', '') AS 渠道类型,
REPLACE(SUBSTR(cust.StandardCustAttribute,
INSTR(cust.StandardCustAttribute, '"CustChannelName":') + 18,
INSTR(cust.StandardCustAttribute, '"CustArea":') - INSTR(cust.StandardCustAttribute, '"CustChannelName":') - 19), '"', '') AS 客户类型,
rDic.省, rDic.市, rDic.`县/区`, rDic.`镇/街道`, rDic.村,
cust.Address AS 客户地址,
p.Name AS 联系人, p.Mobile AS 联系电话
FROM companycustomers cust
INNER JOIN firms f ON cust.Id = f.Id
INNER JOIN companybranch cb ON cust.CompanyBranchCompBranchID = cb.CompBranchID
INNER JOIN distributor d on cb.DistributorID = d.DistributorID
INNER JOIN orgdistributorcontractrelationship ship ON d.DistributorContractID = ship.DistributorContractID and ship.IsValid = 1
LEFT JOIN firmcontacts fc on f.Id = fc.FirmId
LEFT JOIN persons p on p.Id = fc.Id
LEFT JOIN
(
SELECT r.Id, r.Name AS 省, '' AS 市, '' AS '县/区', '' AS '镇/街道', '' AS 村 FROM regions r where r.Level = 1 and r.IsValid = 1
UNION ALL
SELECT r.Id, r1.Name AS 省, r.Name AS 市, '' AS '县/区', '' AS '镇/街道', '' AS 村 from regions r
LEFT JOIN regions r1 ON r.RegionId = r1.Id AND r1.IsValid = 1 AND r1.Level = 1
WHERE r.Level = 2 AND r.IsValid = 1
union all
SELECT r.Id, r2.Name AS 省, r1.Name AS 市, r.Name AS '县/区', '' AS '镇/街道', '' AS 村 FROM regions r
LEFT JOIN regions r1 ON r.RegionId = r1.Id AND r1.IsValid = 1 AND r1.Level = 2
LEFT JOIN regions r2 ON r1.RegionId = r2.Id AND r2.IsValid = 1 AND r2.Level = 1
WHERE r.Level = 3 AND r.IsValid = 1
union all
SELECT r.Id, r3.Name AS 省, r2.Name AS 市, r1.Name AS '县/区', r.Name AS '镇/街道', '' AS 村 FROM regions r
LEFT JOIN regions r1 ON r.RegionId = r1.Id AND r1.IsValid = 1 AND r1.Level = 3
LEFT JOIN regions r2 ON r1.RegionId = r2.Id AND r2.IsValid = 1 and r2.Level = 2
LEFT JOIN regions r3 ON r2.RegionId = r3.Id AND r3.IsValid = 1 AND r3.Level = 1
WHERE r.Level = 4 AND r.IsValid = 1
union all
SELECT r.Id, r4.Name AS 省, r3.Name AS 市, r2.Name AS '县/区', r1.Name AS '镇/街道', r.Name AS 村 FROM regions r
LEFT JOIN regions r1 ON r.RegionId = r1.Id AND r1.IsValid = 1 AND r1.Level = 4
LEFT JOIN regions r2 ON r1.RegionId = r2.Id AND r2.IsValid = 1 AND r2.Level = 3
LEFT JOIN regions r3 ON r2.RegionId = r3.Id AND r3.IsValid = 1 AND r3.Level = 2
LEFT JOIN regions r4 ON r3.RegionId = r4.Id AND r4.IsValid = 1 AND r4.Level = 1
WHERE r.Level = 5 AND r.IsValid = 1
) rDic ON cust.RegionId = rDic.Id
WHERE ship.OrgID = %s AND ship.IsValid=1 AND ship.BussLineID = 100000042 AND cust.RegionId is NOT NULL;'''
params = (orgID)
elif dbType == DBType.SQLSERVER:
cmdText = '''SELECT *
FROM (
SELECT
'KDS2' AS 来源系统
,cb.CompBranchCode_SAP '户头sap代码'
,cb.CompBranchName AS '户头名称'
,tcb.CustID
,CustCode AS '客户编码'
,CustName AS '客户名称'
,tcct.CustChannelTypeName AS '渠道类型'
,tcc.CustChannelName AS '客户类型'
,provice.AreaName AS '省',city.AreaName AS '市',ta.AreaName AS '县/区','' AS '镇/街道','' AS '村'
,tce.DeliveryAddr AS '客户地址'
,tce.CustContact AS '联系人'
,tce.MobilePhone AS '联系电话'
FROM dbo.T_CustomerBase tcb
INNER JOIN dbo.T_CustomerExt tce ON tcb.CustID=tce.CustID
INNER JOIN dbo.t_CompanyBranch cb ON tcb.CompBranchID=cb.CompBranchID
INNER JOIN dbo.t_CustomerChannel tcc ON tcb.CustChannelID=tcc.CustChannelID
INNER JOIN dbo.t_CustomerChannelType tcct ON tcc.CustChannelTypeID=tcct.CustChannelTypeID
INNER JOIN dbo.t_Area ta ON tcb.AreaID=ta.AreaID AND ta.AreaLevel=3
INNER JOIN dbo.t_Area city ON ta.ParentID =city.AreaID AND city.AreaLevel=2
INNER JOIN dbo.t_Area provice ON city.ParentID=provice.AreaID AND provice.AreaLevel=1
INNER JOIN dbo.t_Distributor td ON cb.DistributorID=td.DistributorID
INNER JOIN dbo.t_DistributorContract tdc ON td.DistributorContractID=tdc.DistributorContractID
INNER JOIN dbo.t_Org org ON tdc.OrgBranchID=org.OrgID
WHERE org.OrgID=%s AND tcb.Disabled=0
UNION
SELECT
'KDS2' AS 来源系统
,cb.CompBranchCode_SAP '户头sap代码'
,cb.CompBranchName AS '户头名称'
,tcb.CustID
,CustCode AS '客户编码'
,CustName AS '客户名称'
,tcct.CustChannelTypeName AS '渠道类型'
,tcc.CustChannelName AS '客户类型'
,provice.AreaName AS '省',city.AreaName AS '市',county.AreaName AS '县/区',ta.AreaName AS '镇/街道','' AS '村'
,tce.DeliveryAddr AS '客户地址'
,tce.CustContact AS '联系人'
,tce.MobilePhone AS '联系电话'
FROM dbo.T_CustomerBase tcb
INNER JOIN dbo.T_CustomerExt tce ON tcb.CustID=tce.CustID
INNER JOIN dbo.t_CompanyBranch cb ON tcb.CompBranchID=cb.CompBranchID
INNER JOIN dbo.t_CustomerChannel tcc ON tcb.CustChannelID=tcc.CustChannelID
INNER JOIN dbo.t_CustomerChannelType tcct ON tcc.CustChannelTypeID=tcct.CustChannelTypeID
INNER JOIN dbo.t_Area ta ON tcb.AreaID=ta.AreaID AND ta.AreaLevel=4
INNER JOIN dbo.t_Area county ON ta.ParentID=county.AreaID AND county.AreaLevel=3
INNER JOIN dbo.t_Area city ON county.ParentID =city.AreaID AND city.AreaLevel=2
INNER JOIN dbo.t_Area provice ON city.ParentID=provice.AreaID AND provice.AreaLevel=1
INNER JOIN dbo.t_Distributor td ON cb.DistributorID=td.DistributorID
INNER JOIN dbo.t_DistributorContract tdc ON td.DistributorContractID=tdc.DistributorContractID
INNER JOIN dbo.t_Org org ON tdc.OrgBranchID=org.OrgID
WHERE org.OrgID=%s AND tcb.Disabled=0
UNION
SELECT
'KDS2' AS 来源系统
,cb.CompBranchCode_SAP '户头sap代码'
,cb.CompBranchName AS '户头名称'
,tcb.CustID
,CustCode AS '客户编码'
,CustName AS '客户名称'
,tcct.CustChannelTypeName AS '渠道类型'
,tcc.CustChannelName AS '客户类型'
,provice.AreaName AS '省',city.AreaName AS '市',county.AreaName AS '县/区',town.AreaName AS '镇/街道',ta.AreaName AS '村'
,tce.DeliveryAddr AS '客户地址'
,tce.CustContact AS '联系人'
,tce.MobilePhone AS '联系电话'
FROM dbo.T_CustomerBase tcb
INNER JOIN dbo.T_CustomerExt tce ON tcb.CustID=tce.CustID
INNER JOIN dbo.t_CompanyBranch cb ON tcb.CompBranchID=cb.CompBranchID
INNER JOIN dbo.t_CustomerChannel tcc ON tcb.CustChannelID=tcc.CustChannelID
INNER JOIN dbo.t_CustomerChannelType tcct ON tcc.CustChannelTypeID=tcct.CustChannelTypeID
INNER JOIN dbo.t_Area ta ON tcb.AreaID=ta.AreaID AND ta.AreaLevel=5
INNER JOIN dbo.t_Area town ON ta.ParentID=town.AreaID AND town.AreaLevel=4
INNER JOIN dbo.t_Area county ON town.ParentID=county.AreaID AND county.AreaLevel=3
INNER JOIN dbo.t_Area city ON county.ParentID =city.AreaID AND city.AreaLevel=2
INNER JOIN dbo.t_Area provice ON city.ParentID=provice.AreaID AND provice.AreaLevel=1
INNER JOIN dbo.t_Distributor td ON cb.DistributorID=td.DistributorID
INNER JOIN dbo.t_DistributorContract tdc ON td.DistributorContractID=tdc.DistributorContractID
INNER JOIN dbo.t_Org org ON tdc.OrgBranchID=org.OrgID
WHERE org.OrgID=%s AND tcb.Disabled=0
) temp
ORDER BY temp.CustID ASC
'''
params = (orgID, orgID, orgID)
res = da.ExecuteNonQuery(cmdText, params)
return res
def ExecuteScalar():
da = DataAccess()
cmdText = "Truncate table Student;"
res = da.ExecuteScalar(cmdText)
# res = da.ExecuteScalarByConn(cmdText)
print(res)