exporting pandas dataframe into a access table using to_sql generate error

Question

I am trying to use sqlalchemy-access library to insert a data frame into an access database:

The code that I have is:

import msaccessdb
import pyodbc
import pandas as pd

print(pyodbc.version)
db_file = r'database\sampledatabase.accdb'
input_csv_file= r'database\sample_data.csv'
print(input_csv_file)
msaccessdb.create(db_file)
cnxn_str = (
    r'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
    r'DBQ={};'
    r'ExtendedAnsiSQL=1;'.format(db_file)
)
print(cnxn_str)
cnxn = pyodbc.connect(cnxn_str,autocommit=True)
input_data=pd.read_csv(input_csv_file)
input_data.to_sql('sample_table', cnxn, index=False, if_exists='replace')
cnxn.close()

but when I run this code, I am getting this error:

Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot find the input table or query 'sqlite_master'. Make sure it exists and that its name is spelled correctly. (-1305) (SQLExecDirectW)")

the error is generated when I am trying to run this line:

input_data.to_sql('sample_table', cnxn, index=False, if_exists='replace')

what is wrong with this code and how can I fix it?

Edit 1

Based on the comments and this post, I changed the code to this:

import msaccessdb
import pandas as pd
from sqlalchemy import create_engine
import urllib

db_file = r'database\sampledatabase.accdb'
input_csv_file= r'database\sample_data.csv'

msaccessdb.create(db_file)
cnxn_str = (
    r'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
    r'DBQ={};'
    r'ExtendedAnsiSQL=1;'.format(db_file)
)
params = urllib.parse.quote_plus(cnxn_str)
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

print(cnxn_str)
create_engine("access+pyodbc://@your_dsn")
input_data=pd.read_csv(input_csv_file)
input_data.to_sql('sample_table', engine, index=False, if_exists='replace')

but I am still getting error:

(pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Undefined function 'schema_name' in expression. (-3102) (SQLExecDirectW)")
[SQL: SELECT schema_name()]
(Background on this error at: http://sqlalche.me/e/14/f405)

Edit 2

copied what was stated here: https://github.com/gordthompson/sqlalchemy-access/wiki/Getting-Connected#connecting-with-an-odbc-connection-string

so the code looks this now:

import msaccessdb
import pandas as pd
from sqlalchemy import create_engine
import urllib

db_file = r'database\sampledatabase.accdb'
input_csv_file= r'database\sample_data.csv'

msaccessdb.create(db_file)
connection_string = (
    r'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
    r'DBQ=database\sample_data.csv;'
    r'ExtendedAnsiSQL=1;'
)
connection_uri = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}"
engine = create_engine(connection_uri)

input_data=pd.read_csv(input_csv_file)
input_data.to_sql('sample_table', engine, index=False, if_exists='replace')

but I am still getting this error:

(pyodbc.Error) ('IM012', '[IM012] [Microsoft][ODBC Driver Manager] DRIVER keyword syntax error (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/14/dbapi)

it should be noted that I have installed Access on my system.

Looks similar to this: https://stackoverflow.com/questions/45326026/to-sql-pandas-data-frame-into-sql-server-error-databaseerror — perl, May 29 '21 at 09:21
`from sqlalchemy import create_engine` `engine = create_engine("access+pyodbc://@your_dsn")` (from https://pypi.org/project/sqlalchemy-access/) — perl, May 29 '21 at 09:27
@perl Thanks for your help. It did not work as planed! Please see me mu edit. Any other suggestion? — mans, May 29 '21 at 09:54
https://github.com/gordthompson/sqlalchemy-access/wiki/Getting-Connected, see 'Connecting with an ODBC connection string' — perl, May 29 '21 at 09:58
@perl Thanks for your help and patience, I am still having a problem, please see edit 2 in question. — mans, May 29 '21 at 10:11

Gord Thompson · Accepted Answer · 2021-05-29T12:26:00.813

You have two sets of curly brackets surrounding the driver name …

connection_string = (
    r'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
    r'DBQ=database\sampledatabase.accdb;'
    r'ExtendedAnsiSQL=1;'
)
connection_uri = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}"
engine = create_engine(connection_uri)

… but because you are using an r'string' (not an f'string') you should only have one pair of curly brackets:

connection_string = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=database\sampledatabase.accdb;'
    r'ExtendedAnsiSQL=1;'
)
connection_uri = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}"
engine = create_engine(connection_uri)

exporting pandas dataframe into a access table using to_sql generate error

Edit 1

Edit 2

1 Answers1