本文介紹如何利用Python來對MySQL數據庫進行操作，本文將主要從以下幾個方面展開介紹:
1.數據庫介紹
2.MySQL數據庫安裝和設置
3.Python操作MySQL
在Python3.X上安裝MySQL驅動
創建數據庫連接
創建數據表
增、改、刪、查
分組、聚合
按批量讀取和處理數據
4.小結
01 數據庫介紹
數據庫(Database)是按照數據結構來組織、存儲和管理數據的倉庫，能直接通過條件快速查詢到指定的數據。隨著信息技術和市場的發展，數據管理不再僅僅是存儲和管理數據，而轉變成用戶所需要的各種數據管理的方式。
目前，數據庫主要有兩種形式，一種是非關系型數據庫，另一種是關系型數據庫。目前，我們用得非常廣泛的一種數據庫類型是關系型數據庫，它可以分為以下幾種:
Oracle:付費產品，主要是銀行在用(萬一出錯了有Oracle背鍋)
DB2:付費產品，IBM產品
SQL Sever:付費產品，微軟產品，windows專用
PostgreSQL:免費產品，主要是高校學術上使用
MySQL:大眾，免費，開源
作為手無寸金的大學生，我們應該用哪種數據庫呢?當然是MySQL。一方面是因為MySQL免費，另一方面是因為普及率最高，出了錯，可以很容易找到解決方法。而且，圍繞MySQL有一大堆監控和運維的工具，安裝和使用很方便。所以，本文接下來也會介紹如何用Python來操作MySQL。
02 MySQL安裝和設置2.1 下載安裝:
STEP1****:MySQL官方網站上下載最新的MySQL Installer 8.0.14版本，下載鏈接為:
https://dev.mysql.com/downloads/installer/
STEP2:按照指示操作默認安裝，在安裝時，MySQL會要求我們設置一個本地登陸賬號，賬號名一般命為root，端口為3306，自定義一個password即可。
2.2 MySQL workbench創建用戶與授權STEP1:創建新用戶。以root用戶登錄MySQL workbench，先選擇users and privileges，然后選擇下方的add account去添加用戶。
STEP2:我們以創建好的blank為例，創建好后在左邊的表中出現了blank這個用戶。返回MySQL Workbench主頁，新建MySQL Connection，用戶名和密碼為新創建的用戶名和密碼，這個時候，我們就能看到除了root用戶外，還能看到新創建的blank這個user賬戶了。
STEP3:創建數據庫和數據表。在root賬戶中新建一個名字為test_s的Schema，然后我們會在左下角的schemas中看到我們新建的schema。對于Schema，在MySQL中，它等同于database，它是數據庫對象的集合，這個集合包括了各種對象，如Tables(表)、Views(視圖)、Sorted Procedures(存儲過程)、Functions等，我們可以選中Table，點擊鼠標右鍵，選擇creat table，即可在該數據庫下創建數據表。創建過程和創建數據庫類似。
STEP4****:設置用戶權限:而當我們用blank連接數據庫時，是沒有test_s這個schema的，這個時候我們需要通過root開放權限給blank這個用戶。返回root用戶操作選項卡，選擇users and privileges，選中blank用戶，再選擇schema privileges，點擊add entry;在彈出來的窗口中選擇權限范圍，這里我們選擇指定的test_s給blank這個user，點擊ok;雙擊tets_s，privileges全部授予,點擊select all。
STEP5:進入主界面，重新進入blank用戶操作選項卡，我們會在schemas中看到test_s這個schema。blank這個用戶可以對root授權的test_s這個schema中的表進行操作。
至此，我們完成了MySQL中用戶的新建和授權。
03 Python操作MySQL
目前，關于Python操作數據庫主要有以下幾種方法:
MySQLdb的使用
MySQLdb是用于Python連接MySQL數據庫的接口，它實現了Python數據庫API規范V2.0，基于MySQL C API上建立的，目前只支持Python2.x。
PyMySQL的使用
PyMySQL是Python中用于連接MySQL服務器的一個庫，它支持Python3.x，是一個純Python寫的MySQL客戶端，它的目標是替代MySQLdb。PyMySQL在MIT許可下發布。
mysql.connector 的使用
由于 MySQL 服務器以獨立的進程運行，并通過網絡對外服務，所以，需要支持 Python 的 MySQL 驅動來連接到 MySQL 服務器。
目前，有兩個 MySQL 驅動:
mysql-connector-python:是 MySQL 官方的純 Python 驅動
MySQL-python :是封裝了 MySQL C驅動的 Python 驅動
SQLAlchemy的使用
是一種ORM(Object-Relational MApping)框架，將關系數據庫的表結構映射到對象上，隱藏了數據庫操作背后的細節，簡化了數據操作。
3.1 在Python3.X上安裝MySQL驅動
STEP1:由于MySQL官方提供了mysql-connector-python驅動。安裝時，在Anaconda Prompt中輸入:
conda install mysql-connector-python
STEP2:使用以下代碼來測試mysql-connector是否安裝成功:
import mysql.connector
如果沒有產生錯誤，則表明安裝成功。
3.2 創建數據庫連接
這里連接的是我之前創建的blank這個user。如果數據庫已經存在的話，我們可以直接連接;如果數據庫不存在，直接連接則會報錯，這個時候我們就需要創建一個數據庫，創建數據庫可以在MySQL Workbench中創建，也可以在python中使用"CREATE DATABASE"語句，在本實驗中，我們使用已經在MySQL workbench中已經建好的test_s這個數據庫。
import mysql.connector #連接數據庫 config = { 'user' : 'blank' #用戶名 'password' : 'password' #自己設定的密碼 'host' : '127.0.0.1' #ip地址，本地填127.0.0.1，也可以填localhost 'port' : '3306' #端口，本地的一般為3306 'database' : 'test_s' #數據庫名字，這里選用test_s } con = mysq;.connector.connect(**config)3.3 創建數據表STEP1:當Python 和數據之間的連接建立起來之后，要操作數據庫，就需要讓 Python對數據庫執行SQL語句。創建數據表我們使用"CREATE TABLE"語句，在test_s這個數據庫中創建一個叫做customers的表格，其中包含id、name、address、sex、age、sl這六個columns。Python是通過游標執行SQL語句的，所以，連接建立之后，就要利用連接對象得到游標對象。
cursor():表示游標
execute():是執行語句
STEP2****:一般在創建新表的時候，我們還會設置一個主鍵(PRIMARY KEY)來方便進行查詢工作。創建主鍵，我們可以用"INT AUTO_INCREMENT PRIMARY KEY"#創建一個表 # buffered = True 不設的話，查詢結果沒有讀完會報錯 # raise errors.InternalError("Unread result found") mycursor = con.cursor(buffered = True) mycursor.execute("CREATE TABLE customers(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) , address VARCHAR(255), 7sex VARCHAR(225) , age INT(10) , sl INT(10))")
VARCHAR()表示的是數據類型，定義的是變長字符串;INT()表示整型
STEP3:執行語句。執行完后，我們可以回到MySQL workbench，可以看到在test_s下面的customers這個表格，其中Columns為我們創建的id，name，address，sex，age和sl。STEP4:但是，當我們再次執行語句的時候，由于已經創建了"customers"這個表，所以再次執行會報錯，這個時候就需要加一個判斷，判斷這個表是否已經存在于test_s這個數據庫中ProgrammingError: Table 'customers' alreadyy exists STEP5****:我們可以用"SHOW TABLES"語句來查看數據表是否已經存在，如果存在就print"table already exists",如果不存在，就print"table does not exist"。def tableExists(mycursor, name): stmt = "SHOW TABLES LIKE '" +name+ "'" mycursor.execute(stmt) return mycursor.fetchone() mycursor = con.cursor() if tableExists(mycursor , 'customers'): print("table already exists") else: print("table not exists")STEP6:上面的語句只是為了幫助我們判斷是否有同名表，當我們要新建一個表時，我們可以在這個判斷的基礎上，在創建新表前刪掉數據庫內的同名表，再建新表。刪除我們用的是"DROP TABLE"，新建表是"CERATE TABLE"import mysql.connector #連接數據庫 config = { 'user' : 'blank', 'password' :'fuying123888', 'host' : '127.0.0.1', 'port':'3306', 'database' : 'test_s' } con = mysql.connector.connect(**config) # 檢查一個表是否存在 def tableExists(mycursor, name): stmt = "SHOW TABLES LIKE '"+name+"'" mycursor.execute(stmt) return mycursor.fetchone() # 刪除一個表(無論它是否已經存在) def dropTable(mycursor, name): stmt = "DROP TABLE IF EXISTS "+name mycursor.execute(stmt) # buffered=True 不設的話，查詢結果沒有讀完會報錯 # raise errors.InternalError("Unread result found") mycursor = con.cursor(buffered=True) # 刪除臨時表 tableName = 'customers' dropTable(mycursor, tableName) # 創建一個表 mycursor.execute("CREATE TABLE customers(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255), sex VARCHAR(225), age INT(10), sl INT(10))")3.4 增、改、刪、查3.4.1 增
在cutomers表中插入數據用的是"INSERT INTO"語句。
除了用一條條用execute( )插入之外，我們還可以用executemany()的方式批量插入，也就是val中包含的是一個元組列表，包含我們想要插入的數據。
需要注意的事是:如果數據表格有更新，那么必須用到commit()語句，否則在workbench是看不到插入的數據的。
#往表里插入一些記錄 sql="INSERT INTO customers(name,address,sex,age,sl) VALUES(%s, %s,%s,%s,%s)" val = ("John", "Highway 21","M",23,5000) mycursor.execute(sql, val) val = ("Jenny", "Highway 29","F",30,12500) mycursor.execute(sql, val) val=[("Tom","ABC 35","M",35,14000), ("Tom1","Highway 29","M",28,6700), ("Lily","Road 11","F",30,8000), ("Martin","Road 24","M",35,14000), ("Sally","Fast 56","M",32,15000)] mycursor.executemany(sql, val) con.commit()
執行以上代碼后，回到workbench，，我們可以看到最終的結果為:
image 3.4.2 改
在cutomers表中更改數據用的是"UPDATE"語句。例如，我們將最后一條 “Sally”的名字改成“Tiny”:
#將Sally改為Tiny sql="UPDATE customers SET name='Tiny' WHERE name ='Sally'" mycursor.execute(sql) con.commit()
執行代碼，回到workbench我們可以看到結果為:
3.4.3 刪
關于刪，我們在上文提到了刪除表格，用的是“DROP TABLE ”語句，“IF EXISTS”關鍵字是用于判斷表是否存在，只有在存在的情況才刪除當我們要刪除一條數據記錄時候，用到的語句是“DELETE FROM”語句。例如:我們想在customers這個表格當中，刪除name為Tiny的這一條記錄:
#刪除名字為Tiny的記錄 sql="DELETE FROM customers WHERE name='Tiny'" mycursor.execute(sql) con.commit()
執行代碼，回到workbench我們可以看到結果為:
3.4.4 查普通查詢
普通查詢數據用的是SELECT語句。例如:我們想查詢customers的所有信息，并且進行打印輸出:
#查詢這里面所有的人: sql="SELECT * FROM customers" mycursor.execute(sql) myresult = mycursor.fetchall() # fetchall() 獲取所有記錄 for x in myresult: print(x)
得到最終結果為:
值得注意的是:fetchall()表示的是獲得所有記錄;fetchone()表示只獲取一條數據;fetchmany(size=3)表示獲取三條記錄;
限定條件查找
為了獲取指定條件下的查找結果，我們可以使用where語句。例如:我們想在查詢customers的所有信息基礎上，輸出年齡大于30歲的消費者的信息:
sql="SELECT * FROM customers WHERE age > 30" mycursor.execute(sql) myresult = mycursor.fetchall() # fetchall() 獲取所有記錄 for x in myresult: print(x)
最終得到的結果為:
通配符查找
有時候為了進行模糊查詢，可以匹配通配符，通過“LIKE”來進行查找:
百分號 (%):代表零個、一個或多個數字或字符;
下劃線 (_):代表一個單一的數字或字符。
例如:查出所有名字中含有t的記錄:
#%代表零個、一個或者多個數字或字符 #_代表一個單一的數字或者字符 sql = "SELECT * FROM customers WHERE name LIKE '%t%'" mycursor.execute(sql) myresult=mycursor.fetchall() for x in myresult: print(x)
執行代碼，我們得到的結果如下:
值得注意的是:但是使用Like查詢時，即使我們在代碼輸入的是“t”，執行過程中也會將含有“T”的記錄同樣輸出，即用LIKE匹配通配符對大小寫不敏感。為了區分大小寫，可以用“GLOB”進行查詢。
排序
查詢結果排序可以使用 ORDER BY 語句，默認的排序方式為升序，如果要設置降序排序，可以設置關鍵字 DESC。例如:我們要按照年齡對customers進行升序排列:
#排序 #按照年齡排序 sql = "SELECT * FROM customers ORDER BY age" mycursor.execute(sql) myresult=mycursor.fetchall() for x in myresult: print(x)
執行代碼，得到的結果為:
nzxx m nb
LIMIT
當數據庫數量非常大的時候，為了限制查詢的數據量，可以采用"LIMIT"語句來指定，比如我們希望在customers表中找出工資最高的三個人:
#找出其中工資最高的3個人 sql = "SELECT * FROM customers ORDER BY sl DESC LIMIT 3" mycursor.execute(sql) myresult=mycursor.fetchall() for x in myresult: print(x)
執行代碼，得到結果為:
二次篩選
有時候我們在進行一次篩選后，還需要設定一個篩選條件進行二次篩選，我們就可以采用“HAVING”語句。例如:我們希望統計在年齡處于20-30(不包括20歲，但是包括30歲)的人當中，選擇薪資大于5000的消費者:
#二次過濾 #統計在年齡處于20-30之間的人中，選擇薪資大于5000的人 sql = "SELECT * FROM customers WHERE age>20 and age<=30>5000 " mycursor.execute(sql) myresult=mycursor.fetchall() for x in myresult: print(x) con.commit() con.close()
執行代碼后，得到的結果如下:
3.5 分組聚合
在數據庫中，分組常用的語句為“GROUP BY”語句，聚合函數，通常是配合分組進行使用，在數據庫中常用的聚合函數為:
COUNT(*):表示計算總行數，括號可以寫*和字段名字
MAX(column):表示求此列的最大值
MIN(column):表示求此列的最小值
SUM(column):表示求此列的和
AVG(column):表示求此列的平均值
從customers表中統計出男女薪資總和
以sex為類別進行GROUP BY 分組，加上WHERE來做條件判斷。
#統計出男女的薪水總數 sql = "SELECT sex,sum(sl) FROM customers GROUP BY sex" mycursor.execute(sql) myresult=mycursor.fetchall() for x in myresult: print(x)
最終結果為:
從customers表中，按性別進行分組，統計出年齡在20-30的消費者的薪資，并且按照薪資高低進行排序#按照性別進行分組，統計出年齡在20-30歲的消費者的薪資 sql = "SELECT sex,sum(sl) FROM customers WHERE age>20 and age<=30>
值得注意的是:本例是以sex為類別進行GROUP BY 分組，加上WHERE來做條件判斷，加上ORDER BY 排序，但是GROUP BY 的位置必須要在WHERE 之后，在ORDER BY 之前。
3.6 分批量讀取和處理數據
程序運行的時候，數據都是在內存中的，但是有時候如果數據量太大，內存會裝不下，這個時候我們就需要分批從數據庫去讀取數據，然后再處理，等到處理完了之后，再去讀取。比如:我們要從customers當中分批讀取和處理薪資大于8000的消費者，并將其存入另一張表中。我們的做法是先新建一個表，然后從數據庫當中讀取3個，并且將讀取的這3個進行處理，處理完讀取的這三個后，再去數據庫重新讀取三個，直到數據庫的數據讀完為止。
#分批讀取并且處理將薪資大于8000的消費者的記錄存到另一張表中 # 創建一個臨時表 tmpName = 'cust_tmp' dropTable(mycursor, tmpName) mycursor.execute("CREATE TABLE cust_tmp(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255), sex VARCHAR(225), age INT(10), sl INT(10))") ins = con.cursor(buffered=True) if tableExists(mycursor, tableName): print("process table: %s", tableName) # 查詢表里的記錄 sql = "SELECT * FROM customers WHERE address is not null" mycursor.execute(sql) # 每次處理 batchsize 條記錄，直到所有查詢結果處理完 batchsize = 3 readsize = batchsize while readsize == batchsize: print("before batch") myresult = mycursor.fetchmany(size=batchsize) for x in myresult: if x[5]>8000: ins.execute("INSERT INTO"+tmpName+"(id,name,address,sex,age,sl) VALUES (%s, %s,%s, %s,%s,%s)", x) print(x) readsize = len(myresult) else: print("table: does not exists", tableName) con.commit() con.close()
我們回到workbench找到這個新建的表格cust_tmp，我們可以發現薪資大于8000的消費者都被記錄上了:
執行代碼，我們可以看到處理的過程如下:
在第一批讀取的三條記錄中，只有兩條是滿足薪資大于8000的要求，第二批讀取的三條記錄中，只有一條滿足薪資大于8000的要求，而在第三批讀取的三條記錄中，沒有任何記錄是滿足薪資大于8000的要求，當沒有記錄可以讀的時候，程序即停止。
值得注意的是:就分批讀取的batchsize而言，當batchsize太大時，會導致內存裝不下，batchsize太小，會導致每次通過網絡連接數據庫會很慢。因此，我們選取batchsize大小的原則是在內存夠用的前提下盡可能的大，在真實的業務場景下，建議每次讀取100以上，當內存夠用的話，也可以增加至幾千上萬條。
04小結
本文介紹了Python+MySQL的基本操作，包括如何安裝Mysql，如何裝驅動，如何創建連接以及對數據庫進行增刪改查、分組聚合以及批量讀取和處理等操作。但是，本文涉及到的只是對單表進行操作，只是數據庫操作的冰山一角;在實際的開發和工作環境中，需要根據實際內容對多表進行操作，這部分請持續關注數據魔術師關于數據庫的后期推文。
最后，小編想說：我是一名python開發工程師，
整理了一套最新的python系統學習教程，
想要這些資料的可以關注私信小編“01”即可（免費分享哦）希望能對你有所幫助
