Home >Backend Development >Python Tutorial >Record the MySQL implementation under python that imports data from a txt file once
Environment: python2.7
ComsenzXP comes with MySQL
Installation of python-MySQL module
Data format: Account information in txt format.
The data is one row per row.
Difficulty: Some lines only have account numbers and no passwords; some are blank lines; some lines have triple quotation marks at the beginning and end of the line; some are empty lines; some are not account and password information.
Code:
<span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span> <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> encoding: utf-8</span> <span style="color: #008080"> 3</span> <span style="color: #008080"> 4</span> <span style="color: #008080"> 5</span> <span style="color: #800000">"""</span> <span style="color: #008080"> 6</span> <span style="color: #800000">@version: ?? </span><span style="color: #008080"> 7</span> <span style="color: #800000">@author: elijahxb </span><span style="color: #008080"> 8</span> <span style="color: #800000">@contact: elijahxb@163.com </span><span style="color: #008080"> 9</span> <span style="color: #800000">@site: </span><span style="color: #008080">10</span> <span style="color: #800000">@software: PyCharm Community Edition </span><span style="color: #008080">11</span> <span style="color: #800000">@file: main.py </span><span style="color: #008080">12</span> <span style="color: #800000">@time: 2017/7/8 23:47 </span><span style="color: #008080">13</span> <span style="color: #800000">"""</span> <span style="color: #008080">14</span> <span style="color: #0000ff">import</span><span style="color: #000000"> MySQLdb </span><span style="color: #008080">15</span> <span style="color: #0000ff">import</span><span style="color: #000000"> os </span><span style="color: #008080">16</span> #<span style="color: #0000ff">import</span><span style="color: #000000"> re </span><span style="color: #008080">17</span> <span style="color: #008080">18</span> Conn_IP = <span style="color: #800000">'</span><span style="color: #800000">127.0.0.1</span><span style="color: #800000">'</span> <span style="color: #008080">19</span> Conn_UserName = <span style="color: #800000">'</span><span style="color: #800000">root</span><span style="color: #800000">'</span> <span style="color: #008080">20</span> Conn_PassWord = <span style="color: #800000">'</span><span style="color: #800000">11111111</span><span style="color: #800000">'</span> <span style="color: #008080">21</span> Conn_database = <span style="color: #800000">'</span><span style="color: #800000">qqdata</span><span style="color: #800000">'</span> <span style="color: #008080">22</span> Conn_Table = <span style="color: #800000">'</span><span style="color: #800000">login</span><span style="color: #800000">'</span> <span style="color: #008080">23</span> Conn_Port = 3306 <span style="color: #008080">24</span> <span style="color: #008080">25</span> importpath = u<span style="color: #800000">"""</span><span style="color: #800000">D:\QQ数据库</span><span style="color: #800000">"""</span>.encode(<span style="color: #800000">"</span><span style="color: #800000">gbk</span><span style="color: #800000">"</span><span style="color: #000000">) </span><span style="color: #008080">26</span> pattern = <span style="color: #800000">"</span><span style="color: #800000">[0-9,a-z,A-Z]{4,12}</span><span style="color: #800000">"</span> <span style="color: #008080">27</span> sumlist =<span style="color: #000000"> [] </span><span style="color: #008080">28</span> <span style="color: #0000ff">def</span><span style="color: #000000"> gett(path): </span><span style="color: #008080">29</span> filedata =<span style="color: #000000"> [] </span><span style="color: #008080">30</span> onedata =<span style="color: #000000"> [] </span><span style="color: #008080">31</span> filelist =<span style="color: #000000"> os.listdir(path) </span><span style="color: #008080">32</span> <span style="color: #0000ff">for</span> file <span style="color: #0000ff">in</span><span style="color: #000000"> filelist: </span><span style="color: #008080">33</span> <span style="color: #0000ff">print</span> <span style="color: #800000">"</span><span style="color: #800000">处理文件中... -></span><span style="color: #800000">"</span> +<span style="color: #000000"> file </span><span style="color: #008080">34</span> with open(os.path.join(path,file),<span style="color: #800000">'</span><span style="color: #800000">r</span><span style="color: #800000">'</span><span style="color: #000000">) as fh: </span><span style="color: #008080">35</span> lines =<span style="color: #000000"> fh.readlines() </span><span style="color: #008080">36</span> <span style="color: #0000ff">for</span> index,line <span style="color: #0000ff">in</span><span style="color: #000000"> enumerate(lines): </span><span style="color: #008080">37</span> <span style="color: #0000ff">print</span> <span style="color: #800000">"</span><span style="color: #800000">正在处理第{0}行数据,进度{0}/{1},【{2}】</span><span style="color: #800000">"</span>.format(index,len(lines),str(float(<span style="color: #800000">"</span><span style="color: #800000">%0.2f</span><span style="color: #800000">"</span>%(float(index)/len(lines)))*100) + <span style="color: #800000">"</span><span style="color: #800000">%</span><span style="color: #800000">"</span><span style="color: #000000">) </span><span style="color: #008080">38</span> <span style="color: #0000ff">if</span> len(line) < 14<span style="color: #000000">: </span><span style="color: #008080">39</span> <span style="color: #0000ff">continue</span> <span style="color: #008080">40</span> <span style="color: #0000ff">elif</span> <span style="color: #800000">'</span><span style="color: #800000">"""</span><span style="color: #800000">'</span> <span style="color: #0000ff">in</span><span style="color: #000000"> line: </span><span style="color: #008080">41</span> line = line.split(<span style="color: #800000">'</span><span style="color: #800000">"""</span><span style="color: #800000">'</span>)[1<span style="color: #000000">] </span><span style="color: #008080">42</span> text_l = line.split(<span style="color: #800000">"</span> <span style="color: #800000">"</span><span style="color: #000000">) </span><span style="color: #008080">43</span> username =<span style="color: #000000"> text_l[0] </span><span style="color: #008080">44</span> passwd = text_l[1].split(<span style="color: #800000">"</span><span style="color: #800000">\n</span><span style="color: #800000">"</span><span style="color: #000000">)[0] </span><span style="color: #008080">45</span> <span style="color: #0000ff">if</span> len(username) < 4 <span style="color: #0000ff">or</span> len(passwd) < 4<span style="color: #000000">: </span><span style="color: #008080">46</span> <span style="color: #0000ff">continue</span> <span style="color: #008080">47</span> <span style="color: #000000"> onedata.append(username) </span><span style="color: #008080">48</span> onedata.append(<span style="color: #800000">"</span><span style="color: #800000">'</span><span style="color: #800000">"</span> + passwd + <span style="color: #800000">"</span><span style="color: #800000">'</span><span style="color: #800000">"</span><span style="color: #000000">) </span><span style="color: #008080">49</span> <span style="color: #000000"> filedata.append(tuple(onedata)) </span><span style="color: #008080">50</span> onedata =<span style="color: #000000"> [] </span><span style="color: #008080">51</span> filedata = list(set(filedata))<span style="color: #008000">#</span><span style="color: #008000">清除一个文件里面的所有重复项</span> <span style="color: #008080">52</span> <span style="color: #000000"> sumlist.append(tuple(filedata)) </span><span style="color: #008080">53</span> <span style="color: #0000ff">return</span><span style="color: #000000"> sumlist </span><span style="color: #008080">54</span> <span style="color: #008080">55</span> <span style="color: #008080">56</span> <span style="color: #008080">57</span> conn = MySQLdb.Connect(host =<span style="color: #000000"> Conn_IP, </span><span style="color: #008080">58</span> user =<span style="color: #000000"> Conn_UserName, </span><span style="color: #008080">59</span> passwd =<span style="color: #000000"> Conn_PassWord, </span><span style="color: #008080">60</span> db =<span style="color: #000000"> Conn_database, </span><span style="color: #008080">61</span> port =<span style="color: #000000"> Conn_Port </span><span style="color: #008080">62</span> <span style="color: #000000"> ) </span><span style="color: #008080">63</span> cur =<span style="color: #000000"> conn.cursor() </span><span style="color: #008080">64</span> cur.execute(<span style="color: #800000">"</span><span style="color: #800000">use qqdata</span><span style="color: #800000">"</span><span style="color: #000000">) </span><span style="color: #008080">65</span> cur.execute(<span style="color: #800000">"</span><span style="color: #800000">truncate table login</span><span style="color: #800000">"</span><span style="color: #000000">) </span><span style="color: #008080">66</span> sqlcmd = <span style="color: #800000">"</span><span style="color: #800000">insert into login (QQ,PWD) values(%s,%s)</span><span style="color: #800000">"</span> <span style="color: #008080">67</span> t =<span style="color: #000000"> gett(importpath) </span><span style="color: #008080">68</span> <span style="color: #0000ff">for</span> singlefiledata <span style="color: #0000ff">in</span><span style="color: #000000"> t: </span><span style="color: #008080">69</span> <span style="color: #000000"> cur.executemany(sqlcmd,singlefiledata) </span><span style="color: #008080">70</span> <span style="color: #000000">cur.close() </span><span style="color: #008080">71</span> conn.close()
The above is the detailed content of Record the MySQL implementation under python that imports data from a txt file once. For more information, please follow other related articles on the PHP Chinese website!