Python中操作mysql的pymysql模組詳解_python

前言

pymsql是Python中操作MySQL的模組，其使用方法和MySQLdb幾乎相同。但目前pymysql支援python3.x而後者不支援3.x版本。

本文測試python版本：2.7.11。mysql版本：5.6.24

一、安裝

pip3 install pymysql

二、使用操作

1、執行SQL

#!/usr/bin/env pytho# -*- coding:utf-8 -*-import pymysql # 建立串連conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1', charset='utf8')# 建立遊標cursor = conn.cursor() # 執行SQL，並返回收影響行數effect_row = cursor.execute("select * from tb7") # 執行SQL，並返回受影響行數#effect_row = cursor.execute("update tb7 set pass = '123' where nid = %s", (11,)) # 執行SQL，並返回受影響行數,執行多次#effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])  # 提交，不然無法儲存建立或者修改的資料conn.commit() # 關閉遊標cursor.close()# 關閉串連conn.close()

注意：存在中文的時候，串連需要添加charset='utf8'，否則中文顯示亂碼。

2、擷取查詢資料

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')cursor = conn.cursor()cursor.execute("select * from tb7")# 擷取剩餘結果的第一行資料row_1 = cursor.fetchone()print row_1# 擷取剩餘結果前n行資料# row_2 = cursor.fetchmany(3)# 擷取剩餘結果所有資料# row_3 = cursor.fetchall()conn.commit()cursor.close()conn.close()

3、擷取新建立資料自增ID

可以擷取到最新自增的ID，也就是最後插入的一條資料ID

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = 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()#擷取自增idnew_id = cursor.lastrowid      print new_id

4、移動遊標

操作都是靠遊標，那對遊標的控制也是必須的

註：在fetch資料時按照順序進行，可以使用cursor.scroll(num,mode)來移動遊標位置，如：cursor.scroll(1,mode='relative') # 相對當前位置移動cursor.scroll(2,mode='absolute') # 相對絕對位置移動

 

5、fetch資料類型

關於預設擷取的資料是元祖類型，如果想要或者字典類型的資料，即：

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = 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': 10, u'pass': '213'}conn.commit()cursor.close()conn.close()

6、調用預存程序

a、調用無參預存程序

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')#遊標設定為字典類型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)#無參數預存程序cursor.callproc('p2')  #等價於cursor.execute("call p2()")row_1 = cursor.fetchone()print row_1conn.commit()cursor.close()conn.close()

b、調用有參預存程序

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = 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, 22, 3, 4))#擷取執行完儲存的參數,參數@開頭cursor.execute("select @p1,@_p1_1,@_p1_2,@_p1_3")  #{u'@_p1_1': 22, u'@p1': None, u'@_p1_2': 103, u'@_p1_3': 24}row_1 = cursor.fetchone()print row_1conn.commit()cursor.close()conn.close()

三、關於pymysql防注入

 1、字串拼接查詢，造成注入

正常查詢語句：

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = 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_1conn.commit()cursor.close()conn.close()

構造注入語句：

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = 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)#拼接語句被構造成下面這樣，永真條件，此時就注入成功了。因此要避免這種情況需使用pymysql提供的參數化查詢。#select user,pass from tb7 where user='u1' or '1'-- ' and pass='u1pass'row_count=cursor.execute(sql)row_1 = cursor.fetchone()print row_count,row_1conn.commit()cursor.close()conn.close()

 

 2、避免注入，使用pymysql提供的參數化語句

正常參數化查詢

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = 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_1conn.commit()cursor.close()conn.close()

構造注入，參數化查詢注入失敗。

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = 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語句，對特殊字元進行了加\轉義，避免注入語句產生。# 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'被轉義的語句。row_1 = cursor.fetchone()print row_count,row_1conn.commit()cursor.close()conn.close()

結論：excute執行SQL語句的時候，必須使用參數化的方式，否則必然產生SQL注入漏洞。

3、使用存mysql儲過程動態執行SQL防注入

使用MYSQL預存程序自動提供防注入，動態傳入SQL到預存程序執行語句。

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 * from tb2 where nid>? and nid<?';  傳入的值為字串，？為預留位置--   用@p1，和@p2填充預留位置    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)

pymsql中調用

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlconn = 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=(11, 15, mysql))rows = cursor.fetchall()print rows #((12, 'u1', 'u1pass', 11111), (13, 'u2', 'u2pass', 22222), (14, 'u3', 'u3pass', 11113))conn.commit()cursor.close()conn.close()

四、使用with簡化串連過程

每次都串連關閉很麻煩，使用上下文管理，簡化串連過程

#! /usr/bin/env python# -*- coding:utf-8 -*-# __author__ = "TKQ"import pymysqlimport contextlib#定義上下文管理器，串連後自動關閉串連@contextlib.contextmanagerdef 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, db=db, charset=charset)  cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  try:    yield cursor  finally:    conn.commit()    cursor.close()    conn.close()# 執行sqlwith mysql() as cursor:  print(cursor)  row_count = cursor.execute("select * from tb7")  row_1 = cursor.fetchone()  print row_count, row_1

總結

以上就是關於Python中pymysql模組的全部內容，希望對大家學習或使用python能有一定的協助，如果有疑問大家可以留言交流。