Pulling MS access tables and putting them in data frames in python

Question

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?

Try dataf = DataFrame([tuple(x) for x in qry.fetchall()])

Gord Thompson
– Gord Thompson

2017-06-27 22:21:06 +00:00
Commented Jun 27, 2017 at 22:21 — Gord Thompson
– Gord Thompson, Commented Jun 27, 2017 at 22:21

Parfait · Accepted Answer · 2017-06-28 02:08:42Z

13

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

answered Jun 28, 2017 at 2:08

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pulling MS access tables and putting them in data frames in python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related