如何使用PyoDBC将变量作为列名传递？

Question

我有一个有两个电话号码的列表，我想将每个电话号码放入访问数据库中的自己的列中。列名为phone_number1和phone_number2。如何将其传递给插入语句？

phone_numbers = ['###.218.####', '###.746.####']

driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
filepath = 'C:/Users/Notebook/Documents/master.accdb'
myDataSources = pyodbc.dataSources()
access_driver = myDataSources['MS Access Database']

conn = pyodbc.connect(driver=driver, dbq=filepath)
cursor = conn.cursor()

phone_number_count = 1
for phone_number in phone_numbers:
    column_name = "Phone_Number" + str(phone_number_count)
    cursor.execute("INSERT INTO Business_Cards (column_name) VALUES (?)", (phone_number))

conn.commit()
print("Your database has been updated.")

这就是我到目前为止的。

Traceback (most recent call last):
  File "C:/Users/Notebook/PycharmProjects/Jarvis/BusinessCard.py", line 55, in <module>
    database_entry(phone_numbers, emails, name, title)
  File "C:/Users/Notebook/PycharmProjects/Jarvis/BusinessCard.py", line 47, in database_entry
    cursor.execute("INSERT INTO Business_Cards (column_name) VALUES (?)", (phone_number))
pyodbc.Error: ('HYS22', "[HYS22] [Microsoft][ODBC Microsoft Access Driver] The INSERT INTO statement contains the following unknown field name: 'column_name'. Make sure you have typed the name correctly, and try the operation again. (-1507) (SQLExecDirectW)")

Answer 1

如果要在同一行中插入两个数字，请删除for循环，然后调整insert以考虑两列：

phone_numbers = ['###.218.####', '###.746.####']
# ...
column_names = [f"PhoneNumber{i}" for i in range(1, len(phone_numbers) + 1)]
placeholders = ['?'] * len(phone_numbers)
cursor.execute(f"INSERT INTO Business_Cards ({', '.join(column_names)}) VALUES ({', '.join(placeholders)})", tuple(phone_numbers))

conn.commit()
# ...