[sqlalchemy] underscore in mssql column names?

[mte]

Hi,
I need to connect to an existing MS SQL database with SqlAlchemy. I'm
using a declarative_base (haven't tried with manual mapping) and it
seems to work except for a few fields.
Those are all defined as "col1 =
Column('some_field_with_underscores_in_name', AnyType)"
(AnyType meaning Integer, String or whatever else).
If I do a session.query(MyClass).first().col1 I get None returned. I
get the correct value if I do session.query(MyClass).first().col2
(which doesn't have underscores in its name) or if I fetch the row
with session.execute("SELECT * FROM mytable").
Can this be considered a bug or is there a setting I haven't specified
correctly?

Thanks,
Matej

--
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.

[Michael Bayer]

there's no issue with underscore column names so something else must be going on.

[Conor]

On 05/19/2010 06:47 AM, mte wrote:

Hi,
I need to connect to an existing MS SQL database with SqlAlchemy. I'm
using a declarative_base (haven't tried with manual mapping) and it
seems to work except for a few fields.
Those are all defined as "col1 =
Column('some_field_with_underscores_in_name', AnyType)"
(AnyType meaning Integer, String or whatever else).
If I do a session.query(MyClass).first().col1 I get None returned. I
get the correct value if I do session.query(MyClass).first().col2
(which doesn't have underscores in its name) or if I fetch the row
with session.execute("SELECT * FROM mytable").
Can this be considered a bug or is there a setting I haven't specified
correctly?

  

Here is a possible reason: SQLAlchemy has a "use_labels" feature which adds table/alias prefixes to columns in the columns clause to help disambiguate columns from different tables with the same name. It could be that your col1 is named in such a way that makes SQLAlchemy think that "use_labels" is in effect, which could cause it to not recognize it properly. For example, if "MyClass" uses the "myclass" table, and col1's name is "myclass_id", then SQLAlchemy may try to interpret that as the "id" column of "myclass" instead of the "myclass_id" column.

If this is indeed the case, the problem should go away if you really enable the "use_labels" feature:

session.query(MyClass).with_labels().first().col1

If this does not fix it, please post the SQL that is emitted by SQLAlchemy when you use session.query(MyClass).first(). You can see the emitted SQL by passing echo=True to create_engine.

-Conor