Python operation MySQL pymysql module detailed _python

Objective

Pymsql is the module that operates MySQL in Python, and its use is almost the same as MySQLdb. But currently pymysql support python3.x the latter does not support the 3.x version.

This article tests the Python version: 2.7.11. MySQL version: 5.6.24

First, installation

PIP3 Install Pymysql

Second, the use of operation

1. Execute SQL

#!/usr/bin/env Pytho
#-*-coding:utf-8-*-
import pymysql
 
# Create connection
conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ', charset= ' UTF8 ')
# create cursor
cursor = conn.cursor ()
 
# Executes SQL and returns the number of affected rows
Effect_row = Cursor.execute ("SELECT * from Tb7")
 
# executes SQL and returns the number of affected rows
#effect_row = Cursor.execute ("update tb7 set pass = ' 123 ' Where Nid =%s ', (one,))
 
# Executes SQL and returns the number of affected rows, executes multiple
#effect_row = CURSOR.E Xecutemany ("INSERT into TB7 (User,pass,licnese) VALUES (%s,%s,%s)", [("U1", "U1pass", "11111"), ("U2", "U2pass", "22222") ]
 
 
# Commit, or you cannot save the new or modified data
conn.commit ()
 
# Close cursor
cursor.close ()
# Close connection
conn.close ()

Note: When there is Chinese, the connection needs to add charset= ' UTF8 ', otherwise the Chinese display garbled.

2. Get Query data

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"
import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
cursor = conn.cursor () cursor.execute
(" SELECT * from Tb7 ")

# Get the first row of data for the remaining results
row_1 = Cursor.fetchone ()
print row_1
# Get the remaining results before n rows of data
# row_2 = CU Rsor.fetchmany (3)

# Get the rest of the results all data
# row_3 = Cursor.fetchall ()

conn.commit ()
cursor.close
() Conn.close ()

3, get the newly created data self ID

You can get the latest self-added ID, which is the last data ID inserted

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"
import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
cursor = conn.cursor () effect_row
= Cursor.executemany ("INSERT into TB7 (User,pass,licnese) VALUES (%s,%s,%s)", [("U3", "U3pass", "11113"), ("U4", "U4pass", "22224")] Conn.commit () cursor.close () conn.close ()
#获取自增id
new_id = Cursor.lastrowid      
Print new_id

4. Move the cursor

Operations are cursors, and the control of cursors is also necessary

Note: In order to fetch data, you can use Cursor.scroll (Num,mode) to move the cursor position, such as:

cursor.scroll (1,mode= ' relative ') # moves
relative to the current position Cursor.scroll (2,mode= ' absolute ') # Moving relative to absolute position

5. Fetch data type

The data about the default gets is the Ganso type, if you want or the dictionary type of data, that is:

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"
import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
#游标设置为字典类型
cursor = conn.cursor (cursor= Pymysql.cursors.DictCursor)
cursor.execute ("SELECT * from Tb7")

row_1 = Cursor.fetchone ()
print Row_1 #{ U ' Licnese ': 213, U ' user ': ' 123 ', U ' nid ': ten, U ' Pass ': ' 213 '} conn.commit () Cursor.close () conn.close ()

6. Calling stored procedures

A, calling the parameter-free stored procedure

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"

import pymysql

conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
#游标设置为字典类型
cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor)
#无参数存储过程
cursor.callproc (' P2 ')  # Equivalent to Cursor.execute ("Call P2 ()")

Row_1 = Cursor.fetchone ()
print row_1


conn.commit (
) Cursor.close ()
conn.close ()

b, invoking a stored procedure

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"

import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
cursor = conn.cursor (cursor= Pymysql.cursors.DictCursor)

cursor.callproc (' P1 ', args= (1, 3, 4))
#获取执行完存储的参数, parameter @ opening
Cursor.execute ("Select @p1, @_p1_1,@_p1_2,@_p1_3")  #{u ' @_p1_1 ': U ' @p1 ': None, U ' @_p1_2 ':, U ' @_p1_3 ': row_1
= Cursor.fetchone ()
print Row_1


Conn.commit ()
cursor.close ()
conn.close ()

Iii. about Pymysql anti-injection

1, string stitching query, resulting in injection

Normal query statement:

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"
import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
cursor = conn.cursor () user=
"U1" passwd=
"U1pass"
#正常构造语句的情况
sql= Select User,pass from Tb7 where user= '%s ' and pass= '%s '% (user,passwd)
#sql = Select User,pass from Tb7 where user= ' U1 ' and pass= ' U1pass ' Row_count=cursor.execute
(sql) Row_1 = Cursor.fetchone ()
Print row_count,row_1

conn.commit ()
cursor.close ()
conn.close ()

Construct the injection statement:

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"
import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
cursor = conn.cursor () user= ' U1

' or ' 1 '--"
passwd= "U1pass"
sql= select User,pass from Tb7 where user= '%s ' and pass= '%s '% (USER,PASSWD)

# The concatenation statement is constructed as follows, the eternal condition, at which time the injection succeeds. Therefore, to avoid this situation, you need to use the parameterized query provided by Pymysql.
#select User,pass from Tb7 where user= ' U1 ' or ' 1 '--' and pass= ' U1pass '

(SQL)
Row_1 = Cursor.fetchone ()
print row_count,row_1


conn.commit ()
cursor.close () conn.close ()

2, to avoid injection, using pymysql provided by the parameterized statements

Normal parameterized Query

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"

import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
cursor = conn.cursor () user=
"U1" passwd=
"U1pass"
#执行参数化查询
row_count=cursor.execute ("Select User,pass from Tb7 where user=%s and pass=%s", user,passwd )
row_1 = Cursor.fetchone ()
print row_count,row_1

conn.commit ()
cursor.close (
) Conn.close ()

Constructed injection, parameterized query injection failed.

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"
import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
cursor = conn.cursor () user= ' U1

' or ' 1 '--"
passwd= "U1pass"
#执行参数化查询
row_count=cursor.execute ("Select User,pass from Tb7 where user=%s and pass=%s", (USER,PASSWD))
#内部执行参数化生成的SQL语句, the special characters are added \ Escaped to avoid the injection statement generation.
# sql=cursor.mogrify ("Select User,pass from Tb7 where user=%s and pass=%s", (USER,PASSWD))
# Print SQL
# Select User,pass from Tb7 where user= ' u1\ ' or \ ' 1\ '--' and pass= ' U1pass ' the escaped statement.

row_1 = Cursor.fetchone ()
print row_count,row_1

conn.commit ()
cursor.close (
) conn.close ()

Conclusion: When executing the SQL statement, the Excute must use the parameterized method, otherwise the SQL injection vulnerability will inevitably occur.

3. Dynamic execution of SQL anti-injection using stored MySQL storage process

Use MySQL stored procedures to automatically provide anti injection, dynamically incoming SQL to stored procedure execution statements.

Delimiter \
DROP PROCEDURE IF EXISTS proc_sql \
CREATE PROCEDURE proc_sql (
  in Nid1 INT, in
  nid2 int,
   in callsql VARCHAR (255)
  )
BEGIN
  Set @nid1 = NID1;
  Set @nid2 = Nid2;
  Set @callsql = Callsql;
    PREPARE Myprod from @callsql;
--   PREPARE prod from ' select * to TB2 where nid>? and nid<? ';  The value passed in is a string,? As placeholders-
-   @p1, and @p2 populate the placeholder
    EXECUTE myprod using @nid1, @nid2;
  deallocate prepare Myprod;

End\\
delimiter;

Set @nid1 =12;
Set @nid2 =15;
Set @callsql = ' SELECT * from Tb7 where nid>? and nid<? ';
Call Proc_sql (@nid1, @nid2, @callsql)

Called in Pymsql

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"
import pymysql

conn = Pymysql.connect ( Host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' tkq1 ')
cursor = conn.cursor () mysql=
"SELECT * from Tb7 where Nid>? and nid<? "
Cursor.callproc (' Proc_sql ', args= (one, MySQL))

rows = Cursor.fetchall ()
Print Rows # ((), ' U1 ', ' U1pass ', 11111), (' U2 ', ' U2pass ', 22222), (+, ' U3 ', ' U3pass ', 11113)) Conn.commit ()
cursor.close
() Conn.close ()

Use with to simplify the connection process

It's cumbersome to connect off each time, using context management to simplify the connection process

#! /usr/bin/env python
#-*-coding:utf-8-*-
# __author__ = "TKQ"

import pymysql
import contextlib
# Defines a context manager that automatically closes the connection @contextlib after the connection
. ContextManager
def mysql (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ') ', db= ' tkq1 ', charset= ' UTF8 '): conn = Pymysql.connect, Host=host, Port=port, User=user, passwd=passwd
  Charset=charset)
  cursor = conn.cursor (cursor=pymysql.cursors.dictcursor)
  try:
    yield cursor
  Finally:
    conn.commit ()
    cursor.close ()
    conn.close ()

# executes SQL with
MySQL () as cursor:
  Print (cursor)
  Row_count = Cursor.execute ("SELECT * from Tb7")
  row_1 = Cursor.fetchone ()
  print Row_count , Row_1

Summarize

The above is about the Pymysql module in Python All the content, I hope to learn or use Python can have some help, if you have questions you can message exchange.