sqlalchemy创建多对多数据表的映射

1、先把需要做多对多的模型定义出来
2、使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”
3、在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候需要传入一个secondary中间表

 1 #encoding:utf8
 2 # sqlalchemy创建多对多的关系表
 3 from sqlalchemy import create_engine,Column,Integer,String,Table,ForeignKey
 4 from sqlalchemy.ext.declarative import declarative_base
 5 from mysql_config import DB_URI
 6 from sqlalchemy.orm import sessionmaker,relationship
 7 
 8 
 9 # 数据库连接代码从from mysql_config import DB_URI中导入
10 # mysql连接格式
11 # DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format("这里填写需要的数据")
12 
13 engine = create_engine(DB_URI)
14 Base = declarative_base(engine)
15 session = sessionmaker(engine)()
16 
17 article_tag = Table(
18     "article_tag",
19     Base.metadata,
20     Column("article_id",Integer,ForeignKey("article.id"),primary_key=True),
21     Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True)
22                     )
23 class Article(Base):
24     __tablename__ = "article"
25     id = Column(Integer,primary_key=True,autoincrement=True)
26     title = Column(String(50),nullable=False)
27     tags = relationship("Tag",backref="articles",secondary=article_tag)
28     def __repr__(self):
29         return "<Article(title:%s)>"%self.title
30 class Tag(Base):
31     __tablename__ = "tag"
32     id = Column(Integer, primary_key=True, autoincrement=True)
33     name = Column(String(50), nullable=False)
34     def __repr__(self):
35         return "<Article(name:%s)>"%self.name
36 
37 def create_table():
38     Base.metadata.create_all()
39 
40 #测试数据，分别给article1、article2、tag1、tag2添加两条数据
41 def add_data():
42     article1 = Article(title="article1")
43     article2 = Article(title="article2")
44     tag1 = Tag(name="tag1")
45     tag2 = Tag(name="tag2")
46 
47     tag1.articles.append(article1)
48     tag1.articles.append(article2)
49 
50     tag2.articles.append(article1)
51     tag2.articles.append(article2)
52     session.add(tag1)
53     session.add(tag2)
54     session.commit()
55 
56     # 这是数据库插入后的数据
57     # mysql> select * from article
58     #     -> ;
59     # +----+----------+
60     # | id | title    |
61     # +----+----------+
62     # |  1 | article1 |
63     # |  2 | article2 |
64     # +----+----------+
65     # 2 rows in set (0.00 sec)
66     #
67     # mysql> select * from tag
68     #     -> ;
69     # +----+------+
70     # | id | name |
71     # +----+------+
72     # |  1 | tag1 |
73     # |  2 | tag2 |
74     # +----+------+
75     # 2 rows in set (0.00 sec)
76     #
77     # mysql> select * from article_tag
78     #     -> ;
79     # +------------+--------+
80     # | article_id | tag_id |
81     # +------------+--------+
82     # |          1 |      1 |
83     # |          2 |      1 |
84     # |          1 |      2 |
85     # |          2 |      2 |
86     # +------------+--------+
87     # 4 rows in set (0.00 sec)
88 
89 #测试查询
90 def search_data():
91     article = session.query(Article).first()
92     print("这是通过article参数查找tags:%s"%article.tags)
93     tag = session.query(Tag).first()
94     print("这是通过tag参数查找article:%s"%tag.articles)
95 
96 if __name__ == "__main__":
97     # create_table
98     # add_data()
99     search_data()

 

转载于:https://www.cnblogs.com/szq1998/p/10394632.html