I have a Python script where I am using PyODBC to send data to a server via POST request. I have a column in my SQL server table that contains the values for two fields, and I need a Python or SQL expression to do so. My two fields are resolution code and reason code.
我有一个Python脚本,我使用PyODBC通过POST请求将数据发送到服务器。我的SQL服务器表中有一列包含两个字段的值,我需要一个Python或SQL表达式才能这样做。我的两个字段是解析代码和原因代码。
All reason codes are numbers, i.e. 1, 2, 3, 4, 39, 54, 6, 7, etc. All resolution codes are letters, i.e. A, B, C, RBI, Q, etc.
所有原因代码都是数字,即1,2,3,4,39,54,6,7等。所有分辨率代码都是字母,即A,B,C,RBI,Q等。
My SQL server column type which contains all values is a string.
我的包含所有值的SQL服务器列类型是一个字符串。
The name of the column is RESOLUTION_CODE, but contains ReasonCodes as well.
列的名称是RESOLUTION_CODE,但也包含ReasonCodes。
What is a simple expression to say; if RESOLUTION_CODE contains integer then RESOLUTION_CODE is reason code while still maintaining my original RESOLUTION_CODE values, these are independent of each other, so where ever there is a row with a REASON there is no RESOLUTION and vice versa.
什么是简单的表达方式;如果RESOLUTION_CODE包含整数,那么RESOLUTION_CODE是原因代码,同时仍然保持我原来的RESOLUTION_CODE值,这些值彼此独立,所以哪里有一个带有REASON的行没有RESOLUTION,反之亦然。
Example JSON Output where 39 is resolution but should be reason.
示例JSON输出,其中39是分辨率,但应该是理由。
{
"MetaData": {},
"SRData": {
"ListOfLa311MetalHouseholdAppliancesPickup": {
"La311MetalHouseholdAppliancesPickup": [
{
"DriverFirstName": "sal",
"DriverLastName": "Aguilar",
"HouseholdItem": "None",
"LastUpdatedBy": "SANSTAR1",
"Name": "063020150920409621",
"Type": "Metal/Household Appliances"
}
]
},
"ReasonCode": " ",
"ResolutionCode": "B",
"SRNumber": "1-20541231"
}
}
]
[
{
"MetaData": {},
"SRData": {
"ListOfLa311MetalHouseholdAppliancesPickup": {
"La311MetalHouseholdAppliancesPickup": [
{
"DriverFirstName": "SA",
"DriverLastName": "Aguilar",
"HouseholdItem": "None",
"LastUpdatedBy": "SANSTAR1",
"Name": "063020150550017771",
"Type": "Metal/Household Appliances"
}
]
},
"ReasonCode": " ",
"ResolutionCode": "RBI",
"SRNumber": "1-20529111"
}
}
]
[
{
"MetaData": {},
"SRData": {
"ListOfLa311MetalHouseholdAppliancesPickup": {
"La311MetalHouseholdAppliancesPickup": [
{
"DriverFirstName": "sal",
"DriverLastName": "Aguilar",
"HouseholdItem": "None",
"LastUpdatedBy": "SANSTAR1",
"Name": "063020150919014731",
"Type": "Metal/Household Appliances"
}
]
},
"ReasonCode": " ",
"ResolutionCode": "A",
"SRNumber": "1-20538411"
}
}
]
[
{
"MetaData": {},
"SRData": {
"ListOfLa311MetalHouseholdAppliancesPickup": {
"La311MetalHouseholdAppliancesPickup": [
{
"DriverFirstName": "SA",
"DriverLastName": "Aguilar",
"HouseholdItem": "None",
"LastUpdatedBy": "SANSTAR1",
"Name": "063020150856220561",
"Type": "Metal/Household Appliances"
}
]
},
"ReasonCode": " ",
"ResolutionCode": "39",
"SRNumber": "1-20539231"
}
}
]
[
{
"MetaData": {},
"SRData": {
"ListOfLa311MetalHouseholdAppliancesPickup": {
"La311MetalHouseholdAppliancesPickup": [
{
"DriverFirstName": "sal",
"DriverLastName": "Aguilar",
"HouseholdItem": "Trash Compactor",
"LastUpdatedBy": "SANSTAR1",
"Name": "063020150857132911",
"Type": "Metal/Household Appliances"
}
]
},
"ReasonCode": " ",
"ResolutionCode": "A",
"SRNumber": "1-20539291"
}
}
Script Sample:
connstr = 'DRIVER={SQL Server};SERVER=SVR;DATABASE=DB; UID=UID;PWD=PWD'
conn = pyodbc.connect(connstr)
cursor = conn.cursor()
FN_SRNumber = "SRNumber"
lFields = [FN_SRNumber, "RESOLUTION_CODE", "ITEM_1","ITEM_2", "ITEM_3", "ITEM_4", "ITEM_5", "ITEM_6","ITEM_7","ITEM_8", "ITEM_9", "ITEM_10", "UID","last_edited_user"]
#lFields = ["SRNumber"]
sFields = ""
for fld in lFields:
if(sFields==""):
sFields = fld
else:
sFields = sFields + "," + fld
#List of the fields you'd like to exclude, like ItemCount for example.
lFieldsExcluded = ["ITEM_1", "ITEM_2", "ITEM_3", "ITEM_4", "ITEM_5", "ITEM_6","ITEM_7","ITEM_8", "ITEM_9", "ITEM_10", "UID", "RESOLUTION_CODE", "last_edited_user"]
#sSQL = "SELECT SRNumber FROM {}".format(pyFC)
sSQL = "SELECT {} FROM {} {}".format(sFields, pyFC, clauseSBE)
print sSQL
cursor.execute(sSQL)
#columns = [column[0] for column in cursor.description]
columns = []
for column in cursor.description:
if not (column[0] in lFieldsExcluded):
columns.append(column[0])
try:
ii = 0
white_item_1 = ""
white_item_2 = ""
white_item_3 = ""
white_item_4 = ""
white_item_5 = ""
white_item_6 = ""
white_item_7 = ""
white_item_8 = ""
white_item_9 = ""
white_item_10 = ""
rescode = ""
reasoncode = " "
ewaste_uid = ""
last_edited_user = " "
for row in cursor.fetchall():
lResults = [] #make sure lResults are initiated here
lValues = []
for i in range(len(columns)):
lValues.append( str(row[i]))
white_item_1 = (str(row[lFields.index("ITEM_1")]))
white_item_2 = (str(row[lFields.index("ITEM_2")]))
white_item_3 = (str(row[lFields.index("ITEM_3")]))
white_item_4 = (str(row[lFields.index("ITEM_4")]))
white_item_5 = (str(row[lFields.index("ITEM_5")]))
white_item_6= (str(row[lFields.index("ITEM_6")]))
white_item_7 =(str(row[lFields.index("ITEM_7")]))
white_item_8 = (str(row[lFields.index("ITEM_8")]))
white_item_9 =(str(row[lFields.index("ITEM_9")]))
white_item_10 = (str(row[lFields.index("ITEM_10")]))
white_uid = (str(row[lFields.index("UID")]))
rescode = (str(row[lFields.index("RESOLUTION_CODE")])
last_edited_user = (str(row[lFields.index("last_edited_user")]))
#dResult = dict(zip(columns, row))
dResult = dict(zip(columns, lValues))
dResult.setdefault("ReasonCode", reasoncode)
dResult.setdefault("ResolutionCode",rescode )
d = dict()
d.setdefault("DriverFirstName",last_edited_user )
d.setdefault("DriverLastName","Aguilar" )
d.setdefault("LastUpdatedBy", "SANSTAR1")
d.setdefault("HouseholdItem", white_item_1)
d.setdefault("Type", "Metal/Household Appliances")
d.setdefault("Name", white_uid )
l311.append(d)
dL311 = dict()
dL311.setdefault("La311MetalHouseholdAppliancesPickup", l311)
dResult.setdefault("ListOfLa311MetalHouseholdAppliancesPickup",dL311)
except:
print "failed"
1 个解决方案
#1
1
Use if expression to achieve this, for example -
使用if表达式来实现这一点,例如 -
dResult.setdefault("ReasonCode", rescode if rescode.isdigit() else "")
dResult.setdefault("ResolutionCode",rescode if not rescode.isdigit() else "")
If you want to set empty string for the opposite case (that is if reasoncode is set, then resolution code to be set with empty string.
如果要为相反的情况设置空字符串(即如果设置了原因码,则使用空字符串设置分辨率代码。
#1
1
Use if expression to achieve this, for example -
使用if表达式来实现这一点,例如 -
dResult.setdefault("ReasonCode", rescode if rescode.isdigit() else "")
dResult.setdefault("ResolutionCode",rescode if not rescode.isdigit() else "")
If you want to set empty string for the opposite case (that is if reasoncode is set, then resolution code to be set with empty string.
如果要为相反的情况设置空字符串(即如果设置了原因码,则使用空字符串设置分辨率代码。