python操作数据库游标_python的pymysql游标的使用

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()中调用。

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))

cursor.MySQLCursorDict Class用法(继承自MySQLCursor,返回每行的字典)

cnx = mysql.connector.connect(database='world')

cursor = cnx.cursor(dictionary=True)

cursor.execute("SELECT * FROM country WHERE Continent = 'Europe'")

print("Countries in Europe:")

for row in cursor:

print("* {Name}".format(Name=row['Name']

也可以通过format来访问

cursor.execute("SELECT Name, Population FROM country WHERE Continent = 'Europe'")

print("Countries in Europe with population:")

for row in cursor:

print("* {Name}: {Population}".format(**row))

cursor.MySQLCursorRaw Class(行号游标)

import mysql.connector

cnx = mysql.connector.connect()

# Only this particular cursor will be raw

cursor = cnx.cursor(raw=True)

# All cursors created from cnx2 will be raw by default

cnx2 = mysql.connector.connect(raw=True)

cursor.MySQLCursorNamedTuple(行元组)

cnx = mysql.connector.connect(database='world')

cursor = cnx.cursor(named_tuple=True)

cursor.execute("SELECT * FROM country WHERE Continent = 'Europe'")

print("Countries in Europe with population:")

for row in cursor:

print("* {Name}: {Population}".format(

Name=row.Name,

Population=row.Population

))