前言
昨天写小项目的时候遇到了一个需求:把txt文档的数据导入到mysql数据库中,开始本来想直接用Mysql Workbench导入TXT文件,但是最后发现不支持TXT导入,结果我吧嗒吧嗒的去把TXT转了Excel,拿到Linux上导入的时候又发现了各种乱码问题。
抱着没有什么是程序员干不了的原则,我手写了一个Python代码直接操作文件进行导入了。结果大概一万多条的文件,导入时间大概两分钟。
下面是具体的代码:
- mysqlpython.py文件: 自定义的连接mysql数据库的类
- importtxt.py文件: 读TXT文件并进行插入操作
- dict.txt文件: 要操作的TXT文件
mysqlpython.py文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
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 result if __name__ = = "__main__" : sqlh = Mysqlpython( "dictionary" ) sel = "select * from user" r = sqlh. all (sel) print (r) |
importtxt.py文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
import re import sys from mysqlpython import Mysqlpython sqlh = 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文件(我复制了几条文件)
1
2
3
4
5
6
7
8
9
|
a indef art one abacus n.frame with beads that slide along parallel rods, used for teaching numbers to children, and ( in some countries) for counting abandon v. go away from (a person or thing or place) not intending to return ; forsake; desert abandonment n. abandoning abase v. ~ oneself / sb lower oneself / sb in dignity; degrade oneself / sb ; abash to destroy the self - possession or self - confidence of:disconcert abashed adj. ~ embarrassed; ashamed abate v. make or become less abattoir n. = slaughterhouse (slaughter) |
针对不同的分隔符修改一下正则表达式即可。全部代码都贴上去了,直接复制修改下数据库的配置就可以运行了。
总结:
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对服务器之家的支持。
原文链接:https://juejin.im/post/5be24149f265da61461da466