TypeError: not all arguments converted during string formatting in vertica

I'm trying to insert some values into my vertica database using the vertica_python module:


data = {'SalesNo':  ['12345', '678910'],
        'ProductID': ['12345_2021-10-21_08:51:22', '678910_2021-10-21_10:27:03'],
        'StoreID': ['6d522936e240cd64e1cf9176c5bfdff3bfe8146a345ff2', 'a7274d507d443c752be66b2851415138d75bd913d4949e'],
        'PurchaseTime': ['2021-10-21 08:51:22.846000', '2021-10-21 10:44:06.218000'],
        'Date': ['2021-10-21', '2021-10-21'],
        'StoreNumber': ['0', '1'],
        'PurchaseValue': ['348.0', '4893.23']
        }

dataset = pd.DataFrame(data)

column = dataset.columns
n = len(column)
SQL_insert = f"INSERT INTO table_name ({','.join(list(column))}) VALUES ({' ?,'*(n-1)} ?);"

valueTuplelst = [] 
for i,row in dataset.iterrows():
    valuelist = list(map(lambda x: str(x), row))
    valuelist = [None if element == "None" else element for element in valuelist]
    valuetup = tuple(valuelist)
    valueTuplelst.append(valuetup)

connection.cursor().executemany(SQL_insert, valueTuplelst)

The equivalent SQL statement is

INSERT INTO table_name (SalesNo,ProductID,StoreID,PurchaseTime,Date,StoreNumber,PurchaseValue) VALUES ('12345', '12345_2021-10-21_08:51:22', '6d522936e240cd64e1cf9176c5bfdff3bfe8146a345ff2', '2021-10-21 08:51:22.846000', '2021-10-21', '0', '348.0'), ('678910', '678910_2021-10-21_10:27:03', 'a7274d507d443c752be66b2851415138d75bd913d4949e', '2021-10-21 10:44:06.218000', '2021-10-21', '1', '4893.23')

which works perfectly in SQL when I execute it. However I get the error

File "C:\tools\Anaconda3\lib\site-packages\vertica_python\vertica\cursor.py", line 576, in format_operation_with_parameters
    operation = operation % tuple(tlist)
TypeError: not all arguments converted during string formatting

I can't seem to figure out why I get this error as I convert all of my data to string format. Any idea where I'm going wrong?

CodePudding user response：

My guess is that you should use %s and not ? as placeholder in your string:

SQL_insert = f"INSERT INTO table_name ({','.join(list(column))}) VALUES ({' %s,'*(n-1)} %s);"

Then the output string will be 'INSERT INTO table_name (SalesNo,ProductID,StoreID,PurchaseTime,Date,StoreNumber,PurchaseValue) VALUES ( %s, %s, %s, %s, %s, %s, %s);', which is compatible with operation = operation % tuple(tlist) replacement