記錄一次從txt文件導(dǎo)入數(shù)據(jù)的python下的MySQL實(shí)現(xiàn)

 1 #!/usr/bin/env python
 2 # encoding: utf-8
 3 
 4 
 5 """
 6 @version: ??
 7 @author: elijahxb
 8 @contact: elijahxb@163.com
 9 @site: 
10 @software: PyCharm Community Edition
11 @file: main.py
12 @time: 2017/7/8 23:47
13 """
14 import MySQLdb
15 import os
16 #import re
17 
18 Conn_IP = '127.0.0.1'
19 Conn_UserName = 'root'
20 Conn_PassWord = '11111111'
21 Conn_database = 'qqdata'
22 Conn_Table = 'login'
23 Conn_Port = 3306
24 
25 importpath = u"""D:QQ數(shù)據(jù)庫(kù)""".encode("gbk")
26 pattern = "[0-9,a-z,A-Z]{4,12}"
27 sumlist = []
28 def gett(path):
29 filedata = []
30 onedata = []
31 filelist = os.listdir(path)
32 for file in filelist:
33 print "處理文件中... ->" + file
34 with open(os.path.join(path,file),'r') as fh:
35 lines = fh.readlines()
36 for index,line in enumerate(lines):
37 print "正在處理第{0}行數(shù)據(jù)，進(jìn)度{0}/{1},【{2}】".format(index,len(lines),str(float("%0.2f"%(float(index)/len(lines)))*100) + "%")
38 if len(line) < 14:
39 continue
40 elif '"""' in line:
41 line = line.split('"""')[1]
42 text_l = line.split(" ")
43 username = text_l[0]
44 passwd = text_l[1].split("
")[0]
45 if len(username) < 4 or len(passwd) < 4:
46 continue
47 onedata.append(username)
48 onedata.append("'" + passwd + "'")
49 filedata.append(tuple(onedata))
50 onedata = []
51 filedata = list(set(filedata))#清除一個(gè)文件里面的所有重復(fù)項(xiàng)
52 sumlist.append(tuple(filedata))
53 return sumlist
54 
55 
56 
57 conn = MySQLdb.Connect(host = Conn_IP,
58 user = Conn_UserName,
59 passwd = Conn_PassWord,
60 db = Conn_database,
61 port = Conn_Port
62 )
63 cur = conn.cursor()
64 cur.execute("use qqdata")
65 cur.execute("truncate table login")
66 sqlcmd = "insert into login (QQ,PWD) values(%s,%s)"
67 t = gett(importpath)
68 for singlefiledata in t:
69 cur.executemany(sqlcmd,singlefiledata)
70 cur.close()
71 conn.close()