I am trying to reflect an existing MS Access database into a new model. To link the database to SQLAlchemy, I am using
engine = create_engine("access+pyodbc://@db-dns")
Since the database already exists, I am following the basic use scheme from this article: https://docs.sqlalchemy.org/en/14/orm/extensions/automap.html
# reflect the table
Base.prepare(engine, reflect=True)
# mapped classes are now created with names by default
# matching that of the table name
Personen = Base.classes.ref_personen #here, 'ref_personen' is a table from my database
session = Session(engine)
When I try to access the table using Personen.query.all() I get an AttributeError (error log see below).
EDIT: To raise the error, I dont even have to query anything. I assume that the error is already caused at this line: Personen = Base.classes.ref_personen
Often, the reason for this error is due to not hanving defined a primary key in the external database. However, this is not the case for me. In my MS Access DB rel_personen table, a primary key is defined.
Thanks, I appreciate any help on this topic!
(appenv4) C:\Users\Code\webapps4>flask run
* Environment: production
WARNING: This is a development server. Do not use it in a production deployment.
Use a production WSGI server instead.
* Debug mode: off
c:\users\code\webapps4\appenv4\lib\site-packages\sqlalchemy\engine\reflection.py:919: SAWarning: The Access ODBC driver does not support the ODBC "SQLPrimaryKeys" function. get_pk_constraint() is returning an empty list.
pk_cons = self.get_pk_constraint(
c:\users\code\webapps4\appenv4\lib\site-packages\sqlalchemy\engine\reflection.py:947: SAWarning: The Access ODBC driver does not support the ODBC "SQLForeignKeys" function. get_foreign_keys() is returning an empty list.
fkeys = self.get_foreign_keys(
Traceback (most recent call last):
File "c:\users\code\webapps4\appenv4\lib\site-packages\sqlalchemy\util\_collections.py", line 186, in __getattr__
return self._data[key]
KeyError: 'ref_personen'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\anaconda3\lib\runpy.py", line 194, in _run_module_as_main
return _run_code(code, main_globals, None,
File "C:\Users\anaconda3\lib\runpy.py", line 87, in _run_code
exec(code, run_globals)
File "C:\Users\Code\webapps4\appenv4\Scripts\flask.exe\__main__.py", line 7, in <module>
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 990, in main
cli.main(args=sys.argv[1:])
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 596, in main
return super().main(*args, **kwargs)
File "c:\users\code\webapps4\appenv4\lib\site-packages\click\core.py", line 1062, in main
rv = self.invoke(ctx)
File "c:\users\code\webapps4\appenv4\lib\site-packages\click\core.py", line 1668, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "c:\users\code\webapps4\appenv4\lib\site-packages\click\core.py", line 1404, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "c:\users\code\webapps4\appenv4\lib\site-packages\click\core.py", line 763, in invoke
return __callback(*args, **kwargs)
File "c:\users\code\webapps4\appenv4\lib\site-packages\click\decorators.py", line 84, in new_func
return ctx.invoke(f, obj, *args, **kwargs)
File "c:\users\code\webapps4\appenv4\lib\site-packages\click\core.py", line 763, in invoke
return __callback(*args, **kwargs)
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 845, in run_command
app = DispatchingApp(info.load_app, use_eager_loading=eager_loading)
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 321, in __init__
self._load_unlocked()
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 346, in _load_unlocked
self._app = rv = self.loader()
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 406, in load_app
app = locate_app(self, import_name, None, raise_if_not_found=False)
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 273, in locate_app
return find_best_app(script_info, module)
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 68, in find_best_app
app = call_factory(script_info, app_factory)
File "c:\users\code\webapps4\appenv4\lib\site-packages\flask\cli.py", line 119, in call_factory
return app_factory(*args, **kwargs)
File "C:\Users\Code\webapps4\app\__init__.py", line 19, in create_app
from .zkl import zkl as zkl_bp
File "C:\Users\Code\webapps4\app\zkl\__init__.py", line 6, in <module>
from . import zkl_forms, zkl_views
File "C:\Users\Code\webapps4\app\zkl\zkl_views.py", line 3, in <module>
from .zkl_database import db_session, init_db
File "C:\Users\Code\webapps4\app\zkl\zkl_database.py", line 12, in <module>
Personen = Base.classes.ref_personen
File "c:\users\code\webapps4\appenv4\lib\site-packages\sqlalchemy\util\_collections.py", line 188, in __getattr__
raise AttributeError(key)
AttributeError: ref_personen
Update: sqlalchemy-access version 1.1.0 now supports reflection of Primary Key (PK) and Foreign Key (FK) constraints.
(previous answer)
As noted by one of the warning messages in your stack trace:
SAWarning: The Access ODBC driver does not support the ODBC "SQLPrimaryKeys" function. get_pk_constraint() is returning an empty list.
This is a known issue with the Access ODBC driver. It is also discussed here
https://github.com/gordthompson/sqlalchemy-access/issues/9
and here