Update and Delete

[ferasawadi]

Hi, first of all thank you for this project . i really appreciate it.

problem : am trying to figure out how to update or delete rows in a table of database. i really appreciate any help. Thanks

[gogobook]

It may denpendent on which database you used, This is for sqlite.

from databases import Database as Db

create_deal_table = '''create table DEAL(
    id integer primary key AUTOINCREMENT,
    PrimaryKey varchar,
    taxNo varchar,
    getCase int 
    )'''

create_deal_data ='''insert into DEAL(
        PrimaryKey,
        taxNo,
        getCase
    ) 
    values (
        :PrimaryKey,
        :taxNo,
        :getCase
    )'''
data = {'PrimaryKey':1, 'taxNo': '123456', 'getCase':0}
data2 = {'PrimaryKey':1, 'taxNo': '123456', 'getCase':1}

async with Db('sqlite:///data.sqlite3') as db:
    await db.execute(query=create_deal_table)
    await db.execute(query=create_deal_data, values=data)
    # await db.execute(query='update DEAL set taxNo=:taxNo, getCase=:getCase where PrimaryKey=:PrimaryKey', values=data2)
    # await db.execute(query='delete from DEAL where PrimaryKey=:PrimaryKey', values= {'PrimaryKey':1})
    rows = await db.fetch_all(query='select * from DEAL')
    print(rows)

The code can run in jupyter notebook directively.

[wotori]

@gogobook the question was about update and delete statement. @ferasawadi This is how you can do an update. Found at sqlalchemy core documentation.

update

    async with database.transaction():
        query = Model.update() \
            .where(Model.c.uid == data.uid)
        await database.execute(query=query, values=dict(order))

delete

query = Model.delete().where(Model.c.uid == uid)
await database.execute(query)