记录一次从txt文件导入数据的python下的MySQL实现

 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数据库""".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()