1

I'm making a small and simple API, to do some CRUD operations. And I have never used an ORM before.

I have two tables with the exact same schema, one for active state and one for historic events.

I came up with this hacky way of inserting the records to the historic tables:

crud.py

def update_active(db: Session, active: schemas.Active):
    if active.case_1:
        active.status = "Case 1"
    elif active.case_2:
        active.status = "Case 2"
    elif active.case_3:
        active.status = "Case 3"
    else:
        active.case_4= "Case 0"
    active.timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    if not active.case_end:
        active.case_end = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    if active.status == "Case 0":
        history = db.query(models.Active).filter(
            models.Active.node_id == active.node_id and 
            models.Active.frequency == active.frequency and 
            models.Active.status != active.status
            ).all()
        for result in history:
            copy_res = result.__dict__.copy()
            copy_res.pop('_sa_instance_state')
            query = insert(models.History).values(**copy_res)
            db.execute(query)
      
    db.query(models.Active).filter(
        models.Active.node_id == active.node_id and 
        models.Active.frequency == active.frequency
    ).update(active)
    db.commit()
    return active

I don't like this part:

    for result in history:
        copy_res = result.__dict__.copy()
        copy_res.pop('_sa_instance_state')
        query = insert(models.History).values(**copy_res)
        db.execute(query)

Is there a better way to insert these into the historic table?

kenlukas
  • 3,616
  • 9
  • 25
  • 36
Paal Pedersen
  • 1,070
  • 1
  • 10
  • 13
  • If the schemas are the same I'd use "bare" SQL, ending up to be faster. – MetallimaX Dec 17 '20 at 17:13
  • I only want to maintain schema in the models.py and schemas.py if I abreviate from this I would have to maintain schema in the bare sql query too. of course I could build it from the dictionary, but then we are still on the hacky side. Or do you have a better though in mind? – Paal Pedersen Dec 17 '20 at 17:30
  • `INSERT INTO table2 SELECT * FROM table1 WHERE condition;` – MetallimaX Dec 17 '20 at 17:32
  • I don't want to maintain sql query. I just though there was a simple way in sqlalchemy to do this: SELECT * INTO history FROM active WHERE condition when working with two identical schemas. – Paal Pedersen Dec 17 '20 at 17:39
  • 1
    https://stackoverflow.com/questions/1849375/how-do-i-insert-into-t1-select-from-t2-in-sqlalchemy – MetallimaX Dec 17 '20 at 17:43

0 Answers0