删除数据
删除单条数据
#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()
原文:http://blog.51cto.com/13496943/2152097