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.

Answer 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.

Answer 2

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]"