pyodbc.Error SQLBindParameter

[pcarra]

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:

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.