使用 Python 和 SQLAlchemy 构建一个 CRUD 示例

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

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

开始前准备

相关软件的简单介绍：

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

  • Faker：Faker 是一个生成假数据的 Python 库。虚假数据通常用于测试或用一些虚拟数据填充数据库。

环境配置

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

使用下面的代码检查 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://dump: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://dump: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://dump: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://dump: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)