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"
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))")
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)
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，，我們可以看到最終的結果為：
3.4.2 改在cutomers表中更改資料用的是"UPDATE"語句。例如，我們將最後一條 “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的這一條記錄：
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)
執行程式碼，得到的結果為：
n zxx m nb
LIMIT
當資料庫數量非常大的時候，為了限制查詢的資料量，可以採用"LIMIT"語句來指定，比如我們希望在customers表中找出工資最高的三個人：
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的消費者：
sql = "SELECT * FROM customers WHERE age>20 and age<=30 HAVING sl>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的消費者的薪資，並且按照薪資高低進行排序
sql = "SELECT sex,sum(sl) FROM customers WHERE age>20 and age<=30 GROUP BY sex ORDER BY sl"
mycursor.execute(sql)
myresult=mycursor.fetchall()
for x in myresult:
print(x)
值得注意的是：本例是以sex為類別進行GROUP BY 分組，加上WHERE來做條件判斷，加上ORDER BY 排序，但是GROUP BY 的位置必須要在WHERE 之後，在ORDER BY 之前。
3.6 分批量讀取和處理資料
程式執行的時候，資料都是在記憶體中的，但是有時候如果資料量太大，記憶體會裝不下，這個時候我們就需要分批從資料庫去讀取資料，然後再處理，等到處理完了之後，再去讀取。比如：我們要從customers當中分批讀取和處理薪資大於8000的消費者，並將其存入另一張表中。我們的做法是先新建一個表，然後從資料庫當中讀取3個，並且將讀取的這3個進行處理，處理完讀取的這三個後，再去資料庫重新讀取三個，直到資料庫的資料讀完為止。
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以上，當記憶體夠用的話，也可以增加至幾千上萬條。