資料庫-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)