文章目录1 问题
- 1 问题
- 2. 解决方案
- 3. 讨论
- SQL 语句的分类
- SQL 语句的执行
你希望使用 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 ......
你可能需要先参考【MySQL 每日一技】使用 Python 连接、选择、断开数据库 ↩︎ ↩︎
常见的支持事务的存储引擎为 InnoDB 。对于使用如 MyISAM 等非事务类存储引擎创建的数据表，针对其的 SQL 语句会自动提交。 ↩︎
