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.

Solution 1

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.

Solution 2

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.

Comments

[Arie]

A shortcut to using and_() is to chain together multiple where() clauses. So: .where(a==1).where(b==2).where(c==3). See docs.

[Fabrice Jammes]

This might also help: from sqlalchemy import and_

[Buford Sweatley]

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.