构建一个 Python CRUD 示例

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

开始前准备

环境配置

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

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

  1. ```
  2. #To check with Python installation and its version
  3. python3 -V
  4. ```

  • 确认你已完成安装 MySQL。

  • 下载安装 pymysql 和 cryptography 工具。

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

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

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

新建表

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

  1. #!/usr/bin/python3
  3. import pymysql.cursors
  5. SQL_CONNECTION = pymysql.connect(
  6.         host='127.0.0.1',
  7.     port=6001,
  8.         user='dump',
  9.         password = "111",
  10.         db='test',
  11.         cursorclass=pymysql.cursors.DictCursor,
  12.         autocommit=True
  13.         )
  15. SQL = "CREATE TABLE cars (id INT NOT NULL AUTO_INCREMENT, car_model VARCHAR(45) NULL,car_brand VARCHAR(45) NULL,PRIMARY KEY (`id`))"
  17. with SQL_CONNECTION.cursor() as cursor:
  18.     try:
  19.         sql_exec = cursor.execute(SQL)
  20.         print("Table created")
  21.     except (pymysql.Error, pymysql.Warning) as e:
  22.         print(f'error! {e}')
  24.     finally:
  25.         SQL_CONNECTION.close()

打开终端，使用以下代码运行此 python 文件。这将在 MatrixOne 中的数 ​​ 据库 test 内创建一个名为 cars 表。

  1. > python3 create.py
  2. Table created

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

  1. mysql> show tables;
  2. +----------------+
  3. | tables_in_test |
  4. +----------------+
  5. | cars           |
  6. +----------------+
  7. 1 row in set (0.03 sec)
  8. mysql> show create table cars;
  9. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
  10. | Table | Create Table                                                                                                                                             |
  11. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
  12. | cars  | CREATE TABLE `cars` (
  13. `id` INT NOT NULL AUTO_INCREMENT,
  14. `car_model` VARCHAR(45) DEFAULT NULL,
  15. `car_brand` VARCHAR(45) DEFAULT NULL,
  16. PRIMARY KEY (`id`)
  17. ) |
  18. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
  19. 1 row in set (0.03 sec)

插入数据

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

  1. #!/usr/bin/python3
  3. import pymysql.cursors
  5. SQL_CONNECTION = pymysql.connect(
  6.         host='127.0.0.1',
  7.         port=6001,
  8.         user='dump',
  9.         password = "111",
  10.         db='test',
  11.         cursorclass=pymysql.cursors.DictCursor,
  12.         autocommit=True
  13.         )
  15. SQL = "INSERT INTO cars(car_model, car_brand) VALUES ('accord', 'honda')"
  17. with SQL_CONNECTION.cursor() as cursor:
  18.     try:
  19.         sql_exec = cursor.execute(SQL)
  20.         if sql_exec:
  21.             print(sql_exec)
  22.             print("Record Added")
  23.         else:
  24.             print(sql_exec)
  25.             print("Not Added")
  26.     except (pymysql.Error, pymysql.Warning) as e:
  27.         print(f'error! {e}')
  29.     finally:
  30.         SQL_CONNECTION.close()

执行下面的代码会在 cars 表中插入一条记录：

  1. > python3 insert.py
  2. 1
  3. Record Added

你可以在 MySQL 客户端中验证这条记录是否插入成功：

  1. mysql> select * from cars;
  2. +------+-----------+-----------+
  3. | id   | car_model | car_brand |
  4. +------+-----------+-----------+
  5. |    1 | accord    | honda     |
  6. +------+-----------+-----------+
  7. 1 row in set (0.03 sec)

查询数据

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

  1. #!/usr/bin/python3
  3. import pymysql.cursors
  5. SQL_CONNECTION = pymysql.connect(
  6.         host='127.0.0.1',
  7.         port=6001,
  8.         user='dump',
  9.         password = "111",
  10.         db='test',
  11.         cursorclass=pymysql.cursors.DictCursor,
  12.         autocommit=True
  13.         )
  15. SQL = "SELECT * FROM cars"
  17. with SQL_CONNECTION.cursor() as cursor:
  18.     try:
  19.         sql_exec = cursor.execute(SQL)
  20.         if sql_exec:
  21.             print(sql_exec)
  22.             print(cursor.fetchall())
  23.         else:
  24.             print(sql_exec)
  25.             print("No Record")
  26.     except (pymysql.Error, pymysql.Warning) as e:
  27.         print(f'error! {e}')
  29.     finally:
  30.         SQL_CONNECTION.close()

执行下面的代码查询并返回 cars 表中的所有记录：

  1. > python3 read.py
  2. 1
  3. [{'id': 1, 'car_model': 'accord', 'car_brand': 'honda'}]

更新数据

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

  1. #!/usr/bin/python3
  3. import pymysql.cursors
  5. SQL_CONNECTION = pymysql.connect(
  6.         host='127.0.0.1',
  7.         port=6001,
  8.         user='dump',
  9.         password = "111",
  10.         db='test',
  11.         cursorclass=pymysql.cursors.DictCursor,
  12.         autocommit=True
  13.         )
  15. SQL = "UPDATE cars SET car_model = 'explorer', car_brand = 'ford' WHERE id = '1'"
  17. with SQL_CONNECTION.cursor() as cursor:
  18.     try:
  19.         sql_exec = cursor.execute(SQL)
  20.         if sql_exec:
  21.             print(sql_exec)
  22.             print("Record Updated")
  23.         else:
  24.             print(sql_exec)
  25.             print("Not Updated")
  26.     except (pymysql.Error, pymysql.Warning) as e:
  27.         print(f'error! {e}')
  29.     finally:
  30.         SQL_CONNECTION.close()

执行下面代码更新 id 为 “1” 的记录：

  1. > python3 update.py
  2. 1
  3. Record Updated

你可以在 MySQL 客户端中验证这条记录是否更新成功：

  1. mysql> select * from cars;
  2. +------+-----------+-----------+
  3. | id   | car_model | car_brand |
  4. +------+-----------+-----------+
  5. |    1 | explorer  | ford      |
  6. +------+-----------+-----------+
  7. 1 row in set (0.02 sec)

删除数据

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

  1. #!/usr/bin/python3
  3. import pymysql.cursors
  5. SQL_CONNECTION = pymysql.connect(
  6.         host='127.0.0.1',
  7.         port=6001,
  8.         user='dump',
  9.         password = "111",
  10.         db='test',
  11.         cursorclass=pymysql.cursors.DictCursor,
  12.         autocommit=True
  13.         )
  15. SQL = "DELETE FROM cars WHERE id = '1'"
  17. with SQL_CONNECTION.cursor() as cursor:
  18.     try:
  19.         sql_exec = cursor.execute(SQL)
  20.         if sql_exec:
  21.             print(sql_exec)
  22.             print("Record Deleted")
  23.         else:
  24.             print(sql_exec)
  25.             print("Not Deleted")
  26.     except (pymysql.Error, pymysql.Warning) as e:
  27.         print(f'error! {e}')
  29.     finally:
  30.         SQL_CONNECTION.close()

执行下面代码删除 id 为 “1” 的记录：

  1. > python3 delete.py
  2. 1
  3. Record Deleted

你可以在 MySQL 客户端中验证这条记录是否删除成功：

  1. mysql> select * from cars;
  2. Empty set (0.03 sec)