Sqlalchemy from_statement() cannot locate column

Question

I am following the sqlalchemy tutorial in http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html

Nevertheless, instead of using a SQLite backend, I am using MySQL. The problem is that when I try to execute a literal MySQL statement to select a column from the users table, such as

SELECT name from users;

it will fail with

NoSuchColumnError: "Could not locate column in row for column 'users.id'"

whereas doing a

SELECT * FROM users

will work just fine. Am I missing anything?

The Users class is defined as :

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(50))
    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password)

The session is defined as:

Session = sessionmaker(bind=engine)
session = Session()

And I am trying to do

session.query(Users).from_statement('SELECT name from users')

By the way, this statement works fine when run on a MySQL client. Am I missing something or is it a bug?

As an additional detail, "SELECT id FROM users" does not fail, but it does not return the expected (a list of the ids), but all the columns, just what it would be expected if doing "SELECT * FROM users" — fenomenoxp, Apr 28 '14 at 15:18
Definitely not a bug. When you query for an object (as you do in `query(User)`), ORM must have a primary_key column for identity of the object. This is why it needs an `id` column. When you select only `id` column, it will still return instances of `User`, and it will then issue another SQL statement to load other columns of each User as soon as you access other attributes of it. — van, Apr 29 '14 at 05:55

score 13 · Answer 1 · answered Apr 28 '14 at 15:50

I answer myself in case someone has the same problem. The syntax of

session.query(Users).from_statement('SELECT name from users')

is wrong. It should be

session.query('name').from_statement('SELECT name from users')

The list of columns should be in the call to query().

Sqlalchemy from_statement() cannot locate column

1 Answers1

Linked