2

I want to import a dataframe into a access database but I got an error NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:access.pyodbc

from sqlalchemy import create_engine
import urllib
import pyodbc

conec = (r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
         r"DBQ=C:\Users\redim\Desktop\18_marzo\Libr2.accdb"
        )
    
con = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(conec)}"
acc_engine = create_engine(con)
df.to_sql('hola', acc_engine)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Blessing Bribiesca
  • 47
  • 1
  • 5
  • Have you installed sqlalchemy-access? `pip install sqlalchemy-access` – rfkortekaas Mar 29 '21 at 17:51
  • Yes rfkortekaas, if you have other way to make it, I would appreciate it – Blessing Bribiesca Mar 29 '21 at 18:13
  • Have a look at the [wiki](https://github.com/gordthompson/sqlalchemy-access/wiki/Getting-Connected). It looks like your connection string is missing a slash. Also there is a space between the comma and * in the Driver name! – rfkortekaas Mar 29 '21 at 18:20
  • I just did it but it doesn't work – Blessing Bribiesca Mar 29 '21 at 18:34
  • Does `pip list|findstr access` return anything? – Gord Thompson Mar 29 '21 at 18:53
  • returns me sqlalchemy-access 1.0.8 – Blessing Bribiesca Mar 29 '21 at 19:10
  • @BlessingBribiesca - At the beginning of your app, add`import sqlalchemy_access as sa_a`. Does it throw an `ImportError`? – Gord Thompson Mar 29 '21 at 19:39
  • @GordThompson I have a new error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xfa8 Thread 0x2cc0 DBC 0x17707914 Jet'. (63) (SQLDriverConnect); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xfa8 Thread 0x2cc0 DBC 0x17707914 Jet'. (63); – Blessing Bribiesca Mar 29 '21 at 20:31
  • [HY000] [Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt. (-1028)") – Blessing Bribiesca Mar 29 '21 at 20:32
  • @BlessingBribiesca - See [this question](https://stackoverflow.com/q/26244425/2144390) for information on that error message. – Gord Thompson Mar 29 '21 at 21:05
  • @GordThompson thank you, it help me but now my databse is in blank and weigth 3,140 KB and my code dont stop run, I guess that I need to find another way to solve it – Blessing Bribiesca Mar 29 '21 at 22:08
  • @BlessingBribiesca - You could try making a copy of the .accdb (for safety) and then open it in the Access UI and do a "Compact and Repair Database" operation on it. – Gord Thompson Mar 29 '21 at 22:54
  • @GordThompson - I'm having this exact same error. When I run the pip list|findstr access I get an error 'C:\Users\Patrick' is not recognized as an internal or external command, operable program or batch file. This is likely due to a space being in the file path. – Peej1226 Nov 22 '22 at 23:08

1 Answers1

0

For some reason this only works in Jupyter notebooks, not in PyCharm which I was having the same problems for quite some time in both until I upgraded SQLalchemy

I use conda install with most of my libraries:

conda update sqlalchemy

But if you are using pip, then below is command:

pip install --upgrade sqlalchemy

I went from sqlalchemy 1.3.7 to 1.4.39 and the error went away, at least in Jupyter Notebooks anyways.

Peej1226
  • 132
  • 12