数据库-part3-pymysql模块操作数数据库

import pymysql

'''
pymysql接收几个参数用来连接数据库
'''
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='root',
    database='db1',
    port=3306,
)

'''
创建一个游标，类似文件指针
'''
cur = connection.cursor()  #

username = input('please input your name:')
pwd = input('please input your password:')
sql = "select * from userinfo where name='%s' and pwd='%s';" % (username, pwd)
print(sql)
resCount = cur.execute(sql)
print(resCount)
if resCount:  # 顺利执行这个 sql ，就是 True ， 数据库里面有这个用户名
    print('login success')
else:  # 否则就是 False
    print('login fail')
cur.close()  # 用完一定要关闭！
connection.close()  # 用完一定要关闭连接，否则大的数据量的时候，会卡死数据库
'''
但是有一个致命的危机！！
sql注入：
当用户名字输入： alex' -- XXX，把sql拼接，后半段成注释的时候，
也会查询成功，这样就避免了后台对于密码的校验
'''

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='root',
    database='db1',
    port=3306,
)

'''
创建一个游标，类似文件指针
'''
cur = connection.cursor()  #

username = input('please input your name:')
pwd = input('please input your password:')

'''
使用如下方法可避免 sql 注入 ★★★★★
'''

sql = "select * from userinfo where name=%(username)s and pwd=%(password)s;"
print(sql)
resCount = cur.execute(sql, {'username': username, 'password': pwd})
print(resCount)
if resCount:
    print('login success')
else:
    print('login fail')
cur.close()
connection.close()

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='root',
    database='db1',
    port=3306,
    charset='utf8'
)

cur = connection.cursor()

sql = "insert into userinfo(name, pwd) values (%(username)s,%(password)s);"
username = input('please input your name:')
password = input('please input your password:')

resCount = cur.execute(sql, {"username": username, "password": password})
connection.commit()  # 插入一定要 commit()
print(resCount)
print(sql)

connection.close()
cur.close()

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='root',
    database='db1',
    port=3306,
)

cur = connection.cursor(cursor=pymysql.cursors.DictCursor)  # 以字典的形式返回

sql = "select * from userinfo"

resCount = cur.execute(sql)

print(resCount)  # 返回查询到的行数-1

# row = cur.fetchone()  # 打印第一行，cur<游标>执行 .fetchone() 方法，打出当前游标所在行
# print(row)
# row = cur.fetchone()  # 打印第二行
# print(row)
# row = cur.fetchone()  # 打印第三行
# print(row)


# rows = cur.fetchmany(5)  # 将当前数据库里面所有的数据前5条以单条字典的形式放在列表内全部返回
# print(rows)

# rows = cur.fetchall()  # 将当前数据库里面所有的数据以单条字典的形式放在列表内全部返回
# print(rows)

# row = cur.fetchone()
# print(row)
# row = cur.fetchone()
# print(row)
# cur.scroll(1, mode='relative')  # 相对于自己当前的位置向下跳一行，可以是负数，向上
# row = cur.fetchone()
# print(row)
# row = cur.fetchone()
# print(row)
# row = cur.fetchone()
# print(row)

row = cur.fetchone()
print(row)
row = cur.fetchone()
print(row)
cur.scroll(0, mode='absolute')  # 游标相对于起始位置
row = cur.fetchone()
print(row)
row = cur.fetchone()
print(row)
row = cur.fetchone()
print(row)