SQLAlchemy 基础示例

本篇文档将指导你如何使用 Python 和 SQLAlchemy 构建一个简单的应用程序，并实现 CRUD（创建、读取、更新、删除）功能。

SQLAlchemy 是 Python 语言中最流行的 ORM 工具之一。

开始前准备

相关软件的简单介绍：

  • SQLAlchemy：SQLAlchemy 是一个 Python 库，可以促进 Python 程序和数据库之间的通信。大多数时候，这个库用作对象关系映射器 (ORM) 工具，将 Python 类转换为关系数据库上的表，并自动将函数调用转换为 SQL 语句。
  • Faker：Faker 是一个生成假数据的 Python 库。虚假数据通常用于测试或用一些虚拟数据填充数据库。

环境配置

在你开始之前，确认你已经下载并安装了如下软件：

  • 确认你已完成单机部署 MatrixOne。通过 MySQL 客户端连接 MatrixOne 并创建一个命名为 test 的数据库：

1. mysql> create database test; - 确认你已完成安装 Python 3.8(or plus) version。

使用下面的代码检查 Python 版本确认安装成功：

1. ```
2. #To check with Python installation and its version
3. python3 -V
4. ```
  • 确认你已完成安装 MySQL。
  • 下载安装 sqlalchemy、pymysql、cryptography 和 faker 工具。

使用下面的代码下载安装 sqlalchemy、pymysql、cryptography 和 faker 工具：

1. ```
2. pip3 install sqlalchemy
3. pip3 install pymysql
4. pip3 install cryptography
5. pip3 install faker

7. #If you are in China mainland and have a low downloading speed, you can speed up the download by following commands.
8. pip3 install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
9. pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
10. pip3 install cryptography -i https://pypi.tuna.tsinghua.edu.cn/simple
11. pip3 install faker -i https://pypi.tuna.tsinghua.edu.cn/simple
12. ```

你可以参考 Python 连接 MatrixOne 服务了解如何通过 SQLAlchemy 连接到 MatrixOne，本篇文档将指导你如何实现 CRUD（创建、读取、更新、删除)。

新建表

作为对象关系映射器（ORM）工具，SQLAlchemy 允许开发人员创建 Python 类来映射关系数据库中的表。

在下面的代码示例中，将创建一个 Customer 类，它定义的 Customer 的代码相当于一条 SQL 语句，它表示 MatrixOne 中的命名为 Customer 的表：

1. CREATE TABLE `User` (
2. `id` INT NOT NULL AUTO_INCREMENT,
3. `cname` VARCHAR(64) DEFAULT NULL,
4. `caddress` VARCHAR(512) DEFAULT NULL,
5. PRIMARY KEY (`id`)
6. )

新建一个 sqlalchemy_create.py 的文本文件，将以下代码拷贝粘贴到文件内：

1. from faker import Factory
2. from sqlalchemy import create_engine, Column, Integer, String
3. from sqlalchemy.ext.declarative import declarative_base
4. from sqlalchemy.orm import sessionmaker

6. faker = Factory.create()

8. engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')

10. Session = sessionmaker(bind=engine)
11. session = Session()

13. Base = declarative_base()

16. class Customer(Base):
17. __tablename__ = "Customer"
18. id = Column(Integer, primary_key=True,autoincrement=True)
19. cname = Column(String(64))
20. caddress = Column(String(512))

22. def __init__(self,name,address):
23. self.cname = name
24. self.caddress = address

26. def __str__(self):
27. return "cname:"+self.cname +" caddress:"+self.caddress

29. def __repr__(self):
30. return "cname:"+self.cname +" caddress:"+self.caddress

33. # Generate 10 Customer records
34. Customers = [Customer(name= faker.name(),address = faker.address()) for i in range(10)]

36. # Create the table
37. Base.metadata.create_all(engine)

39. # Insert all customer records to Customer table
40. session.add_all(Customers)

42. session.commit()

打开终端，使用以下代码运行此 python 文件：

1. > python3 sqlalchemy_create.py

你可以使用 MySQL 客户端验证表是否创建成功：

1. mysql> show tables;
2. +----------------+
3. | tables_in_test |
4. +----------------+
5. | Customer       |
6. +----------------+
7. 1 row in set (0.04 sec)
8. mysql> select * from `Customer`;
9. +------+------------------+-----------------------------------------------------+
10. | id   | cname            | caddress                                            |
11. +------+------------------+-----------------------------------------------------+
12. |    1 | Wendy Luna       | 002 Brian Plaza
13. Andrewhaven, SC 88456               |
14. |    2 | Meagan Rodriguez | USCGC Olson
15. FPO AP 21249                            |
16. |    3 | Angela Ramos     | 029 Todd Curve Apt. 352
17. Mooreville, FM 15950        |
18. |    4 | Lisa Bruce       | 68103 Mackenzie Mountain
19. North Andrew, UT 29853     |
20. |    5 | Julie Moore      | Unit 1117 Box 1029
21. DPO AP 87468                     |
22. |    6 | David Massey     | 207 Wayne Groves Apt. 733
23. Vanessashire, NE 34549    |
24. |    7 | David Mccann     | 97274 Sanders Tunnel Apt. 480
25. Anthonyberg, DC 06558 |
26. |    8 | Morgan Price     | 57463 Lisa Drive
27. Thompsonshire, NM 88077            |
28. |    9 | Samuel Griffin   | 186 Patel Crossing
29. North Stefaniechester, WV 08221  |
30. |   10 | Tristan Pierce   | 593 Blankenship Rapids
31. New Jameshaven, SD 89585     |
32. +------+------------------+-----------------------------------------------------+
33. 10 rows in set (0.03 sec)

读取数据

在下面的示例中，将通过两种方式从 Customer 表中读取数据。

第一种方式是全表扫描：

1. select * from `Customer`

第二种方式是点查询：

1. select * from `Customer` where `cname` = 'David Mccann';

新建一个 sqlalchemy_read.py 的文本文件，将以下代码拷贝粘贴到文件内：

1. from sqlalchemy import create_engine, Column, Integer, String
2. from sqlalchemy.ext.declarative import declarative_base
3. from sqlalchemy.orm import sessionmaker

5. engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')

7. Session = sessionmaker(bind=engine)
8. session = Session()

10. Base = declarative_base()

12. class Customer(Base):
13. __tablename__ = "Customer"
14. id = Column(Integer, primary_key=True,autoincrement=True)
15. cname = Column(String(64))
16. caddress = Column(String(512))

18. def __init__(self,name,address):
19. self.cname = name
20. self.caddress = address

22. def __str__(self):
23. return "cname:"+self.cname +" caddress:"+self.caddress

25. def __repr__(self):
26. return "cname:"+self.cname +" caddress:"+self.caddress

29. # query all data
30. customers = session.query(Customer).all()

32. for customer in customers:
33. print(customer.__str__() +"\n--------------------------\n")

35. # query with a filter condition
36. Mccann = session.query(Customer).filter_by(cname='David Mccann').first()
37. print(Mccann)
38. print("\n------------------------\n")

打开终端，使用以下代码运行此 python 文件并查看结果：

1. > python3 sqlalchemy_read.py
2. cname:Wendy Luna caddress:002 Brian Plaza
3. Andrewhaven, SC 88456
4. --------------------------

6. cname:Meagan Rodriguez caddress:USCGC Olson
7. FPO AP 21249
8. --------------------------

10. cname:Angela Ramos caddress:029 Todd Curve Apt. 352
11. Mooreville, FM 15950
12. --------------------------

14. cname:Lisa Bruce caddress:68103 Mackenzie Mountain
15. North Andrew, UT 29853
16. --------------------------

18. cname:Julie Moore caddress:Unit 1117 Box 1029
19. DPO AP 87468
20. --------------------------

22. cname:David Massey caddress:207 Wayne Groves Apt. 733
23. Vanessashire, NE 34549
24. --------------------------

26. cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
27. Anthonyberg, DC 06558
28. --------------------------

30. cname:Morgan Price caddress:57463 Lisa Drive
31. Thompsonshire, NM 88077
32. --------------------------

34. cname:Samuel Griffin caddress:186 Patel Crossing
35. North Stefaniechester, WV 08221
36. --------------------------

38. cname:Tristan Pierce caddress:593 Blankenship Rapids
39. New Jameshaven, SD 89585
40. --------------------------

42. cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
43. Anthonyberg, DC 06558

45. ------------------------

更新数据

在下面的示例中，将指导你更新 Customer 表的第一个 cname 列为另一个值。

新建一个 sqlalchemy_update.py 的文本文件，将以下代码拷贝粘贴到文件内：

1. from sqlalchemy import create_engine, Column, Integer, String
2. from sqlalchemy.ext.declarative import declarative_base
3. from sqlalchemy.orm import sessionmaker

5. engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')

7. Session = sessionmaker(bind=engine)
8. session = Session()

10. Base = declarative_base()

12. class Customer(Base):
13. __tablename__ = "Customer"
14. id = Column(Integer, primary_key=True,autoincrement=True)
15. cname = Column(String(64))
16. caddress = Column(String(512))

18. def __init__(self,name,address):
19. self.cname = name
20. self.caddress = address

22. def __str__(self):
23. return "cname:"+self.cname +" caddress:"+self.caddress

25. def __repr__(self):
26. return "cname:"+self.cname +" caddress:"+self.caddress

29. customer = session.query(Customer).first()
30. print(customer)
31. print("\n---------------------\n")

33. # Rename customer
34. customer.cname = "Coby White"

37. session.commit()

39. # See the updated result
40. customer = session.query(Customer).first()
41. print(customer)

打开终端，使用以下代码运行此 python 文件并查看结果：

1. > python3 sqlalchemy_update.py
2. cname:Wendy Luna caddress:002 Brian Plaza
3. Andrewhaven, SC 88456

5. ---------------------

7. cname:Coby White caddress:002 Brian Plaza
8. Andrewhaven, SC 88456

你可以使用 MySQL 客户端验证表是否更新成功：

1. mysql> select * from `Customer`;
2. +------+------------------+-----------------------------------------------------+
3. | id   | cname            | caddress                                            |
4. +------+------------------+-----------------------------------------------------+
5. |    1 | Coby White       | 002 Brian Plaza
6. Andrewhaven, SC 88456               |
7. |    2 | Meagan Rodriguez | USCGC Olson
8. FPO AP 21249                            |
9. |    3 | Angela Ramos     | 029 Todd Curve Apt. 352
10. Mooreville, FM 15950        |
11. |    4 | Lisa Bruce       | 68103 Mackenzie Mountain
12. North Andrew, UT 29853     |
13. |    5 | Julie Moore      | Unit 1117 Box 1029
14. DPO AP 87468                     |
15. |    6 | David Massey     | 207 Wayne Groves Apt. 733
16. Vanessashire, NE 34549    |
17. |    7 | David Mccann     | 97274 Sanders Tunnel Apt. 480
18. Anthonyberg, DC 06558 |
19. |    8 | Morgan Price     | 57463 Lisa Drive
20. Thompsonshire, NM 88077            |
21. |    9 | Samuel Griffin   | 186 Patel Crossing
22. North Stefaniechester, WV 08221  |
23. |   10 | Tristan Pierce   | 593 Blankenship Rapids
24. New Jameshaven, SD 89585     |
25. +------+------------------+-----------------------------------------------------+
26. 10 rows in set (0.02 sec)

删除数据

在下面的示例中，将指导你删除 Customer 表的第一条数据。

新建一个 sqlalchemy_detele.py 的文本文件，将以下代码拷贝粘贴到文件内：

1. from sqlalchemy import create_engine, Column, Integer, String
2. from sqlalchemy.ext.declarative import declarative_base
3. from sqlalchemy.orm import sessionmaker

5. engine = create_engine('mysql+pymysql://root:111@127.0.0.1:6001/test')

7. Session = sessionmaker(bind=engine)
8. session = Session()

10. Base = declarative_base()

12. class Customer(Base):
13. __tablename__ = "Customer"
14. id = Column(Integer, primary_key=True,autoincrement=True)
15. cname = Column(String(64))
16. caddress = Column(String(512))

18. def __init__(self,name,address):
19. self.cname = name
20. self.caddress = address

22. def __str__(self):
23. return "cname:"+self.cname +" caddress:"+self.caddress

25. def __repr__(self):
26. return "cname:"+self.cname +" caddress:"+self.caddress

29. # delete the first record
30. customer = session.query(Customer).first()

32. session.delete(customer)
33. session.commit()

35. # query all data
36. customers = session.query(Customer).all()

38. for customer in customers:
39. print(customer.__str__() +"\n--------------------------\n")

打开终端，使用以下代码运行此 python 文件并查看结果：

1. > python3 sqlalchemy_delete.py
2. cname:Meagan Rodriguez caddress:USCGC Olson
3. FPO AP 21249
4. --------------------------

6. cname:Angela Ramos caddress:029 Todd Curve Apt. 352
7. Mooreville, FM 15950
8. --------------------------

10. cname:Lisa Bruce caddress:68103 Mackenzie Mountain
11. North Andrew, UT 29853
12. --------------------------

14. cname:Julie Moore caddress:Unit 1117 Box 1029
15. DPO AP 87468
16. --------------------------

18. cname:David Massey caddress:207 Wayne Groves Apt. 733
19. Vanessashire, NE 34549
20. --------------------------

22. cname:David Mccann caddress:97274 Sanders Tunnel Apt. 480
23. Anthonyberg, DC 06558
24. --------------------------

26. cname:Morgan Price caddress:57463 Lisa Drive
27. Thompsonshire, NM 88077
28. --------------------------

30. cname:Samuel Griffin caddress:186 Patel Crossing
31. North Stefaniechester, WV 08221
32. --------------------------

34. cname:Tristan Pierce caddress:593 Blankenship Rapids
35. New Jameshaven, SD 89585
36. --------------------------

你可以使用 MySQL 客户端验证表中的记录是否删除成功：

1. mysql> select * from `Customer`;
2. +------+------------------+-----------------------------------------------------+
3. | id   | cname            | caddress                                            |
4. +------+------------------+-----------------------------------------------------+
5. |    2 | Meagan Rodriguez | USCGC Olson
6. FPO AP 21249                            |
7. |    3 | Angela Ramos     | 029 Todd Curve Apt. 352
8. Mooreville, FM 15950        |
9. |    4 | Lisa Bruce       | 68103 Mackenzie Mountain
10. North Andrew, UT 29853     |
11. |    5 | Julie Moore      | Unit 1117 Box 1029
12. DPO AP 87468                     |
13. |    6 | David Massey     | 207 Wayne Groves Apt. 733
14. Vanessashire, NE 34549    |
15. |    7 | David Mccann     | 97274 Sanders Tunnel Apt. 480
16. Anthonyberg, DC 06558 |
17. |    8 | Morgan Price     | 57463 Lisa Drive
18. Thompsonshire, NM 88077            |
19. |    9 | Samuel Griffin   | 186 Patel Crossing
20. North Stefaniechester, WV 08221  |
21. |   10 | Tristan Pierce   | 593 Blankenship Rapids
22. New Jameshaven, SD 89585     |
23. +------+------------------+-----------------------------------------------------+
24. 9 rows in set (0.04 sec)