With the following code:
#################################################
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import column_property, relation
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.sql.expression import select
from sqlalchemy.types import Integer, String
Base = declarative_base()
Session = scoped_session(sessionmaker(
autoflush=False,
autocommit=True,
))
engine = create_engine('mssql+pyodbc://localhost/test')
Session.configure(bind=engine)
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(50), nullable=False)
last_name = Column(String(50), nullable=False)
name = column_property((first_name + ' ' +
last_name).label('name'))
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(50), nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'),
nullable=False,
index=True)
author = relation(Author, backref='books')
author_name = column_property(
select(
[Author.name],
author_id == Author.id,
).label('author_name')
)
Base.metadata.create_all(engine)
Session.query(Book).order_by('id').all()
Session.query(Book).order_by('id').limit(5).offset(5).all()
#################################################
the 2nd query will fail with this error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42S22', "[42S22]
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'name'. (207) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Statement(s) could not be prepared. (8180)")
u'SELECT anon_1.name, anon_1.books_id, anon_1.books_title,
anon_1.books_author_id \nFROM (SELECT (SELECT authors.first_name + ?
+authors.last_name AS name \nFROM authors \nWHERE books.author_id =
authors.id) AS author_name, books.id AS books_id, books.title AS
books_title, books.author_id AS books_author_id, ROW_NUMBER() OVER
(ORDER BY id) AS mssql_rn \nFROM books) AS anon_1 \nWHERE mssql_rn > ?
AND mssql_rn <= ?' (' ', 5, 10)
Somehow it selects anon_1.name instead of anon_1.contact_name. I got
the error on both 0.6.7 and 0.7b4.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
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.
