Below is the model structure of my source code, represented as an array in a dictionary format.
# data structure
user_list = [{user_name: 'A',
email: '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="8feeeeeecfeeeeeea1ece0e2">[email protected]</a>',
items:[{name:'a_itme1', properties:[{1....},{2....}...]}
]} * 100]
I am attempting to insert this data into a Postgresql database using SQLAlchemy. The database includes tables for users, entities, attributes, and relationships between users/items and items/properties.
for u in user_list:
new_user = User(user_name=u.get('user_name'),....)
session.add(new_user)
session.flush()
for item in u.get('items'):
new_item = Item(name=item.get('name'),.....)
session.add(new_item)
session.flush()
new_item_link = UserItemLink(user_id=new_user.id, item_id=new_item.id,...)
session.add(new_item_link)
session.flush()
for prop in item.properties:
new_properties = Properties(name=prop.get('name'),...)
session.add(new_properties)
session.flush()
new_prop_link = ItemPropLink(item_id=new_item.id, prop_id=new_properties.id,...)
session.add(new_prop_link)
session.flush()
session.commit()
This is the simplified version of my models:
class User(Base):
__tablename__ = 'user'
id = Column(Integer, Identity(always=True, start=1, increment=1, minvalue=1, maxvalue=2147483647, cycle=False, cache=1), primary_key=True)
name = Column(String(20))
email = Column(String(50))
user_item_link = relationship('UserItemLink', back_populates='user')
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, Identity(always=True, start=1, increment=1, minvalue=1, maxvalue=2147483647, cycle=False, cache=1), primary_key=True)
name = Column(String(50))
note = Column(String(50))
user_item_link = relationship('UserItemLink', back_populates='item')
class Properties(Base):
__tablename__ = 'properties'
id = Column(Integer, Identity(always=True, start=1, increment=1, minvalue=1, maxvalue=2147483647, cycle=False, cache=1), primary_key=True)
name = Column(String(50))
value = Column(String(50))
item_prop_link = relationship('ItemPropLink', back_populates='properties')
class UserItemLink(Base):
__tablename__ = 'user_item_link'
id = Column(Integer, Identity(always=True, start=1, increment=1, minvalue=1, maxvalue=2147483647, cycle=False, cache=1), primary_key=True)
user_id = Column(ForeignKey('db.user.id'), nullable=False)
item_id = Column(ForeignKey('db.item.id'), nullable=False)
The above information has been condensed for clarity. However, I have noticed that there is a significant delay when adding user information sequentially in the current setup, taking around 8 seconds or more for inputting 100 users.
I would appreciate any advice on how to improve the efficiency of Python and SQLAlchemy processes.