1

I keep encountering this error upon executing df.to_sql.I want to append existing data from a csv file into a MS DB file (accdb). Can you please help me out? I cant see anywhere in the internet a solution about this error.The DB has been created already, with column names but no data yet.

TypeError: has_table() got an unexpected keyword argument 'info_cache'

See Error See Error

Here's my code:

import sqlalchemy as sa
import pandas as pd

connection_string = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:/Users/usrname/Desktop/Folder/test.accdb;"
    r"ExtendedAnsiSQL=1;"
)

connection_url = sa.engine.URL.create(
    "access+pyodbc",
    query={"odbc_connect": connection_string}
)

engine = sa.create_engine(connection_url)

df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
df.to_sql('users', con=engine)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Could you add the version of `sqlalchemy` ? (using for example `pip show sqlalchemy`) – Achille G Jan 30 '23 at 08:43
  • Name: SQLAlchemy, Version: 2.0.0 –  Jan 30 '23 at 08:47
  • Works for me with SQLA 2.0 and pandas-1.5.3. – snakecharmerb Jan 30 '23 at 09:03
  • I downgraded to 1.4.16 and it seemed to solved the error but now im getting this: DBAPIError: (pyodbc.Error) ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106) (SQLBindParameter)') –  Jan 30 '23 at 09:03
  • @snakecharmerb weird, same version of both. –  Jan 30 '23 at 09:10
  • Hmm, maybe it's pyodbc that's the problem then - if you share the complete traceback we can be certain. – snakecharmerb Jan 30 '23 at 09:12
  • I was able to bypass the error but when I tried to use an actual dataframe to export to access db I got that pyodbc error. When I check the accdb, the columns were successfully created, but the rows were not :( [error1](https://i.stack.imgur.com/5XGYU.png) [error2](https://i.stack.imgur.com/4At9f.png) [error3](https://i.stack.imgur.com/JXv5h.png) –  Jan 31 '23 at 01:38

2 Answers2

1

TypeError: AccessDialect.has_table() got an unexpected keyword argument 'info_cache'

This error was caused by a bug in sqlalchemy-access 2.0.0. It has been fixed in sqlalchemy-access 2.0.1.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I have recently tried upgrading to the latest version but I am still encountering this error. I have downgraded again to 1.14.16 –  Feb 21 '23 at 02:47
0

I found this and it might help. It seems that SQLAlchemy verifies the database types prior the insert. There might be discrepancy with the df types and the database itself when trying to bulk insert the data. Try using the 'dtype' mapping of 'to_sql'.

SQLAlchemy Reflection Line 401


    def has_table(
        self, table_name: str, schema: Optional[str] = None, **kw: Any
    ) -> bool:
        r"""Return True if the backend has a table, view, or temporary
        table of the given name.
        :param table_name: name of the table to check
        :param schema: schema name to query, if not the default schema.
        :param \**kw: Additional keyword argument to pass to the dialect
         specific implementation. See the documentation of the dialect
         in use for more information.
        .. versionadded:: 1.4 - the :meth:`.Inspector.has_table` method
           replaces the :meth:`_engine.Engine.has_table` method.
        .. versionchanged:: 2.0:: :meth:`.Inspector.has_table` now formally
           supports checking for additional table-like objects:
           * any type of views (plain or materialized)
           * temporary tables of any kind
           Previously, these two checks were not formally specified and
           different dialects would vary in their behavior.   The dialect
           testing suite now includes tests for all of these object types
           and should be supported by all SQLAlchemy-included dialects.
           Support among third party dialects may be lagging, however.
        """
        with self._operation_context() as conn:
            return self.dialect.has_table(
                conn, table_name, schema, info_cache=self.info_cache, **kw
            )
Deovi
  • 51
  • 7