初始化包

连接mysql，需要安装包

安装pymysql模块

cmd安装

  1.  pip3 install pymysql
  2.  # 验证：python进入窗口输入 -> import pymysql 不报错即可

手动安装

File=>settings
image.png

image.png

创建表

  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  4. -- ----------------------------
  5. -- Table structure for test_order
  6. -- ----------------------------
  7. DROP TABLE IF EXISTS `test_order`;
  8. CREATE TABLE `test_order`  (
  9.   `id` int(11) NOT NULL AUTO_INCREMENT,
  10.   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  11.   `age` int(11) NULL DEFAULT NULL,
  12.   PRIMARY KEY (`id`) USING BTREE
  13. ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  15. -- ----------------------------
  16. -- Records of test_order
  17. -- ----------------------------
  18. INSERT INTO `test_order` VALUES (2, 'Lucy', 12);
  19. INSERT INTO `test_order` VALUES (3, 'Lucy', 12);
  21. SET FOREIGN_KEY_CHECKS = 1;

Demo

  1. # 导入pymysql包
  2. import pymysql
  4. # 创建连接对象
  5. conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="root", database="seata",
  6.                        charset="utf8")
  8. # 获取游标对象
  9. cs = conn.cursor()
  11. print("查询表数据")
  12. # pymysql完成数据的查询条件
  13. sql = "select * from test_order;"
  14. cs.execute(sql)
  15. # for循环来显示数据
  16. cs.execute(sql)
  17. content = cs.fetchall()
  19. for i in content:
  20.     print(i)
  21. print("修改数据")
  22. # 修改数据
  23. sql = "update test_order set name='Lilo' where id=1;"
  24. cs.execute(sql)
  25. # 提交操作
  26. conn.commit()
  27. print("新增数据")
  28. # 增加数据:pycharm默认是开启事务的，此时客户端访问students表还是原来的数据，因此需要提交事务
  29. sql = "insert into test_order(name,age) values('Lucy',12);"
  30. cs.execute(sql)
  31. # 提交操作
  32. conn.commit()
  33. print("再次查询表数据")
  34. # pymysql完成数据的查询条件
  35. sql = "select * from test_order;"
  36. cs.execute(sql)
  38. # for循环来显示数据
  39. cs.execute(sql)
  40. content = cs.fetchall()
  41. for i in content:
  42.     print(i)
  46. print("删除查询表数据")
  47. # 删除数据
  48. sql = "delete from test_order where id = 1;"
  49. cs.execute(sql)
  53. # 提交操作
  54. conn.commit()
  56. # 关闭游标和连接
  57. cs.close()
  58. conn.close()

Q&A

1. pip安装三方库不成功

提示：WARNING: You are using pip version 20.2.3, however version 20.2.4 is available.

原因：pip版本过低导致安装第三方库失败
image.png

  1. ## 直接输入下面命令解决
  2. python -m pip install --upgrade pip