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
......
你可能需要先参考【MySQL 每日一技】使用 Python 连接、选择、断开数据库 ↩︎ ↩︎
常见的支持事务的存储引擎为 InnoDB 。对于使用如 MyISAM 等非事务类存储引擎创建的数据表,针对其的 SQL 语句会自动提交。 ↩︎