数据库多对多关联表（Python&MySQL）

Python

Python对MySQL数据库操作使用的是sqlalchemy这个ORM框架

#一本书可以有多个作者，一个作者又可以出版多本书


from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


Base = declarative_base()

book_m2m_author = Table('book_m2m_author', Base.metadata,
                        Column('book_id',Integer,ForeignKey('books.id')),
                        Column('author_id',Integer,ForeignKey('authors.id')),
                        )

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    authors = relationship('Author',secondary=book_m2m_author,backref='books')

    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    def __repr__(self):
        return self.name

orm 多对多

book_m2m_author是第三张表也就是关联表

添加数据

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
s = Session_class() #生成session实例
 
b1 = Book(name="你是123")
b2 = Book(name="你是456")
b3 = Book(name="你是789")
b4 = Book(name="你是990")
 
a1 = Author(name="Bob")
a2 = Author(name="Jack")
a3 = Author(name="Rain")
 
b1.authors = [a1,a2]
b2.authors = [a1,a2,a3]
 
s.add_all([b1,b2,b3,b4,a1,a2,a3])
 
s.commit()

结果查询关联表

mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       2 |        10 |
|       2 |        11 |
|       2 |        12 |
|       1 |        10 |
|       1 |        11 |
+---------+-----------+

book_m2m_author中自动创建了多条纪录用来连接book和author表
同样也会自动删除

A behavior which is unique to the secondary argument to relationship() is that the Table which is specified here is automatically subject to INSERT and DELETE statements, as objects are added or removed from the collection. There is no need to delete from this table manually. The act of removing a record from the collection will have the effect of the row being deleted on flush

参考：http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many

MySQL多对多关联

create table user
(
   user_id              int not null auto_increment,
   user_name            varchar(20),
   primary key (user_id)
);


create table tags
(
   tag_id               int not null auto_increment,
   tag_name             varchar(100),
   primary key (tag_id)
);


create table user_tag
(
   user_id   int,
   tag_id    int，
 key(user_id) references user(user_id),
foreign key(tag_iod) references parts(tag_id)   
);

插入数据

insert into user values(1,'小A');
insert into user values(2,'小B');
insert into user values(3,'小C');

insert into tags values(1,'唱歌');
insert into tags values(2,'跳舞');
insert into tags values(3,'宅');
insert into tags values(4,'看书');
insert into tags values(5,'旅游');

insert into user_tag values(1,1);
insert into user_tag values(1,2);
insert into user_tag values(2,1);
insert into user_tag values(3,1);
insert into user_tag values(3,4);
insert into user_tag values(3,5);