Bulk insert into multiple tables using SQLAlchemy ORM

Question

I am trying to do a bulk insert into 2 tables using Flask-SQLAlchemy. The 2 tables are:

author table: PK author_id (auto-increment)
book table: PK book_id (ai), FK author_author_id

In the body of the JSON, I have a list of dictionaries. Each dictionary entry has some author-related info and some book-related info. Something like this, there can be many more dictionaries being sent in one go:

[
    {
        "author_first_name": "John",
        "author_last_name": "Doe",
        "author_yob": "1988",
        "book_name": "Mournings of a nun",
        "book_genre": "Drama"
    },
    {
        "author_first_name": "Jane",
        "author_last_name": "Doe",
        "author_yob": "1987",
        "book_name": "Need for speed",
        "book_genre": "Action"
    }
]

Currently, I am looping through each dictionary, inserting data into the author table, and then into book table. When I insert into the author table and commit, I get a primary key back, which is author_id. That is the foreign key for my book table.

I repeat this step for every entry in this list. Is there a way to do bulk inserts so that if any insert fails everything is rolled back and I don't have inconsistent data in my database? So if there are 15 dictionaries in the JSON above, if the 12th one has some invalid data or the the database goes down, I want that none of the data sent in the JSON should be posted to the AWS RDS. Below, "results" refers to the JSON I have mentioned above.

    @classmethod
    def post_to_database(cls, results):
        for result in results:
            BookModel.post_entry_to_database(result)


    @classmethod
    def post_entry_to_database(cls, result):
        BookModel.insert_author_entry(result)
        author_id = BookModel.get_author_id(result)
        BookModel.insert_book_entry(author_id, result)


    @classmethod
    def insert_book_entry(cls, author_id, result):
        book_data = BookModel(result["testname"], result["result"], author_id)
        db.session.add(book_data)
        db.session.commit()

Similarly, I have insert_author_entry as well.

Thanks, Aditya

Wes Doyle · Accepted Answer · 2018-12-15 02:45:56Z

2

You could consider using flush() to flush changes to the database, which will update your primary key field.

From the SqlAlchemy Docs: flush

Database operations will be issued in the current transactional context and do not affect the state of the transaction, unless an error occurs, in which case the entire transaction is rolled back. You may flush() as often as you like within a transaction to move changes from Python to the database’s transaction buffer.

When calling flush, The database maintains your CRUD operations as pending in a transaction and are not permanently persisted until the database receives a COMMIT for that current transaction. This will happen when you subsequently call commit().

edited Dec 15, 2018 at 2:45

answered Dec 15, 2018 at 2:40

Wes Doyle

2,2973 gold badges19 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Bulk insert into multiple tables using SQLAlchemy ORM

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related