8.4Python與MySQL數(shù)據(jù)庫編程本章主要內(nèi)容：（1）MySQL的技術架構(gòu)：四層架構(gòu)、存儲引擎、系統(tǒng)數(shù)據(jù)庫、

日志、性能優(yōu)化（2）掌握MySQL存儲過程的編程，包括游標對象Cursor的使用（3）掌握MySQL觸發(fā)器的編程（4）掌握Python對MySQL數(shù)據(jù)庫的插入、修改、刪除、查詢操作8.4Python與MySQL數(shù)據(jù)庫編程

Python提供了對各種主流數(shù)據(jù)庫的訪問。本章以MySQL8.0、Python3.x為例講解。

學習本節(jié)內(nèi)容前，需要先按照本章提供的Python安裝說明，把Python3.x及Anaconda安裝好，其中Anaconda是Python生態(tài)下集成的數(shù)據(jù)分析開發(fā)環(huán)境。pymysql是在

Python3.x版本中用于連接

MySQL服務器的一個第三方庫，需要另外安裝。在Anaconda環(huán)境下安裝：點擊電腦左下角，進入AnacondaPrompt（Anaconda3），輸入下面語句，直接進行安裝，如圖8-20所示：

pipinstallpymysql如果直接安裝比較慢，也可以利用豆瓣鏡像安裝，輸入語句如下：

pipinstall–i/simplepymysql8.4Python與MySQL數(shù)據(jù)庫編程圖8-20Anaconda環(huán)境下pymysql庫的安裝MySQL安裝成功后，默認主機名：localhost，默認數(shù)據(jù)庫IP地址：，默認端口號：3306，默認的用戶：root。Python與MySQL數(shù)據(jù)庫的編程，主要分3步：（1）建立數(shù)據(jù)庫連接對象connection；（2）通過游標對象cursor操縱數(shù)據(jù)庫；（3）關閉游標、關閉連接。其流程，如圖8-21所示。8.4Python與MySQL數(shù)據(jù)庫編程圖8-21Python與MySQL數(shù)據(jù)庫的連接處理流程8.4.1Python建立與MySQL連接的connection對象表8-8數(shù)據(jù)庫連接對象connection的方法序號參數(shù)名類型說明1hostStringMySQL的服務器IP地址，或服務器電腦名稱2portIntMySQL的端口號，默認：33063userString用戶名4passwordString密碼5databaseString使用的數(shù)據(jù)庫6charsetString連接字符集，默認：utf8表8-7數(shù)據(jù)庫連接對象connection的參數(shù)說明序號方法名說明序號方法名說明1cursor（）創(chuàng)建并且返回游標4begin（）開始事務2close()關閉Connection5commit（）提交當前事物3rollback()回滾當前事物8.4.2Python操縱MySQL數(shù)據(jù)庫的cursor對象通過connection建立Python與數(shù)據(jù)庫的連接后，接下來通過游標對象cursor操縱數(shù)據(jù)庫，包括：查詢、插入、修改、刪除記錄，cursor對象的方法，如表8-9所示。序號方法名說明1execute(SQL)用于執(zhí)行一個數(shù)據(jù)庫的SQL命令2fetchone()獲取結(jié)果集中的下一行3fetchmany(size)獲取結(jié)果集中的下（size）行，其中size為正整數(shù)4fetchall()獲取結(jié)果集中剩下的所有行，返回一個元組5rowcount最近一次execute返回數(shù)據(jù)/影響的行數(shù)6close()關閉游標8.4.3Python與MySQL數(shù)據(jù)庫編程例8-13Python查詢MySQL數(shù)據(jù)表記錄?；舅悸罚河螛耍╟ursor）執(zhí)行查詢語句后，返回的是元組變量，可以通過元組的索引，訪問查詢的結(jié)果。例8-13Python查詢MySQL數(shù)據(jù)表記錄。1234567891011121314151617importpymysqlIP="localhost"#數(shù)據(jù)庫所在計算機名或IP地址userNo="root"#數(shù)據(jù)庫連接用戶vcPass="Sa12345678"#數(shù)據(jù)庫連接用戶密碼db="student"#數(shù)據(jù)庫名稱charset="utf8"#這是默認的，可以不用管它conn=pymysql.connect(host=IP,user=userNo,password=vcPass,database=db,port=3306)cur=conn.cursor()#定義游標strSQL="selectsNo,sName,casewhensex='M'then'男'else'女'endassexfromswheresNolike'05%';"cur.execute(strSQL)#執(zhí)行SQL語句

rows=cur.fetchall()#獲取游標所有行記錄，返回元組變量print(type(rows))forrinrows:#遍歷元組print("學號：%s,姓名：%s,%s"%(r[0],r[1],r[2]))

cur.close()#結(jié)束前，要關閉對象,先關閉游標，再關閉連接conn.close()8.4.3Python與MySQL數(shù)據(jù)庫編程例8-14Python更新MySQL數(shù)據(jù)表記錄。

基本思路：游標（cursor）執(zhí)行更新SQL語句后，返回一個整數(shù)變量，表示影響記錄的條數(shù)。但這還只是在數(shù)據(jù)緩沖區(qū)中，若要影響到數(shù)據(jù)庫中，則要調(diào)用連接對象connection的commit()方法，進行提交；如果失敗，則回滾rollback()。例8-14Python更新MySQL數(shù)據(jù)表記錄。12345678910111213importpymysql#導入pymysql模塊conn=pymysql.connect(host="",user="root",password="Sa12345678",database="student")cursor=conn.cursor()#生成一個游標對象sql="insertintoC(cNo,cName)values('C08','Python')"#在課程表中，插入一條記錄try:conn.begin()#開始事務cursor.execute(sql)#執(zhí)行SQL語句mit()#提交事務print("數(shù)據(jù)插入成功！影響行數(shù)：",cursor.rowcount)exceptpymysql.Errorase:conn.rollback()#若出現(xiàn)意外，則事務回滾，即撤銷事務print("插入錯誤！錯誤信息：",e.args[1])cursor.close()#關閉光標對象conn.close()#關閉數(shù)據(jù)庫連接8.4.4編程案例：股票交易數(shù)據(jù)處理、分析例8-15文件“股票日交易數(shù)據(jù)20120425.xls”收錄了滬深A股股票2012.04.25日的日交易數(shù)據(jù)，共3173條記錄，如表8-10所示（數(shù)據(jù)見本章附件）。各列含義：1）Symbol股票代碼，2）Name股票名稱，3）

Date交易日期，4）Open開盤價，

5）High最高價，6）Low最低價，7）Close收盤價，8）Change1漲幅，9）Volume成交量(手)，

10）Amount成交額（元），11）TurnoverRate換手率；

例8-15將“股票日交易數(shù)據(jù)20120425.xls”數(shù)據(jù)導入到MySQL數(shù)據(jù)庫中進入MySQL，先創(chuàng)建一個空數(shù)據(jù)庫MyTest，再創(chuàng)建2張基本表：

smStock（cStockNo，vcStockName，dcLTP）--股票基本信息表1234567891011121314CREATETABLEtrDay /*--------------股票日交易數(shù)據(jù)記錄-------------------------------*/(cStockNo char(8) notnull, /*股票代碼*/cDay datetime notnull, /*交易日期*/mOpen real notnulldefault0, /*開盤價*/mHigh real notnulldefault0, /*最高價*/mLow real notnulldefault0, /*最低價*/mClose real notnulldefault0, /*收盤價*/iVol real notnulldefault0, /*成交量*/mm real notnulldefault0, /*成交額*/dcChangedecimal(8,4)notnulldefault0, /*換手率*/dcRate decimal(6,2)notnulldefault0, /*漲幅*/constrainttrDay_pkprimarykey(cStockNo,cDay),/*定義主鍵：cStockNo,cDay*/constrainttrDay_fkforeignkey(cStockNo)referencessmStock(cStockNo)/*定義外鍵：cStockNo*/);

例8-15將“股票日交易數(shù)據(jù)20120425.xls”數(shù)據(jù)導入到MySQL數(shù)據(jù)庫中1234567891011121314151517importnumpyasnp;importxlrd;importpymysqlfromdatetimeimportdatetime;fromxlrdimportxldate_as_tuplestock=xlrd.open_workbook("d:\\股票日交易數(shù)據(jù)20120425.xls")sheet=stock.sheet_by_index(0)#通過索引獲取sheetrows=sheet.nrows#獲得E行數(shù),cols=sheet.ncols為獲得列數(shù)IP=""ps="Sa12345678"conn=pymysql.connect(host=IP,user="root",password=ps,database="myTest",port=3306)cur=conn.cursor()#創(chuàng)建游標對象conn.begin()#開始事務foriinnp.arange(1,rows):row=sheet.row_values(i)#獲得第i行，為

listcStockNo=row[1].strip()#股票代碼vcStockName=row[2]#股票名稱sCell=row[3]#交易日期

date=datetime(*xldate_as_tuple(sCell,0))#處理日期型cDay=date.strftime('%Y-%m-%d')#('%Y/%m/%d%H:%M:%S')

例8-15將“股票日交易數(shù)據(jù)20120425.xls”數(shù)據(jù)導入到MySQL數(shù)據(jù)庫中18192021222324252627282930313233mOpen=str(row[4])#開盤價mHigh=str(row[5])#最高價mLow=str(row[6])#最低價mClose=str(row[7])#收盤價dcRate=str(row[8])#漲幅iVol=str(row[9])#成交量(手)mm=str(row[10])#成交額（元）dcChange=str(row[11])#換手率#------------------先插入股票基本信息-----------------------------------------------------------------sql="selectcStockNofromsmStockwherecStockNo='"+cStockNo+"'"cur.execute(sql)#檢查該股票信息是否已經(jīng)在表中n=cur.rowcount#執(zhí)行查詢語句，返回的記錄行數(shù)ifn==0:#若不在股票基本信息表中，則插入股票基本信息sql="insertintosmStock(cStockNo,vcStockName)"sql=sql+“values(‘”+cStockNo+“’,‘”+vcStockName+“’);"cur.execute(sql)#執(zhí)行SQL語句

例8-15將“股票日交易數(shù)據(jù)20120425.xls”數(shù)據(jù)導入到MySQL數(shù)據(jù)庫中3435363738394041424344454647sql="insertintotrDay(cStockNo,cDay,mOpen,mHigh,mLow,"sql=sql+"mClose,iVol,mm,dcChange,dcRate)values('"sql=sql+cStockNo+"','"+cDay+"',"+mOpen+","+mHigh+","sql=sql+mLow+","+mClose+","+iVol+","+mm+","sql=sql+dcChange+","+dcRate+")"cur.execute(sql)#----------------再插入股票日交易記錄----------------------------try:mit()#數(shù)據(jù)更新，最后一次性提交，否則不會保存到數(shù)據(jù)庫中print("一次性全部導入成功！")exceptpymysql.Errorase:conn.rollback()#如果出現(xiàn)意外，則回滾print("導入失?。″e誤信息：",e.args[1])cur.close()conn.close()8.4.4編程案例：股票交易數(shù)據(jù)處理、分析例8-16根據(jù)例8-2中恢復的股票日交易數(shù)據(jù)表trDay中的漲幅dcRate，用Python編程，計算所有股票漲幅的樣本方差（即波動率），并輸出方差最大的前10只股票的股票代碼、股票名稱、漲幅的標準差。

編程思路：先按股票代碼、股票名稱分組，查詢所有股票的日平均漲幅、交易天數(shù)，根據(jù)該查詢結(jié)果，對股票代碼進行迭代，查詢每一只股票的所有日漲幅，再根據(jù)該股票的平均漲幅計算樣本方差。將計算結(jié)果添加到列表變量中，按列表變量索引的第2個下標，即按樣本方差排降序，最后輸出列表中，前10個元素，即可。

例8-16計算所有股票漲幅的樣本方差（即波動率）123456789101112131415151718importpymysql#引入pymysql模塊IP=""ps="Sa12345678"conn=pymysql.connect(host=IP,user="root",password=ps,database="stock",port=3306)cur=conn.cursor()#定義游標#---分組查詢：股票代碼、股票名稱、平均漲幅、交易天數(shù)，其中交易天數(shù)大于10-------strSQL="selecta.cStockno,b.vcStockName,avg(dcRate),count(cDay)fromtrDaya,smStockb"strSQL=strSQL+"wherea.cStockNo=b.cStockNoandb.vcStockName<>''"strSQL=strSQL+"groupbya.cStockno,b.vcStockNamehavingcount(cDay)>10;"

cur.execute(strSQL)#執(zhí)行查詢語句rows=cur.fetchall()#獲取游標所有行記錄，返回元組變量list_var=[]#定義一個列表：股票代碼，股票名稱，方差forrinrows:cStockNo=r[0]#股票代碼vcStockName=r[1]#股票名稱avg=r[2]#漲幅平均值iDay=r[3]#交易天數(shù)

例8-16計算所有股票漲幅的樣本方差（即波動率）19202122232425262728293031323334353637strSQL="selectdcRatefromtrDaywherecStockNo='"+cStockNo+"'"cur.execute(strSQL)#查詢該股票所有的日漲幅All_Rate=cur.fetchall()