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.
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.
My SQL server column type which contains all values is a string.
The name of the column is RESOLUTION_CODE, but contains ReasonCodes as well.
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.
Example JSON Output where 39 is resolution but should be reason.
{
"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"
"39"into"ResolutionCode"."ReasonCode"and"ResolutionCode"are being constructed.