python cursor游标重置位置scroll_MySQLdb操作数据库（三）

删除数据

删除单条数据

#conding=utf-8

import MySQLdb

import random

try:

conn = MySQLdb.connect(

host = '127.0.0.1',

user = 'root',

passwd = "123456",

port = 3306)

conn.select_db('python')# 选择pythonDB数据库

cur = conn.cursor()# 获取游标

cur.execute("delete from user where id=0")

cur.execute("select * from user")

for v in cur.fetchall():

print v

cur.close()

conn.commit()

conn.close()

print u"sql执行成功"

except Exception, e:

print e

删除多条数据

#conding=utf-8

import MySQLdb

import random

try:

conn = MySQLdb.connect(

host = '127.0.0.1',

user = 'root',

passwd = "123456",

port = 3306)

conn.select_db('python')# 选择pythonDB数据库

cur = conn.cursor()# 获取游标

sql = "delete from user where id=%s"

cur.executemany(sql,[(3),(2,)])

cur.execute("select * from user")

for v in cur.fetchall():

print v

cur.close()

conn.commit()

conn.close()

print u"sql执行成功"

except Exception, e:

print e

事务回滚

import MySQLdb

import random

try:

conn = MySQLdb.connect(

host = '127.0.0.1',

user = 'root',

passwd = "123456",

port = 3306)

conn.select_db('python')#

cur = conn.cursor()# 获取游标

cur.execute("select * from user")

datas = cur.fetchall()

print u"修改前的数据：\n", datas[0]

cur.execute("update user set birthday='2100-08-12'where name='tom6")

cur.execute("select * from user")

datas = cur.fetchall()

print u"修改后的数据：\n", datas[0]

#回滚事务

conn.rollback()

cur.execute("select * from user")

datas = cur.fetchall()

print u"事务回滚后的数据：\n", datas[0]

#关闭游标

cur.close()

#提交事务

conn.commit()

#关闭数据库连接

conn.close()

print u"sql语句执行成功！"

except Exception, e:

print e

重置游标位置

scroll(value, mode='relative')

移动指针到参数value指定的行；

Mode = relative则表示从当前所在行前移value行

Mode=absolute表示移动到绝对位置的value行。游标索引从0开始

cursor.rownumber

返回当前游标所在位置

数据库表内容:

#coding=utf-8

import MySQLdb

#打开数据库连接

conn = MySQLdb.connect(

host = "localhost",

port = 3306,

user = "root",

passwd = "123456",

db = "python",

charset = "utf8")

#使用cursor()方法获取数据库的操作游标

cursor = conn.cursor()

cursor.execute("select * from user order by id")

print u"当前游标所在位置：", cursor.rownumber

print cursor.fetchone()

print u"当前游标所在位置：", cursor.rownumber

cursor.scroll(0,mode="absolute")#移动游标到第一行

print u"当前游标所在位置：", cursor.rownumber

print cursor.fetchone()

#coding=utf-8

import MySQLdb

#打开数据库连接

conn = MySQLdb.connect(

host = "localhost",

port = 3306,

user = "root",

passwd = "123456",

db = "python",

charset = "utf8")

#使用cursor()方法获取数据库的操作游标

cursor = conn.cursor()

cursor.execute("select * from user order by id")

print u"当前游标所在位置：", cursor.rownumber

res = cursor.fetchmany(5)

for value in res:

print value

print u"当前游标所在位置：", cursor.rownumber

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

print u"当前游标所在位置：", cursor.rownumber

print cursor.fetchone()