数据库-part3-pymysql模块操作数数据库
1.关于sql注入★★★★★
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拼接，后半段成注释的时候， 也会查询成功，这样就避免了后台对于密码的校验 '''
2.标准查询方式（避免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()
3.数据库的插入操作
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()
4.查询数据库的进阶（用的最多的模块及方法）
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)
