SQLAlchemy relationship

SQLAlchemy

One_to_Many(Many_to_One)

首先创建两张表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
from sqlalchemy import create_engine, Column, String, Date, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
from datetime import date

HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = '123456'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URI)
Base = declarative_base(engine)
class Singer(Base):
__tablename__ = 'singer'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(20))
age = Column(Integer)
# songs = relationship('Song')
def __repr__(self):
return 'id: {}|name: {}|age: {}'.format(self.id, self.name, self.age)


class Song(Base):
__tablename__ = 'song'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(20))
publish_time = Column(Date)
# ForeignKey是相对于MYSQL而言,及singer表的id字段
sid = Column(Integer, ForeignKey('singer.id'))
# singer = relationship('Singer')
# singer = relationship('Singer', backref='songs')

def __repr__(self):
return 'id: {}|name: {}|publish_time: {}|sid: {}|singer:{}'.format(self.id, self.name, self.publish_time, self.sid, self.singer.name)

Base.metadata.drop_all()
Base.metadata.create_all()
session = sessionmaker(engine)()
session.add(Singer(name='周杰伦', age=39))
session.add(Song(name='晴天', publish_time=date(year=2003, month=7, day=31), sid=1))
session.commit()

创建表并插入数据后结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from singer;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 周杰伦 | 39 |
+----+-----------+------+
1 row in set (0.00 sec)

mysql> select * from song;
+----+--------+--------------+------+
| id | name | publish_time | sid |
+----+--------+--------------+------+
| 1 | 晴天 | 2003-07-31 | 1 |
+----+--------+--------------+------+
1 row in set (0.00 sec)

此时若想获取singer表中歌手的歌曲信息,则只能如下

1
2
3
singer = session.query(Singer).first()
sid = singer.id
print(session.query(Song).get(sid))

通过获取singer表中歌手的id以此id作为查询歌曲的sid来对song表查询

查询结果如下

1
id: 1|name: 晴天|publish_time: 2003-07-31|sid: 1|singer:周杰伦

ORM中则提供了relationship来简化操作

在Song类中加入(注释部分)

1
singer = relationship('Singer')

则此时可通过如下操作

1
2
3
4
song = session.query(Song).first()
print(song.singer)
print(song.singer.name)
print(song.singer.age)

查询结果为

1
2
3
id: 1|name: 周杰伦|age: 39
周杰伦
39

如上是通过song获取singer,若想通过singer获取song则可通过如下操作

在Singer类中加入如下(注释部分)

1
songs = relationship('Song')

由于Singer -> Song为一对多关系,为显示更好,则在song表中新增一条数据,如下

1
2
session.add(Song(name='搁浅', publish_time=date(year=2003, month=7, day=31), sid=1))
session.commit()

此时song表中多了一条数据

1
2
3
4
5
6
7
8
mysql> select * from song;
+----+--------+--------------+------+
| id | name | publish_time | sid |
+----+--------+--------------+------+
| 1 | 晴天 | 2003-07-31 | 1 |
| 2 | 搁浅 | 2003-07-31 | 1 |
+----+--------+--------------+------+
2 rows in set (0.00 sec)

接着进行如下操作

1
2
3
singer = session.query(Singer).first()
print(singer.songs)
print([i.name for i in singer.songs])

结果为

1
2
[id: 1|name: 晴天|publish_time: 2003-07-31|sid: 1|singer:周杰伦, id: 2|name: 搁浅|publish_time: 2003-07-31|sid: 1|singer:周杰伦]
['晴天', '搁浅']

当然为了实现互相访问,还有更简便的方法,在Song类中加入

1
2
singer = relationship('Singer', backref='songs')
# backref反向访问,即可通过在以当前表的外键作为主键的表中直接以属性的方式(singer.songs)访问当前表中的字段(singer.songs.name)

接着进行同样的操作

1
2
3
singer = session.query(Singer).first()
print(singer.songs)
print([i.name for i in singer.songs])

结果相同

1
2
[id: 1|name: 晴天|publish_time: 2003-07-31|sid: 1|singer:周杰伦, id: 2|name: 搁浅|publish_time: 2003-07-31|sid: 1|singer:周杰伦]
['晴天', '搁浅']

接着通过singer添加song可如下操作

1
2
3
4
5
6
singer = Singer(name='林俊杰', age=36)
song1 = Song(name='江南', publish_time=date(year=2007, month=4, day=12))
song2 = Song(name='学不会', publish_time=date(year=2012, month=6, day=29))
singer.songs.extend([song1, song2])
session.add(singer)
session.commit()

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from singer;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 周杰伦 | 39 |
| 2 | 林俊杰 | 36 |
+----+-----------+------+
2 rows in set (0.00 sec)

mysql> select * from song;
+----+-----------+--------------+------+
| id | name | publish_time | sid |
+----+-----------+--------------+------+
| 1 | 晴天 | 2003-07-31 | 1 |
| 2 | 搁浅 | 2003-07-31 | 1 |
| 3 | 江南 | 2007-04-12 | 2 |
| 4 | 学不会 | 2012-06-29 | 2 |
+----+-----------+--------------+------+
4 rows in set (0.00 sec)

通过song添加singer可如下操作

1
2
3
4
5
singer = Singer(name='July', age=36)
song3 = Song(name='My Soul', publish_time=date(year=2009, month=1, day=23))
song3.singer = singer
session.add(song3)
session.commit()

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from singer;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 周杰伦 | 39 |
| 2 | 林俊杰 | 36 |
| 3 | July | 36 |
+----+-----------+------+
3 rows in set (0.00 sec)

mysql> select * from song;
+----+-----------+--------------+------+
| id | name | publish_time | sid |
+----+-----------+--------------+------+
| 1 | 晴天 | 2003-07-31 | 1 |
| 2 | 搁浅 | 2003-07-31 | 1 |
| 3 | 江南 | 2007-04-12 | 2 |
| 4 | 学不会 | 2012-06-29 | 2 |
| 5 | My Soul | 2009-01-23 | 3 |
+----+-----------+--------------+------+
5 rows in set (0.00 sec)

One_to_One

首先创建两张表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
from sqlalchemy import create_engine, Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref

HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = '123456'

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URI)
Base = declarative_base(engine)

class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(10), nullable=False)
grade = relationship('Grade', uselist=False)


class Grade(Base):
__tablename__ = 'grade'
id = Column(Integer, primary_key=True, autoincrement=True)
math = Column(Integer(), nullable=False)
english = Column(Integer(), nullable=False)
sid = Column(Integer, ForeignKey('student.id', ondelete='CASCADE'), nullable=False)
student = relationship('Student')
# student = relationship('Student', backref=backref('grade', uselist=False))

Base.metadata.drop_all()
Base.metadata.create_all()
session = sessionmaker(engine)()

s1 = Student(name='张三')
g1 = Grade(math=88, english=88)
s1.grade = g1

s2 = Student(name='李四')
g2 = Grade(math=99, english=99)
s2.grade = g2

session.add(s1)
session.add(s2)
session.commit()

要实现一对一,可在一对多基础上,加入uselist=False

1
grade = relationship('Grade', uselist=False)

此时插入后数据库结果为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select * from student;
+----+------+
| id | name |
+----+------+
| 1 | 88 |
| 2 | 99 |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from grade;
+----+------+---------+-----+
| id | math | english | sid |
+----+------+---------+-----+
| 1 | 88 | 8 | 1 |
| 2 | 99 | 99 | 2 |
+----+------+---------+-----+
2 rows in set (0.00 sec)

还有更简便的方法,删除Student类中的relationship,并在Grade类中的relationship里的backref参数后面使用backref()方法,即Grade类中的注释部分

1
student = relationship('Student', backref=backref('grade', uselist=False))

此时查询结果同上

Many_to_Many

多对多关系可创建一张中间表设置两个外键用来联系两张表,并将两个外键作为中间表的联合主键。让两张表建立联系只需在其中任意一张表中设置relationship,并传入backrefsecondary参数即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
from sqlalchemy import create_engine, Column, String, Integer, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = '123456'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URI)
Base = declarative_base(engine)

article_tag = Table(
'article_tag',
Base.metadata,
Column('article_id', Integer, ForeignKey('article.id'), autoincrement=True),
Column('tag_id', Integer, ForeignKey('tag.id'), autoincrement=True)
)

class Article(Base):
__tablename__ = 'article'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50), nullable=False)

def __repr__(self):
return '文章:{}'.format(self.name)


class Tag(Base):
__tablename__ = 'tag'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(10), nullable=False)
articles = relationship('Article', backref='tags', secondary=article_tag)

def __repr__(self):
return '标签:{}'.format(self.name)


Base.metadata.drop_all()
Base.metadata.create_all()

session = sessionmaker(engine)()
a1 = Article(name='科技体育文章1')
a2 = Article(name='科技体育文章2')
t1 = Tag(name='科技')
t2 = Tag(name='体育')
t1.articles.extend([a1, a2])
t2.articles.extend([a1, a2])
session.add(t1)
session.add(t2)
session.commit()

建立数据后查询为如下结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select * from article;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | 科技体育文章1 |
| 2 | 科技体育文章2 |
+----+---------------------+
2 rows in set (0.00 sec)

mysql> select * from tag;
+----+--------+
| id | name |
+----+--------+
| 1 | 科技 |
| 2 | 体育 |
+----+--------+
2 rows in set (0.00 sec)

此时三张表的结构为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> show create table article\G
*************************** 1. row ***************************
Table: article
Create Table: CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table tag\G
*************************** 1. row ***************************
Table: tag
Create Table: CREATE TABLE `tag` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table article_tag\G
*************************** 1. row ***************************
Table: article_tag
Create Table: CREATE TABLE `article_tag` (
`article_id` int(11) DEFAULT NULL,
`tag_id` int(11) DEFAULT NULL,
KEY `article_id` (`article_id`),
KEY `tag_id` (`tag_id`),
CONSTRAINT `article_tag_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`),
CONSTRAINT `article_tag_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

此时通过ORM查询就会变得很简便

1
2
a3 = session.query(Article).first()
print(a3.tags)

查询结果如下

1
[标签:科技, 标签:体育]

通过数据库sql语句查询结果同上

1
2
3
4
5
6
7
8
mysql> select tag.name from tag, article where article.name='科技体育文章1';
+--------+
| name |
+--------+
| 科技 |
| 体育 |
+--------+
2 rows in set (0.00 sec)