【MySQL 每日一技】使用 Python 执行 SQL 语句并获取结果集

最新推荐文章于 2024-07-05 03:48:28 发布
最新推荐文章于 2024-07-05 03:48:28 发布
阅读量5.5k 收藏 14
点赞数
分类专栏： # MySQL Cookbook 文章标签： mysql sql python
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_37780776/article/details/124654741
版权
本文介绍了如何使用Python的mysql.connector模块连接MySQL数据库，执行SQL语句，包括不返回结果集（如UPDATE）和返回结果集（如SELECT）的情况。通过示例展示了如何增加记录、查询数据并处理返回的结果集。强调了游标的rowcount属性和fetchone/fetchall方法在处理数据时的作用。
摘要由CSDN通过智能技术生成

文章目录

1 问题

你希望使用 Python 1发送一条 SQL 语句至 MySQL 服务端，并且获取结果集。

2. 解决方案

有一些 SQL 语句仅返回状态码；另有一些 SQL 语句会返回一个结果集（一系列记录行）。通过 mysql.connector 1中的 connect() 函数可以得到一个数据库连接的句柄对象，使用该句柄对象的 cursor() 方法可以得到一个游标对象，最后通过该游标对象的 execute() 方法可以将 SQL 语句以字符串的形式发送给 MySQL 服务端。

3. 讨论

通常，可以将 MySQL 服务端可执行的 SQL 语句分为两大类，第一类可以从数据库获取数据，第二类可以修改数据库信息。为了后续演示方便，下面先按照下列 SQL 语句创建数据表并向其中插入数据：

# profile.sql
# Character names are from "The Greater Trumps," Charles Williams.

DROP TABLE IF EXISTS profile;
#@ _CREATE_TABLE_
CREATE TABLE profile
(
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name  VARCHAR(20) NOT NULL,
  birth DATE,
  color ENUM('blue','red','green','brown','black','white'),
  foods SET('lutefisk','burrito','curry','eggroll','fadge','pizza'),
  cats  INT,
  PRIMARY KEY (id)
);
#@ _CREATE_TABLE_

INSERT INTO profile
  VALUES
    (NULL,'Sybil','1970-04-13','black','lutefisk,pizza,fadge',0),
    (NULL,'Nancy','1969-09-30','white','curry,eggroll,burrito',3),
    (NULL,'Ralph','1973-11-02','red','pizza,eggroll',4),
    (NULL,'Lothair','1963-07-04','blue','burrito,curry',5),
    (NULL,'Henry','1965-02-14','red','curry,fadge',1),
    (NULL,'Aaron','1968-09-17','green','fadge,lutefisk',1),
    (NULL,'Joanna','1952-08-20','green','fadge,lutefisk',0),
    (NULL,'Stephen','1960-05-01','white','pizza,burrito',0)
;

关于上述建表语句，值得一提的是：

  • 尽管希望 profile 表中保存年龄相关信息，但却没有显式的年龄信息，相反，这里有一个名为 birth 且类型为 DATE 的字段。这样做的好处是，年龄是会变的，而出生日期不会，而年龄也可以很容易地通过 birth 计算出来；
  • 字段 color 的类型是枚举 ENUM 类型，即取值仅能在枚举出的值中选择；
  • 字段 foods 的类型是 SET ，表明该字段的取值可以是其中元素的任意组合。

在执行完上述 SQL 语句之后，数据表 profile 中的数据如下：

mysql> SELECT * FROM profile;
+----+---------+------------+-------+-----------------------+------+
| id | name    | birth      | color | foods                 | cats |
+----+---------+------------+-------+-----------------------+------+
|  1 | Sybil   | 1970-04-13 | black | lutefisk,fadge,pizza  |    0 |
|  2 | Nancy   | 1969-09-30 | white | burrito,curry,eggroll |    3 |
|  3 | Ralph   | 1973-11-02 | red   | eggroll,pizza         |    4 |
|  4 | Lothair | 1963-07-04 | blue  | burrito,curry         |    5 |
|  5 | Henry   | 1965-02-14 | red   | curry,fadge           |    1 |
|  6 | Aaron   | 1968-09-17 | green | lutefisk,fadge        |    1 |
|  7 | Joanna  | 1952-08-20 | green | lutefisk,fadge        |    0 |
|  8 | Stephen | 1960-05-01 | white | burrito,pizza         |    0 |
+----+---------+------------+-------+-----------------------+------+
8 rows in set (0.00 sec)

SQL 语句的分类

基于 SQL 语句是否会返回结果集（也就是一行行记录组成的集合），可以大致将其分为两大类：

  • 不返回结果集的 SQL 语句，例如： INSERT ， DELETE 或 UPDATE 。通常，这一类 SQL 语句都会在某种程度上修改数据库。当然，也有例外，例如 USE db_name 。下面是后续演示使用的这一类 SQL 语句：
UPDATE profile SET cats = cats + 1 WHERE name = 'Sybil'

后面将演示如何通过 Python 及其对应驱动执行该 SQL 语句以及确定该语句所影响记录数量。

  • 返回结果集的 SQL 语句，例如： SELECT ， SHOW ， EXPLAIN 或 DESCRIBE 。下面是后续演示使用的这一类 SQL 语句：
SELECT id, name, cats FROM profile

后面将演示如何通过 Python 及其对应驱动执行该 SQL 语句，以及如何获取结果集中的记录。

需要指出的是，有的读者可能注意到了，在上述 SQL 语句中，都没有包括包含结束符，例如 ; 或 \g 。后续可以知道，这是因为 SQL 语句将会被转化为字符串发送给 MySQL 服务端，而字符串的结尾就可以实现相同功能。

SQL 语句的执行

  • 执行不返回结果集的 SQL 语句：

下面的代码演示了如何将 name 为 Sybil 对应记录的 cats 属性加 1 1 1 ：

#!/usr/bin/python
import mysql.connector

try:
    conn_params = {
        "database": "cookbook",
        "host": "localhost",
        "user": "cbuser",
        "password": "password", }
    conn = mysql.connector.connect(**conn_params)
    print("Connected")
    cursor = conn.cursor()
    cursor.execute("UPDATE profile SET cats = cats + 1 WHERE name = 'Sybil'")
    print("Number of rows updated: %d" % cursor.rowcount)
    cursor.close()
    conn.commit()
except mysql.connector.Error as e:
    print("Cannot connect to server")
    print("Error code: %s" % e.errno)
    print("Error message: %s" % e.msg)
    print("Error SQLSTATE: %s" % e.sqlstate)
else:
    print("Closing connection...")
    conn.close()
    print("Disconnected")

针对上述代码，需要注意的是：

  • 游标对象 cursor 的 rowcount 属性会记录 execute() 中的 SQL 语句执行后影响的记录条数；
  • 在 Python 的数据库 API 中规定，针对新创建的数据库连接，自动提交的功能是默认关闭的，所以如果你的 SQL 语句是涉及修改数据库的信息，且针对的是使用了支持事务的存储引擎2而创建，那么在关闭游标之后以及关闭连接之前，你需要显式调用连接对象的 commit() 方法，否则在连接对象关闭后，SQL 语句所做的修改会被回滚（可以简单理解为不生效）。
  • 执行返回结果集的 SQL 语句：

如果 SQL 语句返回了结果集，则在获得结果集中行后再关闭游标对象。游标对象的 fetchone() 会以序列的方式返回下一行，如果已经没有更多行了，那么则返回 None ：

#!/usr/bin/python
import mysql.connector

try:
    conn_params = {
        "database": "cookbook",
        "host": "localhost",
        "user": "cbuser",
        "password": "password", }
    conn = mysql.connector.connect(**conn_params)
    print("Connected")
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, cats FROM profile")
    while True:
        row = cursor.fetchone()
        if not row:
            break
        print("id: %s, name: %s, cats: %s" % (row[0], row[1], row[2]))
    print("Number of rows returned: %d" % cursor.rowcount)
    cursor.close()
except mysql.connector.Error as e:
    print("Cannot connect to server")
    print("Error code: %s" % e.errno)
    print("Error message: %s" % e.msg)
    print("Error SQLSTATE: %s" % e.sqlstate)
else:
    print("Closing connection...")
    conn.close()
    print("Disconnected")

执行上述代码的输出结果如下：

Connected
id: 1, name: Sybil, cats: 2
id: 2, name: Nancy, cats: 3
id: 3, name: Ralph, cats: 4
id: 4, name: Lothair, cats: 5
id: 5, name: Henry, cats: 1
id: 6, name: Aaron, cats: 1
id: 7, name: Joanna, cats: 0
id: 8, name: Stephen, cats: 0
Number of rows returned: 8
Closing connection...
Disconnected

实际上，也可以将 cursor 对象本身视为一个迭代器，进而对其使用 for 循环等：

cursor = conn.cursor()
cursor.execute("SELECT id, name, cats FROM profile")
for (id, name, cats) in cursor:
    print("id: %s, name: %s, cats: %s" % (id, name, cats))
print("Number of rows returned: %d" % cursor.rowcount)
cursor.close()

最后，游标对象还有一个名为 fetchall() 的方法，该方法可以列表的方式返回结果集：

cursor = conn.cursor()
cursor.execute("SELECT id, name, cats FROM profile")
rows = cursor.fetchall()
print(rows)
for (id, name, cats) in rows:
    print("id: %s, name: %s, cats: %s" % (id, name, cats))
print("Number of rows returned: %d" % cursor.rowcount)
cursor.close()

上述代码的执行结果如下：

......
[(1, 'Sybil', 2), (2, 'Nancy', 3), (3, 'Ralph', 4), (4, 'Lothair', 5), (5, 'Henry', 1), (6, 'Aaron', 1), (7, 'Joanna', 0), (8, 'Stephen', 0)]
id: 1, name: Sybil, cats: 2
id: 2, name: Nancy, cats: 3
id: 3, name: Ralph, cats: 4
id: 4, name: Lothair, cats: 5
id: 5, name: Henry, cats: 1
id: 6, name: Aaron, cats: 1
id: 7, name: Joanna, cats: 0
id: 8, name: Stephen, cats: 0
Number of rows returned: 8
......

  1. 你可能需要先参考【MySQL 每日一技】使用 Python 连接、选择、断开数据库 ↩︎ ↩︎

  2. 常见的支持事务的存储引擎为 InnoDB 。对于使用如 MyISAM 等非事务类存储引擎创建的数据表，针对其的 SQL 语句会自动提交。 ↩︎

TakingCoding4Granted
关注
  • 0
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
python 读取列表指定列_Python的pymysql查询结果获取字段列表
weixin_39559804的博客
11-26 1541
在使用pymysql的时候，通过fetchall()或fetchone()可以获得查询结果，但这个返回数据是不包含字段信息的（不如php方便）。查阅pymysql源代码后，其实获取查询结果源代码也是非常简单的，直接调用cursor.description即可。譬如：db = pymysql.connect(...)cur = db.cursor()cur.execute(sql)print(cur...
解析sql查询时生成的tmp文件
HEROWANG的专栏
05-05 6471
/***************************************************          作者：herowang(让你望见影子的墙）    日期：2009.5.5          注：    转载请保留此信息    更多内容，请访问我的博客：blog.csdn.net/herowang*************************
python执行sql语句返回查询结果
最新发布
weixin_29109553的博客
07-05 49
Python,数据库相关视频讲解：python的or运算赋值用法用python编程Excel有没有用处？011_编程到底好玩在哪？查看python文件_输出py文件_cat_运行python文件_shel使用Python执行SQL语句返回查询结果 在开发过程中，经常需要与数据库交互，执行SQL语句并获取查询结果。Py...
python 连接mysql批量sql查询并导出成excel
不定时分享最优质的网络技术与教程，满满的干货。
09-27 817
3. 配置sql， 格式为列表格式 : sheet页名:sql 多个用,隔开 ["sheet页:sql","sheet页2:sql2"]将配置的所有sql 循环在每一个配置号的mysql连接-库中执行，并分别为每一个连接生成一张标，一个查询sql的结果为1个sheet页。PS：excel老版本打开有可能会提示文件错误哦。1. 配置多个Mysql连接、库、文件名。2. 配置SavePath存储路径。导出为多个sheet合并到1个文件。
用Python导出多个sql的查询结果
lx853771701的博客
06-24 155
用Python导出多个sql的查询结果
配置 mybatis 打印出执行的 sql 及返回的结果集
Just do IT
07-17 2695
介绍了如果配置 mybatis 以打印出执行的 sql 及返回的结果集, 从而方便开发阶段的调试
pymssql查询并导出结果到Excel
weixin_48913629的博客
09-12 577
pymssql查询并导出结果到Excel
mysql 导出查询结果/导入.sql文件数据/mysqldump导出表or数据
热门推荐
梦~'
12-30 1万+
1. 将mysql生成结果导入指定文件： 用mysql -e生成结果导入指定文件时 ● 若要同时显示语句本身：-v ● 若要增加查询结果行数：-vv ● 若要增加执行时间： -vvv 示例： // 将mysql查询结果重定向到 test.txt mysql -h x.x.x.X -P 11320 -uroot -p'xxxxxx' -D xxx -e "SELECT a FROM b WHERE create_time > 'xxxx-xx-x...
Python使用sql语句对mysql数据库多条件模糊查询.pdf
11-27
Python 使用 SQL 语句对 MySQL ...使用 Python 对 MySQL 数据库进行多条件模糊查询需要我们使用字符串拼接的方式构建 SQL 语句，并执行 SQL 语句获取查询结果。同时，我们需要注意参数是否为空，并避免 SQL 注入攻击。
Python MySQLdb 执行sql语句时的参数传递方式
09-08
当SQL语句中不需要任何参数时，可以直接构建SQL语句并执行。例如： ```python conn = MySQLdb.connect(user="root", passwd="123456", host="192.168.101.23", db="cmdb") orange_id = 98 sql = "select * from ...
基于SQLAlchemy实现操作MySQL并执行原生sql语句
09-08
本篇将详细讲解如何使用SQLAlchemy来操作MySQL数据库以及如何执行原生的SQL语句。 首先，我们需要引入必要的模块，如`sqlalchemy`, `declarative_base`, `create_engine`, `sessionmaker`等，这些模块提供了与...
python执行sql语句后的返回信息_django执行原始查询sql,并返回Dict字典例子
weixin_39557419的博客
12-11 562
很多时候执行sql语句，数据比django的model来的快，但并不想关心返回的字段，例如你可以执行：select * from product这种sql，这里个方法将会返回与数据库列名相同的键值对 ，格式是这样子的：result = [{“id”:1,”name”:”product1”},{“id”:2,”name”:”product2”}]当然你还可以import jsonjson.dumps...
python如何循环sql语句_Python MySQL连接器在游标循环中执行第二个sql语句？
weixin_32497403的博客
12-23 459
以下逻辑适用于mysqldb模块(请参见python mysqldb multiple cursors for one connection)，但我在cursor2.execute(sql)上的mysql.connector中得到以下错误“找到未读结果。”我意识到我可以使用一个join来组合这两个简单的sql语句，从而避免需要第二个游标，但是我的实际例子更复杂，需要第二个sql语句。假设我需要执行...
5、Python-循环执行多次sql语句
qq_41917061的博客
03-29 1321
【代码】5、Python-循环执行多次sql语句。
python 中SQL的查询结果数据结构
DBA之路的博客
06-21 471
通过SQL查询出来的结果元组的嵌套 数据结构如下 instance_list = (('solar-solar-mysql', 'dx-solar-solar-mysql1-online'), ('ape-misc-mysql', 'dx-ape-misc-mysql1-online')) 可以用索引取 instance[0][1] 就取到了'dx-solar-solar-mysql1-online' ...
python操作sql
weixin_42219287的博客
05-28 194
from pymysql import connect def main(): # 创建connection连接 conn = connect(host='localhost', port=3306, user='root', password='123456',database = 'scott', charset = 'utf8') # 获得Cursor对象 cs1 = conn.cursor() # 执行sql语句，并返回受影响的行数 count.
python 读取mysql、sql server中数据
weixin_39663060的博客
11-02 1640
python 读取mysql、sql server中数据
Python怎么循环查询sql语句
09-22
您好！对于您的问题，可以使用Python中的循环结构和数据库操作模块来实现循环查询SQL语句。以下是一个基本的示例代码： ```python import pymysql # 连接数据库 conn = pymysql.connect(host='localhost', port=3306, user='root', password='password', database='test') # 创建游标 cursor = conn.cursor() # 编写SQL语句 sql = "SELECT * FROM table_name WHERE column_name = %s" # 定义要查询的参数列表 params = ['param1', 'param2', 'param3'] # 循环查询 for param in params: # 执行SQL语句 cursor.execute(sql, param) # 获取查询结果 result = cursor.fetchall() # 输出查询结果 print(result) # 关闭游标和连接 cursor.close() conn.close() ``` 在上述代码中，我们首先使用pymysql模块连接到MySQL数据库，并创建了一个游标对象。然后，我们编写了一条查询SQL语句，使用%s作为参数占位符。接着，我们定义了一个参数列表params，其中包含了要查询的参数值。最后，我们使用for循环遍历参数列表，并依次执行SQL查询操作，将查询结果输出到控制台。最后，我们关闭了游标和数据库连接。 希望这个示例对您有帮助！
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值