Python操作MySQL(2)

查询数据

使用execute()函数执行查询sql语句后，得到的只是受影响的行数，并不能真正拿到我们查询的内容。没关系，这里游标cursor中还提供了三种提取数据的方法：fetchone、fetchmany、fetchall，每个方法都会导致游标游动，所以必须注意游标的位置
cursor. fetchone()
获取游标所在处的一行数据，返回的是元组，没有则返回None,
cursor. fetchmany(size=None)
接收size条返回结果行。如果size的值大于返回的结果行的数量，则会返回cursor.arraysize条数据。返回的结果是一个元组，元组的元素也是元组，由每行数据组成；
cursor. fetchall()
接收全部的返回结果行。返回的结果是一个元组，元组的元素也是元组，由每行数据组成；

注意：
这些函数返回的结果数据均来自exceute()函数查询的结果集。如果exceute()结果集中没有数据，将会返回空元组。

fetchone示例
#encoding=utf-8
import pyMysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()

    cursor.execute("select * from user")
    while 1:
        res = cursor.fetchone()
        if res is not None:
            print(res)
        else:
            break

    cursor.close()
    conn.close()

except pymysql.Error as e:
print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

从execute()函数的查询结果中取数据，以元组的形式返回游标所在处的一条数据，如果游标所在处没有数据，将返回空元组，该数据执行一次，游标向下移动一个位置。fetchone()函数必须跟exceute()函数结合使用，并且在exceute()函数之后使用

fetchmany示例
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()

cursor.execute("select * from user")
#此处取2条数据，返回一个包含2个元素的元组，元组的元素还是元组
resTuple = cursor.fetchmany(2)
print(type(resTuple))
    for v in resTuple:
        print(v)

    cursor.close()
    conn.close()

except pymysql.Error as e:
    print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

从exceute()函数结果中获取游标所在处的size条数据，并以元组的形式返回，元组的每一个元素都也是一个由一行数据组成的元组，如果size大于有效的结果行数，将会返回cursor.arraysize条数据，但如果游标所在处没有数据，将返回空元组。查询几条数据，游标将会向下移动几个位置。fetmany()函数必须跟exceute()函数结合使用，并且在exceute()函数之后使用

fetchall示例
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()

    cursor.execute("select * from user")
resTuple = cursor.fetchall()
print("共%s 条数据" %len(resTuple))
    print(type(resTuple))
    print(resTuple)

    cursor.close()
    conn.close()

except pymysql.Error as e:
    print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

获取游标所在处开始及以下所有的数据，并以元组的形式返回，元组的每一个元素都也是一个由一行数据组成的元组，如果游标所在处没有数据，将返回空元组。执行完这个方法后，游标将移动到数据库表的最后

更新数据
更新单条数据
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()

    res = cursor.execute("update user set name = 'hhq' where name = 'lucy0';")
    print("受影响的行数: ",res)
    cursor.execute("select * from user where name = 'hhq';")
    print(cursor.fetchone())

    cursor.close()
    conn.commit()
    conn.close()

except pymysql.Error as e:
    print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

批量更新数据
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()
    sql = "update user set name = %s where name = %s;"
    res = cursor.executemany(sql,[("hhq1","lucy1"),("hhq2","lucy2")])
    print("受影响的行数: ",res)

    cursor.execute("select * from user where name in ('hhq1','hhq2');")
    for i in cursor.fetchall():
        print(i)

    cursor.close()

    conn.close()

except pymysql.Error as e:
print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

删除数据
删除单条数据
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()
    sql = "delete from user where name = 'lucy17';"
    res = cursor.execute(sql)
    print("受影响的行数: ",res)

    cursor.close()

    conn.close()

except pymysql.Error as e:
    print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

批量删除多条数据
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()
sql = "delete from user where name = %s;"

    res = cursor.executemany(sql,[('lucy20',),('lucy21',)])#需要传元组
    print("受影响的行数: ",res)

    cursor.close()

    conn.close()

except pymysql.Error as e:
    print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

回滚事务
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()
    cursor.execute("select * from user;")
    res = cursor.fetchall()
    print("更新前的数据: ",res[-1])

    cursor.execute("update user set name = 'hhq'")

    cursor.execute("select * from user;")
    res1 = cursor.fetchall()
    print("更新后的数据: ",res1[-1])

    #回滚事务
    conn.rollback()

    cursor.execute("select * from user;")
    res1 = cursor.fetchall()
    print("回滚后的数据: ",res1[-1])

    cursor.close()
    #提交事务
    conn.commit()
    conn.close()

except pymysql.Error as e:
    print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

重置游标位置
scroll(value, mode='relative')
移动指针到参数value指定的行；
Mode = relative则表示从当前所在行前移value行
Mode=absolute表示移动到绝对位置的value行。游标索引从0开始

cursor.rownumber
返回当前游标所在位置

示例:
#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()
    cursor.execute("select * from user;")
print("游标当前位置:" ,cursor.rownumber)

    print(cursor.fetchone())
print("游标当前位置:" ,cursor.rownumber)

    cursor.scroll(0,mode="absolute")
    print("游标当前位置:" ,cursor.rownumber)

    cursor.fetchmany(2)
    print("游标当前位置:" ,cursor.rownumber)

except pymysql.Error as e:
    print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))

#encoding=utf-8
import pymysql
try:
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        passwd = "123456"
        )
    conn.select_db("pydb")
    cursor = conn.cursor()
    cursor.execute("select * from user;")
    print("游标当前位置:" ,cursor.rownumber)

    res = cursor.fetchmany(2)
    print(res)
    print("游标当前位置:" ,cursor.rownumber)

cursor.scroll(3,mode="relative")#游标会向前移动3，对应数据库表就是向后移动3行
print("游标当前位置:" ,cursor.rownumber)
    print(cursor.fetchone())  

except pymysql.Error as e:
    print("pymysql.Error %d: %s" %(e.args[0],e.args[1]))