SQLAlchemy clone table row with relations

Question

Following on from this question SQLAlchemy: Modification of detached object.

This makes a copy of the object fine, but it loses any many-to-many relationships the original object had. Is there a way to copy the object and any many-to-many relationships as well?

Cheers!

score 4 · Answer 1 · edited May 23 '17 at 12:26

4

I got this to work by walking the object graph and doing the expunge(), make_transient() and id = None steps on each object in the graph as described in SQLAlchemy: Modification of detached object.

edited May 23 '17 at 12:26

Community

1
1

answered Apr 21 '14 at 03:22

David K. Hess

16,632
2
49
73

How did you implement the 'graph walking'? is there any sample code you can paste in? Cheers. (I have a parent row, with lots of child rows, each of which has more child rows; and I want to make a clone of the parent row in a table, and also automatically clone all the related rows in child and grand-child tables.) – Soferio Jan 01 '15 at 06:56
I just found this old discussion, and I suppose this answers it. It has to be done manually: https://groups.google.com/forum/#!topic/sqlalchemy/wb2M_oYkQdY – Soferio Jan 01 '15 at 10:15
1

Yes, you iterate the containers and follow the relationships manually. – David K. Hess Jan 02 '15 at 14:14

score 1 · Answer 2 · edited May 25 '20 at 10:24

Here is my sample code. The agent has at most one campaign.

from sqlalchemy.orm.session import make_transient

def clone_agent(id):
    s = app.db.session
    agent = s.query(Agent).get(id)
    c = None

    # You need to get child before expunge agent, otherwise the children will be empty
    if agent.campaigns:
        c = agent.campaigns[0]
        s.expunge(c)
        make_transient(c)
        c.id = None

    s.expunge(agent)
    agent.id = None

    # I have unique constraint on the following column.
    agent.name = agent.name + '_clone'
    agent.externalId = - agent.externalId # Find a number that is not in db.

    make_transient(agent)
    s.add(agent)
    s.commit() # Commit so the agent will save to database and get an id

    if c:
        assert agent.id
        c.agent_id = agent.id # Attach child to parent (agent_id is a foreign key)
        s.add(c)
        s.commit()

SQLAlchemy clone table row with relations

2 Answers2

Linked