10分鐘教你Python+MySQL數據庫操作

本文介紹如何利用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****:

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”即可（免費分享哦）希望能對你有所幫助

閱讀更多 Python智禪 的文章

關鍵字: 數據結構 操作 Windows