記錄一次從txt文件導入數據的python下的MySQL實現

環境：　python2.7

　　　　ComsenzXP自帶MySQL

　　　　安裝python-MySQL模塊

數據格式：txt格式的賬號信息。

　　　　 數據一行一條數據。

　　　　難點：有的行只有賬號，沒有密碼；有的為空行；有的行首行尾有三連引號；有的空行；有的不是賬號密碼信息。

代碼實現：

 1 #!/usr/bin/env python
 2 # encoding: utf-8
 3 
 4 
 5 """
 6 @version: ??
 7 @author: elijahxb
 8 @contact: [email protected]
 9 @site: http://www.cnblogs.com/elijahxb
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資料庫""".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}行數據，進度{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("\n")[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))#清除一個文件裡面的所有重覆項
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()