提取MS访问表并将其放入python的数据帧中 [英] Pulling MS access tables and putting them in data frames in python

问题描述

我已经尝试了许多不同的方法来从Access中提取数据并将其放入一个整洁的数据帧中.现在我的代码看起来像这样.

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.

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)

这会将数据放入数据帧，但第二行是列表.我需要以下代码段位于4个单独的列中，而不是2个带有列表的地方.

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.

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

我曾经使用过像kdb_utils这样的模块，该模块具有read_query函数，它从kdb中提取数据并将其分离为整洁的数据帧.是否有类似这样的访问方式或另一种提取数据并将其整齐地放入数据帧的方法?

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?

推荐答案

考虑使用熊猫的直接

Consider using pandas' direct read_sql method:

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

这篇关于提取MS访问表并将其放入python的数据帧中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！