SQLalchemy Bulk insert with one to one relation

Question

I have the following model where TableA and TableB have 1 to 1 relationship:

class TableA(db.Model):
    id = Column(db.BigInteger, primary_key=True)
    title = Column(String(1024))
    table_b = relationship('TableB', uselist=False, back_populates="table_a")


class TableB(db.Model):
    id = Column(BigInteger, ForeignKey(TableA.id), primary_key=True)
    a = relationship('TableA', back_populates='table_b')
    name = Column(String(1024))

when I insert 1 record everything goes fine:

rec_a = TableA(title='hello')
rec_b = TableB(a=rec_a, name='world')
db.session.add(rec_b)
db.session.commit()

but when I try to do this for bulk of records:

bulk_ = []

for title, name in zip(titles, names):
    rec_a = TableA(title=title)
    bulk_.append(TableB(a=rec_a, name=name))

db.session.bulk_save_objects(bulk_)
db.session.commit()

I get the following exception:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1364, "Field 'id' doesn't have a default value")

Am I doing something wrong? Did I configure the model wrong? Is there a way to bulk commit this type of data?

score 5 · Accepted Answer · answered Apr 03 '16 at 16:19

The error you see is thrown by Mysql. It is complaining that the attempt to insert records into table_b violates the foreign key constraint.

One technique could be to write all the titles in one bulk statement, then write all the names in a 2nd bulk statement. Also, I've never passed relationships successfully to bulk operations, to this method relies on inserting simple values.

bulk_titles = [TableA(title=title) for title in titles]
session.bulk_save_objects(bulk_titles, return_defauls=True)
bulk_names = [TableB(id=title.id, name=name) for title, name in zip(bulk_titles, names)]
session.bulk_save_objects(bulk_names)

return_defaults=True is needed above because we need title.id in the 2nd bulk operation. But this greatly reduces the performance gains of the bulk operation

To avoid the performance degradation due to return_defauts=True, you could generate the primary keys from the application, rather than the database, e.g. using uuids, or fetching the max id in each table and generating a range from that start value.

Another technique might be to write your bulk insert statement using sqlalchemy core or plain text.

SQLalchemy Bulk insert with one to one relation

1 Answers1

Linked