bindparam does not work

[alvassin]

Perhaps you could advice some fast fix or workaround for that? I need update for many different rows with different values. Previously used bindparam for that.

import asyncio

from asyncpgsa import PG
from sqlalchemy import Table, MetaData, Column, Integer, String, bindparam, \
    create_engine

metadata = MetaData()

table = Table(
    'citizens',
    metadata,
    Column('test_id', Integer, primary_key=True),
    Column('name', String, nullable=False),
)

DB_URL = 'postgresql://user:[email protected]/db'


async def main():
    # create table
    engine = create_engine(DB_URL)
    metadata.create_all(engine)

    # connect to db
    pg = PG()
    await pg.init(DB_URL)
    async with pg.transaction() as conn:
        # create
        query = table.insert().values([
            {'name': str(i)} for i in range(10)
        ]).returning(table)
        rows = await conn.fetch(query)

        # update
        query = table.update().values(name=bindparam('name'))
        await conn.execute(query, [
            {'test_id': row['test_id'], 'name': row['name'] + '_new'}
            for row in rows
        ])

        # check
        # asyncpg.exceptions.NotNullViolationError: null value in column "name" violates not-null constraint
        # DETAIL:  Failing row contains (31, null).
        results = await conn.execute(table.select())
        print(results)

asyncio.run(main())

[alvassin]

It seems that args are lost here:
https://github.com/CanopyTax/asyncpgsa/blob/master/asyncpgsa/connection.py#L99

[nhumrich]

im not really sure how to fix/handle this one. Sqlalchemy has an "execute many" function in which it strings together a bunch of queries in one transaction for. If it detects execute getting a list, it calls this method instead of the standard excecute. As asyncpg doesnt have that functionality, I am not really sure how to support this.
We could potentially do a similar thing as sqlalchemy and detect whether this is a executemany or not, string together all the requests, seperated by ;, and then execute the entire string. Then replace all the bind's ourselves with the current params.
its enough effort that I dont have time for it currently. But if this is a needed feature for you I would be willing to accept a pull request with tests.

[kchaliki]

Hello there, am currently stuck on the same issue. Just curious, can this be achieved using the explicit executemany somehow?

[kchaliki]

Turns out that it does using $1, $2, etc style params and a list of tuples