SQLAlchemy 基础示例

本篇文档将指导你如何使用 PythonSQLAlchemy 构建一个简单的应用程序,并实现 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。

  • 下载安装 sqlalchemypymysqlcryptographyfaker 工具。

使用下面的代码下载安装 sqlalchemypymysqlcryptographyfaker 工具:

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

你可以参考 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
  5. faker = Factory.create()
  6. engine = create_engine('mysql+pymysql://dump:111@127.0.0.1:6001/test')
  7. Session = sessionmaker(bind=engine)
  8. session = Session()
  9. Base = declarative_base()
  10. class Customer(Base):
  11. __tablename__ = "Customer"
  12. id = Column(Integer, primary_key=True,autoincrement=True)
  13. cname = Column(String(64))
  14. caddress = Column(String(512))
  15. def __init__(self,name,address):
  16. self.cname = name
  17. self.caddress = address
  18. def __str__(self):
  19. return "cname:"+self.cname +" caddress:"+self.caddress
  20. def __repr__(self):
  21. return "cname:"+self.cname +" caddress:"+self.caddress
  22. # Generate 10 Customer records
  23. Customers = [Customer(name= faker.name(),address = faker.address()) for i in range(10)]
  24. # Create the table
  25. Base.metadata.create_all(engine)
  26. # Insert all customer records to Customer table
  27. session.add_all(Customers)
  28. 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
  4. engine = create_engine('mysql+pymysql://dump:111@127.0.0.1:6001/test')
  5. Session = sessionmaker(bind=engine)
  6. session = Session()
  7. Base = declarative_base()
  8. class Customer(Base):
  9. __tablename__ = "Customer"
  10. id = Column(Integer, primary_key=True,autoincrement=True)
  11. cname = Column(String(64))
  12. caddress = Column(String(512))
  13. def __init__(self,name,address):
  14. self.cname = name
  15. self.caddress = address
  16. def __str__(self):
  17. return "cname:"+self.cname +" caddress:"+self.caddress
  18. def __repr__(self):
  19. return "cname:"+self.cname +" caddress:"+self.caddress
  20. # query all data
  21. customers = session.query(Customer).all()
  22. for customer in customers:
  23. print(customer.__str__() +"\n--------------------------\n")
  24. # query with a filter condition
  25. Mccann = session.query(Customer).filter_by(cname='David Mccann').first()
  26. print(Mccann)
  27. print("\n------------------------\n")

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

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

更新数据

在下面的示例中,将指导你更新 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
  4. engine = create_engine('mysql+pymysql://dump:111@127.0.0.1:6001/test')
  5. Session = sessionmaker(bind=engine)
  6. session = Session()
  7. Base = declarative_base()
  8. class Customer(Base):
  9. __tablename__ = "Customer"
  10. id = Column(Integer, primary_key=True,autoincrement=True)
  11. cname = Column(String(64))
  12. caddress = Column(String(512))
  13. def __init__(self,name,address):
  14. self.cname = name
  15. self.caddress = address
  16. def __str__(self):
  17. return "cname:"+self.cname +" caddress:"+self.caddress
  18. def __repr__(self):
  19. return "cname:"+self.cname +" caddress:"+self.caddress
  20. customer = session.query(Customer).first()
  21. print(customer)
  22. print("\n---------------------\n")
  23. # Rename customer
  24. customer.cname = "Coby White"
  25. session.commit()
  26. # See the updated result
  27. customer = session.query(Customer).first()
  28. print(customer)

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

  1. > python3 sqlalchemy_update.py
  2. cname:Wendy Luna caddress:002 Brian Plaza
  3. Andrewhaven, SC 88456
  4. ---------------------
  5. cname:Coby White caddress:002 Brian Plaza
  6. 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
  4. engine = create_engine('mysql+pymysql://dump:111@127.0.0.1:6001/test')
  5. Session = sessionmaker(bind=engine)
  6. session = Session()
  7. Base = declarative_base()
  8. class Customer(Base):
  9. __tablename__ = "Customer"
  10. id = Column(Integer, primary_key=True,autoincrement=True)
  11. cname = Column(String(64))
  12. caddress = Column(String(512))
  13. def __init__(self,name,address):
  14. self.cname = name
  15. self.caddress = address
  16. def __str__(self):
  17. return "cname:"+self.cname +" caddress:"+self.caddress
  18. def __repr__(self):
  19. return "cname:"+self.cname +" caddress:"+self.caddress
  20. # delete the first record
  21. customer = session.query(Customer).first()
  22. session.delete(customer)
  23. session.commit()
  24. # query all data
  25. customers = session.query(Customer).all()
  26. for customer in customers:
  27. print(customer.__str__() +"\n--------------------------\n")

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

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

你可以使用 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)