記錄一次從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) :
<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) or len(passwd) :
<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