SQLAlchemy: SQL expression with multiple conditions

Question

I'm having difficulty writing what should be a simple SQL update statement in SQLAlchemy Core. However, I cannot find documentation, examples, or manuals that show how to combine several conditions. I am sure that he is there - he simply cannot find him.

Here is 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 and update instruction:

 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 insertion in order, but updates all the rows in the table. Can you help me understand the syntax of this where clause? All suggestions are welcome - in advance.

EDIT: The corrected sentence is as follows:

  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']))).\ 

This is a very simple syntax, but given the many levels of SQLAlchemy, it was unexpectedly difficult to determine what exactly applies in this context.

Answer 1

It seems to me that you are using the operation "and" Python, which will evaluate only one of the sentences associated with it. Instead, you should try using the "and_" function from SQLAlchemy. Place these two sentences inside the "and_" function.

Answer 2

In SQLAlchemy, tablename.c is the special value that you use when constructing the conditions that SQLAlchemy will process at runtime.

In this particular case, you simply say: "Update all rows where the column named struct_name matches the value passed to struct_put(struct_name="struct_value", schema_name="schema_value") , and the column named schema_name matches the value passed as schema_name .