Update multiple records with 'bindparam' found errors.

[nopparatc]

Hi,
I am exploring the databases module and found this issue.
Following examples how to update record using 'bindparam' on sqlalchemy.org website [here] and found this message

UPDATE users SET user_name=:newname WHERE users.user_name = :oldname
Traceback (most recent call last):
  File "dbmodel\model.py", line 299, in <module>
    loop.run_until_complete(run_async())
  File "C:\Python37\Lib\asyncio\base_events.py", line 579, in run_until_complete
    return future.result()
  File "dbmodel\model.py", line 253, in run_async
    await database.execute_many(query=stmt, values=values)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\databases\core.py", line 158, in execute_many
    return await connection.execute_many(query, values)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\databases\core.py", line 247, in execute_many
    await self._connection.execute_many(queries)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\databases\backends\sqlite.py", line 121, in execute_many
    await self.execute(single_query)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\databases\backends\sqlite.py", line 113, in execute
    query, args, context = self._compile(query)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\databases\backends\sqlite.py", line 140, in _compile
    compiled = query.compile(dialect=self._dialect)
  File "<string>", line 1, in <lambda>
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\elements.py", line 462, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\elements.py", line 468, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\compiler.py", line 571, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\compiler.py", line 319, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\compiler.py", line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\visitors.py", line 92, in _compiler_dispatch
    return meth(self, **kw)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\compiler.py", line 2569, in visit_update
    self, update_stmt, crud.ISUPDATE, **kw
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\crud.py", line 62, in _setup_crud_params
    return _get_crud_params(compiler, stmt, **kw)
  File "C:\Users\Acer\.virtualenvs\appserv-LCgxpdsg\lib\site-packages\sqlalchemy\sql\crud.py", line 177, in _get_crud_params
    % (", ".join("%s" % c for c in check))
sqlalchemy.exc.CompileError: Unconsumed column names: oldname, newname

This is the code that I used.

async def run_async():
    # Establish the connection
    await database.connect()
    async with database.transaction():
        stmt = users.update().\
                where(users.c.user_name == bindparam('oldname')).\
                values(user_name=bindparam('newname'))
        print(stmt)
        values = [
            {'oldname':'jack', 'newname':'ed'},
            {'oldname':'wendy', 'newname':'mary'},
            {'oldname':'jim', 'newname':'jake'}
        ]
        await database.execute_many(stmt,values)
    await database.disconnect()

loop = asyncio.get_event_loop()
loop.run_until_complete(run_async())
loop.close()

[reillysiemens]

@nopparat-chomchoei: I think this is related to issues with using bindparam that I uncovered in #79.

[ojii]

ran into the same issue today. Is the best workaround for this to do a for-loop in a transaction and update them all inidividually?

[cyberbudy]

+1

[acgnhiki]

+1

[dev-sdrosa]

+1

[zshell31]

+1