上篇文章我們成功將資料寫入,今天讓我們將學習 MySql 的其他操作.
搜尋返回的結果型態皆為tuple
import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='30days', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM PttSoftJob"
fetchone()
:取得單條查詢結果data = cursor.fetchone()
print(data)
#輸出:
#(1, '[徵才] 亞洲遊科技 - 資深前端工程師', 'https://www.ptt.cc/bbs/Soft_Job/M.1541237261.A.ED4.html', datetime.date(2018, 11, 3), datetime.datetime(2018, 11, 4, 22, 55, 30))
fetchall()
:取得所有查詢筆數data = cursor.fetchall()
print(data)
#輸出:
#((1, '[徵才] 亞洲遊科技 - 資深前端工程師', 'https://www.ptt.cc/bbs/Soft_Job/M.1541237261.A.ED4.html', datetime.date(2018, 11, 3), datetime.datetime(2018, 11, 4, 22, 55, 30)), (2, '[徵才] 亞洲遊科技 - 資深後端工程師', 'https://www.ptt.cc/bbs/Soft_Job/M.1541237171.A.5A6.html', datetime.date(2018, 11, 3), datetime.datetime(2018, 11, 4, 22, 55, 30)), (3, '[徵才] TinkLabs 徵 Android Engineer', 'https://www.ptt.cc/bbs/Soft_Job/M.1541127584.A.FAC.html', datetime.date(2018, 11, 2), datetime.datetime(2018, 11, 4, 22, 55, 30)), (4, '[徵才] 高雄香港商台灣千里目-網路程式設計師', 'https://www.ptt.cc/bbs/Soft_Job/M.1541127580.A.331.html', datetime.date(2018, 11, 2), datetime.datetime(2018, 11, 4, 22, 55, 30)), (5, '[徵才] OneDegree 徵資安主管 (60~100K up/5Y)', 'https://www.ptt.cc/bbs/Soft_Job/M.1541001848.A.6A2.html', datetime.date(2018, 11, 1), datetime.datetime(2018, 11, 4, 22, 55, 30)), (6, '[徵才] 百睿達有限公司 誠徵後端工程師', 'https://www.ptt.cc/bbs/Soft_Job/M.1541049353.A.4C1.html', datetime.date(2018, 11, 1), datetime.datetime(2018, 11, 4, 22, 55, 30)), (7, '[徵才] 留學顧問公司徵前端工程師(台北)', 'https://www.ptt.cc/bbs/Soft_Job/M.1541051202.A.EA2.html', datetime.date(2018, 11, 1), datetime.datetime(2018, 11, 4, 22, 55, 30)), (8, '[徵才] H&L 代徵 Software Engineer (70K~120K+)', 'https://www.ptt.cc/bbs/Soft_Job/M.1541063366.A.BDC.html', datetime.date(2018, 11, 1), datetime.datetime(2018, 11, 4, 22, 55, 30)), (9, '[徵才] H&L 代徵 DevOps Engineer (80K~120K+)', 'https://www.ptt.cc/bbs/Soft_Job/M.1541063561.A.E88.html', datetime.date(2018, 11, 1), datetime.datetime(2018, 11, 4, 22, 55, 30)), (10, 'Fw: [徵才] 思華科技-DBA資料庫技術工程師', 'https://www.ptt.cc/bbs/Soft_Job/M.1541067017.A.5FD.html', datetime.date(2018, 11, 1), datetime.datetime(2018, 11, 4, 22, 55, 30)), (11, '[徵才] COBINHOOD 徵求前端工程師(72K~120K/mon)', 'https://www.ptt.cc/bbs/Soft_Job/M.1540950761.A.B25.html', datetime.date(2018, 10, 31), datetime.datetime(2018, 11, 4, 22, 55, 30)), (12, '[徵才] 徵Senior DevOps Engineer(90K~120K/mont', 'https://www.ptt.cc/bbs/Soft_Job/M.1540891740.A.FCB.html', datetime.date(2018, 10, 30), datetime.datetime(2018, 11, 4, 22, 55, 30)))
fetchmany(size=xx)
:指定取回查詢筆數data = cursor.fetchmany(size = 3)
print(data)
#輸出:
#((1, '[徵才] 亞洲遊科技 - 資深前端工程師', 'https://www.ptt.cc/bbs/Soft_Job/M.1541237261.A.ED4.html', datetime.date(2018, 11, 3), datetime.datetime(2018, 11, 4, 22, 55, 30)), (2, '[徵才] 亞洲遊科技 - 資深後端工程師', 'https://www.ptt.cc/bbs/Soft_Job/M.1541237171.A.5A6.html', datetime.date(2018, 11, 3), datetime.datetime(2018, 11, 4, 22, 55, 30)), (3, '[徵才] TinkLabs 徵 Android Engineer', 'https://www.ptt.cc/bbs/Soft_Job/M.1541127584.A.FAC.html', datetime.date(2018, 11, 2), datetime.datetime(2018, 11, 4, 22, 55, 30)))
基本上與新增一樣的用法,只是差別在SQL語句上:
sql = "新增、修改、刪除語法"
try:
cursor.execute(sql)
#提交
db.commit()
except:
#發生錯誤時停止執行SQL
db.rollback()
print('error')
若希望程式效能必較好使用過的東西需要關閉,上篇文章中我們有使用了db
物件的關閉,爬文有發現游標物件也能關閉,關閉方式與db
相同,但關閉後若還需要使用需重新宣告:
#關閉游標
cursor.close()
#關閉連線
db.close()
簡單的爬蟲到存入資料庫比想像中早結束,後續可能就一些特別的爬蟲狀況與資料分析模組做學習方向.
參考文章:https://www.jianshu.com/p/02cc142d950e
參考文章:https://tw.saowen.com/a/97338938002c38123dccbc8d2f0b9c9b684610994335854219713f7e1643dfc0
參考文章:https://www.yiibai.com/python/python_database_access.html
文章內容如果有錯誤歡迎留言告知,可以幫忙糾正錯誤的觀念,感謝!