[python] pymysql讀取mysql資料庫及資料庫操作

來自專欄 煲飯醬的鍋

1）：

# -*- coding:utf-8 -*-# 導入開發包import pymysql.cursors# 獲取鏈接connection = pymysql.connect(host="localhost", user="root", password="SY940705", db="wiki")try: # 獲取會話指針 with connection.cursor() as cursor: # 查詢語句 sql = "select urlname, urlhref from urls where id is not null" # 執行sql語句 conut = cursor.execute(sql) print(conut) # 查詢數據 # result = cursor.fetchall() # 查詢所有數據 # print(result) # 查詢下一行 # resultnext = cursor.fetchone() # 查詢下一行 # 得到指定大小 resultto = cursor.fetchmany(size=10) # 得到指定行數的數據 前size條 print(resultto)finally: connection.close()

2）：

# -*- coding:utf-8 -*-from urllib.request import urlopenfrom bs4 import BeautifulSoupimport reimport pymysql.cursors# 請求URL內容並把結果用utf-8編碼resp = urlopen("https://en.wikipedia.org/wiki/Main_Page").read().decode("utf-8")# 使用BeautifulSoup解析soup = BeautifulSoup(resp, "html.parser")# 獲取所有以/wiki/開頭的a標籤的href屬性listUrls = soup.findAll("a", href=re.compile(r"^/wiki/"))# 輸出所有的詞條對應的名稱和urlfor url in listUrls: # 過濾URL的文字和對應的鏈接 # string 只能獲取一個 get_text()獲取標籤下所有的文字 if not re.search(".(jpg|JPG|gif)$", url[href]): print(url.get_text(), "----", "https://en.wikipedia.org" + url[href]) # 獲取資料庫連接 connect = pymysql.connect(host="localhost", user="root", password="SY940705", db="wiki") try: # 獲取會話指針 with connect.cursor() as cursor: # 創建sql語句 sql = "INSERT INTO `urls`(`urlname`, `urlhref`) VALUES (%s,%s)" # 執行sql語句 cursor.execute(sql, (url.get_text(), "https://en.wikipedia.org" + url[href])) # 提交 connect.commit() # 關閉指針 cursor.close() finally: # 關閉資料庫 connect.close()

推薦閱讀：