TypeError:在vertica_python的字符串格式化过程中不是所有的参数都转换

Question

我正在尝试使用vertica_python模块插入一些值到我的vertica数据库:

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)   

对应的SQL语句是

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')

当我执行时，它在SQL中完美地工作。但是我得到了错误

File "C:toolsAnaconda3libsite-packagesvertica_pythonverticacursor.py", line 576, in format_operation_with_parameters
operation = operation % tuple(tlist)
TypeError: not all arguments converted during string formatting

我似乎不能弄清楚为什么我得到这个错误，因为我把所有的数据转换成字符串格式。知道我哪里做错了吗?

Answer 1

我的猜测是，您应该使用%s而不是?作为字符串中的占位符:

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

则输出字符串为'INSERT INTO table_name (SalesNo,ProductID,StoreID,PurchaseTime,Date,StoreNumber,PurchaseValue) VALUES ( %s, %s, %s, %s, %s, %s, %s);'，兼容operation = operation % tuple(tlist)替换