CODEWITHSUNDEEP
pythonpyodbc
Brandon Jacobson
Brandon Jacobson

Reputation: 159

How to pass a variable as a column name with pyodbc?

I have a list that has two phone numbers and I'd like to put each phone number into its own column in an Access database. The column names are Phone_Number1 and Phone_Number2. How do I pass that to the INSERT statement?

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.")

This is what I have so far.

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

Upvotes: 0

Views: 320

Answers (1)

Dami&#227;o Martins
Dami&#227;o Martins

Reputation: 1849

If you want to insert both numbers in the same row, remove the for loop and adjust the INSERT to consider the two columns:

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()
# ...

Upvotes: 1

Related Questions