pandas 拉取 MS 访问表并将它们放入 python 中的数据框中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44790195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pulling MS access tables and putting them in data frames in python
提问by michaelg
I have tried many different things to pull the data from Access and put it into a neat data frame. right now my code looks like this.
我尝试了许多不同的方法来从 Access 中提取数据并将其放入一个整洁的数据框中。现在我的代码看起来像这样。
from pandas import DataFrame
import numpy as np
import pyodbc
from sqlalchemy import create_engine
db_file = r'C:\Users\username\file.accdb'
user = 'user'
password = 'pw'
odbc_conn_str = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;UID=%s;PWD=%s' % (db_file, user, password)
conn = pyodbc.connect(odbc_conn_str)
cur = conn.cursor()
qry = cur.execute("SELECT * FROM table WHERE INST = '796116'")
dataf = DataFrame(qry.fetchall())
print(dataf)
this puts the data into a data frame but the second row is a list. I need the snippet below to be in 4 separate columns, not 2 with a list.
这会将数据放入数据框中，但第二行是列表。我需要将下面的代码段放在 4 个单独的列中，而不是 2 个带有列表的列。
0 (u'RM257095', u'c1', u'796116')
1 (u'RM257097', u'c2', u'796116')
2 (u'RM257043', u'c3', u'796116')
3 (u'RM257044', u'c4', u'796116')
I have used modules like kdb_utils which has a read_query function and it pulled the data from kdb and separated it into a neat dataframe. Is there anything like this for access or another way to pull the data and neatly put it into a data frame?
我使用了像 kdb_utils 这样的模块，它有一个 read_query 函数，它从 kdb 中提取数据并将其分成一个整洁的数据帧。是否有这样的访问或其他方式来提取数据并将其整齐地放入数据框中？
回答by Parfait
Consider using pandas' direct read_sqlmethod:
考虑使用Pandas的直接read_sql方法：
import pyodbc
import pandas as pd
...
cnxn = pyodbc.connect('DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ=' + \
'{};Uid={};Pwd={};'.format(db_file, user, password)
query = "SELECT * FROM mytable WHERE INST = '796116'"
dataf = pd.read_sql(query, cnxn)
cnxn.close()
