May 11 Python Learning summary subquery, Pymysql module additions and deletions to prevent SQL injection problems

One, sub-query

Subquery: A query statement enclosed in parentheses, as a condition of another query statement to use, called a subquery

Select Emp.name from emp inner JOIN dep on emp.dep_id = dep.id where dep.name= "technology";

Select name from emp where dep_id =
(select ID from DEP where name= "technology");

Check the department name of the average age over 25 years
Select name from DEP where ID in
(select dep_id from EMP GROUP by DEP_ID have AVG (age) > 25);

Select Dep.name from emp inner JOIN dep on emp.dep_id = Dep.id
GROUP BY Dep.name
have avg (age) > 25;

View department names for less than 2 people (subqueries get someone's department ID)

　Supplement: Exists exists

SELECT * from emp where exists (
Select ID from DEP where ID > 3
);

Find the employee who has the latest entry for each department

Select T1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from emp as T1 inner JOIN
(select Post,max (hire_date) as max_date from EMP Group by post) as T2
On t1.post = T2.post
where t1.hire_date = T2.max_date
;

Second, Pymysql module

Basic operation of the query:　

ImportPymysql#PIP3 Install PymysqlConn=Pymysql.connect (Host='127.0.0.1', Port=3306, the user='Root', Password='123', Database='db42', CharSet='UTF8') Cursor=conn.cursor (Pymysql.cursors.DictCursor)#query results are displayed in a dictionary formatSQL='SELECT * from class;'rows=cursor.execute (SQL)#executes an SQL statement with the return value being the number of rows executing the resultPrint(rows)Print(Cursor.fetchone ())#take out a row to execute the result, the cursor moves backward onePrint(Cursor.fetchone ())#take out the second line execution result the cursor moves backward onePrint(Cursor.fetchmany (2))#remove two rows execution result cursor moves backwards two bitsPrint(Cursor.fetchall ())#Take out all the restPrint(Cursor.fetchall ())#print (Cursor.fetchall ())#Cursor.scroll (3, ' absolute ') # Absolute path cursor moves backward 3 positions from the start position#print (Cursor.fetchone ()) #取出当前光标所在位置的数据, and move the cursor backPrint(Cursor.fetchone ())Print(Cursor.fetchone ()) Cursor.scroll (1,'relative')#relative path cursor moves backward 3 positions from the current positionPrint(Cursor.fetchone ()) Conn.commint ()#Commit a transactioncursor.close () conn.close ( )

　　Modification of basic operation:

ImportPymysql#PIP3 Install PymysqlConn=Pymysql.connect (Host='127.0.0.1', Port=3306, the user='Root', Password='123', Database='db42', CharSet='UTF8') Cursor=conn.cursor (pymysql.cursors.DictCursor)#Insert one row of records at a timeSql='INSERT INTO User (Username,password) VALUES (%s,%s)'rows=cursor.execute (SQL, ('EGON','123456'))Print(rows)Print(cursor.lastrowid) rows=cursor.execute ('Update user set username= "ALEXSB" where id=2')Print(rows)#insert multiple rows of records at onceSql='INSERT INTO User (Username,password) VALUES (%s,%s)'rows=cursor.executemany (sql,[('Lwz','123'),('Evia','455'),('LSD','333')])Print(rows)Print(Cursor.lastrowid) conn.commit ()#only commit commits will complete the real changecursor.close () conn.close ( )

Third, prevent SQL injection problem

1, when the correct user name is entered and appended with the '--'--' SQL comment , we execute the password verification judgment behind this SQL is commented out, so that we can log in without a password

2, or when the user input user name, arbitrary input user name and add ' or 1=1 ' this statement, then skipped the account password verification directly landed in the

So Rows=cursor.execute (SQL) of this kind of splicing SQL, direct execution method is not reliable　　　

ImportPymysql#PIP3 Install PymysqlConn=Pymysql.connect (Host='127.0.0.1', Port=3306, the user='Root', Password='123', Database='db42', CharSet='UTF8') Cursor=conn.cursor (pymysql.cursors.DictCursor) Inp_user=input ('user name >>:'). Strip ()#inp_user= ""Inp_pwd=input ('password >>:'). Strip ()#inp_pwd= ""Sql="SELECT * from user where username= '%s ' and password= '%s '"%(INP_USER,INP_PWD)#1, when the correct user name is entered and appended with the '--'--' SQL comment, we execute the password verification judgment behind this SQL is commented out, so that we can log in without a password#2, or when the user input user name, arbitrary input user name and add ' or 1=1 ' this statement, then skipped the account password verification directly landed in therows=cursor.execute (SQL)#so this concatenation of SQL, direct execution of the method is not reliableifrows:Print('Login Successful')Else:    Print('Logon Failure') Cursor.close () conn.close ()

Workaround:

Pymysql provides us with the ability to filter special symbols in strings

Using the following method to pass in the parameters, will automatically help us to filter out special characters, to prevent SQL injection

Inp_user=input (' username >>:'#inp_user= ""inp_pwd=input (' password >>: ' # inp_pwd= ""sql="select * from user where username=%s and password=%s"  Rows=cursor.execute (sql, (INP_USER,INP_PWD))

May 11 Python Learning summary subquery, Pymysql module additions and deletions to prevent SQL injection problems