31 KiB

Raw Permalink Blame History

使用 Connector/Python 执行查询

原文：https://overiq.com/mysql-connector-python-101/executing-queries-using-connector-python/

最后更新于 2020 年 7 月 27 日

在最后一课中，我们学习了如何使用 Connector/Python 连接 MySQL 数据库。在本课中，我们将学习如何执行查询。

创建光标对象

游标对象允许我们执行查询和检索行。光标对象是MySQLCursor类的一个实例。

我们可以通过使用连接对象的cursor()方法(即MySQLConnection)或者直接调用MySQLCursor类来创建光标对象。

就像连接对象一样，当你使用完光标后，你必须通过调用close()方法来关闭它。

下面是一个示例，它说明了创建游标的两种不同方式。

import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor1 = db.cursor()  # 1st method
cursor2 = MySQLCursor(db)   # 2nd method

print(db)

print("cursor1: ", cursor1)
print("cursor2: ", cursor2)

cursor1.close()
cursor2.close()

db.close()

预期输出:

<mysql.connector.connection.MySQLConnection object at 0x7fcf99799da0>
cursor1:  MySQLCursor: (Nothing executed yet)
cursor2:  MySQLCursor: (Nothing executed yet)

我们现在准备执行对数据库的查询。

一个简单的 SELECT 语句

有几种方法可以执行查询。最常见的是execute()法。其语法如下:

`execute(query, params=None)`

参数	描述
`query`	(必需)要执行的 SQL 查询或命令
`params`	(可选)用于为查询参数提供值的序列或映射。

现在让我们看一些例子:

下面的列表从城市表中选择前五行。

例 1:

import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("select * from city limit 5")

# iterate over result
for row in cursor:
    print(row)

cursor.close()
db.close()

预期输出:

(1, 'Kabul', 'AFG', 'Kabol', 1780000)
(2, 'Qandahar', 'AFG', 'Qandahar', 237500)
(3, 'Herat', 'AFG', 'Herat', 186800)
(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800)
(5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)

执行查询后，我们使用 cursor 对象来迭代结果。请注意，每行都以元组的形式返回。要访问单个列中的数据，请使用索引。还要注意，列中的数据被转换为其等效的 Python 类型。

下面是另一个访问单个列中的数据并打印其类型的示例。

例 2:

import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("select * from city limit 5")

for row in cursor:
    print(row[0], type(row[0]), row[1], type(row[1]) )

cursor.close()
db.close()

预期输出:

1 <class 'int'> Kabul <class 'str'>
2 <class 'int'> Qandahar <class 'str'>
3 <class 'int'> Herat <class 'str'>
4 <class 'int'> Mazar-e-Sharif <class 'str'>
5 <class 'int'> Amsterdam <class 'str'>

请记住在使用 res 之前阅读行

获取方法

我们可以使用以下方法一次访问一行或多行，而不是迭代光标对象。

方法	描述
`fetchone()`	将结果集中的下一行作为元组返回。如果没有要检索的行，则返回`None`。
`fetchmany([size])`	从结果集中返回指定数量的行(作为元组列表)。如果没有要检索的行，则返回`[]`。默认大小为 1。
`fetchall()`	返回结果集中的所有(或剩余)行。

以下是一些例子:

示例 1:使用fetchone()检索行。

import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

r = cursor.execute("select * from city limit 5")

# first row
row = cursor.fetchone()

# loop over the remaining result set
while row:
    print(row)
    row = cursor.fetchone()

cursor.close()
db.close()

预期输出:

(1, 'Kabul', 'AFG', 'Kabol', 1780000)
(2, 'Qandahar', 'AFG', 'Qandahar', 237500)
(3, 'Herat', 'AFG', 'Herat', 186800)
(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800)
(5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)

在第 5 行，我们读取结果集中的第一行。然后，我们使用 while 循环来访问剩余的行。当fetchone()方法返回None时，循环停止。

例 2:

import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

r = cursor.execute("select * from city limit 5")

print(cursor.fetchone())  # fetch the first row
print(cursor.fetchmany(2))  # fetch the next 2 rows
print(cursor.fetchall())  # fetch all the remaining rows
print(cursor.fetchmany())  # the result set is now empty

cursor.close()
db.close()

预期输出:

(1, 'Kabul', 'AFG', 'Kabol', 1780000)
[(2, 'Qandahar', 'AFG', 'Qandahar', 237500), (3, 'Herat', 'AFG', 'Herat', 186800)]
[(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800), (5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)]
[]

在第 1 行，我们调用fetchone()从结果集中读取第一行。接下来，我们调用fetchmany()读取接下来的 2 行，最后调用fetchall()获取剩余的一行。结果集现在是空的，所以下一次调用fetchmany()会返回一个空列表。

缓冲和非缓冲光标

默认情况下，光标对象没有缓冲。这是什么意思？

这仅仅意味着，在调用行提取方法或迭代游标对象之前，不会从数据库服务器中提取行。换句话说，无缓冲游标按需生成结果。另一方面，当使用缓冲游标执行查询时，行被立即读入缓冲区，行提取方法从缓冲行集中返回行。

仅在以下情况下使用缓冲光标:

您已经知道结果集很小。或者，
您希望在另一个查询中使用一个查询的结果

请记住，缓冲的游标会将所有行一次加载到内存中。如果您有一个大的结果集，那么这会占用大量的内存，如果您没有足够的内存，甚至可能导致程序崩溃。因此，在使用大型结果集时，您应该始终按需加载结果(即使用无缓冲游标)。

在无缓冲游标的情况下，试图关闭游标对象或在从旧的结果集中读取结果之前执行新的查询将导致InternalError: Unread result found异常。

以下两个例子证明了这些事实:

示例 1:在读取结果集之前关闭光标对象

import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("select * from city limit 5")

cursor.close() # closing cursor before reading the result set

db.close()

预期输出:

Traceback (most recent call last):
...
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

示例 2:在从旧查询读取结果集之前，执行新查询。

import mysql.connector
from mysql.connector.cursor import MySQLCursor

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("select * from city limit 5")

# result set from the above query is not read

# now we are executing a new query

cursor.execute("""select Code, Name, Region, Population, Continent from 
country where Continent='North America' limit 5""")

for row in cursor:
    print(row)

cursor.close()
db.close()

预期输出:

Traceback (most recent call last):
...
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

但是，使用缓冲游标，您可以执行新的查询，但是之前查询的结果集将被丢弃。

我们将在本章后面学习如何创建缓冲光标。

光标属性

下表列出了一些只读属性，这些属性有助于我们获取关于上次执行的查询的相关信息。

属性	描述
`column_names`	将列名作为元组返回，并从中返回数据。
`with_rows`	如果查询产生结果集，返回`True`的布尔属性，否则返回`False`。
`description`	返回包含结果集中列信息的元组列表。表单的元组`(name, type_code, display_size, internal_ size, precision, scale, null_ok)`。`name`是指列名，type_code 是表示列类型的整数，`null_ok`决定一列是否可以接受`NULL`值(1 表示真，0 表示假)。其余字段始终设置为`None`。
`lastrowid`	返回最后修改或插入的行的 id，或者当没有这样的值时返回`None`(例如对于 SELECT 语句)。如果插入或更新了多行，那么它只返回第一行的 id。
`statement`	以字符串形式返回上次执行的查询。
`rowcount`	返回上次查询产生或影响的行数。对于无缓冲游标，`rowcount`最初设置为-1，并随着行的读取而递增。

下面是一个例子，展示了这些属性的作用:

import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("""select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5""")

print("rowcount (initial):", cursor.rowcount, end='\n\n')

# reading rows but not displaying
for row in cursor: pass

print("column_names:", cursor.column_names, end='\n\n')
print("with_rows:", cursor.with_rows, end='\n\n')

print("description: ", end="")
pprint(cursor.description)

print("\nstatement:", cursor.statement, end='\n\n')

print("lastrowid:", cursor.lastrowid, end='\n\n')

print("rowcount (final):", cursor.rowcount, end='\n\n')

cursor.close()
db.close()

预期输出:

rowcount (initial): -1

column_names: ('Code', 'Name', 'Region', 'Population', 'Continent')

with_rows: True

description: [('Code', 254, None, None, None, None, 0, 16387),
 ('Name', 254, None, None, None, None, 0, 1),
 ('Region', 254, None, None, None, None, 0, 1),
 ('Population', 3, None, None, None, None, 0, 1),
 ('Continent', 254, None, None, None, None, 0, 257)]

statement: select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5

lastrowid: None

rowcount (final): 5

请注意，在上面的输出中，列类型被打印为整数，这不是很有帮助。

我们可以使用FieldType类获得列的实际类型。FieldType类提供了 MySQL 所有支持的数据类型。要将整数类型代码转换为其字符串表示，我们使用FieldType类的get_info()方法，如下所示:

import mysql.connector
from mysql.connector import FieldType
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

cursor.execute("""select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5""")

cursor.fetchall() # read the result without printing anything

print("description: ", end="")
pprint(cursor.description)

print()

for desc in cursor.description:
    print("Column '{}' is of type {}".format( desc[0], FieldType.get_info(desc[1])))

cursor.close()
db.close()

预期输出:

description: [('Code', 254, None, None, None, None, 0, 16387),
 ('Name', 254, None, None, None, None, 0, 1),
 ('Region', 254, None, None, None, None, 0, 1),
 ('Population', 3, None, None, None, None, 0, 1),
 ('Continent', 254, None, None, None, None, 0, 257)]

Column 'Code' is of type STRING
Column 'Name' is of type STRING
Column 'Region' is of type STRING
Column 'Population' is of type LONG
Column 'Continent' is of type STRING

查询参数

到目前为止，我们只执行了静态查询。为了动态创建 SQL 查询，我们需要将用户提供的数据传递到查询中。我们使用查询参数来实现这一点。

查询参数只是值的占位符，在执行查询时将被实际值替换。以下是用于指定查询参数的两种常见样式。

格式- %s，%d
pyformat - %（name）s

在format样式中，我们使用像%s、%d等格式代码。作为占位符。这些占位符的值是使用序列(如列表或元组)指定的。

在pyformat样式中，扩展格式代码，如%(name)s用作占位符，值使用映射(如字典)指定。

下面的列表演示了每个参数样式的作用:

import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

# query with format parameter style
sql1 = """select Code, Name, Region, Population, Continent from
country where Continent=%s limit %s"""

data1 = ('Europe', 5)

cursor.execute(sql1, data1)

for row in cursor:
    print(row)

print('-' * 80)

# query with pyformat parameter style
sql2 = """select Code, Name, Region, Population, Continent from
country where Continent=%(continent)s limit %(limit)s"""

data2 = {
        'continent': 'Asia',
        'limit': 4
}

cursor.execute(sql2, data2)

for row in cursor:
    print(row)

cursor.close()
db.close()

预期输出:

('ALB', 'Albania', 'Southern Europe', 3401200, 'Europe')
('AND', 'Andorra', 'Southern Europe', 78000, 'Europe')
('AUT', 'Austria', 'Western Europe', 8091800, 'Europe')
('BEL', 'Belgium', 'Western Europe', 10239000, 'Europe')
('BGR', 'Bulgaria', 'Eastern Europe', 8190900, 'Europe')
--------------------------------------------------------------------------------
('AFG', 'Afghanistan', 'Southern and Central Asia', 22720000, 'Asia')
('ARE', 'United Arab Emirates', 'Middle East', 2441000, 'Asia')
('ARM', 'Armenia', 'Middle East', 3520000, 'Asia')
('AZE', 'Azerbaijan', 'Middle East', 7734000, 'Asia')

使用 execute()执行多个查询

execute()方法接受一个名为multi的可选关键字参数。默认设置为False。如果设置为True，允许execute()执行多个用分号分隔的查询。当用multi=True调用时，execute()方法返回一个迭代器，该迭代器可用于访问查询产生的结果集。

下面是一个实例:

import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

# query with format parameter style

sql1 = "select version()"

sql2 = """select Code, Name, Region, Population, Continent from
country where Continent=%s limit %s"""

sql3 = "select 1+1"

queries = [sql1, sql2, sql3]

data = ('South America', 5)

# returns an iterator
results = cursor.execute(";".join(queries), data, multi=True)

count = 1

for result in results:

    # result is a cursor object i.e result == cursor
    # so we have access to all cursor attributes and methods 

    print("Query {0} - {1} :".format(count, result.statement))

    # does query has result?
    if result.with_rows:
        for row in result:
            print(row)
        count = count + 1
    else:
        print("No result found")

    print()

cursor.close()
db.close()

预期输出:

Query 1 - select version():
('5.7.22-0ubuntu0.16.04.1',)

Query 2 - select Code, Name, Region, Population, Continent from
country where Continent='South America' limit 5:
('ARG', 'Argentina', 'South America', 37032000, 'South America')
('BOL', 'Bolivia', 'South America', 8329000, 'South America')
('BRA', 'Brazil', 'South America', 170115000, 'South America')
('CHL', 'Chile', 'South America', 15211000, 'South America')
('COL', 'Colombia', 'South America', 42321000, 'South America')

Query 3 - select 1+1:
(2,)

这种方法的缺点是所有的查询参数都必须在单个序列或映射中传递。如果您有大量的参数，那么您可能会失去对它们的跟踪。一个更好更直接的方法是单独执行每个查询。

有时，您可能希望使用不同的参数集执行相同的查询。实现这一点的一种方法是在循环中调用execute()方法，如下所示:

import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """update country
set LifeExpectancy = %(life_expectency)s, 
Population = %(population)s
where Name = %(name)s"""

data_list = [
    {
        'life_expectency': 78.9,
        'population': 103500,
        'name': 'Aruba'
    },
    {
        'life_expectency': 46.9,
        'population': 22720400,
        'name': 'Afghanistan'
    },
    {
        'life_expectency': 38.3,
        'population': 12879000,
        'name': 'Angola'
    },
]

for data in data_list:
    cursor.execute(sql, data)

# db.commit()  # call commit() method to save the changes

cursor.close()
db.close()

这个脚本更新了阿鲁巴、阿富汗和安哥拉的预期寿命和人口。

但是，如果您在执行脚本后查看country表，您会发现该表根本没有更新，这是因为在执行任何 DML 语句后，您都必须调用连接对象的commit()方法来提交事务并将更改保存到数据库中。我们将在“交易”一章中了解有关交易的更多信息。

介绍 executemany()

executemany()方法是用不同参数集执行查询的另一种方式。它在内部调用execute()方法。它的语法如下:

executemany(query, parameter_seqeunce)

参数	描述
`query`	要执行的查询
`parameter_sequence`	包含要在查询中使用的参数的序列

executemany()方法丢弃查询返回的结果集，所以不要用它来执行SELECT语句。

前面清单中的代码可以使用executemany()改写如下:

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql = """update country
set LifeExpectancy = %(life_expectency)s,
Population = %(population)s
where Name = %(name)s"""

data_list = [
    {
        'life_expectency': 78.9,
        'population': 103500,
        'name': 'Aruba'
    },
    {
        'life_expectency': 46.9,
        'population': 22720400,
        'name': 'Afghanistan'
    },
    {
        'life_expectency': 38.3,
        'population': 12879099,
        'name': 'Angola'
    },
]

cursor.executemany(sql, data_list)

# db.commit()

cursor.close()
db.close()

我们刚刚删除了 for 头并用executemany()替换了execute()调用，其余代码完全相同。

对于大多数查询来说，在一个循环中调用executemany()和调用execute()并没有真正的区别。但是对INSERT和REPLACE语句进行了优化。

考虑以下INSERT陈述:

insert into customer(name, age)
values('John', 45);

insert into customer(name, age)
values('Max', 25);

insert into customer(name, age)
values('Jane', 20);

insert into customer(name, age)
values('Bob', 34);

executemany()会将上述 4 个语句转换成一个单独的INSERT语句，如下所示:

insert into customer(name, age)
values('John', 45),
('Max', 25),
('Jane', 20),
('Bob', 34);

因为后一种形式将在单个事务中插入四行(而不是 4 行)，所以它比单行INSERT语句快得多。

REPLACE语句也是如此。

下面是一个使用executemany()方法创建一个新表并在其中插入一些行的例子。

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

sql1 = """
create temporary table customer(
  id int AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age SMALLINT NOT NULL     
)
"""

cursor.execute(sql1)

sql2 = """
insert into customer(name, age)
VALUES (%(name)s, %(age)s)
"""

data_list = [
    {
        'name': 'John',
        'age': 45
    },
    {
        'name': 'Max',
        'age': 25
    },
    {
        'name': 'Jane',
        'age': 20
    },
    {
        'name': 'Bob',
        'age': 34
    },
]

cursor.executemany(sql2, data_list)

print("Query:", cursor.statement)
print("Rowcount:", cursor.rowcount)  # rows inserted

db.commit()   # commit the changes

cursor.close()
db.close()

预期输出:

Query: insert into customer(name, age)
VALUES ('John', 45),('Max', 25),('Jane', 20),('Bob', 34)
Rowcount: 4

自定义光标对象

游标子类用于自定义游标返回结果集的方式，或者以其他方式修改对象行为。要创建这些类的对象，我们可以直接调用子类，或者将一次性参数传递给连接对象的cursor()方法。

下面是继承自MySQLCursor类的列表子类。

MySQLCursorBuffered
MySQLCursorRaw
MySQLCursorBufferedRaw
MySQLCursorDict
MySQLCursorBufferedDict
MySQLCursorNamedTuple
MySQLCursorBufferedNamedTuple
MySQLCursorPrepared

让我们逐一详细讨论。

MySQLCursorBuffered 类

MySQLCursorBuffered类创建一个缓冲光标。

要创建缓冲光标，请将buffered=True传递给连接对象的cursor()方法。或者，将buffered=True传递给connect()功能，这将打开从连接对象创建的所有光标对象的缓冲。

这里有一个例子:

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# turn on buffering for all cursor objects created from
# this connection
#
# db = mysql.connector.connect(
#     option_files='my.conf',
#     use_pure=True,
#     buffered=True
# )

# only this cursor will buffer results
cursor = db.cursor(buffered=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

预期输出:

rowcount (initial): 3
('Australia', 'Oceania', 18886000, 79.8)
('Switzerland', 'Europe', 7160400, 79.6)
('Sweden', 'Europe', 8861400, 79.6)
rowcount (final): 3

请注意，执行查询后，rowcount属性返回 3，这是因为我们使用的是缓冲游标，执行查询后会立即提取行。如果我们使用无缓冲光标，rowcount将返回-1。

MySQLCursorRaw 类

默认情况下，游标对象在提取行时会自动将 MySQL 类型转换为其等效的 Python 类型。如果要关闭此转换，请使用MySQLCursorRaw光标。

要创建原始光标，请将raw=True传递给连接对象的cursor()方法。或者，将raw=True传递给connect()功能，这将默认情况下使所有从连接创建的光标都是原始的。

这里有一个例子:

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# all cursor objects created from
# this connection will be raw
#
# db = mysql.connector.connect(
#     option_files='my.conf',
#     use_pure=True,
#     raw=True
# )

# only this cursor is raw
cursor = db.cursor(raw=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

预期输出:

rowcount (initial): -1
(bytearray(b'Australia'), bytearray(b'Oceania'), bytearray(b'18886000'), bytearray(b'79.8'))
(bytearray(b'Switzerland'), bytearray(b'Europe'), bytearray(b'7160400'), bytearray(b'79.6'))
(bytearray(b'Sweden'), bytearray(b'Europe'), bytearray(b'8861400'), bytearray(b'79.6'))
rowcount (final): 3

MySQLCursorBufferedRaw 类

MySQLCursorBufferedRaw类创建一个原始缓冲光标。

向MySQLCursorBufferedRaw光标传递raw=True和buffered=True到连接对象的cursor()方法。或者，将raw=True和buffered=True传递到connect()功能，这将默认使所有从连接创建的光标原始并缓冲。

这里有一个例子:

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# all cursor objects created from
# this connection will be raw and buffered
#
# db = mysql.connector.connect(
#     option_files='my.conf',
#     use_pure=True,
#     raw=True
#     buffered=True
# )

# only this cursor will be raw and buffered
cursor = db.cursor(raw=True, buffered=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

预期输出:

rowcount (initial): 3
(bytearray(b'Australia'), bytearray(b'Oceania'), bytearray(b'18886000'), bytearray(b'79.8'))
(bytearray(b'Switzerland'), bytearray(b'Europe'), bytearray(b'7160400'), bytearray(b'79.6'))
(bytearray(b'Sweden'), bytearray(b'Europe'), bytearray(b'8861400'), bytearray(b'79.6'))
rowcount (final): 3

MySQLCursorDict 类

MySQLCursorDict类允许我们将行作为字典而不是元组来访问。

要创建MySQLCursorDict光标，请将dictionary=True传递给连接对象的cursor()方法。

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return rows as dictionary instead of tuple
cursor = db.cursor(dictionary=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)
    print(row['Name'], row['Continent'])   # access columns by names

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

预期输出:

rowcount (initial): -1
{'Continent': 'Oceania', 'LifeExpectancy': 79.8, 'Population': 18886000, 'Name': 'Australia'}
Australia Oceania
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Population': 7160400, 'Name': 'Switzerland'}
Switzerland Europe
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Population': 8861400, 'Name': 'Sweden'}
Sweden Europe
rowcount (final): 3

MySQLCursorBufferedDict

MySQLCursorBufferedDict的工作方式与MySQLCursorDict完全相同，但返回一个缓冲光标。

要创建MySQLCursorBufferedDict光标，请将buffered=True和dictionary=True传递给连接对象的cursor()方法。

这里有一个例子:

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return a buffered MySQLCursorDict
cursor = db.cursor(dictionary=True, buffered=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)
    print(row['Name'], row['Continent'])

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

预期输出:

rowcount (initial): 3
{'Continent': 'Oceania', 'LifeExpectancy': 79.8, 'Name': 'Australia', 'Population': 18886000}
Australia Oceania
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Name': 'Switzerland', 'Population': 7160400}
Switzerland Europe
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Name': 'Sweden', 'Population': 8861400}
Sweden Europe
rowcount (final): 3

MySQLCursorNamedTuple 类

MySQLCursorNamedTuple类创建一个以namedtuple形式返回行的光标。

创建MySQLCursorNamedTuple将named_tuple=True传递给连接对象的cursor()方法。

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return a unbuffered MySQLCursorNamedTuple
cursor = db.cursor(named_tuple=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)
    print(row.Name, row.Continent)  # access columns as attributes

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

预期输出:

rowcount (initial): -1
Row(Name='Australia', Continent='Oceania', Population=18886000, LifeExpectancy=79.8)
Australia Oceania
Row(Name='Switzerland', Continent='Europe', Population=7160400, LifeExpectancy=79.6)
Switzerland Europe
Row(Name='Sweden', Continent='Europe', Population=8861400, LifeExpectancy=79.6)
Sweden Europe
rowcount (final): 3

MySQLCursorBufferedNamedTuple 类

类似于MySQLCursorNamedTuple，但创建一个缓冲光标。

创建MySQLCursorBufferedNamedTuple将named_tuple=True和buffered=True传递给连接对象的cursor()方法。

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return a buffered MySQLCursorNamedTuple
cursor = db.cursor(named_tuple=True, buffered=True)

sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country 
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC 
limit 3
"""

cursor.execute(sql)

print("rowcount (initial):", cursor.rowcount)

for row in cursor:
    print(row)
    print(row.Name, row.Continent)  # access columns as attributes

print("rowcount (final):", cursor.rowcount)

cursor.close()
db.close()

预期输出:

rowcount (initial): 3
Row(Name='Australia', Continent='Oceania', Population=18886000, LifeExpectancy=79.8)
Australia Oceania
Row(Name='Switzerland', Continent='Europe', Population=7160400, LifeExpectancy=79.6)
Switzerland Europe
Row(Name='Sweden', Continent='Europe', Population=8861400, LifeExpectancy=79.6)
Sweden Europe
rowcount (final): 3

MySQLCursorPrepared 类

MySQLCursorPrepared类创建一个游标来执行准备好的语句。

准备好的语句是一种特性，它允许我们重用一条 SQL 语句，而不会产生多次编译它的成本。当您想要重复执行具有不同参数集的查询时，它们非常有用。

与不同，非准备语句可以使用 format ( %s)或 pyformat ( %(name)s)参数样式。对于准备好的语句，只能使用 format( %d)或 qmark ( ?)参数样式。

要创建MySQLCursorPrepared光标，请将prepared=True传递给连接对象的cursor()方法。

这里有一个例子:

import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

# return a buffered MySQLCursorNamedTuple
cursor = db.cursor(prepared=True)

sql1 = """
create temporary table customer(
  id int AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age SMALLINT NOT NULL     
)
"""

cursor.execute(sql1)

sql2 = """
insert into customer(name, age)
VALUES (%s, %s)
"""

data_list = [
    ('John', 45),
    ('Max', 25),
    ('Jane', 20),
    ('Bob', 34),
]

cursor.executemany(sql2, data_list)

print("rowcount:", cursor.rowcount)
print(cursor)

cursor.close()
db.close()

预期输出:

rowcount: 4
MySQLCursorPrepared: 
insert into customer(name, age)
VALUES ..

在接下来的几课中，我们将看到一些INSERT、UPDATE和DELTETE语句的实际例子:

31 KiB Raw Permalink Blame History Unescape Escape

使用 Connector/Python 执行查询

创建光标对象

一个简单的 SELECT 语句

获取方法

缓冲和非缓冲光标

光标属性

查询参数

使用 execute()执行多个查询

介绍 executemany()

自定义光标对象

MySQLCursorBuffered 类

MySQLCursorRaw 类

MySQLCursorBufferedRaw 类

MySQLCursorDict 类

MySQLCursorBufferedDict

MySQLCursorNamedTuple 类

MySQLCursorBufferedNamedTuple 类

MySQLCursorPrepared 类

31 KiB

Raw Permalink Blame History