Skip to content
The Chat Archive
gpt-3.5-turbo-0301· OpenAI WildChat-4.8M (AI2)

sqlalcemy one to many

  1. User

    sqlalcemy one to many

  2. Assistant

    SQLAlchemy allows creating one-to-many relationships between tables using the relationship() function. Here is an example of how to create a one-to-many relationship between a User table and a Post table:

    from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
    from sqlalchemy.orm import relationship, backref
    from sqlalchemy.ext.declarative import declarative_base
    
    engine = create_engine('sqlite:///example.db', echo=True)
    Base = declarative_base()
    
    class User(Base):
        __tablename__ = 'users'
        id = Column(Integer, primary_key=True)
        name = Column(String)
        # one-to-many relationship between User and Post
        posts = relationship("Post", backref="user")
    
    class Post(Base):
        __tablename__ = 'posts'
        id = Column(Integer, primary_key=True)
        title = Column(String)
        content = Column(String)
        user_id = Column(Integer, ForeignKey('users.id'))
    
    Base.metadata.create_all(engine)
    

    In the User table, we define a one-to-many relationship with Post table using the posts attribute which is created with the relationship() function. This will create a list of posts associated with each user.

    In the Post table, we define a foreign key that references the id column of the User table. This creates a relationship between User and Post such that each post belongs to one user, and each user can have multiple posts.

    We can access the posts associated with a user using the posts attribute, and we can access the user associated with a post using the user attribute.

    Here's an example of using this relationship to create a user and a post associated with that user:

    from sqlalchemy.orm import sessionmaker
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    user = User(name='Alice')
    session.add(user)
    
    post = Post(title='My first post', content='This is my first post on this website.', user=user)
    session.add(post)
    
    session.commit()
    

    In this example, we create a user named Alice and a post with the title "My first post" and content "This is my first post on this website". We set the user attribute of the post to the user we just created. When we commit the session, both the user and the post will be added to the database with the appropriate foreign key relationship.