我正在编写一个脚本来动态查询MS Access数据库并将结果返回到熊猫数据帧中。将有两个参数将动态更改查询。 我使用字符串格式化方法将以下解决方案放在一起。虽然此解决方案有效,但我正在寻找一种不使用字符串方法的更安全的解决方案。 感谢您可以分享的任何知识!
谢谢。
values1 = ('1','2')
values2 = ('1','2','3')
ServerName = r'pathtodb\database.mdb'
connStr = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' %ServerName
cnxn = pyodbc.connect(connStr)
query = 'SELECT * FROM TABLE WHERE item in ({0}) and item2 in ({1})'
query = query.format(','.join('?' * len(values1)), ','.join('?' * len(values2)))
param_list = values1 + values2
df = pd.read_sql_query(query, cnxn, params=param_list)
print(df)
考虑使用临时表、item1 和 items2 来保存您的项目值,然后简单地加入熊猫导入。事实上,JOIN
方法将比IN()
子句中的长列表更有效。
values1 = ('1','2')
values2 = ('1','2','3')
ServerName = r'C:pathtodbdatabase.mdb'
connStr = 'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={0};'.format(ServerName)
cnxn = pyodbc.connect(connStr)
cur = cnxn.cursor()
# CLEAN OUT OLD DATA AND APPEND NEW DATA
cur.execute('DELETE FROM items1')
cnxn.commit()
cur.executemany('INSERT INTO items1 ([item]) VALUES (?)', values1)
cnxn.commit()
cur.execute('DELETE FROM items2')
cnxn.commit()
cur.executemany('INSERT INTO items2 ([item]) VALUES (?)', values2)
cnxn.commit()
# IMPORT JOIN QUERY INTO PANDAS (PARENTHESES ARE REQUIRED)
query = '''SELECT * FROM (TABLE t
INNER JOIN items1 i1 ON t.[item] = i1.[item])
INNER JOIN items2 i2 ON t.[item2] = i2.[item]
'''
df = pd.read_sql_query(query, cnxn)
print(df)
替代查询:
query = '''SELECT * FROM TABLE
WHERE item IN (SELECT [item] FROM items1)
AND item2 IN (SELECT [item] FROM items2)
'''