How to filter the values of a table's column in SQLAlchemy Core

Question

I know how to query on a model now. Suppose there is a Question model:

class Question(Base):
    __tablename__ = "questions"
    id=Column(...)
    user_id=Column(...)
    ...

Now, I can do:

question = Session.query(Question).filter_by(user_id=123).one()

But, now, I have a table (not a model) questions:

questions = Table('questions', Base.metadata,
                  Column(id, ...),
                  Column(user_id, ...),
                  ....)

How to query it as what I do with models?

Session.query(questions).filter_by(user_id=123).one()

This will report an error:

Traceback (most recent call last):
File "<console>", line 1, in <module>
File "E:\Python27\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\orm\query.py", line 851, in filter_by
  for key, value in kwargs.iteritems()]
File "E:\Python27\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\orm\util.py", line 567, in _entity_descriptor
    desc = entity.class_manager[key]
AttributeError: 'NoneType' object has no attribute 'class_manager'

But:

Session.query(questions).all()

is OK.

Is filter_by only work for models? How can I query on tables?

They are both Tables. One is Core and the other is ORM

robertspierre
– robertspierre

2025-06-13 07:57:32 +00:00
Commented Jun 13 at 7:57 — robertspierre
– robertspierre, Commented Jun 13 at 7:57

Jochen Ritzel · Accepted Answer · 2010-09-01 13:54:48Z

40

I think it's Session.query(questions).filter(questions.c.user_id==123).one()

answered Sep 1, 2010 at 13:54

Jochen Ritzel

108k33 gold badges205 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JRodDynamite Over a year ago

Is there any documentation for querying through tables?

chris Over a year ago

Select is exposed directly on Table; took me a bit to figure out: Table.select

Noumenon · Accepted Answer · 2016-03-27 19:50:34Z

11

You can query tables that were created with the Table constructor using Session.query(Base.metadata.tables['myTable']).all().

edited Mar 27, 2016 at 19:50

answered Jan 13, 2016 at 2:07

Noumenon

6,6424 gold badges67 silver badges85 bronze badges

Comments

j-i-l · Accepted Answer · 2022-05-22 11:20:31Z

This is a bit of a late answer, but what the existing ones are missing is the fact that you can both work with Sessions and with Engines & Connections and that you do not need to work with a Session if you defined sqlalchemy.schema.Table directly.

But when should you use a Session, and when a Connection?

The SQLAlchemy documentation has the following to say regarding this:

Its important to note that when using the SQLAlchemy ORM, these objects [Engines and Connections] are not generally accessed; instead, the Session object is used as the interface to the database. However, for applications that are built around direct usage of textual SQL statements and/or SQL expression constructs without involvement by the ORM’s higher level management services, the Engine and Connection are king (and queen?)

In short:

If you are using the ORM (so you write python classes for you data models), you will work with a Session. If you are directly defining Tables, then you don't need to involve any ORM (and related management services) and can work directly with a Connection.

So, how would working with a Connection look like?:

Here is a simple example, adapted from the docs about connections that also answers your question about how to query a table:

from sqlalchemy import create_engine
engine = create_engine('postgresql://user:pw@localhost:5432/mydatabase')

# Assuming questions is a sqlalchemy.schema.Table instance

with engine.begin() as connection:
    query = questions.select().where(
        questions.c.user_id == 1)
    q1 = connection.execute(query).fetch_one()

See also the docs about sqlalchemy.schema.Table.select for more info.

Collectives™ on Stack Overflow

How to filter the values of a table's column in SQLAlchemy Core

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related