如何使用PD.TO_SQL和SQLALCHEMY功能将PANDAS DataFrame传输到匹配外国密钥的数据库？
我想将数据附加到一个数据库表中，该数据库表具有链接到另一个表的外键，但我没有找到一种使用SQLalchemy功能来匹配现有外国密钥的方法。
假设我有一个演员表，该表会自动生成其主要键和一个电影表，该表与演员ID链接到带有外键的演员ID（一对多关系）：
class Actor(Base):
__tablename__ = 'actors'
id = Column(Integer, primary_key=True)
name = Column(String)
def __init__(self, name, birthday):
self.name = name
class Movie(Base):
__tablename__ = 'movies'
id = Column(Integer, primary_key=True)
title = Column(String)
actor_id = Column(Integer, ForeignKey('actors.id'))
actor = relationship("Actor", backref="movies")
def __init__(self, title, release_date):
self.title = title
self.actor = actor
我想在“电影”表中添加带有电影标题的数据框架，对于已经存在的“演员”表中的演员。
movies_df = pd.DataFrame({'title': ['bourne_identity', 'furious_7'], 'actor':['matt_damon', 'dwayne_johnson']})
我可以阅读“演员”表，检索参与者的ID并生成一个可以轻松加载到数据库的数据框架：
movies_df_id = pd.DataFrame({'title': ['bourne_identity', 'furious_7'], 'actor_id':[0, 1]})
movies_df_id.to_sql('movies',con=engine, if_exists='append',index=False)
是否有一种方法可以使用SQLalchemy Orm功能来实现此目的，然后将每个电影的演员名称传递给该数据库无需首先检索和匹配其ID吗？
I want to append data to a database table that has foreign keys linking to another table but I haven’t found a way to use SQLAlchemy functionality to match the existing foreign keys.
Let’s say I have an actor table that automatically generates its primary keys and a movies table that links to the actor id with a foreign key (one to many relationship):
class Actor(Base):
__tablename__ = 'actors'
id = Column(Integer, primary_key=True)
name = Column(String)
def __init__(self, name, birthday):
self.name = name
class Movie(Base):
__tablename__ = 'movies'
id = Column(Integer, primary_key=True)
title = Column(String)
actor_id = Column(Integer, ForeignKey('actors.id'))
actor = relationship("Actor", backref="movies")
def __init__(self, title, release_date):
self.title = title
self.actor = actor
I want to add a dataframe with movie titles to the “movies” table, for actors that already exist in the “actors” table.
movies_df = pd.DataFrame({'title': ['bourne_identity', 'furious_7'], 'actor':['matt_damon', 'dwayne_johnson']})
I could read the “actors” table, retrieve the ids for the actors and generate a dataframe that could easily load to the database:
movies_df_id = pd.DataFrame({'title': ['bourne_identity', 'furious_7'], 'actor_id':[0, 1]})
movies_df_id.to_sql('movies',con=engine, if_exists='append',index=False)
Is there a way to use the SQLAlchemy ORM functionality to achieve this and just pass the actor names for each movie to the database without having to retrieve and match their ids first?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论