Delete rows from Query Object

Question

I was wondering if it's possible to delete some random rows from a Query Object before doing a bulk update.

Example:

writerRes = self.session.query(table)
writerRes = writerRes.filter(table.userID==3)
-> Delete some of the rows randomly
writerRes.update({"userID": 4})

Is there an easy way to do that?

Community · Accepted Answer · 2017-05-23 12:18:45Z

1

Selecting random row with SA depends on the database. Based on that answer.

Postgresql and Sqlite3:

number_of_random_rows = 3
rand_rows = session.query(table.userid).order_by(func.random()).limit(number_of_random_rows).subquery()
session.query(table).filter(table.userid.in_(rand_rows)).delete(synchronize_session='fetch')

MySQL:

number_of_random_rows = 3
rand_rows = session.query(table.userid).order_by(func.rand()).limit(number_of_random_rows).subquery()
session.query(table).filter(table.userid.in_(rand_rows)).delete(synchronize_session='fetch')

...

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Dec 21, 2012 at 1:02

Tony Gibbs

2,5091 gold badge20 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jokober Over a year ago

I made a mistake ... The code looks great but it doesn't work for me. I get this error OperationalError: (OperationalError) only a single result allowed for a SELECT that is part of an expression. Any Idea?

Tony Gibbs Over a year ago

What database are you using and what code are you running? I only tested it on SQLite & Postgres.

jokober Over a year ago

I'm using the SQLite Database and I'm running the code you posted except that I filter the rows before running your code: writerRes = writerRes.filter(table.userID==4)

Collectives™ on Stack Overflow

Delete rows from Query Object

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related