應用場景:工作中經常遇到大量的數據需要整合、去重、按照特定格式導出等情況。如果用 Excel 操作,不僅費時費力,還不準確,有麼有更高效的解決方案呢?
本文以17個 txt 文本,3萬多條數據為例,使用 Python 連接 MySQL 數據庫,實現快速操作。
別人加班乾的活,我的 Python 小助手幾秒鐘就搞定了!
本文主要包括以下三方面內容:
- 數據寫入
- 數據去重
- 數據導出
將數據寫入 MySQL 數據庫
下圖所示文件是本文的數據源:
我們的設想是:編寫一個小程序,扔在這個文件夾裡,雙擊後就可以自動讀取每個 txt 文檔中的數據,並寫入數據庫。
代碼如下:
<code>import pymysql
import os
conn = pymysql.connect(host='localhost', user='root', password='123456', db='qq', charset='utf8')
cur = conn.cursor()
cur.execute("CREATE TABLE qq ( id int(5) NOT NULL auto_increment, qq varchar(20)NOT NULL, PRIMARY KEY (id));")
conn.commit()
path = os.getcwd()
files = os.listdir(path)
i = 0
for file in files:
f = open(file,'r',encoding = 'UTF-8')
next(f)
for line in f:
i += 1
#print(line)
sql = "insert into qq(qq) values(%s);"
cur.execute(sql,line)
print("插入第", i, "條數據!")
conn.commit()
f.close()
cur.close()
conn.close()/<code>
運行效果:
重點代碼解釋:
這段代碼用到了 pymysql 和 os 兩個庫。
- pymysql:用來操作 MySQL 數據庫;
- os:用來遍歷所在文件夾下的所有文件。
現將主要代碼解釋如下:
1、遍歷任意文件夾下所有文件名稱
程序寫好後,使用 pyinstaller 打包成 exe 程序,並放在要操作的文件夾下面。
通過 path = os.getcwd()命令,獲取該 exe 文件所在目錄。
通過 files = os.listdir(path)命令,獲取 exe 文件所在目錄下的所有文件名稱,並存入 files 列表中。
這樣我們就獲得了所有的 txt 文件名稱,你可以任意命名你的 txt 文件名,程序都能讀出來。
2、將數據寫入數據庫
(1)連接數據庫,並在數據庫中創建新表
A. 連接到我的 qq 數據庫
conn = pymysql.connect(host='localhost', user='root', password='123456', db='qq', charset='utf8')
B. 創建新表 qq
在 qq 數據庫中創建新表,表名稱為 qq ,包含 2 個字段:id 字段為主鍵、自動遞增;qq 字段為字符型,用於存儲數據。
cur.execute("CREATE TABLE qq ( id int(5) NOT NULL auto_increment, qq varchar(20)NOT NULL, PRIMARY KEY (id))")
(2)將數據寫入數據庫
這裡使用了兩層循環:
<code>for file in files:
f = open(file,'r',encoding = 'UTF-8')
next(f)
for line in f:
i += 1
#print(line)
sql = "insert into qq(qq) values(%s);"
cur.execute(sql,line)
print("插入第", i, "條數據!")
conn.commit()
f.close()/<code>
第一層循環是用來依次打開上述 17 個 txt 文件。
第二層循環是依次讀取每個 txt 文件的每一行,並將改行數據插入數據庫表 qq 的 qq字段。
至此就完成了數據的導入,總共32073條數據。
數據清洗
這裡以去除重複值為例,簡單介紹一下數據清洗。
1、創建一個新表,用來存儲清洗後的數據
可以在 cmd 窗口下登陸 MySQL,打開 qq 數據庫,執行下列操作:
CREATE TABLE qq_dist ( id int(5) NOT NULL auto_increment, qq varchar(20)NOT NULL, PRIMARY KEY (id));
這樣就創建了新表 qq_dist ,用來存儲清洗後的數據,便於後期調用。
2、清洗數據
登陸 MySQL 後,執行下列操作:
insert into qq_dis(qq) select distinct qq from qq;
將從 qq 表中查找出的不重複的 qq 字段內容,插入到 qq_dist 表中的 qq 字段。
將數據按照特定格式導出
案例:將清洗後的數據的第101-200行導出到新的 txt 文本中。
代碼如下:
<code>import pymysql
conn = pymysql.connect(host='localhost', user='root', password='123456', db='wxid', charset='utf8')
print("寫入中,請等待……")
cur = conn.cursor()
sql = "select wxid from wd_dis limit 100,100;"
cur.execute(sql)
conn.commit()
alldata = cur.fetchall()
f = open('data101-200.txt','a')
i = 0
for data in alldata:
i += 1
f.write(data[0])
f.flush()
f.close
cur.close()
conn.close()
print("寫入完成,共寫入{}條數據!".format(i))/<code>
重點代碼解釋:
1、 limit
MySQL 中 limit m,n 函數的含義是:從第 m+1 行開始讀取 n 行。
所以,本案例中讀取第101-200行,就是 limit 100,100
2、flush()
flush()函數一定要加上,它可以將緩衝區的數據寫入文件中。否則就會出現生成的 txt 文檔為空白的錯誤。
閱讀更多 有趣的程序媛 的文章