教你使用Python玩轉(zhuǎn)MySQL數(shù)據(jù)庫,大數(shù)據(jù)導(dǎo)入不再是難題匣距!

數(shù)據(jù)分析離不開數(shù)據(jù)庫面哥,如何使用python連接MySQL數(shù)據(jù)庫,并進(jìn)行增刪改查操作呢毅待?

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

本文會一一講解尸红,并配合代碼和實例吱涉。

一、背景

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

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

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

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

歡迎大家去閱讀原文檔,相信會理解的更加透徹狂秦。

二灌侣、基本操作

1、安裝PyMySQL庫

最簡單的方式:
在命令行輸入 pip install pymysql

或者:
下載whl文件進(jìn)行安裝裂问,安裝過程自行百度侧啼。

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

類MySQL數(shù)據(jù)庫有兩種:MySQL和MariaDB,我用的是后者M(jìn)ariaDB慨菱。

兩者在絕大部分性能上是兼容的焰络,使用起來感覺不到啥區(qū)別。

給出下載地址:MySQL符喝,MariaDB闪彼,安裝過程很簡單,一路Next Step协饲,不過要記好密碼畏腕。

有個小插曲,MySQL和MariaDB相當(dāng)于姐姐妹妹的關(guān)系茉稠,兩者由同一個人(Widenius)創(chuàng)建的描馅。MySQL被Oracle收購后,Widenius先生覺得不爽而线,于是搞了個MariaDB铭污,可以完全替代MySQL。大牛就是任性膀篮。

3嘹狞、SQL基本語法

下面要用SQL的表創(chuàng)建、查詢誓竿、數(shù)據(jù)插入等功能磅网,這里簡要介紹一下SQL語言的基本語句。

  • 查看數(shù)據(jù)庫:SHOW DATABASES筷屡;

  • 創(chuàng)建數(shù)據(jù)庫:CREATE DATEBASE 數(shù)據(jù)庫名稱涧偷;

  • 使用數(shù)據(jù)庫:USE 數(shù)據(jù)庫名稱;

  • 查看數(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ù)庫

安裝好必要得文件和庫后腥放,接下來正式開始連接數(shù)據(jù)庫吧,雖然神秘卻不難哦绿语!

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

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

5聚请、增刪改查操作

首先來查看一下有哪些數(shù)據(jù)庫:

#創(chuàng)建光標(biāo)對象荠雕,一個連接可以有很多光標(biāo),一個光標(biāo)跟蹤一種數(shù)據(jù)狀態(tài)驶赏。
#光標(biāo)對象作用是:炸卑、創(chuàng)建、刪除煤傍、寫入盖文、查詢等等
cur = connection.cursor()
#查看有哪些數(shù)據(jù)庫,通過cur.fetchall()獲取查詢所有結(jié)果
print(cur.fetchall())

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

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

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

#使用數(shù)據(jù)庫test
cur.execute('USE test')
#在test數(shù)據(jù)庫里創(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)閉連接對象龄恋,否則會導(dǎo)致連接泄漏返帕,消耗數(shù)據(jù)庫資源
connection.close()
#關(guān)閉光標(biāo)
cur.close()

OK了,整個流程大致如此篙挽。

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

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

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

1偏竟、通過SQL的insert方法一條一條導(dǎo)入煮落,適合數(shù)據(jù)量小的CSV文件,這里不做贅述踊谋。

2蝉仇、通過load data方法導(dǎo)入,速度快殖蚕,適合大數(shù)據(jù)文件轿衔,也是本文的重點。

樣本CSV文件如下:


總體工作分為3步:

1睦疫、用python連接mysql數(shù)據(jù)庫害驹;

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

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

sql的load data語法簡介:

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 指文件絕對路徑

table_name指表名稱

FIELDS TERMINATED BY ','指以逗號分隔

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

IGNORE 1 LINES指跳過第一行,因為第一行是表的字段名

下面給出全部代碼:

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


#連接數(shù)據(jù)庫
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ù)庫名稱
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ù)庫
    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()
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市胡控,隨后出現(xiàn)的幾起案子扳剿,更是在濱河造成了極大的恐慌,老刑警劉巖昼激,帶你破解...
    沈念sama閱讀 206,968評論 6 482
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件庇绽,死亡現(xiàn)場離奇詭異,居然都是意外死亡橙困,警方通過查閱死者的電腦和手機(jī)瞧掺,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,601評論 2 382
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來凡傅,“玉大人辟狈,你說我怎么就攤上這事∠孽危” “怎么了哼转?”我有些...
    開封第一講書人閱讀 153,220評論 0 344
  • 文/不壞的土叔 我叫張陵,是天一觀的道長槽华。 經(jīng)常有香客問我壹蔓,道長,這世上最難降的妖魔是什么猫态? 我笑而不...
    開封第一講書人閱讀 55,416評論 1 279
  • 正文 為了忘掉前任佣蓉,我火速辦了婚禮,結(jié)果婚禮上亲雪,老公的妹妹穿的比我還像新娘勇凭。我一直安慰自己,他們只是感情好匆光,可當(dāng)我...
    茶點故事閱讀 64,425評論 5 374
  • 文/花漫 我一把揭開白布套像。 她就那樣靜靜地躺著,像睡著了一般终息。 火紅的嫁衣襯著肌膚如雪夺巩。 梳的紋絲不亂的頭發(fā)上贞让,一...
    開封第一講書人閱讀 49,144評論 1 285
  • 那天,我揣著相機(jī)與錄音柳譬,去河邊找鬼喳张。 笑死,一個胖子當(dāng)著我的面吹牛美澳,可吹牛的內(nèi)容都是我干的销部。 我是一名探鬼主播,決...
    沈念sama閱讀 38,432評論 3 401
  • 文/蒼蘭香墨 我猛地睜開眼制跟,長吁一口氣:“原來是場噩夢啊……” “哼舅桩!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起雨膨,我...
    開封第一講書人閱讀 37,088評論 0 261
  • 序言:老撾萬榮一對情侶失蹤擂涛,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后聊记,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體撒妈,經(jīng)...
    沈念sama閱讀 43,586評論 1 300
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,028評論 2 325
  • 正文 我和宋清朗相戀三年排监,在試婚紗的時候發(fā)現(xiàn)自己被綠了狰右。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 38,137評論 1 334
  • 序言:一個原本活蹦亂跳的男人離奇死亡舆床,死狀恐怖棋蚌,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情挨队,我是刑警寧澤附鸽,帶...
    沈念sama閱讀 33,783評論 4 324
  • 正文 年R本政府宣布,位于F島的核電站瞒瘸,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏熄浓。R本人自食惡果不足惜情臭,卻給世界環(huán)境...
    茶點故事閱讀 39,343評論 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望赌蔑。 院中可真熱鬧俯在,春花似錦、人聲如沸娃惯。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,333評論 0 19
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽趾浅。三九已至愕提,卻和暖如春馒稍,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背浅侨。 一陣腳步聲響...
    開封第一講書人閱讀 31,559評論 1 262
  • 我被黑心中介騙來泰國打工纽谒, 沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人如输。 一個月前我還...
    沈念sama閱讀 45,595評論 2 355
  • 正文 我出身青樓鼓黔,卻偏偏與公主長得像,于是被迫代替她去往敵國和親不见。 傳聞我的和親對象是個殘疾皇子澳化,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 42,901評論 2 345

推薦閱讀更多精彩內(nèi)容