How to resolve SQL bind error when using an pyodbc UPDATE command to update an sql table?

Question

My initial problem occured when I was when I attempted to use pyodbc to update values in a table using a sql UPDATE statement. The error is occuring because the variable MonthNum that I am using is an integer value. The error that occurs is:

pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access
Driver]Optional feature not implemented (106) (SQLBindParameter)')

I changed this variable type to a float in order to resolve the problem and in the database changed the field type to a double to match. I then received this error:

pyodbc.DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access
Driver] Data type mismatch in criteria expression. (-3030) (SQLExecDirectW)')

My code is given 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()

Not sure what the proper solution is here but either method I use it does not seem to work? Any suggestions?

How to resolve SQL bind error when using an pyodbc UPDATE command to update an sql table?

0 Answers0