Creating a Python MS Access Python database table from a Pandas Dataframe using SQLAlchemy

Question

I am trying to create a MS Access database from Python and wondered if it is possible to create a table directly from the pandas data frame. I know that I can use the pandas dataframe.to_sql() function to successfully write data to the SQLite database or using the sqlalchemy mechanism for some other database format (but not access unfortunately), but I can’t get all the parts go together. Here is the code snippet that I tested:

 import pandas as pd import sqlalchemy import pypyodbc # Used to actually create the .mdb file import pyodbc # Connection function to use for 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 to connect to the database try: Conn = Connection() # If it fails because its not been created yet, create it and connect to it except: pypyodbc.win_create_mdb(MDB) Conn = Connection() # Create the sqlalchemy engine using the pyodbc connection Engine = sqlalchemy.create_engine('mysql+pyodbc://', creator=Connection) # Some dataframe data = {'Values' : [1., 2., 3., 4.], 'FruitsAndPets' : ["Apples", "Oranges", "Puppies", "Ducks"]} df = pd.DataFrame(data) # Try to send it to the access database (and fail) df.to_sql('FruitsAndPets', Engine, index = False) 

I'm not sure that what I'm trying to do is possible even with the current packages that I use, but I wanted to check here before writing my own hacker framework in the MS Access table function. Maybe my sqlalchemy engine is configured incorrectly?

Here is the end of my error with mssql+pyodbc in the engine:

 cursor.execute(statement, parameters) sqlalchemy.exc.DBAPIError: (Error) ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] Could 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] AS [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?' (u'FruitsAndPets', u'dbo') 

and the final error for mysql+pyodbc in the 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 LIKE 'character_set%%'" () 

I just want to note that I don’t care if I use sqlalchemy or pandas to_sql() . I'm just looking for an easy way to easily get the database in my MS Access database. If this dump is for JSON, then the loop function for inserting rows using SQL manually, regardless of whether it works well, I'll take it.

Answer 1

The pypyodbc website has a short tutorial for executing SQL commands and populating the Access database:

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

I also found this useful Python wiki article:

• https://wiki.python.org/moin/Microsoft%20Access

It states that mxODBC also has the ability to work with MS Access. I have long believed that I have successfully used ADOdb to connect to MS Access.

A few years ago, SQLAlchemy received experimental support from Microsoft Access. I used it to move the Access database to MS SQL Server at that time. I used SQLAlchemy to automatically load / display the database. It was very convenient. I believe the code was in version 0.5. You can read a little what I did here .

Answer 2

For those who are still studying this, basically you cannot use the pandas to_sql method for MS Access without too much difficulty. If you decide to do it this way, here is the link where someone fixed the sqlalchemy Access dialect (and presumably the OP code will work with this engine):

connecting sqlalchemy to MSAccess

The best way to get the data frame in MS Access is to create INSERT records from the records, and then just connect via pyodbc or pypyodbc and execute them with the cursor. You should do the inserts one at a time, it might be best to break it into pieces (around 5000) if you have a lot of data.