Record the MySQL implementation in python that imports data from a txt file at a time,

Record the MySQL implementation in python that imports data from a txt file at a time,

Environment: python2.7

ComsenzXP comes with MySQL

Install the python-MySQL Module

Data format: account information in txt format.

One row of data.

Difficulty: Some rows only have accounts, no passwords; some are empty rows; some have three quotation marks at the end of the first line; some are empty rows; some are not account password information.

 

Code implementation:

1 #! /Usr/bin/env python 2 # encoding: UTF-8 3 4 5 "6 @ version :?? 7 @ author: elijahxb 8 @ contact: elijahxb@163.com 9 @ site: http://www.cnblogs.com/elijahxb10 @ software: PyCharm Community Edition11 @ file: main. py12 @ time: 4713 23: 127 "14 import MySQLdb15 import os16 # import re17 18 Conn_IP = '2017. 0.0.1 '19 Conn_UserName = 'root' 20 Conn_PassWord = '000000' 21 Conn_database = 'qqdata' 22 Conn_Table = 'login' 23 Conn_Port = 11111111 25 importpath = u "D: \ QQ Database """. encode ("gbk") 26 pattern = "[0-9, a-z, A-Z] {}" 27 sumlist = [] 28 def gett (path ): 29 filedata = [] 30 onedata = [] 31 filelist = OS. listdir (path) 32 for file in filelist: 33 print "processing file... -> "+ file34 with open (OS. path. join (path, file), 'R') as fh: 35 lines = fh. readlines () 36 for index, line in enumerate (lines): 37 print "processing row {0} data, progress {0}/{1 }, [{2 }]". format (index, len (lines), str (float ("% 0.2f" % (float (index)/len (lines) * 100) + "% ") 38 if len (line) <14: 39 continue40 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 continue47 onedata. append (username) 48 onedata. append ("'" + passwd + "'") 49 filedata. append (tuple (onedata) 50 onedata = [] 51 filedata = list (set (filedata) # Clear all repeated items in a file 52 sumlist. append (tuple (filedata) 53 return sumlist54 55 56 57 conn = MySQLdb. connect (host = Conn_IP, 58 user = Conn_UserName, 59 passwd = Conn_PassWord, 60 db = Conn_database, 61 port = Conn_Port62) 63 cur = conn. cursor () 64 cur.exe cute ("use qqdata") 65 cur.exe cute ("truncate table login") 66 sqlcmd = "insert into login (QQ, PWD) values (% s, % s) "67 t = gett (importpath) 68 for singlefiledata in t: 69 cur.exe cute.pdf (sqlcmd, singlefiledata) 70 cur. close () 71 conn. close ()