CODEWITHSUNDEEP
pythondatabasesqlalchemybulkupdate
Malyshev Slava
Malyshev Slava

Reputation: 21

Bulk update in SQLAlchemy with python databases.Database

I have postgres data scheme:

table_tools = sqlalchemy.Table(
    settings.sql_tool_table_name,
    metadata,
    sqlalchemy.Column('id', UUID,
                      primary_key=True,
                      server_default=sqlalchemy.text(
                          "uuid_generate_v4()"),
                      index=True),
    Column('article', sqlalchemy.String(50), nullable=True),
    Column('title', sqlalchemy.String, nullable=False),
    Column('base_title', sqlalchemy.String, nullable=False,
           index=True),
    Column('maker', sqlalchemy.String(50), nullable=False),
    Column('description', sqlalchemy.String, nullable=True),
    Column('price', sqlalchemy.Float),
    Column('currency', sqlalchemy.String(5)),
    Column('created_at', sqlalchemy.DateTime(timezone=True),
           default=func.now()),
    Column('updated_at',
           sqlalchemy.DateTime(timezone=True), default=func.now(),
           onupdate=func.now()),
    UniqueConstraint('base_title', 'maker',
                     name='unique_base_title_maker')
)

I am using pedantic transitional models in this project. I want to update the data by id using the bulk_update method as follows:

async def update_tool_bulk(self, tools: list[ToolIn]):
    tools_load = []
    for tool in tools:
        tool_db: Record = await self.get_tool(tool.title, tool.maker)
        tool_id: UUID = dict(**tool_db).get('id')
        tool_upd = ToolInFull(**tool.dict())
        tools_load.append({'_id': tool_id} | tool_upd.dict())
    self.table_tools.update()
    query = self.table_tools.update(
        self.table_tools.columns.id == bindparam('_id')).values({
            'article': bindparam('article'),
            'title': bindparam('title'),
            'base_title': bindparam('base_title'),
            'maker': bindparam('maker'),
            'description': bindparam('description'),
            'price': bindparam('price'),
            'currency': bindparam('currency')
        })
    await self.database.execute_many(query=query, values=tools_load)

When executing this code, I get an error:

Unconsumed column names: _id

If I replace the '_id' parameter with 'id', I get an error:

Bind parameter 'id' conflicts with unique bind parameter of the same name

Tell me, what am I doing wrong?

Upvotes: 0

Views: 410

Answers (1)

Malyshev Slava
Malyshev Slava

Reputation: 21

I solved the problem - it is necessary to convert to a query string: self.database.execute_many(query=str(query), values=tools_load)

Upvotes: 2

Related Questions