MySQL與Python的交互

01-30

1、交互類型

安裝引入模塊

安裝mysql模塊，在windows和ubuntu中

windows里安裝mysql模塊

Linux里安裝mysql模塊

在文件中引入模塊

import pymysql

connection對象

用於建立與資料庫的連接

創建對象：調用connect()方法

conn=connect(參數列表)

參數host：連接的mysql主機，如果本機是localhost

參數port：連接的mysql主機的埠，默認是3306

參數db：資料庫的名稱

參數user：連接的用戶名

參數password：連接的密碼

參數charset：通信採用的編碼方式，默認是gb2312，要求與資料庫創建時指定的編碼一致，否則中文會亂碼

對象的方法

close()關閉連接

commit()事務，所以需要提交才會生效

rollback()事務，放棄之前的操作

cursor()返回Cursor對象，用於執行sql語句並獲得結果

Cursor對象

執行sql語句

創建對象：調用Connection對象的cursor()方法

cursor1=conn.cursor()

對象的方法

close()關閉

execute(operation [, parameters ])執行語句，返回受影響的行數

fetchone()執行查詢語句時，獲取查詢結果集的第一個行數據，返回一個元組

next()執行查詢語句時，獲取當前行的下一行

fetchall()執行查詢時，獲取結果集的所有行，一行構成一個元組，再將這些元組裝入一個元組返回

scroll(value[,mode])將行指針移動到某個位置

mode表示移動的方式

mode的默認值為relative，表示基於當前行移動到value，value為正則向下移動，value為負則向上移動。相對的

mode的值為absolute，表示基於第一條數據的位置，第一條數據的位置為0，絕對的。

舉個例子便於理解

# 導入python操作mysql的模塊 nimport pymysql n# 獲取連接對象 nconn = pymysql.connect(host=127.0.0.1, user=root, password=123456, database=python01, port=3306, charset=utf8) n# 獲取執行工具 ncur = conn.cursor() n# sql語句,增刪改 n#sql = select birthday from t_user nsql = select id,name,pwd,birthday from t_user n# 執行,返回值。如果是增刪改，返回受影響的行數，如果是查詢，返回查詢的行數 ncount = cur.execute(sql) nprint(查詢的結果有%s條數據%count) n#獲取第一行 ndateOne = cur.fetchone() nprint(dateOne) n#向上移動一行 ncur.scroll(-1) n#向下移動一行 ncur.scroll(1) ncur.scroll(1,mode=absolute) 絕對的，這裡指的是第一行 ncur.scroll(1,mode=relative) 相對的n #獲取所有行的數據 ndataAll = cur.fetchall() nprint(dataAll)n for temp in dataAll: nprint(temp) n print(dataAll[-1][2]) #dataAll[-1]得到的是一個用戶所有的信息，dataAll[-1][2]獲取最後一個人的密碼 nfor temp in cur: nprint(temp) ns = id:%s,name:%s,pwd:%s,birthday:%s nfor temp in dataAll: n print(s%(temp[0],temp[1],temp[2],temp[3])) n# 關閉 ncur.close() nconn.close()n

對象的屬性

rowcount只讀屬性，表示最近一次execute()執行後受影響的行數

connection獲得當前連接對象

2、增刪改查（CRUD）

1、增

創建testInsert.py文件，向學生表中插入一條數據

#encoding=utf-8nimport pymysqlntry:n conn=pymysql.connect(host=localhost,port=3306,db=test1,user=root,passwd=mysql,charset=utf8)n cs1=conn.cursor()n count=cs1.execute("insert into students(sname) values(張良)")n print(count)n conn.commit()n cs1.close()n conn.close()nexcept Exception,e:n print(e)n

2、修改

創建testUpdate.py文件，修改學生表的一條數據

#encoding=utf-8nimport pymysqlntry:n conn=pymysql.connect(host=localhost,port=3306,db=test1,user=root,passwd=mysql,charset=utf8)n cs1=conn.cursor()n count=cs1.execute("update students set sname=劉邦 where id=6")n print(count)n conn.commit()n cs1.close()n conn.close()nexcept Exception,e:n print(e)n

3、刪除

創建testDelete.py文件，刪除學生表的一條數據

#encoding=utf-8nimport pymysqlntry:n conn=pymysql.connect(host=localhost,port=3306,db=test1,user=root,passwd=mysql,charset=utf8)n cs1=conn.cursor()n count=cs1.execute("delete from students where id=6")n print(count)n conn.commit()n cs1.close()n conn.close()nexcept Exception as e:n print(e)n

4、查

創建testSelectOne.py文件，查詢一條學生信息

import Pymysqlntry:n conn=Pymysql.connect(host=localhost,port=3306,db=test1,user=root,passwd=mysql,charset=utf8)n cur=conn.cursor()n cur.execute(select * from students where id=7)n result=cur.fetchone()n print resultn cur.close()n conn.close()nexcept Exception as e:n print(e)n

創建testSelectMany.py文件，查詢全部學生信息

#encoding=utf8nimport Pymysqlntry:n conn=Pymysql.connect(host=localhost,port=3306,db=test1,user=root,passwd=mysql,charset=utf8)n cur=conn.cursor()n cur.execute(select * from students)n result=cur.fetchall()n print resultn cur.close()n conn.close()nexcept Exception as e:n print(e)n

實例一：參數

# 導入python操作mysql的模塊 nimport pymysql nimport time n# 獲取連接對象 nconn = pymysql.connect(host=127.0.0.1, user=root, password=123456, database=python01, port=3306, charset=utf8) n# 獲取執行工具 ncur = conn.cursor()n # sql語句,增刪改，sql注入 nsql = insert into t_user(name,pwd,birthday) values(%s,%s,%s) n# 參數列表n name = input(輸入姓名:) npwd = input(輸入密碼:) nbirthday = input(輸入生日:) # 2017年10月01日-->日期struct_time(--->2017-10-01) nbirthday = time.strptime(birthday,%Y年%m月%d日) n#這裡我們就用到了時間與字元串的相互轉換(詳情見MySQL高級) nparams = [name,pwd,birthday] n# 執行,返回值。如果是增刪改，返回受影響的行數，如果是查詢，返回查詢的行數 ncount = cur.execute(sql,params) n#提交 nconn.commit() nprint(受影響的行數:%s%count) n# 關閉 ncur.close() nconn.close()n

實例二：拋出異常

# 導入python操作mysql的模塊nimport pymysql ntry: n conn = None ncur = None n# 獲取連接對象 nconn = pymysql.connect(host=127.0.0.1, nuser=root, npassword=123456, n database=python01, nport=3306, ncharset=utf8) n# 模擬異常 n# a = 1 / 0 n # 獲取執行工具 ncur = conn.cursor() n# sql語句,增刪改 nsql = insert into t_user(name,pwd,birthday) values("小伊","123456",str_to_date("2017年10月20日","%Y年%m月%d日")) n# 執行,返回值。如果是增刪改，返回受影響的行數，如果是查詢，返回查詢的行數 ncount = cur.execute(sql) n# 提交 nconn.commit() nprint(受影響的行數:%s % count) nexcept Exception as ex: n# 列印異常信息，測試時候使用，項目上線，去掉 n print(str(ex)) n# 將異常繼續拋出 n# raisen finally: n if cur != None: ncur.close() n if conn != None: nconn.close()n

實例三：

3、封裝

這個庫的名字：mySqlHelper

python操作mysql進行增刪改查的封裝

1、增刪改，代碼類似

2、查詢

代碼分析

1、獲取連接對象

2、sql語句不同，參數不同

3、獲取執行對象

增刪改

查詢

1、fetchone

2、fetchall

4、處理結果

5、關閉

面向對象建立類，封裝屬性和函數

import pymysql

class MysqlHelper:

python操作mysql的增刪改查的封裝

def __init__(self, host, user, password, database, port=3306, charset=utf8):

初始化參數

:param host: 主機

:param user: 用戶名

:param password: 密碼

:param database: 資料庫

:param port: 埠號，默認是3306

:param charset: 編碼，默認是utf8

self.host = host

self.port = port

self.database = database

self.user = user

self.password = password

self.charset = charset

def connect(self):

獲取連接對象和執行對象

:return:

self.conn = pymysql.connect(host=self.host,

user=self.user,

password=self.password,

database=self.database,

port=self.port,

charset=self.charset)

self.cur = self.conn.cursor()

def fetchone(self, sql, params=None):

根據sql和參數獲取一行數據

:param sql: sql語句

:param params: sql語句對象的參數元組，默認值為None

:return: 查詢的一行數據

dataOne = None

try:

count = self.cur.execute(sql, params)

if count != 0:

dataOne = self.cur.fetchone()

except Exception as ex:

print(ex)

finally:

self.close()

return dataOne

def fetchall(self, sql, params=None):

根據sql和參數獲取一行數據

:param sql: sql語句

:param params: sql語句對象的參數列表，默認值為None

:return: 查詢的一行數據

dataall = None

try:

count = self.cur.execute(sql, params)

if count != 0:

dataall = self.cur.fetchall()

except Exception as ex:

print(ex)

finally:

self.close()

return dataall

def __item(self, sql, params=None):

執行增刪改 :param sql:

sql語句 :param params:

sql語句對象的參數列表，默認值為None

:return: 受影響的行數

count = 0

try:

count = self.cur.execute(sql, params)

self.conn.commit()

except Exception as ex:

print(ex)

finally:

self.close()

return count

def update(self, sql, params=None):

執行修改

:param sql: sql語句

:param params: sql語句對象的參數列表，默認值為None

:return: 受影響的行數

return self.__item(sql, params)

def insert(self, sql, params=None):

執行新增

:param sql: sql語句

:param params: sql語句對象的參數列表，默認值為None

:return: 受影響的行數

return self.__item(sql, params)

def delete(self, sql, params=None):

執行刪除

:param sql: sql語句

:param params: sql語句對象的參數列表，默認值為None

:return: 受影響的行數

return self.__item(sql, params)

def close(self):

關閉執行工具和連接對象

if self.cur != None:

self.cur.close()

if self.conn != None:

self.conn.close()

1、測試查詢多條數據

import mysqlHelper

# 初始化對象

helper = mysqlHelper.MysqlHelper(127.0.0.1, root, 123456, python01)

# 連接

helper.connect()

# sql

sql = select * from t_user where name = %s and id > %s # params params = [小茗,1]

# 執行

data = helper.fetchall(sql, params)

# 判斷

if data:

for temp in data:

print(temp)

else: # None,False,0

print(沒有數據.)

helper.close()

2、測試查詢一條數據

import mysqlHelper

# 初始化對象

helper = mysqlHelper.MysqlHelper(127.0.0.1, root, 123456, python01)

# 連接

helper.connect()

# sql

sql = select * from t_user where id = %s

#sql = select * from t_user where id = 1

# params params = [2]

# 執行

data = helper.fetchone(sql, params)

#data = helper.fetchone(sql)

# 判斷

if data:

print(data)

else: # None,False,0

print(沒有數據.)

3、測試增刪改

import mysqlHelper

import time

# 初始化對象

helper = mysqlHelper.MysqlHelper(127.0.0.1, root, 123456, python01)

# 連接

helper.connect()

# sql

sql = update t_user set name =%s,pwd=%s,birthday=%s where id=%s

# params

id = input(輸入編號:)

name = input(輸入姓名:)

pwd = input(輸入密碼:)

birthday = time.strptime(input(輸入生日:), %Y年%m月%d日)

params = [name, pwd, birthday,id]

# 執行

count = helper.update(sql, params)

# 判斷

if count:

print(操作成功.)

else: # None,False,0

print(操作失敗.)

4、登錄和註冊

登錄和註冊的時候需要對密碼進行加密

注意：

1、需要對密碼進行加密

2、如果使用md5加密，則密碼包含32個字元

3、如果使用sha1加密，則密碼包含40個字元，這裡使用這種方式

create table userinfos(

id int primary key auto_increment,

uname varchar(20),

upwd char(40),

isdelete bit default 0 );

ret = doPwd(123)

print(ret)

結果：40bd001563085fc35165329ea1ff5c5ecbdbbeef

*/ -- 插入如下數據，用戶名為123,密碼為123,這是sha1加密後的值

insert into userinfos

values(1,123,40bd001563085fc35165329ea1ff5c5ecbdbbeef,0);

登錄與註冊

from mysqlHelper import MysqlHelper

import hashlib

def login():

登錄

name = input(輸入用戶名:)

pwd = input(輸入密碼:)

#加密

pwd = doPwd(pwd)

helper = MysqlHelper(127.0.0.1, root, 123456, python01)

helper.connect()

sql = select * from t_user where name=%s and pwd=%s

params = [name, pwd]

data = helper.fetchone(sql, params)

if data:

print(登錄成功.)

else: # None,False,0

print(登錄失敗.)

def doPwd(pwd):

sha1編碼

mysha1 = hashlib.sha1()

mysha1.update(pwd.encode(utf-8))

pwd = mysha1.hexdigest()

return pwd def register():

註冊

name = input(輸入用戶名:)

pwd = input(輸入密碼:)

# 加密

pwd = doPwd(pwd)

helper = MysqlHelper(127.0.0.1, root, 123456, python01)

helper.connect()

sql = insert into t_user(name,pwd) values(%s,%s)

params = [name, pwd]

count = helper.insert(sql, params)

if count:

print(操作成功.)

else: # None,False,0

print(操作失敗.)

if __name__ == __main__:

#register()