[known issue] column names that contain question marks

The Access user interface allows us to create columns with names that include question marks. Unfortunately, a bug in the Access ODBC driver causes it to choke on such column names. So if we have an existing table named [customer] …

id  first name  active?
--  ----------  -------
 1  Emily       False  

… and we try to use .to_sql() to upload a DataFrame to that table …

df = pd.DataFrame([(2, "Gord", True)], columns=["id", "first name", "active?"])
df.to_sql("customer", engine, if_exists="append", index=False)

… we get the error

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('The SQL contains 4 parameter markers, but 3 parameters were supplied', 'HY000')
[SQL: INSERT INTO customer (id, [first name], [active?]) VALUES (?, ?, ?)]
[parameters: (2, 'Gord', True)]

We can work around this deficiency by using .to_sql() to upload the DataFrame to a temporary table and then use Access DAO to execute an INSERT INTO … SELECT query:

import urllib

import pandas as pd
import sqlalchemy as sa
import win32com.client

accdb_path = r"C:\Users\Public\Database1.accdb"
connection_string = (
    "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    f"DBQ={accdb_path};"
    "ExtendedAnsiSQL=1;"
)
connection_url = sa.engine.URL.create(
    "access+pyodbc", query={"odbc_connect": connection_string}
)
engine = sa.create_engine(connection_url)

table_name = "customer"
temp_table_name = "__temp"

df = pd.DataFrame([(2, "Gord", True)], columns=["id", "first name", "active?"])

name_map = {}
cols_to_rename = {}
for x in df.columns:
    if "?" in x:
        name_map[x] = urllib.parse.quote(x)
        cols_to_rename[x] = name_map[x]
    else:
        name_map[x] = x
print(name_map)
# {'id': 'id', 'first name': 'first name', 'active?': 'active%3F'}
print(cols_to_rename)
# {'active?': 'active%3F'}
df = df.rename(columns=cols_to_rename)

df.to_sql(temp_table_name, engine, if_exists="replace", index=False)

source_column_list = ", ".join(f"[{x}]" for x in name_map.values())
dest_column_list = ", ".join(f"[{x}]" for x in name_map.keys())
sql = f"""\
INSERT INTO [{table_name}] ({dest_column_list})
SELECT {source_column_list} FROM [{temp_table_name}]
"""
print(sql)
"""
INSERT INTO [customer] ([id], [first name], [active?])
SELECT [id], [first name], [active%3F] FROM [__temp]
"""

dbe = win32com.client.Dispatch("DAO.DBEngine.120")
db = dbe.OpenDatabase(accdb_path)
db.Execute(sql)
db.Execute(f"DROP TABLE [{temp_table_name}]")
db.Close()