SqlAlchemy raw SQL queries strange errors

Question

I´ve been facing some weird issues when studding SQL raw queries using SqlAlchemy.

sqlstr = 'SELECT "City" from CHICAGO_SCHOOLS;'
with engine.connect() as conn:
    result = conn.execute(text(sqlstr))
    print (result.all())

The query above returns hundreds of "Chicago" as results. So I just tried to get unique results:

sqlstr = 'SELECT DISTINCT "City" from CHICAGO_SCHOOLS;'
with engine.connect() as conn:
    result = conn.execute(text(sqlstr))
    print (result.all())

Now, all I got is a weird error :

Exception: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0134N Improper use of a string column, host variable, constant, or function "City". SQLSTATE=42907

At first I thought it was somehow related to the DISTINCT set quantifier. So I tried the same query with another column.

sqlstr = 'SELECT DISTINCT "School ID" from CHICAGO_SCHOOLS;'
with engine.connect() as conn:
    result = conn.execute(text(sqlstr))
    print (result.all())

And in this query I got all expected results. I am not being able to truly understand what is wrong!

According to [this](https://stackoverflow.com/questions/15741539/db2-error-improper-use-of-a-string-column-host-variable-constant-or-function) answer, it could be that `city` is of a type not allowed in grouping. `DISTINCT` probably groups — HoneyBadger, Jun 09 '22 at 20:29
Wow, it makes sense! Its a CLOB type! Where can I find information about this kind of restrictions (types that does not allow indexing)? — FábioRB, Jun 09 '22 at 20:53
(unrelated note), do you really have separate school tables for each city? Consider one single schools table. — jarlh, Jun 09 '22 at 20:54
Lol. Such tables are from sample data available on internet. I got them from a course about python handling db2 database — FábioRB, Jun 10 '22 at 23:37

score 0 · Accepted Answer · answered Jun 09 '22 at 20:54

0

The issue was related to the column type. It was a CLOB type and that does not allow use of DISTINCT. Thanks to HoneyBadger

answered Jun 09 '22 at 20:54

FábioRB

335
1
12

SqlAlchemy raw SQL queries strange errors

1 Answers1