CODEWITHSUNDEEP
pythonsqlalchemy
WyllianNeo
WyllianNeo

Reputation: 363

How create a sqlalchemy delete query with multiples parameter from a loop

I'm new in python and sqlalchemy. I already have a delete method working if I construct the where conditions by hand. Now, I need to read the columns and values from an enter request in yaml format and create the where conditions.

#enter data as yaml
items:
    - item:
        table: [MyTable,OtherTable]
        filters:
            field_id: 1234
            #other_id: null

Here is what I try and can't go ahead:

for i in use_case_cfg['items']:
    item = i.get('item')
    for t in item['table']:
        if item['filters']:
            filters = item['filters']
            where_conditions = ''
            count = 0
            for column, value in filters.items():
                aux = str(getattr(t, column) == bindparam(value))
                if count == 0:
                    where_conditions += aux
                else:
                    where_conditions += ', ' + aux
                count += 1
            to_delete = inv[t].__table__.delete().where(text(where_conditions))
                #to_delete = t.__table__.delete().where(getattr(t, column) == value)
        else:
            to_delete = inv[t].__table__.delete()
        CoreData.session.execute(to_delete)

To me, it looks ok, but when I run, I got the error below: sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter '9876' [SQL: DELETE FROM MyTable WHERE "MyTable".field_id = %(1234)s] [parameters: [{}]] (Background on this error at: http://sqlalche.me/e/cd3x)

Can someone explain to me what is wrong or the proper way to do it? Thanks.

Upvotes: 1

Views: 1203

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55589

There are two problems with the code.

Firstly,

str(getattr(t, column) == bindparam(value))

is binding the value as a placeholder, so you end up with

WHERE f2 = :Bob

but it should be the name that maps to the value in filters (so the column name in your case), so you end up with

WHERE f2 = :f2

Secondly, multiple WHERE conditions are being joined with a comma, but you should use AND or OR, depending on what you are trying to do.

Given a model Foo:

class Foo(Base):
    __tablename__ = 'foo'

    id = sa.Column(sa.Integer, primary_key=True)
    f1 = sa.Column(sa.Integer)
    f2 = sa.Column(sa.String)

Here's a working version of a segment of your code:

filters = {'f1': 2, 'f2': 'Bob'}
t = Foo

where_conditions = ''
count = 0
for column in filters:
    aux = str(getattr(t, column) == sa.bindparam(column))
    if count == 0:
        where_conditions += aux
    else:
        where_conditions += ' AND ' + aux
    count += 1
to_delete = t.__table__.delete().where(sa.text(where_conditions))
print(to_delete)
session.execute(to_delete, filters)

If you aren't obliged to construct the WHERE conditions as strings, you can do it like this:

where_conditions = [(getattr(t, column) == sa.bindparam(column))
                    for column in filters]
to_delete = t.__table__.delete().where(sa.and_(*where_conditions))
session.execute(to_delete, filters)

Upvotes: 1

Related Questions