SQLAlchemy: SQL Expression with multiple where conditions

Question

I'm having difficulties writing what should be a simple SQL update statement in SQLAlchemy Core. However, I can't find any documentation, examples or tutorials that show how to combine multiple where conditions. I'm sure it's there - just can't find it.

Here's the table:

self.struct    = Table('struct',
                     metadata,
                     Column('schema_name',         String(40),  nullable=False,
                                                                primary_key=True),
                     Column('struct_name',         String(40),  nullable=False,
                                                                primary_key=True),
                     Column('field_type',          String(10),  nullable=True),
                     Column('field_len',           Integer,     nullable=True) )

Here's the insert & update statement:

def struct_put(self, **kv):
   try:
       i = self.struct.insert()
       result = i.execute(**kv)
   except exc.IntegrityError:   # row already exists - update it:
       u = self.struct.update().\
           where((self.struct.c.struct_name==kv['struct_name']
                  and self.struct.c.schema_name==kv['schema_name'])).\
           values(field_len=kv['field_len'],
                  field_type=kv['field_type'])
       result = u.execute()

The code handles the insert fine, but updates all rows in the table. Can you help me understand the syntax of this where clause? All suggestions are welcome - thanks in advance.

EDIT: The corrected clause looks like this:

        where((and_(self.struct.c.parent_struct_name==kv['parent_struct_name'],
                    self.struct.c.struct_name==kv['struct_name'],
                    self.struct.c.schema_name==kv['schema_name']))).\

It's a very simple syntax, but given the many layers of SQLAlchemy it was surprisingly difficult to determine what exactly applied within this context.

A shortcut to using and_() is to chain together multiple where() clauses. So: .where(a==1).where(b==2).where(c==3). See docs. — Arie
– Arie, Commented Oct 21, 2015 at 14:19

Keith · Accepted Answer · 2012-02-01 07:39:21Z

32

It looks to me like you are using the Python "and" operation, which will evaluate to a only one of the clauses surrounding it. You should try using the "and_" function from SQLAlchemy instead. Put those two clauses inside the "and_" function.

answered Feb 1, 2012 at 7:39

Keith

43.2k11 gold badges61 silver badges77 bronze badges

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

2 Comments

Buford Sweatley Over a year ago

Ah, I figured the answer had to be that simple. I couldn't find any examples of this in the tutorial or anywhere else. Functions were described, but it wasn't clear to me that this was where I would use them. Thanks.

Roshan Chauhan Over a year ago

docs.sqlalchemy.org/en/14/core/… just adding link of an example for and_ in where clause query.

Koushik Das · Accepted Answer · 2022-07-01 10:40:55Z

18

You can also use & python operator.

For example:

query.where(
   (ModelName.c.column_name == "column_value") &
   (ModelName.c.column_name == "column_value)
)

For example, if you had a query like this

user_query = User.select().where(
   (User.c.id == 12) &
   (User.c.email == "[email protected]")
)

This will generate a raw SQL like this

select * from users where id = 12 and email = "[email protected]"

answered Jul 1, 2022 at 10:40

Koushik Das

10.9k3 gold badges58 silver badges55 bronze badges

1 Comment

mo1ein Over a year ago

I'm using .where() without & and everything is OK.

yuritem · Accepted Answer · 2024-01-08 10:24:14Z

13

According to docs, Select.where() also accepts multiple conditions as *whereclause with default AND behavior:

stmt = (
    select(Table)
    .where(
        Table.firstname == "John",
        Table.lastname == "Smith"
    )
)

answered Jan 8, 2024 at 10:24

yuritem

1331 silver badge5 bronze badges

1 Comment

Dirk R Over a year ago

This does appear to work, and is similar to how the .filter statement worked. Thank you. This seems like the answer with the cleanest and easiest to remember syntax

sblom · Accepted Answer · 2012-02-01 06:48:04Z

1

In SQLAlchemy, tablename.c is a special value that you use when constructing conditions that will be treated by SQLAlchemy at runtime.

In this particular case, you're simply saying "update all the rows where the column named struct_name matches the value passed in to struct_put(struct_name="struct_value", schema_name="schema_value"), and the column named schema_name matches the value passed in as schema_name.

answered Feb 1, 2012 at 6:48

sblom

27.5k4 gold badges74 silver badges96 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy: SQL Expression with multiple where conditions

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Related