I am working on updating a table in a database the update data looks like this:
[['CID', 'Status', 'JiraTicket', 'MrcNew', 'MonthBudget', 'BillingStartDate', 'VendorCompletion', 'FOC1', 'FOC2', 'OrderSubmitted'], ['OH1004-01', 'Planned', 'None', '', '10.0', '', '', '', '', '', 10], ['OH1004-02', 'Planned', 'None', '', '10.0', '', '', '', '', '', 10], ['OH1005-01', 'Planned', 'None', '', '10.0', '', '', '', '', '', 10]
The table data looks like
ID CircuitID PID MonthNum ActualMRC BudgetMRC YearNum Region
1049045 OH1004-01 OH1004-01 1.00 $0.00 $0.00 2019 South
1049046 OH1004-01 OH1004-01 2.00 $0.00 $0.00 2019 South
1049047 OH1004-01 OH1004-01 3.00 $0.00 $0.00 2019 South
1049048 OH1004-01 OH1004-01 4.00 $0.00 $0.00 2019 South
1049049 OH1004-01 OH1004-01 5.00 $0.00 $0.00 2019 South
1049050 OH1004-01 OH1004-01 6.00 $0.00 $0.00 2019 South
1049051 OH1004-01 OH1004-01 7.00 $0.00 $0.00 2019 South
1049052 OH1004-01 OH1004-01 8.00 $0.00 $0.00 2019 South
1049053 OH1004-01 OH1004-01 9.00 $0.00 $0.00 2019 South
1049054 OH1004-01 OH1004-01 10.00 $0.00 $0.00 2019 South
1049055 OH1004-01 OH1004-01 11.00 $0.00 $0.00 2019 South
1049056 OH1004-01 OH1004-01 12.00 $0.00 $0.00 2019 South
1049057 OH1004-02 OH1004-02 1.00 $0.00 $0.00 2019 South
1049058 OH1004-02 OH1004-02 2.00 $0.00 $0.00 2019 South
1049059 OH1004-02 OH1004-02 3.00 $0.00 $0.00 2019 South
1049060 OH1004-02 OH1004-02 4.00 $0.00 $0.00 2019 South
1049061 OH1004-02 OH1004-02 5.00 $0.00 $0.00 2019 South
1049062 OH1004-02 OH1004-02 6.00 $0.00 $0.00 2019 South
1049063 OH1004-02 OH1004-02 7.00 $0.00 $0.00 2019 South
1049064 OH1004-02 OH1004-02 8.00 $0.00 $0.00 2019 South
1049065 OH1004-02 OH1004-02 9.00 $0.00 $0.00 2019 South
1049066 OH1004-02 OH1004-02 10.00 $0.00 $0.00 2019 South
1049067 OH1004-02 OH1004-02 11.00 $0.00 $0.00 2019 South
1049068 OH1004-02 OH1004-02 12.00 $0.00 $0.00 2019 South
My code to insert this data into the database uses pyodbc and is below:
Traceback (most recent call last):
File "C:\Users\omitted on purpose", line 133, in <module>
cursor.execute(string, params)
pyodbc.DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. (-3030) (SQLExecDirectW)')
I had changed the MonthNum in the table to a double type in order to correct another error:
pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106) (SQLBindParameter)')
I have not been able to find a solution around this issue. I added a comment above the block of code that is causing this issue. I am using Python 3.7.3 thanks for checking out this post and any suggestions you might have.
[['CID', 'Status', 'JiraTicket', 'MrcNew', 'MonthBudget', 'BillingStartDate', 'VendorCompletion', 'FOC1', 'FOC2', 'OrderSubmitted'], ['OH1004-01', 'Planned', 'None', '', '10.0', '', '', '', '', '', 10], ['OH1004-02', 'Planned', 'None', '', '10.0', '', '', '', '', '', 10], ['OH1005-01', 'Planned', 'None', '', '10.0', '', '', '', '', '', 10]
The table data looks like
ID CircuitID PID MonthNum ActualMRC BudgetMRC YearNum Region
1049045 OH1004-01 OH1004-01 1.00 $0.00 $0.00 2019 South
1049046 OH1004-01 OH1004-01 2.00 $0.00 $0.00 2019 South
1049047 OH1004-01 OH1004-01 3.00 $0.00 $0.00 2019 South
1049048 OH1004-01 OH1004-01 4.00 $0.00 $0.00 2019 South
1049049 OH1004-01 OH1004-01 5.00 $0.00 $0.00 2019 South
1049050 OH1004-01 OH1004-01 6.00 $0.00 $0.00 2019 South
1049051 OH1004-01 OH1004-01 7.00 $0.00 $0.00 2019 South
1049052 OH1004-01 OH1004-01 8.00 $0.00 $0.00 2019 South
1049053 OH1004-01 OH1004-01 9.00 $0.00 $0.00 2019 South
1049054 OH1004-01 OH1004-01 10.00 $0.00 $0.00 2019 South
1049055 OH1004-01 OH1004-01 11.00 $0.00 $0.00 2019 South
1049056 OH1004-01 OH1004-01 12.00 $0.00 $0.00 2019 South
1049057 OH1004-02 OH1004-02 1.00 $0.00 $0.00 2019 South
1049058 OH1004-02 OH1004-02 2.00 $0.00 $0.00 2019 South
1049059 OH1004-02 OH1004-02 3.00 $0.00 $0.00 2019 South
1049060 OH1004-02 OH1004-02 4.00 $0.00 $0.00 2019 South
1049061 OH1004-02 OH1004-02 5.00 $0.00 $0.00 2019 South
1049062 OH1004-02 OH1004-02 6.00 $0.00 $0.00 2019 South
1049063 OH1004-02 OH1004-02 7.00 $0.00 $0.00 2019 South
1049064 OH1004-02 OH1004-02 8.00 $0.00 $0.00 2019 South
1049065 OH1004-02 OH1004-02 9.00 $0.00 $0.00 2019 South
1049066 OH1004-02 OH1004-02 10.00 $0.00 $0.00 2019 South
1049067 OH1004-02 OH1004-02 11.00 $0.00 $0.00 2019 South
1049068 OH1004-02 OH1004-02 12.00 $0.00 $0.00 2019 South
My code to insert this data into the database uses pyodbc and is below:
import datetime from datetime import timedelta import pandas as pd import numpy as np import pyodbc from csv import reader pd.set_option('display.max_columns', 8) pd.set_option('display.expand_frame_repr', False) pd.set_option('display.max_rows', 500) format_str = '%Y-%m-%d' YearStart = pd.Timestamp(datetime.date(2019, 1, 1)) today = datetime.date.today() conn = pyodbc.connect("ommitted on purpose") cursor = conn.cursor() SQL_Query = (pd.read_sql_query('''SELECT [CircuitID], [Status], [LatestJiraTicket], [MrcNew], [MonthBudget] FROM CircuitInfoTable WHERE ([Status] = 'Active') OR ([Status] = 'Pending') OR ([Status] = 'Planned')''', conn).set_axis(['CID', 'Status', 'JiraTicket', 'MrcNew', 'MonthBudget'], axis='columns', inplace=False)) cdf = pd.DataFrame(SQL_Query, columns=['CID', 'Status', 'JiraTicket', 'MrcNew', 'MonthBudget']) cdf['CID'] = cdf['CID'].astype(str) cdf['Status'] = cdf['Status'].astype(str) cdf['JiraTicket'] = cdf['JiraTicket'].astype(str) cdf['MrcNew'] = cdf['MrcNew'].astype(float) cdf['MonthBudget'] = cdf['MonthBudget'].astype(float) JiraSQL_Query = (pd.read_sql_query('''SELECT [JiraTicket], [BillingStartDate], [VendorCompletion], [FOC#1], [FOC#2], [OrderSubmitted] FROM Jira''', conn).set_axis(['JiraTicket', 'BillingStartDate', 'VendorCompletion', 'FOC1', 'FOC2', 'OrderSubmitted'], axis='columns', inplace=False)) jdf = pd.DataFrame(JiraSQL_Query, columns=['JiraTicket', 'BillingStartDate', 'VendorCompletion', 'FOC1', 'FOC2', 'OrderSubmitted']) jdf['JiraTicket'] = jdf['JiraTicket'].astype(str) mdf = pd.merge(cdf, jdf, left_on='JiraTicket', right_on='JiraTicket', how='left') filename = "omitted on purpose" mdf.to_csv(filename, encoding ='utf-8', index=False) opened_file = open(filename) read_file = reader(opened_file) circuitdata = list(read_file) count = 0 for row in circuitdata[1:]: CircuitID = row[0] Status = row[1] JiraTicket = row[2] MrcNew = row[3] if row[4]!='': MonthBudget = float(row[4]) if row[5]!='': BSD = datetime.datetime.strptime(row[5], format_str) else: BSD = '' if row[6]!='': VCD = datetime.datetime.strptime(row[6], format_str) else: VCD = '' if row[7]!='': FOC1 = datetime.datetime.strptime(row[7], format_str) else: FOC1 = '' if row[8]!='': FOC2 = datetime.datetime.strptime(row[8], format_str) else: FOC2 = '' if row[9]!='': OrderSubmitted = datetime.datetime.strptime(row[9], format_str) else: OrderSubmitted = '' if Status == 'Active': if BSD != '' and BSD < YearStart: month = 0 row.append(month) elif BSD != '' and BSD > YearStart: month = BSD.month row.append(month) elif VCD != '' and VCD < YearStart: month = 0 row.append(month) elif VCD != '' and VCD > YearStart: month = VCD.month row.append(month) else: month = 12 row.append(month) elif Status == 'Pending': if FOC2!='': month = FOC2.month row.append(month) elif FOC1 != '': month = FOC1.month row.append(month) elif OrderSubmitted != '': if OrderSubmitted.month < 9: month = OrderSubmitted.month + 4 row.append(month) else: month = 12 row.append(month) elif Status == 'Planned': if today.month <= float(MonthBudget): month = int(MonthBudget) row.append(month) else: if today.month < 9: month = today.month + 4 row.append(month) else:#catches all others month = 12 row.append(month) # print(str(count) + ', ' + CircuitID + ', ' + str(month)) count = count + 1 print(circuitdata) print('Month Calculation complete...') for row in circuitdata[1:]: Month = row[-1] CID = row[0] MRC = row[3] cursor.execute("SELECT * FROM CopyBudgetTable WHERE CircuitID = ?", CID) rows = cursor.fetchall() counter = float(Month) #code below causes the error for i in range(Month, 13): params = (MRC, CID, counter) print(params) if len(rows)>0: #string = "UPDATE CopyBudgetTable SET [ActualMRC] = " + MRC + " WHERE [CircuitID] = " + CID + " AND [MonthNum] = " + '[' + i + ']' string = "UPDATE CopyBudgetTable SET [ActualMRC] = [?] WHERE [CircuitID] = [?] AND [MonthNum] = [?]" cursor.execute(string, params) counter = counter + 1.0 opened_file.close() cursor.close() conn.close()The problem that occurs is a mismatch of data error:
Traceback (most recent call last):
File "C:\Users\omitted on purpose", line 133, in <module>
cursor.execute(string, params)
pyodbc.DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. (-3030) (SQLExecDirectW)')
I had changed the MonthNum in the table to a double type in order to correct another error:
pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106) (SQLBindParameter)')
I have not been able to find a solution around this issue. I added a comment above the block of code that is causing this issue. I am using Python 3.7.3 thanks for checking out this post and any suggestions you might have.