pandaspython-2.7ms-accesspypyodbc

Retrieving and printing a value from an Access database


I'm trying to retrieve and print a row from an Access database. I want the user to input an ID and a field then a value to be printed.

This is my code so far...

import pypyodbc
import pandas

conn = pypyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Operator\Documents\T31_DB.accdb;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM [TABLE_SIGNAL_ID]')

data = cursor.fetchall()
df = pandas.DataFrame(data)
dfi = df.set_index([0], drop=False)
ID = raw_input("enter signal ID:")
ID = int()

res = dfi.iloc[[ID]]

print res

Is there a way I can keep the headings from Access so I can pull specific items from the row? don't want to rename the columns by hand because there are too many.

Any help appreciated.


Solution

  • I managed to do it but using SQL!

    import pandas as pd
    import pypyodbc
    import sqlalchemy
    
    conn = pypyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Operator\Documents\T31_DB.accdb;')
    
    df = pd.read_sql('SELECT * FROM [TABLE_SIGNAL_ID]', conn)
    dfi = df.set_index("signal_id", drop=False)
    
    ID = raw_input("enter signal ID:")
    
    res = dfi.loc[ID, ["signal_id", "abname", "system_id"]]
    
    print res