Python tour. The tenth chapter. MySQL.

First, the last Class review

Select Concat_ws (":", Name,age,sex,post) as info from EMP; # egon:male:18

Second, sub-query (a problem solving a problem)

Enclose a query statement in parentheses, as a condition of another query statement, called a subquery

Select name from the emp where dep_id = (select id from dep where name= " technology "); # Sub-query

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

# Query the department name of the average age over the year

Select name from DEP where ID in (select dep_id from EMP GROUP by DEP_ID have AVG (age) > 25); # Sub-query

Select Dep.name from emp inner JOIN dep in emp.dep_id = Dep.id GROUP BY Dep.name have AVG (age) > 25; # Linked list

# See the department name of less than 2 people ( sub-query gets the department ID of someone)

SELECT * from emp where exists (select ID from dep where ID > 3); #exists usage, when () returns True , the outer query statement is queried, and when the return value is False , the outer query statement does not query (empty set)

# Find the employee who has the latest job in 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 Ma X_date from EMP Group by post) as t2 on t1.post = t2.post where t1.hire_date = t2.max_date;

Three, IDE tools (navicat) Introduction

1. ER Chart (entity-relationship)

2, model; export SQL

3, query, format to beautify SQL

Four, pymysql module (Installation and query)

1.Install pymysql(python -specific mysql client socket)

PIP3 Install Pymysql

2.mysql query

Import Pymysql

Conn=pymysql.connect (

Host= ' 127.0.0.1 ',

port=3306,

User= ' Root ',

Password= ' jxtz0927 ',

Database= ' DB40 ',

charset= ' UTF8 ' # prevents garbled

)

Cursor=conn.cursor (pymysql.cursors.DictCursor) # Displays the resulting results in a dictionary [{' TABLES_IN_DB40 ': ' Department '}, {' Tables _IN_DB40 ': ' Employee '}]

# cursor=conn.cursor ((' Department ',) (' Employee ',)) #

# Rows=cursor.execute (' Show tables; ') #rows to the number of rows affected

Rows=cursor.execute (' select * from class; ')

Print (rows) #2

Print (Cursor.fetchone ()) # One Piece

Print (Cursor.fetchmany (2)) # several

Print (Cursor.fetchall ()) # all

Cursor.scroll (3, ' absolute ') #mode = ' absolute ', Absolute mode, three moves backwards from the first position

Cursor.scroll (1, ' relative ') #mode = ' relative ', relative mode, move backward from current position

Cursor.close ()

Conn.close ()

V. pymysql Module (prevents SQL injection problems)

1, the wrong way, the string stitching itself, causing SQL injection problem (name= Egon '--asdfg; name=xxxx ' or 1=1--ASDFG)

Import Pymysql

Conn=pymysql.connect (

Host= ' 127.0.0.1 ',

port=3306,

User= ' Root ',

Password= ' 123 ',

Database= ' Db42 ',

charset= ' UTF8 '

)

Cursor=conn.cursor (Pymysql.cursors.DictCursor)

Inp_user=input (' username >>:'). Strip () #inp_user = ""

Inp_pwd=input (' password >>:'). Strip () #inp_pwd = ""

Sql= "SELECT * from user where username= '%s ' and password= '%s '"% (inp_user,inp_pwd) # self-stitching, triggering SQL injection Problem

Print (SQL)

Rows=cursor.execute (SQL)

If rows:

Print (' login successful ')

Else

Print (' login failed ')

Cursor.close ()

Conn.close ()

2, in the server to prevent SQL injection problem: Do not own the string, let pymysql module to splice

Import Pymysql

Conn=pymysql.connect (

Host= ' 127.0.0.1 ',

port=3306,

User= ' Root ',

Password= ' 123 ',

Database= ' Db42 ',

charset= ' UTF8 '

)

Cursor=conn.cursor (Pymysql.cursors.DictCursor)

Inp_user=input (' username >>:'). Strip () #inp_user = ""

Inp_pwd=input (' password >>:'). Strip () #inp_pwd = ""

Sql= "SELECT * from user where username=%s and password=%s"

Print (SQL)

Rows=cursor.execute (SQL, (INP_USER,INP_PWD))

If rows:

Print (' login successful ')

Else

Print (' login failed ')

Cursor.close ()

Conn.close ()

Vi. pymysql module (Increase and deletion)

Import Pymysql

Conn=pymysql.connect (

Host= ' 127.0.0.1 ',

port=3306,

User= ' Root ',

Password= ' 123 ',

Database= ' Db42 ',

charset= ' UTF8 '

)

Cursor=conn.cursor (Pymysql.cursors.DictCursor)

Sql= ' Update user set username= "ALEXSB" where id=2 '

Rows=cursor.execute (SQL) # change Data

Print (rows)

Print (CURSOR.LASTROWID)

Sql= ' INSERT into user (Username,password) VALUES (%s,%s) '

Rows=cursor.executemany (sql,[(' lwz ', ' 123 '), (' Evia ', ' 455 '), (' LSD ', ' 333 ')]) # insert multiple rows at once

Print (rows)

Print (CURSOR.LASTROWID) # shows which line is plugged in (ID)

Conn.commit () # only commit commits will complete the real change

Cursor.close ()

Conn.close ()

Python tour. The tenth chapter. MySQL.