乾貨！Python與MySQL數據庫的交互實戰

作者 | Huang supreme，責編 | 郭芮

頭圖 | CSDN 下載自視覺中國

安裝PyMySQL庫

如果你想要使用python操作MySQL數據庫，就必須先要安裝pymysql庫，這個庫的安裝很簡單，直接使用pip install pymysql；假如這種方式還是安裝不上，就用如下鏈接找一個合適的安裝包，進行安裝，這個就不細說了。

• https://www.lfd.uci.edu/~gohlke/pythonlibs/

使用Python連接MySQL數據庫

1）六個常用的連接參數

• 參數host：mysql服務器所在的主機的ip；

• 參數user：用戶名

• 參數password：密碼

• 參數port：連接的mysql主機的端口，默認是3306

• 參數db：連接的數據庫名

• 參數charset：當讀取數據出現中文會亂碼的時候，需要我們設置一下編碼；我們使用python操作數據庫的時候，那麼python就相當於是client，我們是用這個client來操作mysql的server服務器，python3默認採用的utf8字符集，我的mysql服務器默認採用latin1字符集，因此mysql中創建的每張表，都是建表的時候加了utf8編碼的，因此這裡設置的應該就是connection連接器的編碼。

2）python連接mysql的語法

<code>import pymysql /<code><code>db=pymysql.connect(host='localhost',user='root',password='123456',/<code><code> port=3306,db='spiders',charset=' utf8')/<code>

最基本的參數是host,user,password和port，必須要有。剩下兩個參數根據你自己的情況決定是否使用。

host指的是mysql服務器安裝在哪裡，由於我的mysql就是安裝在本機上，因此這裡可以寫localhost，我也可以寫成主機名或者主機ip。

db指的是你要操作的是哪一個數據庫，在進行數據庫連接的時候，最好加上這個參數。

3）一個簡單的熱身案例

<code># 導包/<code><code>import pymysql /<code><code># 使用pymysql連接上mysql數據庫服務器，創建了一個數據庫對象；/<code><code>db=pymysql.connect(host='localhost',user='root', password='123456',/<code><code> port=3306, db='huangwei', charset='utf8')/<code><code># 開啟mysql的遊標功能，創建一個遊標對象；/<code><code>cursor = db.cursor/<code><code># 要執行的SQL語句；/<code><code>sql = "select * from student"/<code><code># 使用遊標對象執行SQL語句；/<code><code>cursor.execute(sql)/<code><code># 使用fetchone方法，獲取返回的結果，但是需要用變量保存返回結果；/<code><code>data = cursor.fetchone/<code><code>print(data)/<code><code># 斷開數據庫的連接，釋放資源；/<code><code>db.close/<code>

結果如下：

Cursor遊標對象的一些常用方法

1）cursor用來執行命令的方法

execute(query, args)：執行單條sql語句，接收的參數為sql語句本身和使用的參數列表，返回值為受影響的行數；

executemany(query, args)：執行單條sql語句，但是重複執行參數列表裡的參數，返回值為受影響的行數；

2）cursor用來接收返回值的方法

• fetchone：返回一條結果行；

• fetchmany(size)：接收size條返回結果行。如果size的值大於返回的結果行的數量，則會返回cursor.arraysize條數據；

• fetchall：接收全部的返回結果行；

創建表（建）

<code>import pymysql /<code><code>db=pymysql.connect(host='localhost',user='root', password='123456',/<code><code> port=3306, db='huangwei', charset='utf8')/<code>
<code># 創建一個遊標對象；/<code><code>cursor = db.cursor/<code>
<code># 建表語句；/<code><code>sql = """create table person(/<code><code> id int auto_increment primary key not ,/<code><code> name varchar(10) not ,/<code><code> age int not ) charset=utf8"""/<code>
<code># 執行sql語句；/<code><code>cursor.execute(sql)/<code>
<code># 斷開數據庫的連接；/<code><code>db.close/<code>

注意：你在mysql中sql語句怎麼寫，在這裡就怎麼寫。還有一個細節需要注意的是，在python中，將代碼進行多次換行的時候，最好使用“三引號”。

查詢數據（查）

<code>import pymysql/<code>
<code>db = pymysql.connect(host='localhost',user='root',db='huangwei',/<code><code> password='123456',port=3306,charset='utf8')/<code>
<code>cursor = db.cursor/<code>
<code>cursor.execute('select count(*) from person')/<code><code>aa = cursor.fetchone/<code><code>print(aa)/<code><code># 注意這一句一定是在循環之外，不能放到循環裡面。/<code><code># 想想這是為什麼？/<code><code>cursor.execute('select name,age from person') /<code><code>for i in range(aa[0]):/<code><code> a,b = cursor.fetchone/<code><code> c = "我的名字叫{}，今年{}歲".format(a,b)/<code><code> display(c)/<code><code>db.close/<code>

結果如下：

2）fetchall：一次獲取所有記錄

<code>import pymysql/<code>
<code>db = pymysql.connect(host='localhost',user='root',db='huangwei',/<code><code> password='123456',port=3306,charset='utf8')/<code>
<code>cursor = db.cursor/<code>
<code>cursor.execute('select name,age from person')/<code><code>aa = cursor.fetchall/<code><code># print(aa)/<code><code>for a,b in aa:/<code><code> c = "我的名字叫{}，今年{}歲".format(a,b)/<code><code> display(c)/<code><code>db.close/<code> 

結果如下：

注：還有一個fetchmany方法，用於一次性獲取指定條數的記錄，請自行下去研究。

3）使用pandas中的read_sql方法，將提取到的數據直接轉化為DataFrame，進行操作

<code>import pymysql /<code><code>import pandas as pd/<code>
<code>db = pymysql.connect(host='localhost',user='root',db='huangwei',/<code><code> password='123456',port=3306,charset='utf8')/<code><code>cursor = db.cursor/<code>
<code>df1 = pd.read_sql("select * from student where ssex='男'",db)/<code><code>display(df1)/<code><code>df2 = pd.read_sql("select * from student where ssex='女'",db)/<code><code>display(df2)/<code>

結果如下：

插入數據（增）

1）一次性插入一條數據

<code>import pymysql/<code>
<code>db=pymysql.connect(host='localhost',user='root', password='123456',/<code><code> port=3306, db='huangwei', charset='utf8')/<code>
<code>cursor = db.cursor/<code>
<code># mysql中SQL語句怎麼寫，這裡就怎麼寫；/<code><code>name = "豬八戒"/<code><code>age = 8000/<code><code>sql = 'insert into person(name,age) values ("豬八戒",8000)'/<code>
<code>try:/<code><code> cursor.execute(sql)/<code><code> db.commit/<code><code> print("插入成功")/<code><code>except:/<code><code> print("插入失敗")/<code><code> db.rollback/<code><code>db.close/<code>

1.1）一次性插入一條數據

<code>import pymysql/<code>
<code>db=pymysql.connect(host='localhost',user='root', password='123456',/<code><code> port=3306, db='huangwei', charset='utf8')/<code> 

<code>cursor = db.cursor/<code>
<code># 插入數據/<code><code>sql = 'insert into person(name,age) values(%s,%s)'/<code><code>try:/<code><code> cursor.execute(sql,('孫悟空',100000))/<code><code> db.commit/<code><code> print("插入成功")/<code><code>except:/<code><code> print("插入失敗")/<code><code> db.rollback/<code><code>db.close/<code>

2）一次性插入多條數據

<code>import pymysql/<code>
<code>db=pymysql.connect(host='localhost',user='root', password='123456',/<code><code> port=3306, db='huangwei', charset='utf8')/<code>
<code>cursor = db.cursor/<code>
<code># 插入數據/<code><code>sql = 'insert into person(name,age) values(%s,%s)'/<code><code># 注意：(('牛魔王',9000),('鐵扇公主',8000),('玉皇大帝',6000))也可以/<code><code># 小括號都可以換為中括號/<code><code>datas = [('牛魔王',9000),('鐵扇公主',8000),('玉皇大帝',6000)]/<code><code>try:/<code><code> cursor.executemany(sql,datas)/<code><code> db.commit/<code><code> print("插入成功")/<code><code>except:/<code><code> print("插入失敗")/<code><code> db.rollback/<code><code>db.close/<code>

更新數據（改）

<code>import pymysql/<code>
<code>db=pymysql.connect(host='localhost',user='root', password='123456',/<code><code> port=3306, db='huangwei', charset='utf8')/<code>
<code>cursor = db.cursor/<code>
<code># 更新數據/<code><code>sql = 'update person set age=%s where name=%s'/<code><code>try:/<code><code> cursor.execute(sql,[90000,"玉皇大帝"])/<code><code> db.commit/<code><code> print("更新成功")/<code><code>except:/<code><code> print("更新失敗")/<code><code> db.rollback/<code><code>db.close/<code>

刪除數據（刪）

<code>import pymysql/<code>
<code>db=pymysql.connect(host='localhost',user='root', password='123456',/<code><code> port=3306, db='huangwei', charset='utf8')/<code>
<code>cursor = db.cursor/<code>
<code># 刪除數據/<code><code>sql = 'delete from person where age=8000'/<code><code>try:/<code><code> cursor.execute(sql)/<code><code> db.commit/<code><code> print("刪除成功")/<code><code>except:/<code><code> print("刪除失敗")/<code><code> db.rollback/<code><code>db.close/<code>

總結如下：

• PyMySQL模塊是默認開啟MySQL的事務功能的，因此，進行 "增"、 "刪"、"改"的時候，一定要使用db.commit提交事務，否則就看不見所插入的數據。

• 進行 "增"、"刪"、"改"的時候，一定要使用try…except…語句，因為萬一沒插入成功，其餘代碼都無法執行。當語句執行不成功，我們就db.rollback回滾到操作之前的狀態；當語句執行成功，我們就db.commit提交事務。

作者：Huang supreme，個人博客地址：https://blog.csdn.net/weixin_41261833。

閱讀更多 CSDN 的文章

關鍵字: 遊標 Python MySQL