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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
--
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/-/pMdklVqe7iwJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
