Class cursor.MySQLCursor

具体方法和属性如下：

Constructor cursor.MySQLCursor

Method MySQLCursor.callproc(procname, args=())

Method MySQLCursor.close()

Method MySQLCursor.execute(operation, params=None, multi=False)

Method MySQLCursor.executemany(operation, seq_params)

Method MySQLCursor.fetchall()

Method MySQLCursor.fetchmany(size=1)

Method MySQLCursor.fetchone()

Method MySQLCursor.fetchwarnings()

Method MySQLCursor.stored_results()

Property MySQLCursor.column_names

Property MySQLCursor.description

Property MySQLCursor.lastrowid

Property MySQLCursor.statement

Property MySQLCursor.with_rows

 

Constructor cursor.MySQLCursor

使用MySQLConnection对象来初始化

Method MySQLCursor.callproc(procname, args=())

调用procname程序，args要包含所有需要用到的参数。返回值类型为MySQLCursorBuffered。

# Definition of the multiply stored procedure:

# CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)

# BEGIN

# SET pProd := pFac1 * pFac2;

# END

>>> args = (5, 5, 0) # 0 is to hold value of the OUT parameter pProd

>>> cursor.callproc('multiply', args)

('5', '5', 25L)

Method MySQLCursor.close()

每次使用完cursor后，调用该函数关闭。

Method MySQLCursor.execute(operation, params=None, multi=False)

该函数用来提出针对数据库的操作。params是操作中的参数。

insert = (

"INSERT INTO employees (emp_no, first_name, last_name, hire_date) "

"VALUES (%s, %s, %s, %s)")

data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))

cursor.execute(insert, data)

select = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"

cursor.execute(select, { 'emp_no': 2 })

如果multi参数设置为true，则可以执行多条语句，返回值为指向每个结果的迭代器。

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'

for result in cursor.execute(operation):

if result.with_rows:

print("Statement '{}' has following rows:".format(

result.statement))

print(result.fetchall())

else:

print("Affected row(s) by query '{}' was {}".format(

result.statement, result.rowcount))

Method MySQLCursor.executemany(operation, seq_params)

数据库操作operation会执行多次，直至seq_params中所有参数执行完毕。

data = [

('Jane', date(2005, 2, 12)),

('Joe', date(2006, 5, 23)),

('John', date(2010, 10, 3)),

]

stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"

cursor.executemany(stmt, data)

Method MySQLCursor.fetchall()

返回查询的结果集合。

>>> cursor.execute("SELECT * FROM employees ORDER BY emp_no")

>>> head_rows = cursor.fetchmany(size=2)

>>> remaining_rows = cursor.fetchall()

Method MySQLCursor.fetchmany(size=1)

返回接下来的size个查询结果，如果没有足够的结果，则返回空的list。

Method MySQLCursor.fetchone()

返回接下来的一个查询结果，该函数在fetchamany()和fetchalll()中调用。

# Using a while-loop

cursor.execute("SELECT * FROM employees")

row = cursor.fetchone()

while row is not None:

print(row)

row = cursor.fetchone()

# Using the cursor as iterator

cursor.execute("SELECT * FROM employees")

for row in cursor:

print(row)

Method MySQLCursor.fetchwarnings()

设置get_warnings为true后，能通过该函数获取警告元组。

>>> cnx.get_warnings = True

>>> cursor.execute('SELECT "a"+1')

>>> cursor.fetchall()

[(1.0,)]

>>> cursor.fetchwarnings()

[(u'Warning', 1292, u"Truncated incorrect DOUBLE value: 'a'")]

Method MySQLCursor.stored_results()

调用 callproc()后，产生的结果集合可以用该函数获取。

>>> cursor.callproc('sp1')

()

>>> for result in cursor.stored_results():

... print result.fetchall()

...

[(1,)]

[(2,)]

Property MySQLCursor.column_names

只读属性。返回一个Unicode编码的string，为结果集合的列名称。

cursor.execute("SELECT last_name, first_name, hire_date "

"FROM employees WHERE emp_no = %s", (123,))

row = dict(zip(cursor.column_names, cursor.fetchone())

print("{last_name}, {first_name}: {hire_date}".format(row))

Property MySQLCursor.description

返回cursor结果集合的描述。

import mysql.connector

from mysql.connector import FieldType

...

cursor.execute("SELECT emp_no, last_name, hire_date "

"FROM employees WHERE emp_no = %s", (123,))

for i in range(len(cursor.description)):

print("Column {}:".format(i+1))

desc = cursor.description[i]

print("column_name = {}".format(desc[0]))

print("type = {} ({})".format(desc[1], FieldType.get_info(desc[1])))

print("null_ok = {}".format(desc[6]))

print("column_flags = {}".format(desc[7]))

输出如下:

Column 1:

column_name = emp_no

type = 3 (LONG)

null_ok = 0

column_flags = 20483

Column 2:

column_name = last_name

type = 253 (VAR_STRING)

null_ok = 0

column_flags = 4097

Column 3:

column_name = hire_date

type = 10 (DATE)

null_ok = 0

column_flags = 4225

Property MySQLCursor.lastrowid

返回最近修改的列的id值。

Property MySQLCursor.statement

返回上一次的执行结果。

Property MySQLCursor.with_rows

如果返回结果提供rows，该值为true。