數(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()