SQLite problem: column aliases with dot are stripped

[Stefan Urbanek]

Hi,

I had this problem ~year ago (see [1]). Now with SQLAlchemy 0.7.6 I am having this same problem back again. Here is a piece of code that shows this behaviour, in comparison to other backends as well:

https://gist.github.com/2506388

Verbosity and non-reusing instances is intentional.

Is there any workaround for this issue?

Thanks for any hints,

Stefan

[1] http://bit.ly/Je8V4I Selecting columns with dots in their names 

[Michael Bayer]

this is an issue of two edge cases.

One edge case is you want to emit this SQL on SQLite:

select query_users.user_id, query_users.user_name from query_users 

    UNION select query_users.user_id, query_users.user_name from query_users

and then access the columns like this:

row["user_id"], row["user_name"]

which is of course how every other database does it.   Only SQLite decides that only when a UNION is present, it's going to prepend the tablename to the keys in cursor.description.    So we strip them off so that the ORM and everything else still works when a query like this comes through.

The other edge case is you want to explicitly use a label with a dot in it.

As it stands, we can choose among these two edge cases, and I'd note in the current behavior, you *can* access your column using "foo.bar", it's just not in keys() that way.   In my view, being able to emit a UNION with table-qualified columns is a lot more common than labels with dots in them.   Why not use another character that isn't meaningful in SQL ?

  

A patch to disable the behavior is below.  If you can get test.sql.test_query, which tests the above UNION case, to pass in some other way with SQLite, that fix will go right in.    

diff -r 813168d05065 lib/sqlalchemy/dialects/sqlite/base.py

--- a/lib/sqlalchemy/dialects/sqlite/base.py Thu Apr 26 11:59:50 2012 -0400

+++ b/lib/sqlalchemy/dialects/sqlite/base.py Fri Apr 27 09:50:48 2012 -0400

@@ -572,7 +572,7 @@

         # in the case of UNION may store col names as 

         # "tablename.colname"

         # in cursor.description

-        if "." in colname:

+        if False: #"." in colname:

             return colname.split(".")[1], colname

         else:

             return colname, None

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/xgqjb8tLAW8J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

Here's another patch which switches the behavior of row.keys(), then adds a test where a special exception needs to be made for SQLite.      This would have keys come right back as they are.   This would break the behavior of keys() in the other direction, for those who might be using UNION with table-qualified column names (as the ORM always does).     

Perhaps you can use another accessor raw_keys() ?   what is the use case here for dots in label names + keys() ?

[Stefan Urbanek]

On Friday, April 27, 2012 3:55:52 PM UTC+2, Michael Bayer wrote:

this is an issue of two edge cases.

One edge case is you want to emit this SQL on SQLite:

select query_users.user_id, query_users.user_name from query_users 

    UNION select query_users.user_id, query_users.user_name from query_users

and then access the columns like this:

row["user_id"], row["user_name"]

which is of course how every other database does it.   Only SQLite decides that only when a UNION is present, it's going to prepend the tablename to the keys in cursor.description.    So we strip them off so that the ORM and everything else still works when a query like this comes through.

Thanks for the explanation, good to know that.

 

The other edge case is you want to explicitly use a label with a dot in it.

Exactly, that is what I want.

 

As it stands, we can choose among these two edge cases, and I'd note in the current behavior, you *can* access your column using "foo.bar", it's just not in keys() that way.   In my view, being able to emit a UNION with table-qualified columns is a lot more common than labels with dots in them.   Why not use another character that isn't meaningful in SQL ?

  

Why not another character? Because of convenience "container.element"/"object.attribute" that is used almost everywhere. I am mapping a logical view to a star/snowflake physical schema. In the logical view, way how attributes are referred to is "dimension.attribute". SQL backend is one of possible backends and the issue with this notation was experienced only with SQLite so far.

This is what I used as workaround [1]:

    # select is sqlalchemy.sql.expression.select() 

    # each selected column was derived as column = table.c[reference].label(label_with_dot)

    labels = [c.name for c in select.columns]

    ...

    record = dict(zip(labels, row))

I expect the sqlalchemy.sql.expression.select.column always return final list of selected columns. I haven't tested yet whether this will work with aggregations or any other computed expressions as well, but I see no reason why it should not.

<patch snipped>

Regards,

Stefan

[1] https://github.com/Stiivi/cubes/blob/master/cubes/backends/sql/star.py#L126

On Apr 27, 2012, at 2:24 AM, Stefan Urbanek wrote:

Hi,

I had this problem ~year ago (see [1]). Now with SQLAlchemy 0.7.6 I am having this same problem back again. Here is a piece of code that shows this behaviour, in comparison to other backends as well:

https://gist.github.com/2506388

Verbosity and non-reusing instances is intentional.

Is there any workaround for this issue?

Thanks for any hints,

Stefan

[1] http://bit.ly/Je8V4I Selecting columns with dots in their names 

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/xgqjb8tLAW8J.
To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

[Michael Bayer]

On Apr 28, 2012, at 5:52 AM, Stefan Urbanek wrote:

This is what I used as workaround [1]:

    # select is sqlalchemy.sql.expression.select() 

    # each selected column was derived as column = table.c[reference].label(label_with_dot)

    labels = [c.name for c in select.columns]

    ...

    record = dict(zip(labels, row))

please try out the patch at http://www.sqlalchemy.org/trac/attachment/ticket/2475/2475.patch .    This would provide:

conn = engine.connect().execution_options({"sqlite_raw_colnames":True})

result = conn.execute(stmt)

no removal of dots would proceed.   

if this works for you it can go right in to 0.7 and 0.8.

[Stefan Urbanek]

Thanks, works nicely. (had to change to: execution_options(sqlite_raw_colnames=True)).

Is there (going to be) a way how I would be able to pass this option to the engine, so I can have all connections like that?

Stefan

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

Stefan Urbanek

data analyst and data brewmaster

Twitter: @Stiivi

Home: http://stiivi.com

Brewery: http://databrewery.org

Github: https://github.com/Stiivi

[Michael Bayer]

On Apr 29, 2012, at 4:06 AM, Stefan Urbanek wrote:

On 28.4.2012, at 18:39, Michael Bayer wrote:

On Apr 28, 2012, at 5:52 AM, Stefan Urbanek wrote:

This is what I used as workaround [1]:

    # select is sqlalchemy.sql.expression.select() 

    # each selected column was derived as column = table.c[reference].label(label_with_dot)

    labels = [c.name for c in select.columns]

    ...

    record = dict(zip(labels, row))

please try out the patch at http://www.sqlalchemy.org/trac/attachment/ticket/2475/2475.patch .    This would provide:

conn = engine.connect().execution_options({"sqlite_raw_colnames":True})

result = conn.execute(stmt)

no removal of dots would proceed.   

if this works for you it can go right in to 0.7 and 0.8.

Thanks, works nicely. (had to change to: execution_options(sqlite_raw_colnames=True)).

Is there (going to be) a way how I would be able to pass this option to the engine, so I can have all connections like that?

sure, create_engine() accepts execution_options as does the Engine itself via update_execution_options.