Table fields not found when trying to select distinct values of a column

Question

I have created a table with a dynamic name as follows:

def create_data_table(table_name):
meta = Base.metadata
my_table= Table(
    table_name, meta,
    Column('id', BigInteger, primary_key=True, nullable=False),
    Column('file_name', String(250), nullable=False)
)
meta.create_all(engine, [my_table])

I can query all records from it by using session.query(table_data).all() (although, curiously, if I try to print it, it will print all records twice).

I am trying to retrieve a list with all distinct file_names. If I run print(Base.metadata.tables[table_name].columns.keys()) I will get ['id', 'file_name'] back, so clearly the field file_name exists. However, I get an error saying that it does not exist when I try to query for distinct file names. Here is the complete function:

def retrieve_classes(table_name):
names = []
Base.metadata.reflect(engine)
with Session() as s:
    table_data = Base.metadata.tables[table_name]
    for info in s.query(table_data).distinct(table_data.file_name).group_by(table_data.file_name):
        names.append(info.file_name)
return names

Any help is very appreciated. Thanks!

-- Update: Please notice that the question listed above, which already has an answer, did not solve my problem.

As suggested, I changed the line to the following:

for info in s.query(table_data).distinct(table_data.c.file_name).group_by(table_data.c.file_name)

This also did resulted in an error. Full traceback is listed below.

    127.0.0.1 - - [04/Sep/2019 15:17:30] "POST /predict HTTP/1.1" 500 -
Traceback (most recent call last):
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "C:\git\a\venv\lib\site-packages\MySQLdb\cursors.py", line 209, in execute
    res = self._query(query)
  File "C:\git\a\venv\lib\site-packages\MySQLdb\cursors.py", line 315, in _query
    db.query(q)
  File "C:\git\a\venv\lib\site-packages\MySQLdb\connections.py", line 226, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1055, '')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\git\a\venv\lib\site-packages\flask\app.py", line 2463, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\git\a\venv\lib\site-packages\flask\app.py", line 2449, in wsgi_app
    response = self.handle_exception(e)
  File "C:\git\a\venv\lib\site-packages\flask\app.py", line 1866, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "C:\git\a\venv\lib\site-packages\flask\_compat.py", line 39, in reraise
    raise value
  File "C:\git\a\venv\lib\site-packages\flask\app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\git\a\venv\lib\site-packages\flask\app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\git\a\venv\lib\site-packages\flask\app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\git\a\venv\lib\site-packages\flask\_compat.py", line 39, in reraise
    raise value
  File "C:\git\a\venv\lib\site-packages\flask\app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\git\a\venv\lib\site-packages\flask\app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
    File "C:\git\a\model\model.py", line 87, in retrieve_names
    for info in s.query(table_data).distinct(table_data.c.file_name).group_by(table_data.c.file_name):
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\orm\query.py", line 3334, in __iter__
    return self._execute_and_instances(context)
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\orm\query.py", line 3359, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\engine\base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "C:\git\a\venv\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "C:\git\a\venv\lib\site-packages\MySQLdb\cursors.py", line 209, in execute
    res = self._query(query)
  File "C:\git\a\venv\lib\site-packages\MySQLdb\cursors.py", line 315, in _query
    db.query(q)
  File "C:\git\a\venv\lib\site-packages\MySQLdb\connections.py", line 226, in query
    _mysql.connection.query(self, query)sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1055, '')

[SQL: SELECT DISTINCT data_model1.id AS data_model1_id, data_model1.file_name AS data_model1_file_name, data_model1.field2 AS data_model1_field2
FROM data_model1 GROUP BY data_model1.file_name]
(Background on this error at: http://sqlalche.me/e/e3q8)

You're using a `Table`, not a declarative model, so to access columns use the `c` or `columns` attribute namespace: `table_data.c.file_name`. I'm writing this as a comment since I remember seeing an answer to this elsewhere before, but did not look it up yet. — Ilja Everilä, Sep 04 '19 at 11:22
@IljaEverilä - Thanks for the answer. I had seen the original question but it did not solve my problem. Using the `c` attribute also resulted in an error. I have edited the question to add the full traceback. Thanks! — Irina, Sep 04 '19 at 13:34
The new error is due to your GROUP BY clause. Newer MySQL do not allow the kind of "partial" groups it used to, where it would produce indeterminate results for non-aggregate columns not in the GROUP BY. `distinct(table_data.c.file_name)` seems like you've used Postgresql's DISTINCT ON in the past, but MySQL does not support it. — Ilja Everilä, Sep 04 '19 at 14:37
@IljaEverilä - Thanks for explaining. That avoided the error, but if I remove the GROUP BY clause, it will just return all records. How can I get a list of all distinct file_name values? — Irina, Sep 04 '19 at 14:56
See here https://stackoverflow.com/questions/2175355/selecting-distinct-column-values-in-sqlalchemy-elixir (just the accepted answer, ignore the rest) — Ilja Everilä, Sep 04 '19 at 17:08

Table fields not found when trying to select distinct values of a column

0 Answers0