英文:
Fail to get the correct insert statment for my Access database
问题
我想要从我的Excel文件导入数据到我的Access数据库中的多个表格。我已经将要导入的数据从我的xlsx文件映射到了Access数据表中的列名，但不知何故插入语句总是出错。你能帮助我吗？
#导入必要的模块
import pandas as pd
import pyodbc
# ODBC连接
connection_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\ArtiPro.accdb;'
conn = pyodbc.connect(connection_string)
def import_excel_to_access(excel_file_path, table_mappings):
# 将Excel文件读取到pandas DataFrame中
df = pd.read_excel(r'C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\A001.xlsx')
# 创建游标以执行SQL查询
cursor = conn.cursor()
for table_name, column_mapping in table_mappings.items():
# 准备带有选定列的INSERT查询
insert_query = f"INSERT INTO {table_name} ({', '.join(column_mapping.values())}) VALUES ({', '.join(['?' for _ in column_mapping.values()])})"
print(insert_query)
# 将数据插入Access表格
for row in df.rename(columns=column_mapping)[column_mapping.values()].itertuples(index=False):
cursor.execute(insert_query, row)
print(insert_query)
# 提交更改并关闭连接
conn.commit()
conn.close()
if __name__ == '__main__':
excel_file_path = r'C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\A001.xlsx'
table_mappings = {
'01_Basic Data': {
'SID': 'SID',
'1': 'AD intake',
'2a': 'AD trade name',
'2b': 'AD substance',
'3a': 'Drug Dose (in mg)',
'3b': 'Drug Rhythm',
'4': 'Age',
'5': 'Gender',
'6': 'Ethnicity'
},
'02_Medication Effect': {
'SID': 'SID',
'7': 'Q7',
'8': 'Q8',
'8a': 'Q8a',
'9': 'Q9',
'9a': 'Q9a',
'10': 'Q10',
'10a': 'Q10a',
'11': 'Q11',
'11a': 'Q11a',
'12': 'Q12',
'12a': 'Q12a',
'13': 'Q13',
'13a': 'Q13a',
'14': 'Q14',
'14a': 'Q14a',
'15': 'Q15',
'16': 'Q16',
},
'03_ADRs': {
'SID': 'SID',
'NW_frei': 'ADRs (free speech)',
'17': 'Q17_dry mouth',
'17a': 'Q17a_dry mouth',
'17b': 'Q17b_dry mouth',
'17c': 'Q17c_dry mouth',
'17d': 'Q17d_dry mouth',
'17e': 'Q17e_dry mouth',
'18': 'Q18_blurred vision',
'18a': 'Q18a_blurred vision',
'18b': 'Q18b_blurred vision',
'18c': 'Q18c_blurred vision',
'18d': 'Q18d_blurred vision',
'18e': 'Q18e_blurred vision',
'19': 'Q19_dizziness',
'19a': 'Q19a_dizziness',
'19b': 'Q19b_dizziness',
'19c': 'Q19c_dizziness',
'19d': 'Q19d_dizziness',
'19e': 'Q19e_dizziness',
'20': 'Q20_fall',
'20a': 'Q20a_fall',
'20b': 'Q20b_fall',
'20c': 'Q20c_fall',
'20d': 'Q20d_fall',
'20e': 'Q20e_fall',
'21': 'Q21_increased appetite',
'21a': 'Q21a_increased appetite',
'21b': 'Q21b_increased appetite',
'21c': 'Q21c_increased appetite',
'21d': 'Q21d_increased appetite',
'21e': 'Q21e_increased appetite',
'22': 'Q22_reduced appetite',
'22a': 'Q22a_reduced appetite',
'22b': 'Q22b_reduced appetite',
'22c': 'Q22c_reduced appetite',
'22d': 'Q22d_reduced appetite',
'22e': 'Q22e_reduced appetite',
'23': 'Q23_weight gain',
'23a': 'Q23a_weight gain',
'23b': 'Q23b_weight gain',
'23c': 'Q23c_weight gain',
'23d': 'Q23d_weight gain',
'23e': 'Q23e_weight gain',
'24': 'Q24_weight loss',
'24a': 'Q24a_weight loss',
'24b': 'Q24b_weight loss',
'24c': 'Q24c_weight loss',
'24d': 'Q24d_weight loss',
'24e': 'Q24e_weight loss',
'25': 'Q25_nausea',
'25a': 'Q25a_nausea',
'25b': 'Q25b_nausea',
'25c': 'Q25c_nausea',
'25d': 'Q25d_nausea',
'25e': 'Q25e_nausea',
'26': 'Q26_vomiting',
'26a': 'Q26a_vomiting',
'26b': 'Q26b_vomiting',
'26c': 'Q26c_vomiting',
'26d': 'Q26d_vomiting',
'26e': 'Q26e_vomiting',
'27': 'Q27_bleeding',
'27a': 'Q27a_bleeding',
'27aa1': 'Q27aa1_bleeding',
'27aa2': 'Q27aa2_bleeding',
'27aa3': 'Q27aa3_bleeding',
'27aa4': 'Q27aa4_bleeding',
'
<details>
<summary>英文:</summary>
I want to import data from my Excel file to several tables in my access database. I already mapped all data I wanted to import from my xlsx.file to the column names in the access data tables, but somehow fail to get the insert statement right. Could you help me?
#import necessary modules
import pandas as pd
import pyodbc
ODBC connection
connection_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\ArtiPro.accdb;'
conn = pyodbc.connect(connection_string)
def import_excel_to_access(excel_file_path, table_mappings):
# Read the Excel file into a pandas DataFrame
df = pd.read_excel(r'C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\A001.xlsx')
# Create a cursor to execute SQL queries
cursor = conn.cursor()
for table_name, column_mapping in table_mappings.items():
# Prepare the INSERT query with selected columns
insert_query = f"INSERT INTO table_name ({', '.join(column_mapping.values())}) VALUES ({', '.join(['?' for _ in column_mapping.values()])})"
print(insert_query)
# Insert data into the Access table
for row in df.rename(columns=column_mapping)[column_mapping.values()].itertuples(index=False):
cursor.execute(insert_query, row)
print(insert_query)
# Commit the changes and close the connection
conn.commit()
conn.close()
if name == 'main':
excel_file_path = r'C:\Users\User\Downloads\OneDrive_2023-07-24\Fragebogen Rohdaten\A001.xlsx'
table_mappings = {
'01_Basic Data': {
'SID': 'SID',
'1': 'AD intake',
'2a': 'AD trade name',
'2b': 'AD substance',
'3a': 'Drug Dose (in mg)',
'3b': 'Drug Rhythm',
'4': 'Age',
'5': 'Gender',
'6': 'Ethnicity'
},
'02_Medication Effect': {
'SID': 'SID',
'7': 'Q7',
'8': 'Q8',
'8a': 'Q8a',
'9': 'Q9',
'9a': 'Q9a',
'10': 'Q10',
'10a': 'Q10a',
'11': 'Q11',
'11a': 'Q11a',
'12': 'Q12',
'12a': 'Q12a',
'13': 'Q13',
'13a': 'Q13a',
'14': 'Q14',
'14a': 'Q14a',
'15': 'Q15',
'16': 'Q16',
},
'03_ADRs': {
'SID': 'SID',
'NW_frei': 'ADRs (free speech)',
'17': 'Q17_dry mouth',
'17a': 'Q17a_dry mouth',
'17b': 'Q17b_dry mouth',
'17c': 'Q17c_dry mouth',
'17d': 'Q17d_dry mouth',
'17e': 'Q17e_dry mouth',
'18': 'Q18_blurred vision',
'18a': 'Q18a_blurred vision',
'18b': 'Q18b_blurred vision',
'18c': 'Q18c_blurred vision',
'18d': 'Q18d_blurred vision',
'18e': 'Q18e_blurred vision',
'19': 'Q19_dizziness',
'19a': 'Q19a_dizziness',
'19b': 'Q19b_dizziness',
'19c': 'Q19c_dizziness',
'19d': 'Q19d_dizziness',
'19e': 'Q19e_dizziness',
'20': 'Q20_fall',
'20a': 'Q20a_fall',
'20b': 'Q20b_fall',
'20c': 'Q20c_fall',
'20d': 'Q20d_fall',
'20e': 'Q20e_fall',
'21': 'Q21_increased appetite',
'21a': 'Q21a_increased appetite',
'21b': 'Q21b_increased appetite',
'21c': 'Q21c_increased appetite',
'21d': 'Q21d_increased appetite',
'21e': 'Q21e_increased appetite',
'22': 'Q22_reduced appetite',
'22a': 'Q22a_reduced appetite',
'22b': 'Q22b_reduced appetite',
'22c': 'Q22c_reduced appetite',
'22d': 'Q22d_reduced appetite',
'22e': 'Q22e_reduced appetite',
'23': 'Q23_weight gain',
'23a': 'Q23a_weight gain',
'23b': 'Q23b_weight gain',
'23c': 'Q23c_weight gain',
'23d': 'Q23d_weight gain',
'23e': 'Q23e_weight gain',
'24': 'Q24_weight loss',
'24a': 'Q24a_weight loss',
'24b': 'Q24b_weight loss',
'24c': 'Q24c_weight loss',
'24d': 'Q24d_weight loss',
'24e': 'Q24e_weight loss',
'25': 'Q25_nausea',
'25a': 'Q25a_nausea',
'25b': 'Q25b_nausea',
'25c': 'Q25c_nausea',
'25d': 'Q25d_nausea',
'25e': 'Q25e_nausea',
'26': 'Q26_vomiting',
'26a': 'Q26a_vomiting',
'26b': 'Q26b_vomiting',
'26c': 'Q26c_vomiting',
'26d': 'Q26d_vomiting',
'26e': 'Q26e_vomiting',
'27': 'Q27_bleeding',
'27a': 'Q27a_bleeding',
'27aa1': 'Q27aa1_bleeding',
'27aa2': 'Q27aa2_bleeding',
'27aa3': 'Q27aa3_bleeding',
'27aa4': 'Q27aa4_bleeding',
'27aa5': 'Q27aa5_bleeding',
'27aa6': 'Q27aa6_bleeding',
'27b': 'Q27b_bleeding',
'27c': 'Q27c_bleeding',
'27d': 'Q27d_bleeding',
'27e': 'Q27e_bleeding',
'28': 'Q28_constipation',
'28a': 'Q28a_constipation',
'28b': 'Q28b_constipation',
'28c': 'Q28c_constipation',
'28d': 'Q28d_constipation',
'28e': 'Q28e_constipation',
'29': 'Q29_diarrhoea',
'29a': 'Q29a_diarrhoea',
'29b': 'Q29b_diarrhoea',
'29c': 'Q29c_diarrhoea',
'29d': 'Q29d_diarrhoea',
'29e': 'Q29e_diarrhoea',
'30': 'Q30_urinary incontinence',
'30a': 'Q30a_urinary incontinence',
'30b': 'Q30b_urinary incontinence',
'30c': 'Q30c_urinary incontinence',
'30d': 'Q30d_urinary incontinence',
'30e': 'Q30e_urinary incontinence',
'31': 'Q31_urinary retention',
'31a': 'Q31a_urinary retention',
'31b': 'Q31b_urinary retention',
'31c': 'Q31c_urinary retention',
'31d': 'Q31d_urinary retention',
'31e': 'Q31e_urinary retention',
'33': 'Q33',
'weitere NW_frei': 'Notes'
}
}
import_excel_to_access(excel_file_path, table_mappings)
But somehow it fails me every time, tried a lot already
INSERT INTO table_name (SID, AD intake, AD trade name, AD substance, Drug Dose (in mg), Drug Rhythm, Age, Gender, Ethnicity) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
ProgrammingError Traceback (most recent call last)
<ipython-input-24-68b37adef976> in <module>
174
175
--> 176 import_excel_to_access(excel_file_path, table_mappings)
<ipython-input-24-68b37adef976> in import_excel_to_access(excel_file_path, table_mappings)
22 # Insert data into the Access table
23 for row in df.rename(columns=column_mapping)[column_mapping.values()].itertuples(index=False):
---> 24 cursor.execute(insert_query, row)
25
26 print(insert_query)
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC-Treiber für Microsoft Access] Syntaxfehler in der INSERT INTO-Anweisung. (-3502) (SQLExecDirectW)')
</details>
# 答案1
**得分**: 0
需要在某些字段名称周围加上*括号*：
```sql
INSERT INTO table_name (SID, [AD intake], [AD trade name], [AD substance], [Drug Dose (in mg)], [Drug Rhythm], Age, Gender, Ethnicity) ...
英文:
You need brackets around some field names:
INSERT INTO table_name (SID, [AD intake], [AD trade name], [AD substance], [Drug Dose (in mg)], [Drug Rhythm], Age, Gender, Ethnicity) ...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论