记录一次从txt文件导入数据的python下的MySQL实现-Python教程-PHP中文网

首页

后端开发

Python教程

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

PHP中文网

Jul 09, 2017 pm 06:13 PM

导入数据文件

环境：　python2.7

　　　　ComsenzXP自带MySQL

　　　　安装python-MySQL模块

数据格式：txt格式的账号信息。

　　　　数据一行一条数据。

　　　　难点：有的行只有账号，没有密码；有的为空行；有的行首行尾有三连引号；有的空行；有的不是账号密码信息。

代码实现：

<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()

以上是记录一次从txt文件导入数据的python下的MySQL实现的详细内容。更多信息请关注PHP中文网其他相关文章！

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

Python的科学计算中如何使用阵列？Apr 25, 2025 am 12:28 AM

Arraysinpython，尤其是Vianumpy，ArecrucialInsCientificComputingfortheireftheireffertheireffertheirefferthe.1）Heasuedfornumerericalicerationalation，dataAnalysis和Machinelearning.2）Numpy'Simpy'Simpy'simplementIncressionSressirestrionsfasteroperoperoperationspasterationspasterationspasterationspasterationspasterationsthanpythonlists.3）inthanypythonlists.3）andAreseNableAblequick

您如何处理同一系统上的不同Python版本？Apr 25, 2025 am 12:24 AM

你可以通过使用pyenv、venv和Anaconda来管理不同的Python版本。1）使用pyenv管理多个Python版本：安装pyenv，设置全局和本地版本。2）使用venv创建虚拟环境以隔离项目依赖。3）使用Anaconda管理数据科学项目中的Python版本。4）保留系统Python用于系统级任务。通过这些工具和策略，你可以有效地管理不同版本的Python，确保项目顺利运行。

与标准Python阵列相比，使用Numpy数组的一些优点是什么？Apr 25, 2025 am 12:21 AM

numpyarrayshaveseveraladagesoverandastardandpythonarrays：1）基于基于duetoc的iMplation，2）2）他们的aremoremoremorymorymoremorymoremorymoremorymoremoremory，尤其是WithlargedAtasets和3）效率化，效率化，矢量化函数函数函数函数构成和稳定性构成和稳定性的操作，制造

阵列的同质性质如何影响性能？Apr 25, 2025 am 12:13 AM

数组的同质性对性能的影响是双重的：1)同质性允许编译器优化内存访问，提高性能；2)但限制了类型多样性，可能导致效率低下。总之，选择合适的数据结构至关重要。

编写可执行python脚本的最佳实践是什么？Apr 25, 2025 am 12:11 AM

到CraftCraftExecutablePythcripts，lollow TheSebestPractices：1）Addashebangline（＃！/usr/usr/bin/envpython3）tomakethescriptexecutable.2）setpermissionswithchmodwithchmod xyour_script.3）

Numpy数组与使用数组模块创建的数组有何不同？Apr 24, 2025 pm 03:53 PM

numpyArraysareAreBetterFornumericalialoperations andmulti-demensionaldata，而learthearrayModuleSutableforbasic，内存效率段

Numpy数组的使用与使用Python中的数组模块阵列相比如何？Apr 24, 2025 pm 03:49 PM

numpyArraySareAreBetterForHeAvyNumericalComputing，而lelethearRayModulesiutable-usemoblemory-connerage-inderabledsswithSimpleDatateTypes.1）NumpyArsofferVerverVerverVerverVersAtility andPerformanceForlargedForlargedAtatasetSetsAtsAndAtasEndCompleXoper.2）

CTYPES模块与Python中的数组有何关系？Apr 24, 2025 pm 03:45 PM

ctypesallowscreatingingangandmanipulatingc-stylarraysinpython.1）usectypestoInterfacewithClibrariesForperfermance.2）createc-stylec-stylec-stylarraysfornumericalcomputations.3）passarraystocfunctions foreforfunctionsforeffortions.however.however，However，HoweverofiousofmemoryManageManiverage，Pressiveo，Pressivero

See all articles