Home >Backend Development >Python Tutorial >Record the MySQL implementation under python that imports data from a txt file once

Record the MySQL implementation under python that imports data from a txt file once

PHP中文网
PHP中文网Original
2017-07-09 18:13:161316browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn