This is a toy example of my problem. I have two tables with a one-to-many relationship, defined in an SQLAlchemy model as follows:
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True, autoincrement=True)
author_name = Column(String(32))
articles = relationship('Article', back_populates='author')
class Article(Base):
__tablename__ = 'articles'
id = Column(Integer, primary_key=True, autoincrement=True)
author_id = Column('author', Integer, ForeignKey('authors.id'))
author = relationship('Author', back_populates='articles')
article_text = Column(String)
When I add a new post, an author's name is passed in as a string in the Article.author field. When adding an article to the database, to create a link between it and the author, I check to see if the user exists by querying with the username, and then obtain the model object for that user:
def get_user(post, session):
if session.query(exists().where(User.username==post.user)).scalar():
user = session.query(User).filter_by(username=post.user).first()
post.user = user
return post
def insert_post(post, session):
post = get_user(post, session)
try:
session.add(row)
session.commit()
except:
session.rollback()
finally:
session.close()
This works fine, but it is slow (tens of minutes for inserting around 200,000 articles). In case it helps to know, I'm using MySQL.
I feel like I'm either making unnecessary queries, or have misunderstood how to make ForeignKey relationships work. Or perhaps this is totally normal?
