经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MySQL » 查看文章
Python基础之操作MySQL数据库
来源：jb51　　时间：2021/5/6 19:38:23　　对本文有异议

一、数据库操作

1.1 安装PyMySQL

  1. pip install PyMySQL

1.2 连接数据库

python连接test数据库

  1. import pymysql
  2.  
  3. host = 'localhost'      # 主机地址
  4. username = 'root'       # 数据库用户名
  5. password = ''           # 数据库密码
  6. db_name = 'test'        # 数据库名称
  7.  
  8. # 创建connect对象
  9. connect = pymysql.connect(host=host, user=username, password=password, database=db_name)
  10.  
  11. # 获取游标对象
  12. cursor = connect.cursor()
  13.  
  14. # 查询数据库版本
  15. cursor.execute('SELECT VERSION()')
  16.  
  17. # 从查询结果集中获取下一行数据，返回值为一个值的序列
  18. result = cursor.fetchone()
  19.  
  20. # 打印结果
  21. print(result)
  22.  
  23. # 关闭游标
  24. cursor.close()
  25.  
  26. # 关闭数据库连接
  27. connect.close()

执行结果：
('10.4.17-MariaDB',)

1.3 创建数据表

创建一个默认编码格式为utf8的数据表users

id：int类型，不能为空，有自增属性，主键约束

name：varchar类型，长度最多为10字符，可以为空

age：int类型，可以为空

  1. import pprint
  2. import pymysql
  3.  
  4. host = 'localhost'      # 主机地址
  5. username = 'root'       # 数据库用户名
  6. password = ''           # 数据库密码
  7. db_name = 'test'        # 数据库名称
  8.  
  9. # 创建connect对象
  10. connect = pymysql.connect(host=host, user=username, password=password, database=db_name)
  11.  
  12. # 获取游标对象
  13. cursor = connect.cursor()
  14.  
  15. # 创建数据表的SQL命令
  16. create_sql = '''
  17. CREATE TABLE `users`(
  18.     `id` INT NOT NULL AUTO_INCREMENT,
  19.     `name` VARCHAR(10) NULL,
  20.     `age` INT NULL,
  21.     PRIMARY KEY (`id`))
  22.     DEFAULT CHARACTER SET = utf8;
  23. '''
  24. # 创建数据表
  25. cursor.execute(create_sql)
  26.  
  27. # 查询我们创建的数据表的结构
  28. cursor.execute('DESC users')
  29.  
  30. # 从查询结果中获取结果的所有（或者剩余）行数据，返回值为包含序列的序列（例如元组序列）
  31. result = cursor.fetchall()
  32.  
  33. # 打印结果
  34. pprint.pprint(result)
  35.  
  36. # 关闭游标
  37. cursor.close()
  38.  
  39. # 关闭数据库连接
  40. connect.close()

执行结果：
(('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment'),
 ('name', 'varchar(10)', 'YES', '', None, ''),
 ('age', 'int(11)', 'YES', '', None, ''))

在这里插入图片描述

1.4 插入，查询数据

插入3行数据：

id：1，name：路飞，age：18
id：2，name：娜美，age：19
id：3，name：索隆，age：20

  1. import pprint
  2. import pymysql
  3.  
  4. host = 'localhost'      # 主机地址
  5. username = 'root'       # 数据库用户名
  6. password = ''           # 数据库密码
  7. db_name = 'test'        # 数据库名称
  8.  
  9. # 创建connect对象,插入中文时需要指定编码格式
  10. connect = pymysql.connect(host=host, user=username, password=password, database=db_name, charset='utf8')
  11.  
  12. # 获取游标对象查询返回字典
  13. cursor = connect.cursor(pymysql.cursors.DictCursor)
  14.  
  15. # 插入数据的SQL命令
  16. insert_sql = '''
  17. INSERT INTO users (id, name, age)
  18.     VALUES (1, '路飞', 18),(2, '娜美', 19),(3, '索隆', 20)
  19. '''
  20.  
  21. try:
  22.     # 插入数据到数据表
  23.     cursor.execute(insert_sql)
  24.     # 提交任何挂起的事务到数据库
  25.     connect.commit()
  26. except Exception as e:
  27.     # 发送数据回滚,回滚到事务开始时的状态
  28.     connect.rollback()
  29.  
  30. # 查询数据
  31. cursor.execute('SELECT * FROM users')
  32.  
  33. # 只返回一行数据
  34. # result_one = cursor.fetchone()
  35. # print('---fetchone---')
  36. # pprint.pprint(result_one)
  37.  
  38. # 返回全部数据
  39. result_all = cursor.fetchall()
  40. print('---fetchall---')
  41. pprint.pprint(result_all)
  42.  
  43. # 关闭游标
  44. cursor.close()
  45.  
  46. # 关闭数据库连接
  47. connect.close()

执行结果：
---fetchall---
[{'age': 18, 'id': 1, 'name': '路飞'},
 {'age': 19, 'id': 2, 'name': '娜美'},
 {'age': 20, 'id': 3, 'name': '索隆'}]

在这里插入图片描述

1.5 更新，查询数据

更新数据id：3，name：山治，age：21

  1. import pprint
  2. import pymysql
  3.  
  4. host = 'localhost'      # 主机地址
  5. username = 'root'       # 数据库用户名
  6. password = ''           # 数据库密码
  7. db_name = 'test'        # 数据库名称
  8.  
  9. # 创建connect对象,插入中文时需要指定编码格式
  10. connect = pymysql.connect(host=host, user=username, password=password, database=db_name, charset='utf8')
  11.  
  12. # 获取游标对象查询返回字典
  13. cursor = connect.cursor(pymysql.cursors.DictCursor)
  14.  
  15. # 查询数据
  16. cursor.execute('SELECT * FROM users')
  17.  
  18. # 返回更新前全部数据
  19. result_all = cursor.fetchall()
  20. print('---更新前---')
  21. pprint.pprint(result_all)
  22.  
  23. # 更新数据的SQL命令
  24. update_sql = '''
  25. 	UPDATE users SET name = '山治',age = 21 WHERE id = 3
  26. '''
  27.  
  28. try:
  29.     # 更新数据到数据表
  30.     cursor.execute(update_sql)
  31.     # 提交任何挂起的事务到数据库
  32.     connect.commit()
  33. except Exception as e:
  34.     # 发送数据回滚,回滚到事务开始时的状态
  35.     connect.rollback()
  36.  
  37. # 查询数据
  38. cursor.execute('SELECT * FROM users')
  39.  
  40. # 返回更新后全部数据
  41. result_all = cursor.fetchall()
  42. print('---更新后---')
  43. pprint.pprint(result_all)
  44.  
  45. # 关闭游标
  46. cursor.close()
  47.  
  48. # 关闭数据库连接
  49. connect.close()

执行结果：
---更新前---
[{'age': 18, 'id': 1, 'name': '路飞'},
 {'age': 19, 'id': 2, 'name': '娜美'},
 {'age': 20, 'id': 3, 'name': '索隆'}]
---更新后---
[{'age': 18, 'id': 1, 'name': '路飞'},
 {'age': 19, 'id': 2, 'name': '娜美'},
 {'age': 21, 'id': 3, 'name': '山治'}]

在这里插入图片描述

1.6 删除，查询数据

删除'age': 19, 'id': 2, 'name': '娜美'该行数据

  1. import pprint
  2. import pymysql
  3.  
  4. host = 'localhost'      # 主机地址
  5. username = 'root'       # 数据库用户名
  6. password = ''           # 数据库密码
  7. db_name = 'test'        # 数据库名称
  8.  
  9. # 创建connect对象,插入中文时需要指定编码格式
  10. connect = pymysql.connect(host=host, user=username, password=password, database=db_name, charset='utf8')
  11.  
  12. # 获取游标对象查询返回字典
  13. cursor = connect.cursor(pymysql.cursors.DictCursor)
  14.  
  15. # 查询数据
  16. cursor.execute('SELECT * FROM users')
  17.  
  18. # 返回删除前全部数据
  19. result_all = cursor.fetchall()
  20. print('---删除前---')
  21. pprint.pprint(result_all)
  22.  
  23. # 删除数据的SQL命令
  24. update_sql = '''
  25. 	DELETE FROM users WHERE id = 2
  26. '''
  27.  
  28. try:
  29.     # 删除数据表的数据
  30.     cursor.execute(update_sql)
  31.     # 提交任何挂起的事务到数据库
  32.     connect.commit()
  33. except Exception as e:
  34.     # 发送数据回滚,回滚到事务开始时的状态
  35.     connect.rollback()
  36.  
  37. # 查询数据
  38. cursor.execute('SELECT * FROM users')
  39.  
  40. # 返回删除后全部数据
  41. result_all = cursor.fetchall()
  42. print('---删除后---')
  43. pprint.pprint(result_all)
  44.  
  45. # 关闭游标
  46. cursor.close()
  47.  
  48. # 关闭数据库连接
  49. connect.close()

执行结果：
---删除前---
[{'age': 18, 'id': 1, 'name': '路飞'},
 {'age': 19, 'id': 2, 'name': '娜美'},
 {'age': 21, 'id': 3, 'name': '山治'}]
---删除后---
[{'age': 18, 'id': 1, 'name': '路飞'}, {'age': 21, 'id': 3, 'name': '山治'}]

在这里插入图片描述

二、连接与游标对象的方法

2.1 连接对象的方法

  • .close()方法：

马上关闭数据连接（而不是当__del__方法被调用的时候）。此后连接变得不可用，再次访问本连接对象会触发一个错误，使用本连接对象的游标对象，也会导致例外发生。在关闭连接对象之前，没有提交（commit）对数据库的改变将会导致一个隐含的回滚动作（rollback），这将丢弃之前的数据改变操作。

  • .commit()方法：

提交任何挂起的事务到数据库中。

  • .rollback()方法：

对于支持事务的数据库。调用此方法将导致数据库回滚到事务开始时的状态。

  • .cursor()方法：

方法返回给定连接上建立的游标对象（Cursor Object），如果数据库没有提供对应的游标对象，那么有程序来模拟实现游标功能。

2.2 游标对象的方法

  • .close()方法：

立即关闭游标（不论__del__方法是否已被调用），此后游标对象就变得不可用了。

  • .execute(operation[,parameters])方法：

准备和执行数据库操作。所提供的参数将会被绑定到语句中的变量，变量的定义和数据库模块有关。

  • .executemany(operation,seq_of_parameters)方法：

准备和执行数据库操作，然后以序列形式的函数来执行该操作。

  • .fetchone()方法：

从查询结果中获取下一行数据，返回值为一个值的序列，如果没有更多数据则返回None。

  • .fetchmany([size=cursor.arraysize])方法：

从查询结果中获取下一组行数据，返回值为包含序列的序列，如果没有数据返回时，则返回空序列。每次调用要获取的行数由参数指定，如果没有指定行数，则游标的arraysize属性决定要获取的行数。

  • .fetchall()方法：

从查询结果中获取所有（或者剩余）行数据，返回值为包含序列的序列。

  • .nextset()方法：

此方法将游标跳到下一个可用的结果集并丢弃当前结果集的所有行，如果没有更有查询结果集则返回None，否则返回True，接下来的fetch操作将会从新结果集返回数据了。

  • .setinputsizes(sizes)方法：

此方法可用在调用.execute系列方法之前使用，用于预定义内存区域。size参数接收一个序列类型的值，每一个元素对应一个输入参数，该元素应该是一个类型对象，对于将要使用的参数，或者是一个整数，用于指定字符串的最大长度。如果元素是None，则没有预定义的内存区域作为保留区域。

  • .setoutputsize(size[,column])方法：

为一个很大的列设置缓冲区大小，不指定将使用默认大小。

三、事务

事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成，事务的目的性是为了保证数据的一致性。假设银行转账操作，从A账户转账100元到B账户需要进行至少两次的数据库修改操作，A账户余额需要减少100元，B账户余额需要增加100元，如果因为由于外部原因导致程序意外终止，就会操作数据出错，事务就是防止此情况的发生。

数据库事务拥有四个特性，习惯称之为ACID特性：

1、原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么不执行。
2、一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态，一致状态的含义是数据库中的数据应满足完整性约束。
3、隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
4、持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

  1. import pprint
  2. import pymysql
  3.  
  4. host = 'localhost'      # 主机地址
  5. username = 'root'       # 数据库用户名
  6. password = ''           # 数据库密码
  7. db_name = 'test'        # 数据库名称
  8.  
  9. # 创建connect对象,插入中文时需要指定编码格式
  10. connect = pymysql.connect(host=host, user=username, password=password, database=db_name, charset='utf8')
  11.  
  12. # 获取游标对象查询返回字典
  13. cursor = connect.cursor(pymysql.cursors.DictCursor)
  14.  
  15. # 正确的插入数据的SQL命令
  16. insert_sql1 = '''
  17. INSERT INTO users (name, age)
  18.     VALUES ('罗宾', 18),('乔巴', 16)
  19. '''
  20.  
  21. # 错误的插入数据的SQL命令
  22. insert_sql2 = '''
  23. INSERT INTO users (name, age)
  24.     VALUES ('弗兰奇')
  25. '''
  26.  
  27. try:
  28.     # 插入数据到数据表
  29.     cursor.execute(insert_sql1)
  30.     cursor.execute(insert_sql2)
  31.     # 提交任何挂起的事务到数据库
  32.     connect.commit()
  33. except Exception as e:
  34.     # 执行失败发送数据回滚,回滚到事务开始时的状态
  35.     connect.rollback()
  36.  
  37. # 查询数据
  38. cursor.execute('SELECT * FROM users')
  39.  
  40. # 返回全部数据
  41. result_all = cursor.fetchall()
  42. print('---fetchall---')
  43. pprint.pprint(result_all)
  44.  
  45. # 关闭游标
  46. cursor.close()
  47.  
  48. # 关闭数据库连接
  49. connect.close()

上例中执行了两条SQL语句，一条正确的一条错误的，只要有一个错误，两条都不会生效，rollback方法会回滚当前游标的所有操作。

到此这篇关于Python基础之操作MySQL数据库的文章就介绍到这了,更多相关Python操作MySQL内容请搜索w3xue以前的文章或继续浏览下面的相关文章希望大家以后多多支持w3xue！

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MySQL热门文章

  • 大项目之网上书城（九）——订单Demo
  • CentOS7下MySQL服务启动失败原因及解决方法
  • MySQL从删库到跑路_高级（六）——索引
  • 55 道MySQL基础题
  • MySQL与PHP的基础与应用专题之自连接
  • 树形结构的菜单表设计与查询
  • MYSQL语句
  • 《高性能MySQL（第3版）》PDF免费下载
  • 《MySQL必知必会》知识汇总二
  • 【必知必会的MySQL知识】④DCL语言

    • MySQL推荐文章

  • 如何让SQL Server像MySQL一样拥有慢查询日志（Slow Query Log慢日志）
  • MySQL中为什么要使用索引合并(Index Merge)？
  • MySQL派生表合并优化的原理和实现
  • 使用EF 连接 数据库 SQLserver、MySql 实现 CodeFirst
  • 升级到 MySQL 8.4，MySQL 启动报错：io_setup() failed with EAGAIN
  • TiKV 源码分析之 PointGet
  • 异构数据源同步之数据同步 → DataX 使用细节
  • 一文搞懂 MySQL 日志
  • 浅析MySQL 8.0直方图原理
  • 分布式任务调度内的 MySQL 分页查询优化

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号