sqlalchemy_one2many_一对多

sqlalchemy_one_to_many_relationship-main/delete.py

from main import Post,User,session


print(session.query(Post).all())
print(session.query(User).all())


# 删除这个user,呢么在post表里面所有关于这个user的数据也会被删除
# posts=relationship('Post',back_populates='author',cascade='all, delete')
user_to_delete=session.query(User).filter(User.id==1).first()
session.delete(user_to_delete)
session.commit()


print(session.query(Post).all())
print(session.query(User).all())

# output:---------------------------------------------------------------------
# => (venv) song@songdembp@ sqlalchemy_one_to_many_relationship-main % python delete.py 
# => [<User Learn Django>, <User Learn Java>, <User Learn Javascript>, <User Learn HTML>, <User Learn css>]
# => [<User testuser>]
# => []
# => []```
# `sqlalchemy_one_to_many_relationship-main/query.py`

```py
from main import Post,User,session

# ----------------------------------------------------------------------------------
# 查询所有的 user.id ==1,的post
# class User(Base):
#     __tablename__='users'
#     id=Column(Integer(),primary_key=True)
#     username=Column(String(40),nullable=False)
#     email=Column(String(40),nullable=True)

#     posts=relationship('Post',back_populates='author',cascade='all, delete')

user=session.query(User).filter(User.id==1).first()
# posts变成user的一个属性,直接打印就行,就是得出结果,不需要在到数据中使用select * from post where id == user.id,类似的语句,直接使用就行
print(user.posts)



# ----------------------------------------------------------------------------------
# 同理,查询author的时候,也是直接查询,通过属性形式
# class Post(Base):
#     __tablename__='posts'
#     id=Column(Integer(),primary_key=True)
#     title=Column(String(45),nullable=False)
#     content=Column(String(255),nullable=False)
#     user_id=Column(Integer(),ForeignKey('users.id'))

#     author=relationship('User',back_populates='posts')
post=session.query(Post).filter(Post.id==1).first()
print(post)
print(post.author)

# --------------------------------------------------------------------------------------------------
# 关键的几行代码:

# class User(Base):
#     posts=relationship('Post',back_populates='author',cascade='all, delete')

# class Post(Base):
#     author=relationship('User',back_populates='posts')

sqlalchemy_one_to_many_relationship-main/query copy.py

from main import Post,User,session


new_user=User(
    username="testuser",
    email="testuser@gmail.com"
)

session.add(new_user)
session.commit()


posts=[
    {
        "title":"Learn Django",
        "content":"Lorem ipsum"
    },
      {
        "title":"Learn Java",
        "content":"Lorem ipsum"
    },
      {
        "title":"Learn Javascript",
        "content":"Lorem ipsum"
    },
      {
        "title":"Learn HTML",
        "content":"Lorem ipsum"
    },
      {
        "title":"Learn css",
        "content":"Lorem ipsum"
    },

]

user=session.query(User).filter(User.id==1).first()


# for post in posts:
#     new_post=Post(
#         title=post['title'],
#         content=post['content'],
#         author=user
#     )

#     session.add(new_post)
#     session.commit()

#     print(f"Post cREATED {post['title']}")


post=session.query(Post).filter(Post.id==1).first()
print(post.author)
print(user.posts)```
# `sqlalchemy_one_to_many_relationship-main/main.py`

```py
#user
#posts
import os
from sqlalchemy import (
    create_engine,
    Integer,
    Column,
    String,
    ForeignKey
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker


BASE_DIR=os.path.dirname(os.path.realpath(__file__))

conn='sqlite:///'+os.path.join(BASE_DIR,'blog.db')


engine=create_engine(conn)


Base=declarative_base()


"""
class User:
    id:int pk
    username:str
    email:str


class Post:
    id:int pk
    title:str
    content:str
    user_id:int foreignkey
"""


class User(Base):
    __tablename__='users'
    id=Column(Integer(),primary_key=True)
    username=Column(String(40),nullable=False)
    email=Column(String(40),nullable=True)
    posts=relationship('Post',back_populates='author',cascade='all, delete')

    def __repr__(self):
        return f"<User {self.username}>"

class Post(Base):
    __tablename__='posts'
    id=Column(Integer(),primary_key=True)
    title=Column(String(45),nullable=False)
    content=Column(String(255),nullable=False)
    user_id=Column(Integer(),ForeignKey('users.id'))

    author=relationship('User',back_populates='posts')


    
    def __repr__(self):
        return f"<User {self.title}>"


Base.metadata.create_all(engine) #creates the database

session=sessionmaker()(bind=engine)

sqlalchemy_one_to_many_relationship-main/populate.py

from main import Post,User,session


new_user=User(
    username="testuser",
    email="testuser@gmail.com"
)

session.add(new_user)
session.commit()


posts=[
    {
        "title":"Learn Django",
        "content":"Lorem ipsum"
    },
      {
        "title":"Learn Java",
        "content":"Lorem ipsum"
    },
      {
        "title":"Learn Javascript",
        "content":"Lorem ipsum"
    },
      {
        "title":"Learn HTML",
        "content":"Lorem ipsum"
    },
      {
        "title":"Learn css",
        "content":"Lorem ipsum"
    },

]


user=session.query(User).filter(User.id==1).first()

# class Post(Base):
#     __tablename__='posts'
#     id=Column(Integer(),primary_key=True)
#     title=Column(String(45),nullable=False)
#     content=Column(String(255),nullable=False)
#     user_id=Column(Integer(),ForeignKey('users.id'))
#     author=relationship('User',back_populates='posts')



for post in posts:
    new_post=Post(
        title=post['title'],
        content=post['content'],
        author=user
    )

    session.add(new_post)
    session.commit()

    print(f"Post cREATED {post['title']}")

生成的数据表