CODEWITHSUNDEEP
pythonmysqlsqlalchemyflask-sqlalchemybulkinsert
Ady
Ady

Reputation: 35

Bulk insert into multiple tables using SQLAlchemy ORM

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

  1. author table: PK author_id (auto-increment)
  2. 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

Upvotes: 1

Views: 2569

Answers (1)

Wes Doyle
Wes Doyle

Reputation: 2287

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().

Upvotes: 2

Related Questions