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，并传入backref和secondary参数即可

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)