Python-day11 Pymysql

Python operation MySQL

###### #select

Import Pymysql

# Get Data
conn = Pymysql. Connect (host= ' 192.168.12.89 ', port=3306,user= ' root ', password= "123", database= "s17day11db", charset= ' UTF8 ')
cursor = Conn.cursor ()
# Number of rows affected
v = cursor.execute (' SELECT * from student ')
result = Cursor.fetchall ()
# result = Cursor.fetchone ()
# result = Cursor.fetchmany (2)
Print (Result)

Cursor.close ()
Conn.close ()

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

 # get Data 
 conn = Pymysql. Connect (host= ' 192.168.12.89 ', port=3306,user= ' root ', password= "123", database= "s17day11db", charset= ' UTF8 ') 
 cursor = Conn.cursor () 
 # Number of rows affected 
 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 () 
 
 ################ #需求 
 Create a new class and create a student to join the class 

 Import pymysql 
 
 # get data 
 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) ', [' New class ']) 
 Conn.commit () 
 new_class_id = cursor.lastrowid # get new data self-increment ID 
 
 Cursor.execute (' INSERT into student (sname,gender,class_id) values (%s,%s,%s) ', [' Li Jie ', ' Male ', new_class_id]) 
 Conn.commit () 
 
 Cursor.close () 
 Conn.close () 
 
 Explanation: You can get the self-increment ID that is executed in this script by Cursor.lastrowid 
 and then put it into a panic in SQL 
 
 
 #################################### #被sql注入的写法 , and SQL injection 

 Import pymysql 
 
 user = input (' Please enter user name: ') 
 pwd = input (' Please enter password: ') 
 
 # get data 
 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 = a Lex "--
 # 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 (' Please enter user name: ')
PWD = input (' Please enter password: ')

# Get Data
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