mardi 7 avril 2020

SQLAlchemy select with random order of multiple rows and then insert

Goal:

An select statement (or any other code), resulting with randomized order of rows (applicable if row count is larger than 2; code is replaced with '...' to obfuscate sensitive/production code)

Python code:

...
ResultProxy = connection.execute(query_t0)
query_t1 = db.select([table1.columns.data_id,table1.columns.username,table1.columns.stack,table1.columns.numerator]).where(db.and_(table1.columns.numerator == numerator, table1.columns.active.is_(False), table1.columns.bet_id == bet_id)).order_by(func.random()).limit(ResultProxy.rowcount/2)
...
db.insert(table2).from_select(['data_id','username', 'stack', 'numerator'], query_t1)
...

Current result:

If first query select returns the table (column username with column stack combination in table is unique, all others can be identical/same/repeated), for example:

enter image description here

, it is as is being inserted.

Expected result:

The rows are shuffled randomly in first select and therefore inserted in random order in insert statement.

How to do it?




Aucun commentaire:

Enregistrer un commentaire