數(shù)據(jù)分析離不開數(shù)據(jù)庫(kù)，如何使用python連接MySQL數(shù)據(jù)庫(kù)，并進(jìn)行增刪改查操作呢？

我們還會(huì)遇到需要將大批量數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)的情況，又該如何使用Python進(jìn)行大數(shù)據(jù)的高效導(dǎo)入呢？

本文會(huì)一一講解，并配合代碼和實(shí)例。

一、背景

我是在Anaconda notebook中進(jìn)行連接實(shí)驗(yàn)的，環(huán)境Python3.6，當(dāng)然也可以在Python Shell里面進(jìn)行操作。

最常用也最穩(wěn)定的用于連接MySQL數(shù)據(jù)庫(kù)的python庫(kù)是PyMySQL。

所以本文討論的是利用PyMySQL連接MySQL數(shù)據(jù)庫(kù)，進(jìn)行增刪改查操作，以及存儲(chǔ)大批量數(shù)據(jù)。

方法參考PyMySQL官方文檔和《python數(shù)據(jù)采集》關(guān)于數(shù)據(jù)存儲(chǔ)的部分。

歡迎大家去閱讀原文檔，相信會(huì)理解的更加透徹。

二、基本操作

1、安裝PyMySQL庫(kù)

最簡(jiǎn)單的方式：
在命令行輸入 pip install pymysql

或者：
下載whl文件進(jìn)行安裝，安裝過(guò)程自行百度。

2、安裝MySQL數(shù)據(jù)庫(kù)

類MySQL數(shù)據(jù)庫(kù)有兩種：MySQL和MariaDB，我用的是后者M(jìn)ariaDB。

兩者在絕大部分性能上是兼容的，使用起來(lái)感覺(jué)不到啥區(qū)別。

給出下載地址：MySQL，MariaDB，安裝過(guò)程很簡(jiǎn)單，一路Next Step，不過(guò)要記好密碼。

有個(gè)小插曲，MySQL和MariaDB相當(dāng)于姐姐妹妹的關(guān)系，兩者由同一個(gè)人（Widenius）創(chuàng)建的。MySQL被Oracle收購(gòu)后，Widenius先生覺(jué)得不爽，于是搞了個(gè)MariaDB，可以完全替代MySQL。大牛就是任性。

3、SQL基本語(yǔ)法

下面要用SQL的表創(chuàng)建、查詢、數(shù)據(jù)插入等功能，這里簡(jiǎn)要介紹一下SQL語(yǔ)言的基本語(yǔ)句。

• 查看數(shù)據(jù)庫(kù)：SHOW DATABASES；

• 創(chuàng)建數(shù)據(jù)庫(kù)：CREATE DATEBASE 數(shù)據(jù)庫(kù)名稱；

• 使用數(shù)據(jù)庫(kù)：USE 數(shù)據(jù)庫(kù)名稱；

• 查看數(shù)據(jù)表：SHOW TABLES；

• 創(chuàng)建數(shù)據(jù)表：CREATE TABLE 表名稱（列名1 （數(shù)據(jù)類型1），列名2 （數(shù)據(jù)類型2））；

• 插入數(shù)據(jù)：INSERT INTO 表名稱（列名1，列名2） VALUES（數(shù)據(jù)1，數(shù)據(jù)2）；

• 查看數(shù)據(jù)：SELECT * FROM 表名稱；

• 更新數(shù)據(jù)：UPDATE 表名稱 SET 列名1=新數(shù)據(jù)1，列名2=新數(shù)據(jù)2 WHERE 某列=某數(shù)據(jù)；

4、連接數(shù)據(jù)庫(kù)

安裝好必要得文件和庫(kù)后，接下來(lái)正式開始連接數(shù)據(jù)庫(kù)吧，雖然神秘卻不難哦！

#首先導(dǎo)入PyMySQL庫(kù)
import pymysql
#連接數(shù)據(jù)庫(kù)，創(chuàng)建連接對(duì)象connection
#連接對(duì)象作用是：連接數(shù)據(jù)庫(kù)、發(fā)送數(shù)據(jù)庫(kù)信息、處理回滾操作（查詢中斷時(shí)，數(shù)據(jù)庫(kù)回到最初狀態(tài)）、創(chuàng)建新的光標(biāo)對(duì)象
connection = pymysql.connect(host = 'localhost' #host屬性
                             user = 'root' #用戶名 
                             password = '******'  #此處填登錄數(shù)據(jù)庫(kù)的密碼
                             db = 'mysql' #數(shù)據(jù)庫(kù)名
                             )

執(zhí)行這段代碼就連接好了！

5、增刪改查操作

首先來(lái)查看一下有哪些數(shù)據(jù)庫(kù)：

#創(chuàng)建光標(biāo)對(duì)象，一個(gè)連接可以有很多光標(biāo)，一個(gè)光標(biāo)跟蹤一種數(shù)據(jù)狀態(tài)。
#光標(biāo)對(duì)象作用是：、創(chuàng)建、刪除、寫入、查詢等等
cur = connection.cursor()
#查看有哪些數(shù)據(jù)庫(kù)，通過(guò)cur.fetchall()獲取查詢所有結(jié)果
print(cur.fetchall())

打印出所有數(shù)據(jù)庫(kù)：

(('information_schema',),
('law',),
('mysql',),
('performance_schema',),
('test',))

在test數(shù)據(jù)庫(kù)里創(chuàng)建表:

#使用數(shù)據(jù)庫(kù)test
cur.execute('USE test')
#在test數(shù)據(jù)庫(kù)里創(chuàng)建表student，有name列和age列
cur.execute('CREATE TABLE student(name VARCHAR(20),age TINYINT(3))')

向數(shù)據(jù)表student中插入一條數(shù)據(jù)：

sql = 'INSERT INTO student (name,age) VALUES (%s,%s)'
cur.execute(sql,('XiaoMing',23))

查看數(shù)據(jù)表student內(nèi)容：

cur.execute('SELECT * FROM student')
print(cur.fetchone())

打印輸出為：('XiaoMing', 23)

Bingo！是我們剛剛插入的一條數(shù)據(jù)

最后，要記得關(guān)閉光標(biāo)和連接：

#關(guān)閉連接對(duì)象，否則會(huì)導(dǎo)致連接泄漏，消耗數(shù)據(jù)庫(kù)資源
connection.close()
#關(guān)閉光標(biāo)
cur.close()

OK了，整個(gè)流程大致如此。

當(dāng)然這里都是很基礎(chǔ)的操作，更多的使用方法需要在PyMySQL官方文檔里去尋找。

三、導(dǎo)入大數(shù)據(jù)文件

以csv文件為例，csv文件導(dǎo)入數(shù)據(jù)庫(kù)一般有兩種方法：

1、通過(guò)SQL的insert方法一條一條導(dǎo)入，適合數(shù)據(jù)量小的CSV文件，這里不做贅述。

２、通過(guò)load data方法導(dǎo)入，速度快，適合大數(shù)據(jù)文件，也是本文的重點(diǎn)。

樣本CSV文件如下：

總體工作分為3步：

1、用python連接mysql數(shù)據(jù)庫(kù)；

2、基于CSV文件表格字段創(chuàng)建表；

3、使用load data方法導(dǎo)入CSV文件內(nèi)容。

sql的load data語(yǔ)法簡(jiǎn)介：

LOAD DATA LOCAL INFILE 'csv_file_path' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES

csv_file_path 指文件絕對(duì)路徑

table_name指表名稱

FIELDS TERMINATED BY ','指以逗號(hào)分隔

LINES TERMINATED BY '\\r\\n'指換行

IGNORE 1 LINES指跳過(guò)第一行，因?yàn)榈谝恍惺潜淼淖侄蚊?/p>

下面給出全部代碼：

#導(dǎo)入pymysql方法
import pymysql


#連接數(shù)據(jù)庫(kù)
config = {'host':'',
          'port':3306,
          'user':'username',
          'passwd':'password',
          'charset':'utf8mb4',
          'local_infile':1
          }
conn = pymysql.connect(**config)
cur = conn.cursor()


#load_csv函數(shù)，參數(shù)分別為csv文件路徑，表名稱，數(shù)據(jù)庫(kù)名稱
def load_csv(csv_file_path,table_name,database='evdata'):
    #打開csv文件
    file = open(csv_file_path, 'r',encoding='utf-8')
    #讀取csv文件第一行字段名，創(chuàng)建表
    reader = file.readline()
    b = reader.split(',')
    colum = ''
    for a in b:
        colum = colum + a + ' varchar(255),'
    colum = colum[:-1]
    #編寫sql，create_sql負(fù)責(zé)創(chuàng)建表，data_sql負(fù)責(zé)導(dǎo)入數(shù)據(jù)
    create_sql = 'create table if not exists ' + table_name + ' ' + '(' + colum + ')' + ' DEFAULT CHARSET=utf8'
    data_sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES" % (csv_filename,table_name)
 
    #使用數(shù)據(jù)庫(kù)
    cur.execute('use %s' % database)
    #設(shè)置編碼格式
    cur.execute('SET NAMES utf8;')
    cur.execute('SET character_set_connection=utf8;')
    #執(zhí)行create_sql，創(chuàng)建表
    cur.execute(create_sql)
    #執(zhí)行data_sql，導(dǎo)入數(shù)據(jù)
    cur.execute(data_sql)
    conn.commit()
    #關(guān)閉連接
    conn.close()
    cur.close()