pymysql模組
基本語法
# (1) 建立連線物件 host user password database 這四個引數必寫
conn = pymysql.connect( host="127.0.0.1" , user="root" , password="123456" , database="db003" , charset="utf8" , port=3306 )
# (2) 建立遊標物件 (用來運算元據庫的增刪改查)
cursor = conn.cursor()
print(cursor)
# (3) 執行sql語句
sql = "select * from employee"
# 執行查詢語句返回的總條數
res = cursor.execute(sql)
print(res)
# (4) 獲取資料 fetchone 獲取一條資料
# 返回的是元組,裡面包含的是第一條的完整資料
# 類迭代器一般操作
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
# (5) 釋放遊標物件
cursor.close()
# (6) 釋放連線物件
conn.close()
# 一般在查詢的時候,透過fetchone來獲取結果
res1 = cursor.fetchone()
建立/刪除表操作
# conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
# cursor = conn.cursor()
# 1.建立一張表
sql = """
create table t1(
id int unsigned primary key auto_increment,
first_name varchar(255) not null,
last_name varchar(255) not null,
sex tinyint not null,
age tinyint unsigned not null,
money float
);
"""
# res = cursor.execute(sql)
# print(res) # 無意義返回值
# 2.查詢表結構
"""
sql = "desc t1"
res = cursor.execute(sql)
print(res) # 返回的是建立的欄位的個數
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
"""
# 3.刪除表
"""
try:
sql = "drop table t1"
res = cursor.execute(sql)
print(res) # 無意義返回值
except:
pass
"""
事務處理
[!IMPORTANT]
增刪改資料必須進行事務處理
# ### 3.事務處理
"""pymysql 預設開啟事務的,所有增刪改的資料必須提交,否則預設回滾;rollback"""
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
cursor = conn.cursor()
sql1 = "begin"
sql2 = "update employee set emp_name='程咬鑽石' where id = 18 "
sql3 = "commit"
res1 = cursor.execute(sql1)
res1 = cursor.execute(sql2)
res1 = cursor.execute(sql3)
print(res1)
cursor.close()
conn.close()
SQL隱碼攻擊
SQL隱碼攻擊現象
import pymysql
user = input("請輸入您的使用者名稱>>>")
pwd = input("請輸入您的密碼>>>")
conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="wbc")
cursor = conn.cursor()
sql1 = """
create table usr_pwd(
id int unsigned primary key auto_increment,
username varchar(255) not null,
password varchar(255) not null
)
"""
sql2 = "select * from usr_pwd where username='%s' and password='%s' " % (user,pwd)
# select * from usr_pwd where username='user' and password='2222 or 4=4 --'
print(sql2)
# res = cursor.execute(sql1)
res = cursor.execute(sql2)
"""
select * from usr_pwd where username='2222' or 4=4 -- aaa' and password=''
相當於 : select * from usr_pwd where 10=10; 繞開了賬戶和密碼的判斷 -- 代表的是註釋;
"""
if res:
print("登入成功")
else:
print("登入失敗")
cursor.close()
conn.close()
請輸入您的使用者名稱>>>111
請輸入您的密碼>>>ddd' or 1=1 --'
預處理機制
# (2) 預處理機制
""" 在執行sql語句之前,提前對sql語句中出現的字元進行過濾最佳化,避免sql注入攻擊 """
""" execute( sql , (引數1,引數2,引數3 .... ) ) execute2個引數預設開啟預處理機制 """
""" 填寫 234234' or 100=100 -- sdfsdfsdfsdf 嘗試攻擊 """
user = input("請輸入您的使用者名稱>>>")
pwd = input("請輸入您的密碼>>>")
conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005")
cursor = conn.cursor()
sql = "select * from usr_pwd where username=%s and password=%s"
res = cursor.execute(sql , (user,pwd) )
print(res)
print( "登入成功" if res else "登入失敗" )
cursor.close()
conn.close()
增刪改查
"""
python 操作mysql增刪改時,預設是開啟事務的,
必須最後commit提交資料,才能產生變化
提交資料: commit
預設回滾: rollback
"""
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db005")
# 預設獲取查詢結果時是元組,可以設定返回字典; cursor=pymysql.cursors.DictCursor
# 獲取字典號進行查詢的處理
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 執行對mysql 的操作
conn.commit()
cursor.close()
conn.close()
增
lastrowid 獲取最後插入的資料ID
一次插入一條
# 1.增
sql = "insert into t1(first_name,last_name,sex,age,money) values(%s,%s,%s,%s,%s)"
# (1) 一次插入一條
res = cursor.execute( sql , ("孫","健",0,15,20000) )
print(res) # 1
# 獲取最後插入這條資料的id號
print(cursor.lastrowid)
一次性插入多條
# 返回插入的條數
res = cursor.executemany( sql , [ ("安","曉東",0,18,30000) , ("劉","玉波",1,20,50000) ,("張","光旭",0,80,60000) , ("李","是元",0,10,10) , ("高","大奧",1,20,80000) ] )
print(res) # 返回插入的條數
# 插入5條資料中的第一條資料的id
print(cursor.lastrowid)
# 獲取最後一個資料的id
sql = "select id from t1 order by id desc limit 1"
res = cursor.execute(sql)
print(res)
查詢
sql = "select * from t1"
res = cursor.execute(sql)
print(res) # 針對於查詢語句來說,返回的res是總條數;
# (1) fetchone 獲取一條 迭代器獲得
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
# (2) fetchmany 獲取多條
res = cursor.fetchmany() # 預設獲取的是一條資料,返回列表,裡面裡面是一組一組的字典;
data = cursor.fetchmany(3)
print(data)
"""
[
{'id': 9, 'first_name': '王', 'last_name': '是元', 'sex': 0, 'age': 10, 'money': 10.0},
{'id': 10, 'first_name': '孫', 'last_name': '健', 'sex': 0, 'age': 15, 'money': 20000.0},
{'id': 11, 'first_name': '安', 'last_name': '曉東', 'sex': 0, 'age': 18, 'money': 30000.0}
]
"""
# 處理字串
for row in data:
first_name = row["first_name"]
last_name = row["last_name"]
sex = row["sex"]
if sex == 0:
sex = "男性"
else:
sex = "女性"
age = row["age"]
money = row["money"]
strvar = "姓:{},名:{},性別:{},年齡:{},收入:{}".format(first_name,last_name,sex,age,money)
print(strvar)
# (3) fetchall 獲取所有
# data = cursor.fetchall()
# print(data)
刪
"""
sql = "delete from t1 where id in (%s,%s,%s)"
res = cursor.execute(sql , (3,4,5) )
print(res) # 返回的是3,代表刪除了3條
if res:
print("刪除成功")
else:
print("刪除失敗")
"""
改
# 3.改
"""
sql = "update t1 set first_name = '王' where id = %s"
sql = "update t1 set first_name = '王' where id in (%s,%s,%s,%s)"
res = cursor.execute(sql , (6,7,8,9))
print(res) # 返回的是4,代表修改了4條
if res:
print("修改成功")
else:
print("修改失敗")
"""
滾動資料
相對滾動
# 1.相對滾動 relative
"""相對於上一次查詢的位置往前移動(負數),或者往後移動(正數)"""
"""
cursor.scroll(-1,mode="relative")
# cursor.scroll(5,mode="relative")
res = cursor.fetchone()
print(res)
"""
絕對滾動
# 2.絕對滾動 absolute"""永遠從資料的開頭起始位置進行移動,不能向前滾"""
cursor.scroll(0,mode="absolute")
res = cursor.fetchone()
print(res)