python-day11 pymysql

標籤：pass   資料   into   res   connect   close   一個   update   注入   

python 操作 mysql

#######select

import pymysql

# 擷取資料
conn = pymysql.Connect(host=‘192.168.12.89‘,port=3306,user=‘root‘,password="123",database="s17day11db",charset=‘utf8‘)
cursor = conn.cursor()
# 受影響的行數
v = cursor.execute(‘select * from student‘)
result = cursor.fetchall()
# result = cursor.fetchone()
# result = cursor.fetchmany(2)
print(result)

cursor.close()
conn.close()

###########other

# 擷取資料
conn = pymysql.Connect(host=‘192.168.12.89‘,port=3306,user=‘root‘,password="123",database="s17day11db",charset=‘utf8‘)
cursor = conn.cursor()
# 受影響的行數
v = cursor.execute(‘insert into userinfo(username,password) values(%s,%s)‘,[‘eric‘,‘99999‘])
conn.commit()
v = cursor.execute(‘delete from userinfo where username=%s‘,[‘eric‘])
conn.commit()
v = cursor.execute(‘update userinfo set password=%s where username=%s‘,[‘999999‘,‘alex‘])
conn.commit()

cursor.close()
conn.close()

#################需求 
新建立一個班級，並且 建立一個學生 加入這個班級 

import pymysql

# 擷取資料
conn = pymysql.Connect(host=‘192.168.12.89‘,port=3306,user=‘root‘,password="123",database="s17day11db",charset=‘utf8‘)
cursor = conn.cursor()

cursor.execute(‘insert into class(caption) values(%s)‘,[‘新班級‘])
conn.commit()
new_class_id = cursor.lastrowid # 擷取新增資料自增ID

cursor.execute(‘insert into student(sname,gender,class_id) values(%s,%s,%s)‘,[‘李傑‘,‘男‘,new_class_id])
conn.commit()

cursor.close()
conn.close()

解釋：可以通過cursor.lastrowid 擷取到 本指令碼中執行的 自增ID
然後放入嚇一條sql中


#####################################被sql注入的寫法，  以及 sql注入 

import pymysql

user = input(‘請輸入使用者名稱:‘)
pwd = input(‘請輸入密碼:‘)

# 擷取資料
conn = pymysql.Connect(host=‘192.168.12.89‘,port=3306,user=‘root‘,password="123",database="s17day11db",charset=‘utf8‘)
cursor = conn.cursor()
sql = ‘select * from userinfo where username="%s" and password="%s" ‘ %(user,pwd,)
# user = alex" --
# pwd= asdf
‘select * from userinfo where username="alex" -- " and password="sdfsdf"‘
# user = asdfasdf" or 1=1  --
# pwd= asdf
‘select * from userinfo where username="asdfasdf" or 1=1  -- " and password="asdfasdf"‘
v = cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()

print(result)


###################防止sql注入

import pymysql

user = input(‘請輸入使用者名稱:‘)
pwd = input(‘請輸入密碼:‘)

# 擷取資料
conn = pymysql.Connect(host=‘192.168.12.89‘,port=3306,user=‘root‘,password="123",database="s17day11db",charset=‘utf8‘)
cursor = conn.cursor()
sql = ‘select * from userinfo where username="%s" and password="%s" ‘ %(user,pwd,)
# user = alex" --
# pwd= asdf
‘select * from userinfo where username="alex" -- " and password="sdfsdf"‘
# user = asdfasdf" or 1=1  --
# pwd= asdf
‘select * from userinfo where username="asdfasdf" or 1=1  -- " and password="asdfasdf"‘
v = cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()

print(result)

 

python-day11 pymysql