Issue: FAB cannot read tables with primary key columns with spaces

[agxc]

I have some existing MS SQL server tables that I need to migrate from Flask-admin to Flask-appbuilder. These tables contain columns with spaces such as [Column name 1], [Column name2].

While the app is working fine with flask-admin and SQLalchemy, I realised that the primary key column with such naming convention doesn't work with F.A.B

Environment

Flask-Appbuilder version:
Flask-AppBuilder==4.3.9
pip freeze output:
werkzeug==2.3.7
Flask-AppBuilder==4.3.9

Describe the expected results

The F.A.B should display a table with three columns as shown in the script below.

Tell us what should happen.
The line #23 in the script below is the cause of the issue. After replacing it with line #24, the problem goes away. However, we cannot change the naming convention from Primary key 1 to primary_key_1 as this is the convention used in the EDW.

Steps to reproduce

1. run pip install -r requirements.txt using the requirements.txt provided below
2. Save a default config.py file
3. run python app.py

requirements.txt

werkzeug==2.3.7
Flask-AppBuilder==4.3.9

app.py

from flask import Flask
from sqlalchemy import Column, Integer, String
from flask_appbuilder import AppBuilder, SQLA, Model
from flask_appbuilder.views import ModelView
from flask_appbuilder.models.sqla.interface import SQLAInterface
from flask_babel import lazy_gettext as _
import os

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config.from_object("config")
SQLALCHEMY_DATABASE_URI = "sqlite:///" + os.path.join(basedir, "app.db")

app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DATABASE_URI

db = SQLA(app)

class TestTable(Model):
    # __table_args__  = {"schema": "TestSchema"}
    __tablename__   = "TestTable"
    primary_key_1    = Column( "Primary key 1",   Integer,  primary_key=True)
    # primary_key_1    = Column(    Integer,  primary_key=True)
    column_name_1    = Column("Column name 1", String(1))
    column_name_2    = Column("Columna name 2", String(3))

class TestTableModelView(ModelView):
    datamodel = SQLAInterface(TestTable)

    list_columns = ['primary_key_1', 'column_name_1', 'column_name_1']

db.drop_all()
appbuilder = AppBuilder(app, db.session)
appbuilder.add_view(TestTableModelView, "Test", icon="fa-file-import", label=_('test'), category_label=_('test'),
                    category="Test", category_icon='fa-file-import',)
json_data = {
    "TestTable": [
        { "primary_key_1": "1", "column_name_1": "E", "column_name_2": "FOO", },
        { "primary_key_1": "2", "column_name_1": "D", "column_name_2": "BAR", }
    ], }

data = [TestTable(**x) for x in json_data["TestTable"]]
db.create_all()

try:
  db.session.add_all(data)
  db.session.commit()
except:
  pass

app.run(host="0.0.0.0", port=8080, debug=True)

Paste the full traceback if there was an exception.
AttributeError
AttributeError: 'TestTable' object has no attribute 'Primary key 1'

Traceback (most recent call last)
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask\app.py", line 2213, in __call__
return self.wsgi_app(environ, start_response)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask\app.py", line 2193, in wsgi_app
response = self.handle_exception(e)
           ^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask\app.py", line 2190, in wsgi_app
response = self.full_dispatch_request()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask\app.py", line 1486, in full_dispatch_request
rv = self.handle_user_exception(e)
     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask\app.py", line 1484, in full_dispatch_request
rv = self.dispatch_request()
     ^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask\app.py", line 1469, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask_appbuilder\security\decorators.py", line 137, in wraps
return f(self, *args, **kwargs)
       ^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask_appbuilder\views.py", line 551, in list
widgets = self._list()
          ^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask_appbuilder\baseviews.py", line 1181, in _list
widgets = self._get_list_widget(
          
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask_appbuilder\baseviews.py", line 1087, in _get_list_widget
pks = self.datamodel.get_keys(lst)
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask_appbuilder\models\base.py", line 296, in get_keys
return [getattr(item, pk_name) for item in lst]
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Dev\flask-appbuilder-simpleview1\venv\Lib\site-packages\flask_appbuilder\models\base.py", line 296, in <listcomp>
return [getattr(item, pk_name) for item in lst]
        ^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'TestTable' object has no attribute 'Primary key 1'

[dpgaspar]

Seems like primary keys with different names then their model fields is not supported. I'll try to find time to fix/support this.

[agxc]

Thank you @dpgaspar for such prompt reply 👍 . You are 100% right in pointing out the underlying issue, which is not quite to do with whitespace.

F.A.B doesn't support if I change line #25 to primary_key_1 = Column("PRIMARY_KEY_1", Integer, primary_key=True).

In our case, our source EDW holds thousands of tables with columns named like [Primary key x], [Column name x] which makes impossible for me to change the underlying column names. Plus there is an increasing popularity of using space as naming convention due to how MS SQL server handles metadata for Power BI.

I have been scratching my head for days trying to have a workaround so I am very excited to hear that this feature is going to be supported....!