SQLAlchemy 'Connection' object has no attribute '_Inspector__engine'

Question

I want to use SQLAlchemy to read databases, which are not mapped to Objects (needs to access DBs unknown at time of development). One of the functionalities is to read the column names of different tables. Therefore I wrote this Connector:

MyConnector.py

import sqlalchemy as db

class MyConnector:
    __engine = None

    def __init__(self, db_connection):
        self.__engine = db.create_engine(db_connection)

    def __get_table(self, tbl_name):
        metadata = db.MetaData()
        table = db.Table(tbl_name, metadata)
        inspector = db.inspect(self.__engine)
        inspector.reflect_table(table, None)
        return table

    def get_table_columns(self, tbl_name):
        table = self.__get_table(tbl_name)
        return table.columns.keys()

Which is tested by following UnitTest:

ConnectorTest.py

import unittest
from MyConnector import MyConnector

class MyTestCase(unittest.TestCase):
    db_string = "sqlite:///myTest.db"
    expected_columns_user = ["id", "f_name", "l_name", "note"]
    expected_columns_address = ["id", "street", "number", "postal_code", "additional_information", "fk_user"]

    def test_get_table_columns_user(self):
        connector = MyConnector(self.db_string)
        answer = connector.get_table_columns("user")
        self.assertListEqual(self.expected_columns_user, answer)

    def test_get_table_columns_address(self):
        connector = MyConnector(self.db_string)
        answer = connector.get_table_columns("address")
        self.assertListEqual(self.expected_columns_address, answer)


if __name__ == '__main__':
    unittest.main()

SQLite DB contains only these two empty tables:

CREATE TABLE user (
    id INTEGER NOT NULL DEFAULT AUTO_INCREMENT,
    f_name VARCHAR,
    l_name VARCHAR,
    note VARCHAR,
    PRIMARY KEY (
        id
    )
);

CREATE TABLE address (
    id INTEGER NOT NULL DEFAULT AUTO_INCREMENT,
    street VARCHAR NOT NULL,
    number INTEGER,
    postal_code INTEGER NOT NULL,
    additional_information VARCHAR,
    fk_user INTEGER NOT NULL,
    PRIMARY KEY (
        id
    ),
    FOREIGN KEY (
        fk_user
    )
    REFERENCES user(id) ON DELETE CASCADE
);

The test test_get_table_columns_user works as expected. The test test_get_table_columns_address raises an error:

Error
Traceback (most recent call last):
  File "ConnectorTest.py", line 17, in test_get_table_columns_address
    answer = connector.get_table_columns("address")
  File "MyConnector.py", line 17, in get_table_columns
    table = self.__get_table(tbl_name)
  File "MyConnector.py", line 13, in __get_table
    inspector.reflect_table(table, None)
  File "venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 802, in reflect_table
    reflection_options,
  File "venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 988, in _reflect_fk
    **reflection_options
  File "<string>", line 2, in __new__
  File "venv\lib\site-packages\sqlalchemy\util\deprecations.py", line 298, in warned
    return fn(*args, **kwargs)
  File "venv\lib\site-packages\sqlalchemy\sql\schema.py", line 601, in __new__
    metadata._remove_table(name, schema)
  File "venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "venv\lib\site-packages\sqlalchemy\sql\schema.py", line 596, in __new__
    table._init(name, metadata, *args, **kw)
  File "venv\lib\site-packages\sqlalchemy\sql\schema.py", line 676, in _init
    resolve_fks=resolve_fks,
  File "venv\lib\site-packages\sqlalchemy\sql\schema.py", line 705, in _autoload
    with insp._inspection_context() as conn_insp:
  File "AppData\Local\Programs\Python\Python37\lib\contextlib.py", line 112, in __enter__
    return next(self.gen)
  File "venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 217, in _inspection_context
    sub_insp = self._construct(self.__class__._init_connection, conn)
  File "venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 117, in _construct
    init(self, bind)
  File "venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 135, in _init_connection
    self.engine = connection.__engine
AttributeError: 'Connection' object has no attribute '_Inspector__engine'

Since both tests run exactly identical code, except the parameter for the table name, I am pretty confused by the outcome.

Using Google I found this PR: https://github.com/laughingman7743/PyAthena/pull/65/files Seems not relevant to this Problem (Engine, not Connection).

Many posts about Sessions (like Python SQLAlchemy Query: AttributeError: 'Connection' object has no attribute 'contextual_connect' or https://github.com/sqlalchemy/sqlalchemy/issues/4046). Which should not matter because using the Inspector in this context seems not even to need a call to engine.connect().

For the sake of ruling out lingering locks or similar problems, I also changed the parameter of test_get_table_columns_address to "user". This works up to the assert, which obviously does not match. So it looks like a problem with the table (-name?) to me - which is pretty weird.

Could someone with more experience and insight to SQLAlchemy point out, where/what the Problem with my code is? Thanks in advance!

Python 3.7.4

SQLAlchemy 1.4.20

I can't reproduce the error, but I strongly suspect it relates to using double-underscores as a prefix for "private" attributes. I recommend _not_ using them. If you want to indicate that an attribute is private, use a single leading underscore. See [this answer](https://stackoverflow.com/a/41762806/5320906) for some discussion. Try your code without any leading double-underscores on `engine` and `get_table` and see if it works. — snakecharmerb, Jul 17 '21 at 13:58
Thanks @snakecharmerb! Dont have access to my environment and will try it tonight. What I found out so far (thanks co-worker) is, that the problem seems to be related to the foreign key: According to https://docs.sqlalchemy.org/en/14/core/reflection.html (second snippet) metadata is supposed to mapp the referenced table. Putting ```inspector.reflect_table(table, None)``` in a try, shows that ```address``` was mapped properly and ignoring the error even makes the method succeed. However the table ```user``` is not found in ```metadata.tables```. Still need to try different versions... — ToxicWaste, Jul 18 '21 at 12:00

score 0 · Accepted Answer · answered Jul 19 '21 at 11:53

Too bad I cannot give anyone a better answer than, that I suspect something with the dependencies was messed up... I wanted to try different versions of SQLAlchemy to write a bug report wit the above described behavior. Therefore I changed my venv a couple of times via the commands:

pip freeze > uninstall.txt
pip uninstall -r uninstall.txt -y
pip install -r requirements.txt

While changing requirements.txt to a couple of different SQLAlchemy versions. Note that requirements.txt always contained only one single requirement (SQLAlchemy in different versions).

Seeing that every single tested versions behaved as expected and succeeded at the unittest from the question, I changed back to 1.4.20. Now even with version 1.4.20 the unittest succeed.

To avoid possible influences from other files, the project always only contained the two py files and the db. So I think we can exclude changes in the project itself as a cause. I also tried both "privates" wit _ and __ as @snakecharmerb pointed out. The tests succeed wit double- and single- underscores. So my only guess is, that something went wrong with the requirements in the first place. Sadly I cannot go back and check the first uninstall.txt because it was overwritten many times.

SQLAlchemy 'Connection' object has no attribute '_Inspector__engine'

1 Answers1