import pandas as pd
import pyodbc

#List of ODBC drivers in your PC
print(pyodbc.drivers())
print()

#Character string used for DB connection
#In the string "{}"And"\, So it's better to use a raw string
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; ' +
    r'DBQ=(Drive name):\(Folder name)\ ～ \file name.accdb;'
)

#Use with statement to safely open and close DB
with pyodbc.connect(conn_str) as conn:

    #When using DataFrame
    print('*When using DataFrame')
    df = pd.io.sql.read_sql(r'select * from T_1', conn)
    print(df)
    print()

    print('*When using the cursor')
    with conn.cursor() as cur:
        #About each table
        for table_info in cur.tables(tableType='TABLE'):
            #Display contents with select statement
            #If you want to use both raw strings and f notation, "rf''To
            cur.execute(rf'select * from {table_info.table_name}')
            for row in cur.fetchall():
                print(row)

['SQL Server', 'SQL Server Native Client 11.0', 'Microsoft Access Driver (*.mdb, *.accdb)', 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)', 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)', 'Microsoft Access Text Driver (*.txt, *.csv)']

*When using DataFrame
   ID field1
0 1 Test value

*When using the cursor
(1, 'Test value')

Recommended Posts