MySQL Database Learning "12th Chapter" Pymysql Module

One, the two methods of installation

First Kind

#安装pip3 Install Pymysql

The second Kind

Second, link, execute SQL, close (cursor)

Import pymysqluser= input (' User name:>> '). Strip () pwd= input (' Password:>> '). Strip () #先链接, get the cursor conn=pymysql.connect ( host= ' localhost ', user= ' root ', password= ' 123456 ',
Database= ' day47 ', charset= ' UTF8 ') cursor=conn.cursor () #拿到游标, that is, mysql > #执行sqlsql = ' select * ' from user where user= '%s ' and password= "%s"; '% ( USER,PWD) print (SQL) #注意%s requires double quotation marks of rows = cursor.execute (sql)  #拿到受影响的行数cursor. Close () conn.close () If rows:    Print (' login successful ') Else:    print (' Login failed ')

Iii. SQL injection of Execute ()

Note: The symbol--it will comment out the SQL after it, the correct syntax:--after at least one arbitrary character

The fundamental principle: based on the program string splicing name= '%s ', we enter a xxx '--haha, with our input xxx Plus ' in the program stitching into a judgment condition name= 'xxx '--haha'

The last space, if a select * is encountered in an SQL statement
from t1 where ID > 3--and Name= ' Egon '; then--After the condition is commented out # # #, SQL injection: The user exists, bypassing the password Egon '--Any character # #, SQL injection: The user does not exist, bypassing the user with the password xxx ' or 1=1 --Any character

Solution Injection

# It turns out we're string concatenation of SQL # sql= "SELECT * from UserInfo where name= '%s ' and password= '%s '"% (user,pwd) # Print (SQL) # Rows=cursor.ex Ecute (SQL) #改写为 (execute string concatenation for us, we do not need and must not quote the%s again) sql= "SELECT * from UserInfo where name=%s
and password=%s "#!!! Note that%s needs to be stripped of the quotation marks, because pymysql automatically adds Rows=cursor.execute (Sql,[user,pwd]) to us
#pymysql模块自动帮我们解决sql注入的问题, as long as we follow the rules of Pymysql.

Iv. increase, deletion and modification: conn.commit ()

Increase:

Import pymysql first link, get cursor conn=pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123456 ', database= ' day47 ') Cursor=conn.cursor () #拿到游标, MySQL > #执行sql   add: sql= ' insert into User1 (User,password) VALUES (%s,%s) ' Print (SQL) # rows = Cursor.execute (sql, (' Xixi ', 123))  #插入一条记录rows = Cursor.executemany (' sql,[', Xixi), (' AAA ', 123), (' TTT '), 147)]) #插入多行记录print ('%s row in Set (0.00 sec) '%rows) Conn.commit () #提交到数据库cursor. Close () Conn.close ()

By deleting:

Import pymysql# first link, get cursor name=input (' >> '). Strip () conn=pymysql.connect (host= ' localhost ', user= ' root ', password = ' 123456 ', database= ' day47 ') cursor=conn.cursor () #拿到游标, i.e. mysql > #执行sql   Delete: sql= ' delete from User1 where user =%s; '  #删除数据print (sql) rows = cursor.execute (sql, (name)) print ('%s row in Set (0.00 sec) '%rows) Conn.commit () # Submit to Database Cursor.close () Conn.close ()

Change:

Import pymysql# first link, get cursor id=input (' >> '). Strip () conn=pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123456 ', database= ' day47 ') cursor=conn.cursor () #拿到游标, i.e. mysql > #执行sql   change: sql= ' update user1 set password = ' 5555555 "Where id=%s; ' print (SQL) rows = cursor.execute (sql, (ID)) print ('%s row in Set (0.00 sec) '%rows) Conn.commit () #提交到数据库cursor. Close () Conn.close ()

Five, check: Fetchone,fetchmany,fetchall

---------Check fetchone,fetchmany,fetchall-----------import pymysqlconn=pymysql.connect (host= ' localhost ', user= ' root ' , password= ' 123456 ', database= ' day47 ') cursor=conn.cursor () #拿到游标, i.e. mysql > #执行sql   check: sql= ' select * from User1; ' rows = cursor.execute (sql) #查单条fetchoneres1 =cursor.fetchone () Res2=cursor.fetchone () Res3=cursor.fetchone () print ( RES1) print (res2) print (RES3) print (res3[0]) #查多条fetchmanyprint (Cursor.fetchmany (3)) print (Cursor.fetchone ()) # Check all Fetchallprint (Cursor.fetchall ()) print (Cursor.fetchone ()) #-------The movement--------#1 of the cursor. Absolute path: Print from the beginning of the file ( Cursor.fetchall ()) Cursor.scroll (1,mode= ' absolute ') print (Cursor.fetchone ()) Cursor.scroll (3,mode= ' absolute ') Print (Cursor.fetchone ()) #2. Relative path: Print (Cursor.fetchone ()) print (Cursor.fetchone ()) cursor.scroll (2,mode= ' relative ') #相对于上面的两条向后移两条print (Cursor.fetchone ()) print ('%s row in Set (0.00 sec) '%rows) Cursor.close () Conn.close ()

Vi. get the Increment ID of the last data inserted

------View the last row of the table in Idimport pymysqlconn=pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123456 ',
Database= ' day47 ', charset= ' UTF8 ') cursor=conn.cursor () sql= ' insert into User1 (User,password) values (%s,%s); ' Rows=cursor.execute (' Alex ', ' 123 ') # Rows=cursor.executemany (sql,[(' Yuanhao ', ' 123 '), (' Laowu ', ' 123 '), (' kgf ', ' 12323 ')] Conn.commit () print (CURSOR.LASTROWID)  #查看表中最后一行的iDcursor. Close () Conn.close ()

MySQL Database Learning "12th Chapter" Pymysql Module