Can't UPDATE date =? SQL server from JSON with Python-CodePudding

Please help, have tried a lot of different ways to solve the problem - can't insert date in column with date/datetime/datetimeoffset db in SQL server. I have different errors depending on tries:

pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC SQL Server Driver]Optional feature not implemented (0) (SQLBindParameter)')

pyodbc.DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

this is origin type of json i've tried datetime.date() i've tried cast in query but all in vain found suggestion to add {SQL Server Native Client 11.0} the same convertion error

tried this:

def main():
    for i in list(range(0,40,1)):
        for item in voucherlist(bearer, accept, i)['content']:
            #voucherId=item['id']
            dataFrameVoucherItems=pandas.DataFrame(vouchers(bearer, accept, voucherId))
            pmnt = payment(bearer, accept, voucherId)
            dataFrame = pandas.concat([dataFrameVouchers, dataFrameVoucherItems], ignore_index= True, sort = False)
            tuples = [tuple(x) for x in dataFrame.to_numpy()]
            f = '%Y-%m-%d'
            print(item['voucherDate'])
            year = int(item['voucherDate'][:4])
            month = int(item['voucherDate'][6:7])
            day = int(item['voucherDate'][9:10])
            voucherDate = str(datetime.date(year,month,day))
            date = datetime.datetime.strptime(voucherDate,f)
            print(date)
            a = (voucherId, pmnt, voucherId,date)
            for t in tuples:
                t = a   t
                print(t)
                cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=' server ';DATABASE=' database ';UID=' username ';PWD='  password)
                cursor = cnxn.cursor()
                query = "IF EXISTS (SELECT * FROM vouchers WHERE voucher = ?) BEGIN SET DATEFORMAT ydm UPDATE vouchers SET openAmount = ?, voucherDate= ? WHERE voucher = ? END ELSE BEGIN INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount,taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, filesChar, createdDate, updatedDate, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END"
                cursor.execute(query,t)
                cnxn.commit()
                cursor.close()

no result

sql query is executed

I have new error

[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type datetime to decimal is not allowed. Use the CONVERT function to run this query.

when wrote

query = "IF EXISTS (SELECT * FROM vouchers WHERE voucher = ?) BEGIN SET DATEFORMAT ydm UPDATE vouchers SET openAmount = convert(datetime,?,105), voucherDate= ? WHERE voucher = ? END ELSE BEGIN INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount,taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, filesChar, createdDate, updatedDate, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END"

and one more:

pyodbc.DataError: ('22018', '[22018] [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: date is incompatible with decimal (206) (SQLExecDirectW); [22018] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)')

CodePudding user response：

I've found solution thanks this post How to create a Date in SQL Server given the Day, Month and Year as Integers

for item in voucherlist(bearer, accept, i)['content']:
        voucherId=item['id']
        dataFrameVoucherItems=pandas.DataFrame(vouchers(bearer, accept, voucherId))
        pmnt = payment(bearer, accept, voucherId)
        dataFrame = pandas.concat([dataFrameVouchers, dataFrameVoucherItems], ignore_index= True, sort = False)
        #dataFrame.to_csv(filename, sep='\t', encoding ='utf-8')
        tuples = [tuple(x) for x in dataFrame.to_numpy()]
        f = '%Y-%m-%d %H:%M:%S.%f %Z'
        f2 = '%Y-%m-%d'
        voucherYear = int(item['voucherDate'][:4])
        voucherMonth = int(item['voucherDate'][6:7])
        voucherDays = int(item['voucherDate'][9:10])
        a = (voucherId, voucherYear, voucherMonth, voucherDays, pmnt, voucherId)
        for t in tuples:
            t = a   t
            print(t)
            cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=' server ';DATABASE=' database ';UID=' username ';PWD='  password)
            cursor = cnxn.cursor()
            query = "IF EXISTS (SELECT * FROM vouchers WHERE voucher = ?) BEGIN declare @year int = ?,  @month int = ?, @days int = ? UPDATE vouchers SET openAmount = ?, voucherDate= cast(cast(@year*10000   @month*100   @days as varchar(255)) as date) WHERE voucher = ? END ELSE BEGIN INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount,taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, filesChar, createdDate, updatedDate, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END"
            cursor.execute(query,t)
            cnxn.commit()
            cursor.close()

CodePudding user response：

have found better solution! thanks: Python does not match format '%Y-%m-%dT%H:%M:%S%Z.%f'

for i in list(range(0,40,1)):
    for item in voucherlist(bearer, accept, i)['content']:
        voucherId=item['id']
        dataFrameVoucherItems=pandas.DataFrame(vouchers(bearer, accept, voucherId))
        pmnt = payment(bearer, accept, voucherId)
        dataFrame = pandas.concat([dataFrameVouchers, dataFrameVoucherItems], ignore_index= True, sort = False)
        dataFrame.info()
        #dataFrame.to_csv(filename, sep='\t', encoding ='utf-8')
        tuples = [tuple(x) for x in dataFrame.to_numpy()]
        #f = '%Y-%m-%dT%H:%M:%S.%f %Z'
        f1=datetime.fromisoformat(item['voucherDate'])
        print(f1)
        a = (voucherId, f1, pmnt, voucherId)
        for t in tuples:
            t = a   t
            print(t)
            cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=' server ';DATABASE=' database ';UID=' username ';PWD='  password)
            cursor = cnxn.cursor()
            query = "IF EXISTS (SELECT * FROM vouchers WHERE voucher = ?) BEGIN declare @year datetime = ? UPDATE vouchers SET openAmount = ?, voucherDate= @year WHERE voucher = ? END ELSE BEGIN INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount,taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, filesChar, createdDate, updatedDate, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END"
            cursor.execute(query,t)
            cnxn.commit()
            cursor.close()