mysql 获取游标索引_游标操作/pymysql事务/sql注入/索引

游标操作

import pymysql

from pymysql.cursors import DictCursor

# 1)建立数据库连接对象 conn

conn = pymysql.connect(user='root', passwd='root', db='oldboy')

# 2)通过 conn 创建操作sql的 游标对象

cursor = conn.cursor(DictCursor)

# 3)编写sql交给 cursor 执行

sql = 'select * from t1'

# 4)如果是查询，通过 cursor对象 获取结果

row = cursor.execute(sql)

if row:

r1 = cursor.fetchmany(2)

print(r1)

# 操作游标

# cursor.scroll(0, 'absolute') # absolute绝对偏移，游标重置，从头开始偏移

cursor.scroll(-2, 'relative') # relative相对偏移，游标在当前位置进行左右偏移

r2 = cursor.fetchone()

print(r2)

# 5)操作完毕，端口操作与连接

cursor.close()

conn.close()

pymysql事务

import pymysql

from pymysql.cursors import DictCursor

conn = pymysql.connect(user='root', passwd='root', db='oldboy')

cursor = conn.cursor(DictCursor)

try:

sql = 'create table t2(id int, name char(4), money int)'

row = cursor.execute(sql)

print(row)

except:

print('表已创建')

pass

# 空表才插入

row = cursor.execute('select * from t2')

if not row:

sql = 'insert into t2 values(%s,%s,%s)'

row = cursor.executemany(sql, [(1, 'tom', 10), (2, 'Bob', 10)])

conn.commit()

# 可能会出现异常的sql

"""

try:

sql1 = 'update t2 set money=money-1 where name="tom"'

cursor.execute(sql1)

sql2 = 'update t2 set moneys=money+1 where name="Bob"'

cursor.execute(sql2)

except:

print('转账执行异常')

conn.rollback()

else:

print('转账成功')

conn.commit()

"""

try:

sql1 = 'update t2 set money=money-1 where name="tom"'

r1 = cursor.execute(sql1)

sql2 = 'update t2 set money=money+1 where name="ruakei"' # 转入的人不存在

r2 = cursor.execute(sql2)

except:

print('转账执行异常')

conn.rollback()

else:

print('转账没有异常')

if r1 == 1 and r2 == 1:

print('转账成功')

conn.commit()

else:

conn.rollback()

sql注入

import pymysql

from pymysql.cursors import DictCursor

conn = pymysql.connect(user='root', passwd='root', db='oldboy')

cursor = conn.cursor(DictCursor)

try:

sql = 'create table user(id int, name char(4), password char(6))'

row = cursor.execute(sql)

print(row)

except:

print('表已创建')

pass

# 空表才插入

row = cursor.execute('select * from user')

if not row:

sql = 'insert into user values(%s,%s,%s)'

row = cursor.executemany(sql, [(1, 'tom', '123'), (2, 'bob', 'abc')])

conn.commit()

# 用户登录

usr = input('usr: ')

pwd = input('pwd: ')

# 自己拼接参数一定有sql注入，将数据的占位填充交给pymysql

"""

sql = 'select * from user where name="%s" and password="%s"' % (usr, pwd)

row = cursor.execute(sql)

if row:

print('登录成功')

else:

print('登录失败')

"""

sql = 'select * from user where name=%s and password=%s'

row = cursor.execute(sql, (usr, pwd))

if row:

print('登录成功')

else:

print('登录失败')

# 知道用户名时

# 输入用户时：

# tom => select * from user where name="tom" and password="%s"

# tom" # => select * from user where name="tom" #" and password="%s"

# 不自定义用户名时

# " or 1=1 # => select * from user where name="" or 1=1 #" and password="%s"

索引

# 索引就是 键 - key

"""

1)键 是添加给数据库表的 字段 的

2)给表创建 键 后，该表不仅会形参 表结构、表数据，还有 键的B+结构图

3)键的结构图是需要维护的，在数据完成增、删、改操作时，只要影响到有键的字段，结构图都要维护一次

所以创建键后一定会降低 增、删、改 的效率

4)键可以极大的加快查询速度(开发需求中，几乎业务都和查有关系)

5)建立键的方式：主键、外键、唯一键、index

"""

import pymysql

from pymysql.cursors import DictCursor

conn = pymysql.connect(user='root', passwd='root', db='oldboy')

cursor = conn.cursor(DictCursor)

# 创建两张表

# sql1 = """create table a1(

# id int primary key auto_increment,

# x int,

# y int

# )"""

# cursor.execute(sql1)

# sql2 = """create table a2(

# id int primary key auto_increment,

# x int,

# y int,

# index(x)

# )"""

# cursor.execute(sql2)

# 每个表插入5000条数据

# import random

# for i in range(1, 5001):

# x = i

# y = random.randint(1, 5000)

# cursor.execute('insert into a1(x, y) values(%s, %s)', (x, y))

# cursor.execute('insert into a2(x, y) values(%s, %s)', (x, y))

#

# conn.commit()

import time

# a1的x、a1的id、a2的x

b_time = time.time()

sql = 'select * from a1 where id=4975'

cursor.execute(sql)

e_time = time.time()

print(e_time - b_time)

b_time = time.time()

sql = 'select * from a1 where x=4975'

cursor.execute(sql)

e_time = time.time()

print(e_time - b_time)

b_time = time.time()

sql = 'select * from a2 where x=4975'

cursor.execute(sql)

e_time = time.time()

print(e_time - b_time)