【嘔心總結】python如何與mysql實現交互及常用sql語句

9 月初，我對 python 爬蟲 燃起興趣，但爬取到的數據多通道實時同步讀寫用文件並不方便，於是開始用起mysql。這篇筆記，我將整理近一個月的實戰中最常用到的 mysql 語句，同時也將涉及到如何在python3中與 mysql 實現數據交換。

關於工具/庫，特別說明下：

1、我安裝了 mysql ，並直接採用管理員身份運行命令行提示符（cmd）查看 mysql，並沒有安裝任何 mysql 的可視化圖形界面工具。

2、在 python 腳本中，我採用 pymysql 和 sqlalchemy 這兩個庫與 mysql 建立連接，用 pandas 來處理數據。

一、建立連接與數據交互

與 mysql 交互的方式，我目前共使用 4 種。其中採用管理員身份運行命令行提示符（cmd）查看 mysql，其操作圖示可另寫一篇。這裡就不占篇幅了。mysql的可視化圖形界面工具，我目前並沒有用到，也沒有迫切使用它的需要。另外 3 種方式都是通過 python 腳本進行。

情境A：python 演算得出數據，想要寫入資料庫

python 腳本已得到表格類大量數據，想要一次性寫入資料庫，常用代碼如下：

import pandas as pd
# 與 mysql 建立連接
from sqlalchemy import create_engine
conn_eng = create_engine('mysql+pymysql://username:password@localhost:3306/databasename',encoding='utf8')  

# 調用 pandas 的方法，數據寫入mysql
pd.io.sql.to_sql(your_df, "table_name", conn_eng, if_exists='append',index=False)

表格類數據，我用的是 pandas 的 dataframe 結構。pd.io.sql.to_sql() 的參數還有許多其它用途，但上面這種是我個人使用最高頻的。效果是：無需自己提前建表，將自動建新表。美中不足是：表的列屬性自動生成，通常不合心意，還需檢查和修改。

如果不想用 pd.io.sql.to_sql() 或者想更精細、複雜的操作，則用到下麵的情境C。

情境B：python 腳本想從 mysql 拿到數據

如果已經存在某個表格，想要向該表格提交某條指令，需返回數據，我用的是 pandas的read_sql () ，返回的數據類型是 pandas 的 dataframe。sql 查詢語句挺好寫的，具體總結在本文下方。

import pymysql
# 與 mysql 建立連接
conn = pymysql.connect('localhost','username','password','databasename')
# sql 語句定義為一個字元串
sql_search = 'select question_id from topic_monitor where is_title=0 ;'
# 調用 pandas 的 read_sql() 方法拿到 dataframe 結構的數據
question_ids = pd.read_sql(sql_search,conn)
# 關閉連接
conn.close()

情境C：python 腳本單方面向 mysql 發出指令，無需拿到數據

如果已經存在某個表格，想要向該表格提交某條指令而無需返回數據時，比如：建表、對數據的增改刪、對列的名稱、列的屬性修改等，代碼如下。

import pymysql
# 與 mysql 建立連接
conn = pymysql.connect('localhost','username','password','databasename')
cursor = conn.cursor()
# sql 語句定義為一個字元串，插入一行數據
sql_insert = 'INSERT INTO questions(q_id,q_title,q_description,q_keywords,q_people,q_pageview,time) VALUES( "'\
                + str(quesition_id) + '", "' + str(one[0])+ '", "' + str(one[1]) + '", "' + str(one[2]) + '", "' \
                + str(one[3]) + '", "' + str(one[4]) + '", "' + str(datetime.datetime.now()) + '");' 
# sql 語句定義為一個字元串，修改某個數據（另一個表格）
sql_update = 'update topic_monitor SET is_title="1" where question_id = "' + str(quesition_id) + '";'
# 提交指令
cursor.execute(sql_insert)
cursor.execute(sql_update)
conn.commit()

# 插入一行數據；僅當該數據與表格已有數據不重覆時才插入，否則就不會插入
sql_insert = 'INSERT INTO `topic_monitor`(question_id,is_title,q_type,topic_id,time) SELECT "'\
                    + x[0] + '", "0", "0","'  + str(topic_id) + '", "'+ str(now) + '" FROM DUAL WHERE NOT EXISTS(\
                    SELECT question_id FROM topic_monitor WHERE question_id = "' + x[0] + '")'
cursor.execute(sql_insert)
conn.commit()

# 關閉連接
cursor.close()
conn.close()

通過上面幾種實用情況可以看到，python 與 mysql 實現交互的過程，通常分為：建立連接、把sql語句定義為字元串，提交指令、關閉連接。核心的技能在於 sql語句；除了定義sql語句字元串，其餘3個處理都是固定的寫法。

我在最初一個月的實踐中，最常出現的錯誤有：

• 值的引用沒有加上引號；
• 符號錯亂：多一個符號，少一個符號；
• 值的類型不符合：不管 mysql 表格中該值是數，還是文本，在定義 sql 語句的字元串時，對每個值都需要轉化為字元串；
• 拷貝自己的代碼時，忘記修改databasename。

二、sql語句：搜索查詢

搜索是指在資料庫的某個表格中查詢符合特定條件的數據，並返回查詢結果。其基本結構為：

SELECT 【範圍】FROM table_name 【條件】; 其中，範圍是必須指定的，而條件可有可無。

變數A：範圍，是指返回查詢結果的範圍。

返回該表格的所有欄位，用 * 表達：

SELECT * FROM table_name ;

僅返回該表格的某個欄位：

SELECT column_name FROM table_name ;

僅返回該表格的多個欄位：

SELECT column_name_1,column_name_3,column_name_3 FROM table_name ;

僅返回符合條件的數據個數：

SELECT count(*) FROM table_name ;

變數B：條件是指，期望返回的數據滿足哪些條件。

不限定條件：

SELECT * FROM table_name ;

數值類：某個欄位（數值類型的，比如double或者int），數值比較的操作符都可以使用比如，大於>，小於<，等於 = ，大於等於 >= ，小於等於 <= ：

SELECT * FROM table_name WHERE num_column_name >= 1;

文本類：某個欄位（字元串類型的，比如char，text）：

SELECT * FROM table_name WHERE str_column_name like “%your_str%”;

也可以表達多個條件，and，or等可用於表達條件之間的關係：

SELECT * FROM table_name WHERE num_column_name_1 >= 1 and  str_column_name like “%your_str%” ;

三、sql語句：修改表屬性

橫向的一整條數據，叫做行；豎向的一整條數據，叫作列。列的名字，叫做 column，這是通用的知識點。

這段時間的實戰中，我完全沒有用到修改表的名稱、重設index等知識點。最常用的，就是對列進行操作。每個列具備：列的名稱、列的屬性、列的數值。

列的名稱，需要留心不使用保留詞。我的技巧是，儘量用一些_來表達該數據，比如 article_title，press_date 這種命名雖然稍長，但易讀，也不會裝上保留詞。

列的屬性包括：類型，最大長度，是否為空，預設值，是否重覆，是否為索引。通常，直接通過 pandas 的 pd.io.sql.to_sql() 一次性創建表格並保存數據時，列的預設屬性並不合需求。要麼提前自己定義表的結構，設置好每列屬性；要麼事後檢查列屬性，並逐列修改。所以，列的屬性設定、修改是高頻基礎知識點。

列的數值，即除了列名稱外的、該列其它值。修改某個值，也是高頻操作。不過我把這個知識點放到第四部分了。

對列的名稱、列的屬性進行修改，主要的關鍵詞都是 ALTER，具體又分為以下幾種情況。

情境A：新增一列。關鍵詞 ADD

在你所指定的 column_name 後面定義列的屬性。

ALTER TABLE table_name ADD COLUMN column_name char(20);

情境B：修改某列的名稱。關鍵詞 CHANGE

在修改列名的同時也可以重新指定列的屬性。

ALTER TABLE table_name CHANGE old_column_name new_column_name char(50);

情境C：修改某列的屬性。關鍵詞是 MODIFY

ALTER TABLE table_name MODIFY column_name char(100);

四、sql語句：數據的增改刪

通常提到資料庫操作時，四字以蔽之：增刪改查。

• 查詢，請看第二部分。關鍵詞是 SELECT。
• 對數據所依賴的屬性的增、改，請看第三部分。關鍵詞是 ALTER。
• 數據的增加，在第一部分的數據交互中也給出實例，就不重覆了。關鍵詞是INSERT。
• 數據的修改，關鍵詞是 UPDATE。
• 數據（甚至表格、庫）的刪除，關鍵詞是DELETE。

數據的修改，副關鍵詞是 set 。

UPDATE table_name SET columns_name = new_value 【條件】;

新數值如果是數值類型的，則直接寫數值即可；如果是文本類型的，必須要加上雙引號，比如，“your_new_value”。

如果把【條件】部分不寫，就相當於修改整列的值；想要修改特定範圍，就要用到條件表達式，這和前面的查詢部分是一致的，就不再重覆。

數據的刪除，對於新手來說，是必須警惕的操作。因為一旦誤操作，你將無力輓回。即便是職業程式員，也可能犯下無疑刪庫的慘劇。其基本語句為：

DELETE FROM table_name【條件】;

想要修改特定範圍，就要用到條件表達式，這和前面的查詢部分也是一致的，稍微啰嗦兩句：不要對自己設定的條件太自信，最好先用搜索語句檢查一下，然後再執行刪除語句。

• 刪除單行數據：添加能唯一標識該行數據的條件語句。
• 刪除多行數據：添加能標識該範圍的條件語句。
• 刪除整張表格：你是認真的嗎？沒有寫錯表格名字吧？！ 做這項操作前，必須確認清楚自己的意圖，畢竟一旦發生，無可輓回。

如果條件留空，將保留表結構，而刪除所有數據行。想要刪除整張表格，什麼都不留下，則執行：

DELETE TABLE table_name;

俗稱的“刪庫”就是刪掉整個資料庫，雖然實戰中幾乎不會用到，但作為新手經常手誤，在練習階段安全起見，最好還是專門創建一個 database 用於練手，練完直接刪掉整個練習庫：

DELETE DATABASE database_name;

如果簡單總結下過去一個月，使用mysql的體驗，那就是：除了mysql 的安裝激活太麻煩，數據的增刪改查比操作文本方便太多了！！完全值得容忍安裝激活的麻煩。另外 mysql 常用語法確實簡單、非常有規律。

希望我的總結帶給你幫助。鼓勵我繼續分享，那就請點個贊吧！勘誤請留言，或挪步我的github：https://github.com/liujuanjuan1984/ucanuupnobb/issues