Pulling MS access tables and putting them in data frames in python
22,263
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()
Author by
michaelg
Updated on June 28, 2020Comments
-
michaelg almost 4 years
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)
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')
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?