Python-4.45 pymysql模块

• pymysql模块之sql注入
  

# pip3 install pymysql
import pymysql

user = input('user>>: ').strip()
pwd = input('password>>: ').strip()

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句
sql = 'select * from t1 where name = "%s" and pwd="%s"' % (user, pwd) # 注意%s需要加引号
rows = cursor.execute(sql)

cursor.close()
conn.close()

# 进行判断
if rows:
    print('登录成功')
else:
    print('登录失败')

# pip3 install pymysql
import pymysql

user = input('user>>: ').strip()
pwd = input('password>>: ').strip()

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句
# sql = 'select * from t1 where name = "%s" and pwd="%s"' % (user, pwd) #注意%s需要加引号
# rows = cursor.execute(sql)

sql = 'select * from t1 where name = %s and pwd= %s'  #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
rows = cursor.execute(sql, [user, pwd])

cursor.close()
conn.close()

# 进行判断
if rows:
    print('登录成功')
else:
    print('登录失败')

• pymysql模块之增删改：conn.commit()

import pymysql

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句：增
sql = 'insert into t1 (name, pwd) values(%s,%s)'
rows = cursor.execute(sql, ('winnie6', '666666'))

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

# pip3 install pymysql
import pymysql

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句：增
sql = 'insert into t1 (name, pwd) values(%s,%s)'
rows = cursor.executemany(sql, [('winnie7', '777777'), ('winnie8', '888888'), ('winnie9', '999999')])

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

• pymysql模块之查：fetchone，fetchmany，fetchall

import pymysql

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句：查询
rows = cursor.execute('select * from t1;')
print(cursor.fetchone())
print(cursor.fetchone())

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


结果：
(1, 'winnie1', 123456)
(2, 'winnie2', 111111)

import pymysql

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句：查询
rows = cursor.execute('select * from t1;')
print(cursor.fetchmany(2))

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

结果：
((1, 'winnie1', 123456), (2, 'winnie2', 111111))

import pymysql

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句：查询
rows = cursor.execute('select * from t1;')
print(cursor.fetchall())

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

import pymysql

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句：查询
rows = cursor.execute('select * from t1;')

cursor.scroll(3, mode='absolute')  # 相对绝对位置移动
print(cursor.fetchone())

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

结果：
(4, 'winnie4', 33333)

import pymysql

# 建立链接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='db9',
    charset='utf8'
)

# 拿到游标
cursor = conn.cursor()

# 执行sql语句：查询
rows = cursor.execute('select * from t1;')

cursor.scroll(2, mode='relative')  # 相对当前位置移动
print(cursor.fetchone())

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

结果：
(3, 'winnie3', 22222)