Python MS Access Database Table Creation From Pandas Dataframe Using SQLAlchemy -

i'm trying create ms access database python , wondering if it's possible create table directly pandas dataframe. know can use pandas dataframe.to_sql() function write dataframe sqlite database or using sqlalchemy engine other database format (but not access unfortunately) can't pieces parts come together. here's code snippet i've been testing with:

import pandas pd import sqlalchemy import pypyodbc     # used create .mdb file import pyodbc  # connection function use sqlalchemy def connection():     mdb = 'c:\\database.mdb'     drv = '{microsoft access driver (*.mdb)}'     connection_string = 'driver={microsoft access driver (*.mdb)};dbq=%s' % mdb     return pyodbc.connect('driver={};dbq={}'.format(drv,mdb))   # try connect database try:     conn = connection() # if fails because not been created yet, create , connect except:     pypyodbc.win_create_mdb(mdb)     conn = connection()  # create sqlalchemy engine using pyodbc connection engine = sqlalchemy.create_engine('mysql+pyodbc://', creator=connection)  # dataframe data = {'values'     : [1., 2., 3., 4.],         'fruitsandpets'  : ["apples", "oranges", "puppies", "ducks"]} df = pd.dataframe(data)  # try send access database (and fail) df.to_sql('fruitsandpets', engine, index = false) 

i'm not sure i'm trying possible current packages i'm using wanted check here before write own hacky dataframe ms access table function. maybe sqlalchemy engine set wrong?

here's end of error mssql+pyodbc in engine:

cursor.execute(statement, parameters) sqlalchemy.exc.dbapierror: (error) ('hy000', "[hy000] [microsoft][odbc microsoft access driver] not find file 'c:\\information_schema.mdb'. (-1811) (sqlexecdirectw)") u'select [columns_1].[table_schema], [columns_1].[table_name], [columns_1].[column_name], [columns_1].[is_nullable], [columns_1].[data_type], [columns_1].[ordinal_position], [columns_1].[character_maximum_length], [columns_1].[numeric_precision], [columns_1].[numeric_scale], [columns_1].[column_default], [columns_1].[collation_name] \nfrom [information_schema].[columns] [columns_1] \nwhere [columns_1].[table_name] = ? , [columns_1].[table_schema] = ?' (u'fruitsandpets', u'dbo') 

and ending error mysql+pyodbc in engine:

cursor.execute(statement, parameters) sqlalchemy.exc.programmingerror: (programmingerror) ('42000', "[42000] [microsoft][odbc microsoft access driver] invalid sql statement; expected 'delete', 'insert', 'procedure', 'select', or 'update'. (-3500) (sqlexecdirectw)") "show variables 'character_set%%'" () 

just note, don't care if use sqlalchemy or pandas to_sql() looking easy way of getting dataframe ms access database easily. if that's dump json loop function insert rows using sql manually, whatever, if works i'll take it.

there short tutorial on pypyodbc website executing sql commands , populating access database:

• https://code.google.com/p/pypyodbc/wiki/pypyodbc_for_access_mdb_file

i found useful python wiki article:

• https://wiki.python.org/moin/microsoft%20access

it states mxodbc has capability work ms access. long time ago, believe used adodb connect ms access well.

a few years ago, sqlalchemy had experimental support microsoft access. used move access database ms sql server @ time. used sqlalchemy autoload / reflect database. super handy. believe code in version 0.5. can read bit what did here.