前言
昨天寫小項目的時候遇到了一個需求：把txt文檔的數據導入到mysql數據庫中，開始本來想直接用Mysql Workbench導入TXT文件，但是最后發現不支持TXT導入，結果我吧嗒吧嗒的去把TXT轉了Excel，拿到Linux上導入的時候又發現了各種亂碼問題。
抱著沒有什么是程序員干不了的原則，我手寫了一個Python代碼直接操作文件進行導入了。結果大概一萬多條的文件，導入時間大概兩分鐘。
下面是具體的代碼：
mysqlpython.py文件： 自定義的連接mysql數據庫的類 importtxt.py文件： 讀TXT文件并進行插入操作 dict.txt文件： 要操作的TXT文件mysqlpython.py文件
| from pymysql import *class Mysqlpython: def __init__(self,database,host="localhost", user="root",password="123456", charset="utf8",port=3306): self.database = database self.host = host self.user = user self.password = password self.charset = charset self.port = port # 創建數據連接和游標對象 def open(self): self.db = connect(host=self.host, user=self.user, password=self.password, port=self.port, database=self.database, charset=self.charset) self.cur = self.db.cursor() # 關閉游標對象和數據庫連接對象 def close(self): self.cur.close() self.db.close() # 執行sql命令 def zhixing(self,sql,L=[]): self.open() self.cur.execute(sql,L) self.db.commit() self.close() # 查詢功能 def all(self,sql,L=[]): self.open() self.cur.execute(sql,L) result = self.cur.fetchall() return resultif __name__ == "__main__": sqlh = Mysqlpython("dictionary") sel = "select * from user" r = sqlh.all(sel) print(r) |
importtxt.py文件
| import reimport sysfrom mysqlpython import Mysqlpythonsqlh = Mysqlpython("dictionary")def insert(data): arr = data.split() name = arr[0] description = " ".join(arr[1:]) ins = "insert into words(name,description) values(%s,%s)" sqlh.zhixing(ins,[name,description])def get_addr(): f = open('./dict.txt') lines=f.readlines() for line in lines: insert(line) f.close() return ''if __name__ =='__main__': print(get_addr()) |
dict.py文件（我復制了幾條文件）
| a indef art oneabacus n.frame with beads that slide along parallel rods, used for teaching numbers to children, and (in some countries) for countingabandon v. go away from (a person or thing or place) not intending to return; forsake; desertabandonment n. abandoningabase v. ~ oneself/sb lower oneself/sb in dignity; degrade oneself/sb ;abash to destroy the self-possession or self-confidence of:disconcertabashed adj. ~ embarrassed; ashamedabate v. make or become lessabattoir n. = slaughterhouse (slaughter) |
針對不同的分隔符修改一下正則表達式即可。全部代碼都貼上去了，直接復制修改下數據庫的配置就可以運行了。
新聞熱點
疑難解答
