Bulk move rows from one table to another with SQLAlchemy

Question

I have two identical tables post and old_post. I have a query that checks for old posts. I would like to move the rows returned by the query into the table old_post and delete the rows from table post.

I could solve this by iterating through the results returned by the initial query and update my results this way, however I am worried this is very inefficient and will start to cause me problems when I have 1,000+ rows. How can I efficiently "bulk move" rows from one table to another?

Answer 1

Query for old Posts. Bulk insert OldPosts with the old Posts' data. Bulk delete the old Posts.

keys = db.inspect(Post).columns.keys()
get_columns = lambda post: {key: getattr(post, key) for key in keys}

posts = Post.query.filter(Post.expiry > ts)
db.session.bulk_insert_mappings(OldPost, (get_columns(post) for post in posts))
posts.delete()
db.session.commit()

The get_columns function takes a Post instance and creates a dictionary out of the column keys and values. Read the docs and warnings about using bulk insert and delete operations.

Answer 2

You can use Common Table Expressions in PostgreSQL

WITH moved_posts AS (
    DELETE FROM post
    WHERE expiry > time_stamp
    RETURNING *
)
INSERT INTO old_post
SELECT * from moved_posts

CTE support for DELETE will be added in SQLAlchemy 1.1. In current release you can execute raw SQL

from sqlalchemy import text

sql = text('''
WITH moved_posts AS (
    DELETE FROM post
    WHERE expiry > ?
    RETURNING *
)
INSERT INTO old_post
SELECT * from moved_posts
''')
db.session.execute(sql, [time_stamp])
db.session.commit()

In SQLAlchemy 1.1 it would look like this

posts = Post.__table__
old_posts = OldPost.__table__
moved_posts = (
    posts.delete()
    .where(posts.c.expiry > ts)
    .returning(*(posts.c._all_columns))
    .cte('moved_posts'))
insert = (
    old_posts.insert()
    .from_select(
        [c.name for c in moved_posts.columns],
        moved_posts.select()
    ))
db.session.execute(insert)
db.session.commit()