Usage of Python pymsql 180903

One.
Download for 1.pymysql
PIP3 Install Pymysql
Use of 2.pymysql
Import Pymysql
Name=input ("Please enter user name:")
Password=input ("Please enter password:")
# 1. Connect
Conn=pymysql.connect (
Host= "127.0.0.1",
port=3306,
db= "DB5",
User= "Root",
Password= "",
charset= "UTF8"
)
# 2. Creating cursors
Cur=conn.cursor ()
# Note%s requires quotation marks
Sql= "SELECT * from UserInfo where name= '%s ' and password= '%s '"% (Name,password)
Result=cur.execute (SQL)
# Execution SQL statement returns the number of successful SQL query records
Print (Result)
# Close Cursors and connections
Cur.close ()
Conn.close ()
If result:
Print ("Login Successful")
Else
Print ("Login Failed")
Two, SQL injection of Execute ()
Problem
# The last space, in an SQL statement if you encounter a select * from UserInfo where username= ' mjj '--asadasdas ' and pwd= '--after the condition is commented out (note-there is a space behind)
# 1.sql injected: The user exists, bypassing the password;
# mjj '-Any character
#2. SQL injection: The user does not exist, bypassing the user and password;
# xxx ' or 1=1--any character
# Solution:
# error Reason: string concatenation of SQL
# rewritten as (execute helps us do string concatenation, we don't need and must not quote%s again)
Import Pymysql
Name=input ("Please enter user name:")
Password=input ("Please enter password:")
# 1. Connect
Conn=pymysql.connect (
Host= "127.0.0.1",
port=3306,
db= "DB5",
User= "Root",
Password= "",
charset= "UTF8"
)
# 2. Creating cursors
Cur=conn.cursor ()
#%s Pymysql Auto-add without quotes
Sql= "SELECT * from UserInfo where name=%s and password=%s"
Result=cur.execute (Sql,[name,password])
# Execution SQL statement returns the number of successful SQL query records
Print (Result)
# Close Cursors and connections
Cur.close ()
Conn.close ()
If result:
Print ("Login Successful")
Else
Print ("Login Failed")
Three additions and deletions change conn.commit ()
Commit () method: When the database is added, deleted, changed, must be committed, otherwise the inserted data does not take effect.
Import Pymysql
Name=input ("Please enter user name:")
Password=input ("Please enter password:")
# 1. Connect
Conn=pymysql.connect (
Host= "127.0.0.1",
port=3306,
db= "DB5",
User= "Root",
Password= "",
charset= "UTF8"
)
# 2. Creating cursors
Cur=conn.cursor ()
Operation
Sql= "INSERT into UserInfo (Name,password) VALUES (%s,%s)"
# Insert a piece of data:
Result=cur.execute (Sql,[name,password]) # Execution SQL statement returns the number of successful SQL query records
# Insert more than one data at a time:
Result=cur.executemany (sql,[("Tai", "888"), ("Wus", "999")])

# Modify Data
sql= "Update userinfo set name=%s where id=3"
Result=cur.execute (Sql,name)

# Delete Data
Sql= "Delete from userinfo where id=7"
Result=cur.execute (SQL)

# Be sure to submit the pit
Conn.commit ()
# Close Cursors and connections
Cur.close ()
Conn.close ()
Four. Check Fetchone Fetchmany fetchall
Fetchone () Gets the next row of data, first first
Import Pymysql
# 1. Connect
Conn=pymysql.connect (
host= "localhost",
port=3306,
User= "Root",
Password= ',
db= "DB5",
charset= "UTF8"
)
# 2. Creating cursors
Cur=conn.cursor ()
Sql= "SELECT * from UserInfo"
Cur.execute (SQL)
# Query the first row of data
Row=cur.fetchone () #获取下一行
Print (Row) # (1, ' Alex ', ' 123 ')
# Query the second row of data
Row=cur.fetchone ()
Print (Row) # (3, ' Lili ', ' 666 ')
# 3. Close the cursor
Cur.close ()
# 4. Close the connection
Conn.close ()
Fetchall () Get all row data sources
Import Pymysql
# 1. Connect
Conn=pymysql.connect (
host= "localhost",
port=3306,
User= "Root",
Password= ',
db= "DB5",
charset= "UTF8"
)
# 2. Creating cursors
Cur=conn.cursor ()
Sql= "SELECT * from UserInfo"
Cur.execute (SQL)
# query all the data
Row=cur.fetchall ()
Print (ROW)

# 3. Close the cursor
Cur.close ()
# 4. Close the connection
Conn.close ()
Fetchmany (4) Get 4 rows of data
Import Pymysql
# 1. Connect
Conn=pymysql.connect (
host= "localhost",
port=3306,
User= "Root",
Password= ',
db= "DB5",
charset= "UTF8"
)
# 2. Creating cursors
Cur=conn.cursor ()
Sql= "SELECT * from UserInfo"
Cur.execute (SQL)
# Query three of data
Row=cur.fetchmany (3)
Print (ROW)
# 3. Close the cursor
Cur.close ()
# 4. Close the connection
Conn.close ()
Add:
Problem
1. By default, the return value we get is a tuple, we can see only the data for each row, but we don't know what each column represents.
Solution:
Use the following method to return the dictionary, and the data for each row will generate a dictionary
When instantiating, set the property cursor to Pymysql.cursors.DictCursor
Example:
Cur=conn.cursor (Cursor=pymysql.cursors.dictcursor)
Case:
Import Pymysql
# 1. Connect
Conn=pymysql.connect (
host= "localhost",
port=3306,
User= "Root",
Password= ',
db= "DB5",
charset= "UTF8"
)
# 2. Creating cursors
Cur=conn.cursor (Cursor=pymysql.cursors.dictcursor)
Sql= "SELECT * from UserInfo"
Cur.execute (SQL)
# query all the data
Row=cur.fetchall ()
Print (ROW)
# [{' id ': 1, ' name ': ' Alex ', ' password ': ' 123 '}, {' id ': 3, ' name ': ' Lili ', ' Password ': ' 666 '}, {' id ': 4, ' name ': ' Tai ', ' p Assword ': ' 888 '}, {' id ': 5, ' name ': ' Wus ', ' Password ': ' 999 '}, {' id ': 6, ' name ': ' Lili--hhk ', ' Password ': ' 666 '}]

# 3. Close the cursor
Cur.close ()
# 4. Close the connection
Conn.close ()
Question 2:
In the Fetchone example, when you get the row data, you can understand that at the beginning, there is a row pointer pointing at the top of the first row, getting a row, it moves down one line,
# So when the line pointer is to the last line, it is no longer possible to get the contents of the row, so we can use the following method to move the row pointer:
Solution:
Cursor.scroll (1,mode= ' relative ') # moves relative to the current position
Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement
The first value is the number of rows moved, the integers are moved downward, the negative numbers move up, mode specifies whether to move relative to the current position, or to move relative to the first row
# 1. Connect
Conn=pymysql.connect (
host= "localhost",
port=3306,
User= "Root",
Password= ',
db= "DB5",
charset= "UTF8"
)
# 2. Creating cursors
Cur=conn.cursor (Cursor=pymysql.cursors.dictcursor)
Sql= "SELECT * from UserInfo"
Cur.execute (SQL)
# query all the data
Row=cur.fetchone ()
Print (ROW)
#相对位置
Cur.scroll ( -1,mode= ' relative ') #设置之后, the cursor moves forward one line in relation to the current position
Row=cur.fetchone ()
Print (ROW)
#绝对位置
Cur.scroll (3,mode= ' absolute ') #设置之后, the cursor moves down the corresponding amount relative to the absolute position.
Row=cur.fetchone ()
Print (ROW)

# 3. Close the cursor
Cur.close ()
# 4. Close the connection
Conn.close ()

Usage of Python pymsql 180903