# <font face='consolas'><font size=5 color=#000080><center>**Sqlalchemy在Windows上連接MySQL及讀寫中文**</center></font>
難度：★★★專家
這篇心得報告討論的是Python利用sqlalchemy在Windows上連接MySQL以及讀寫中文資料的步驟和遇到的問題。
:::info
本文只涉及連線和資料讀寫，至於sqlalchemy的「物件關係對映」(Object Relational Mapping, ORM)功能，並不在探討範圍。所以下面的Python程式只單純執行SQL語法，沒有應用sqlalchemy的虛擬物件技術。
:::
* 測試日期：2017年10月下旬
* 測試環境：
* Windows 7
cpu: AMD A8-3870 3.0GHz, Win7 64bit繁體中文版 + cPython3.6.3 64bit + MySQL5.7.17-log Community
* Windows 10
cpu: Intel Core i5-5200U 2.20GHz, Win10繁體中文版 + cPython3.6.2 64bit + MySQL5.7.17-log Community
## 安裝
用Python的標準安裝程式pip：
> pip install sqlalchemy
>
以下分別說明本人的Win7及Win10電腦安裝sqlalchemy情形。
### Windows 7
Win7一開始安裝sqlalchemy時有error，裝不成功：
目前找到的解決方法是：進入錯誤訊息顯示的那個路徑(應該就是當初安裝Python的路徑)，我的Win7桌機是D:\Program Files\Python36\Lib\site-packages\pip\compat。找出裡面的__init__.py檔案，修改其內容。當然，修改前要先備份原檔。萬一改錯或修改後有甚麼更麻煩的後果，就將備份檔案蓋回去。
要改的是第75行，原來的：
> `return s.decode('utf_8')`
>
改為：
> `return s.decode('cp950')`
>
修改後再pip install sqlalchemy就成功了。
不過我要再強調一次，這種修改Python設定檔的方式，真的不知道會不會有副作用。
### Windows 10
在本人Win10筆電安裝sqlalchemy，一次順利過關:smiley:，不用像Win7那樣得修改__init__.py才行。
---
## 連線
安裝成功後，請別急著跑程式。如果現在就跑，在create_engine()試圖和資料庫連線時，會有類似下圖的execption出現：
註：以上是Win7的情形，Win10連線資料庫則OK，Insert英文也沒問題，但Insert中文資料出現execption。這現象我稍後會加以解釋。
據我目前的粗淺認知，只安裝sqlalchemy是不行的。sqlalchemy其實是個SQL工具包以及ORM，而非database的driver，本身並無連接db的功能。要連線還需要一個能和它配合的database driver。現在缺的就是這個底層的driver，也就是[sqlalchemy官網]上所說的DBAPI。官網在我測試期間列出了8個它支援的DBAPI's(或者說有8個drivers支援sqlalchemy)。這8個DBAPI's我一一測試過，初步發現如下：
:::info
DBAPI測試的結果，Win7/Win10完全相同。
:::
其中5個安裝失敗，3個安裝成功。安裝失敗者可能是不支援Python3，或者沒有pip install正確的driver名稱，也可能該API不能用pip安裝。成功的3個分別是：
* PyODBC
可順利create_engine()連線，但Insert/Update中文或英文資料時都有error("找不到資料來源名稱且未指定預設的驅動程式...")。有可能得在Windows上設定ODBC。這方面我並不熟悉，只好暫時放棄，待日後有空時再研究。
* PyMySQL
PyMySQL網上提到的比較多。create_engine()連資料庫時可以通過，但有warning。這個warning用盡方法都拿不掉。
而且在Insert中文時產生’latin-1’ codec can’t encode characters…的error。須修改其中一個connections.py檔的內容，將所有的latin1改為utf8才行。
結論是這個driver勉強可用，但並不理想。
* CyMySQL
連線及Insert中文都沒有任何errer或warning。目前為止最理想的driver。會持續使用，看有無其他問題。
前面提到我的Win10筆電沒有安裝driver，只有sqlalchemy就可以和db連線，而且Insert英文也沒問題，原因應該是這樣的：[sqlalchemy官網上的文件說明得很清楚]，在和MySQL連線時，如果僅寫create_engine("<font color=red>mysql</font>://scott:tiger@localhost/foo")，沒有宣告DBAPI，MySQL就會使用mysql-python(mysqldb)作為其預設的DBAPI，即"<font color=red>mysql+mysqldb</font>"。這個mysqldb在Insert英文資料時OK，中文的話還是碰到編碼問題。而我的Win10筆電之前不知道在甚麼時候(也許在安裝 sqlalchemy，甚至Python時)，就已暗中自動裝上mysql-python，所以看起來像單有sqlalchemy就能連線，事實不然。
下面的程式就是以sqlalchemy搭配CyMySQL(模組正式名稱是小寫的cymysql)來作示範。從安裝cymysql開始。
安裝cymysql：
> pip install cymysql
>
下一節測試查詢、修改、新增、刪除等資料庫常用操作，也就是Select, Update, Insert, Delete四個指令。特別注意中文資料有無問題。
---
## SQL常用作業測試
程式執行前的資料表Test只有一筆，ID是1。
執行以下Insert及Update程式碼：
```python=
import sqlalchemy
# cymysql
engine = sqlalchemy.create_engine('mysql+cymysql://root:xxx@localhost/Wenvies',
encoding='utf8')
name = '帝女花之香夭'
engine.execute("Insert into Test(Name, Year) values('" + name + "', 1957);")
name = '紫釵記之劍合釵圓'
engine.execute("Insert into Test(Name, Year) values('" + name + "', 1957);")
engine.execute("Update Test set Name='牡丹亭驚夢之幽媾', Year=1956 where ID=1;")
```
程式執行後的資料表多了兩筆，且ID=1這筆內容有修改。
接著測試查詢。用的當然是Select query：
```python=
import sqlalchemy
NAME_LEN = 25
# cymysql
engine = sqlalchemy.create_engine('mysql+cymysql://root:xxx@localhost/Wenvies',
encoding='utf8')
records = engine.execute("Select * from Test;")
for record in records:
# 轉成cp950後取出長度，目的是顯示時欄位對齊。
recordLen = len(record['Name'].encode('cp950'))
padLen = NAME_LEN - recordLen
myStr = "ID: {:<5}Name: {}Year: {}"
print(myStr.format(record['ID'], record['Name'] + ' '*padLen, record['Year']))
print()
```
程式執行結果(table在上次Insert/Update後又新增了幾筆資料)：
利用MySQL的Workbench驗證：
證明Python程式可正確執行Select query。
最後測試Delete。
```python=
import sqlalchemy
NAME_LEN = 25
# cymysql
engine = sqlalchemy.create_engine('mysql+cymysql://root:xxx@localhost/Wenvies',
encoding='utf8')
engine.execute("Delete from Test where id = 4;")
```
在Delete後程式再用Select查詢，第4筆(ID=4那筆)確有刪除。
以MySQL的Workbench驗證：
第4筆果然刪除了。
:::info
以上的資料庫操作結果，我在Win7和Win10都測過，結果是相同的。
:::
---
## 結論
<font color=#E63995>**sqlalchemy+cymysql可成功連接MySQL，Select, Insert, Update, Delete指令均能正確執行，目前未遭遇中文編碼困擾。Win7和Win10都OK。測試日期2017年10月下旬。**</font>
---
## 設定資料庫編碼
另外，為確保成功Insert中文且無亂碼，在MySQL上也得設定utf8編碼。程序是：
* 找出並修改MySQL的configuration檔my.ini(相當於Linux的my.cnf)。在我的電腦是放在c:\ProgramData\MySQL\MySQL Server 5.7目錄中。這是一般文字檔，用記事本或Sublime Text等editors打開就行。Win10須「以系統管理員身分執行」，否則修改後可能無法存檔。
* 開啟檔案後找到[client]及[mysqld]這兩個sections，加入下列設定:
```xml=
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
init_connect='SET NAMES utf8'
```
* 加完後存檔。再進到MySQL的console模式，打指令：
1. 檢查該資料庫是不是都用utf8編碼：
```xml=
use {database};
show variables like 'collation_%';
show variables like 'char%';
show session variables like '%collation_connection%';
status;
```
2. 如果不是，請執行以下指令：
```xml=
alter database {database} default character set utf8 collate=utf8_general_ci;
```
再執行以上的步驟1，檢查是否都用utf8編碼。
\
\
[sqlalchemy tutorial](http://docs.sqlalchemy.org/en/latest/orm/tutorial.html)
[sqlalchemy官網]:http://docs.sqlalchemy.org/en/latest/dialects/mysql.html "官網上列出目前支援的所有MySQL drivers，即sqlalchemy稱的DBAPI Support。"
[sqlalchemy官網上的文件說明得很清楚]:http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls "sqlalchemy官網上的文件對MySQL及其他dbms預設DBAPI的說明。"
###### tags: `sqlalchemy` `MySQL` `cymysql` `pymysql` `SQL` `Insert` `Update` `Select` `Delete`
<style type="text/css">
/* @import url('https://fonts.googleapis.com/css?family=Inconsolata&display=swap');
*/
body {
}
.deepRed{
color: #8B0000;
}
.magentaBold {
color: #FF00FF;
font-weight: bold;
}
#b {
color: #00008B;
}
#r {
color: #8B0000;
}
.b {
color: #00008B;
}
#dk {
color: #1E90FF;
}
.t {
color: #FF6347;
font-size: 110%;
font-weight: bold;
}
.r {
color: red;
font-weight: bold;
}
#cg120 {
color: #008248;
font-weight: bold;
font-size: 120%;
}
.classA {
color: #66E141;
font-weight: bold;
font-size: 130%;
}
.classB {
color: #41C6E1;
font-weight: bold;
font-size: 130%;
}
.classC {
color: #E1B141;
font-weight: bold;
font-size: 130%;
}
.highlight {
color: red;
font-weight: bold;
font-size: 120%;
}
.PK {
color: #E6005C;
font-weight: bold;
font-size: 115%;
}
.FK {
color: #FF8C69;
font-weight: bold;
}
.dot {
color: #DAA520; //#8B4513;
font-weight: bold;
font-size: 125%;
}
.smalldot {
color: #4CDA21;
font-weight: bold;
font-size: 125%;
}
.hi {
color: #E6005C;
font-weight: bold;
//font-size: 115%;
}
.tableTitle {
color: #B22222; //000080
font-weight: bold;
text-align: center;
}
.HeYeLv {
color: #1A6840;
// #008248;
}
.YingWuLv {
color: #5BAE23;
}
.ShenZhuYue {
color: #3C69A6;
}
.HaiQing {
color: #22A2C3;
}
.lightRed {
color: #DC143C;
font-weight: bold;
}
.Bloody {
color: #7E2723;
}
.bloody180 {
color: #7E2723;
//A63732;
font-weight: bold;
font-size: 180%;
}
.Fei {
color: #D15B3A;
}
.DaHong {
color: #FF2121;
}
.QiuHaiTangHong {
color: #EC2B24;
}
.CuiLv {
color: #20A162;
}
.MuGuaHuang {
color: #F9C116;
}
.JieHuang {
color: #D9A40E;
}
.CangHuang {
color: #806332;
}
.ChiHong {
color: #C3272B;
}
.Red {
color: #FF0000;
}
.Magenta {
color: #FF00FF;
}
.lightBack {
background-color: #FFFF99;
}
.beginner {
/* color: #008248; */
color: #20A162;
font-weight: bold;
}
.adv {
/* color: red; */
/* color: #D9A40E; */
color: #FF4C00;
font-weight: bold;
}
.pro {
/* color: #A63732; */
color: #801DAE;
font-weight: bold;
}
.fonts {
font-family: Inconsolata, Consolas, Ubuntu Mono, mononoki, Iosevka, Fantasque Sans Mono, agave, Fira Code, ProggyVector, Office Code Pro, Menlo, monospace, 華康細明體, 細明體, 微軟正黑體, 華康中圓體;
}
.font-Ubuntu {
font-family: Ubuntu Mono, Inconsolata, Iosevka, Fantasque Sans Mono, mononoki, agave, Fira Code, ProggyVector, Office Code Pro, Menlo, Consolas, monospace, 華康中圓體, 微軟正黑體, 文鼎新中黑, 華康中黑體, 華康細明體, 細明體;
}
.titleLev1 {
color: #2A2C4B;
font-family: Ubuntu Mono, Inconsolata, Iosevka, Fantasque Sans Mono, mononoki, agave, Fira Code, ProggyVector, Office Code Pro, Menlo, Consolas, monospace, 華康中圓體, 微軟正黑體, 文鼎新中黑, 華康中黑體, 華康細明體, 細明體;
font-weight: bold;
font-size: 110%;
text-align: center;
}
.titleLev2 {
color: #B54C3F;
font-family: Ubuntu Mono, Inconsolata, Iosevka, Fantasque Sans Mono, mononoki, agave, Fira Code, ProggyVector, Office Code Pro, Menlo, Consolas, monospace, 華康中圓體, 微軟正黑體, 文鼎新中黑, 華康中黑體, 華康細明體, 細明體;
font-weight: bold;
font-size: 120%;
}
.titleLev3 {
color: #22A2C3;
font-family: Ubuntu Mono, agave, Inconsolata, mononoki, Iosevka, Fantasque Sans Mono, Fira Code, Menlo, 華康中圓體, 微軟正黑體, 華康細明體, 細明體;
font-weight: bold;
font-size: 115%;
}
.titleLev4 {
color: #7E5E83;
font-family: Ubuntu Mono, Inconsolata, Iosevka, Fantasque Sans Mono, mononoki, agave, Fira Code, ProggyVector, Office Code Pro, Menlo, Consolas, monospace, 華康中圓體, 微軟正黑體, 文鼎新中黑, 華康中黑體, 華康細明體, 細明體;
font-weight: bold;
font-size: 110%;
}
.titleLev5 {
color: #207F4C;
font-family: Ubuntu Mono, Inconsolata, Iosevka, Fantasque Sans Mono, mononoki, agave, Fira Code, ProggyVector, Office Code Pro, Menlo, Consolas, monospace, 華康中圓體, 微軟正黑體, 文鼎新中黑, 華康中黑體, 華康細明體, 細明體;
font-weight: bold;
font-size: 108%;
}
.titleLev6 {
/* color: #5E878A; */
color: #C89B40;
font-family: Ubuntu Mono, Inconsolata, Iosevka, Fantasque Sans Mono, mononoki, agave, Fira Code, ProggyVector, Office Code Pro, Menlo, Consolas, monospace, 華康中圓體, 微軟正黑體, 文鼎新中黑, 華康中黑體, 華康細明體, 細明體;
font-weight: bold;
font-size: 108%;
}
.Walrus {
color: #FF2121;
font-family: Consolas, Ubuntu Mono, mononoki, Consolas, 細明體 ;
font-size: 1500%;
font-weight: bold;
}
.blockquote {
color: #1BA1E2;
font-size: 100%;
line-height: 18px;
}
.great {
color: #E50000;
/* font-weight: bold; */
}
.awful {
color: #008989;
/* font-weight: bold; */
}
.QA {
color: #1C3A90;
}
.myImg {
margin-left: auto;
margin-right:auto;
display: block;
}
.font500 {
font-weight: bold;
font-size: 500%;
}
.font300 {
font-weight: bold;
font-size: 300%;
}
.font200 {
font-weight: bold;
font-size: 200%;
}
.font150 {
font-weight: bold;
font-size: 150%;
}
.font130 {
font-weight: bold;
font-size: 130%;
}
.font125 {
font-weight: bold;
font-size: 125%;
}
.font120 {
font-weight: bold;
font-size: 120%;
}
.font115 {
font-size: 115%;
}
.font110 {
font-size: 110%;
}
.font108 {
font-size: 108%;
}
.font105 {
font-size: 105%;
}
.font100 {
font-size: 100%;
}
.font96 {
font-size: 96%;
}
.font94 {
font-size: 94%;
}
.font90 {
font-size: 90%;
}
.font88 {
font-size: 88%;
}
.font86 {
font-size: 86%;
}
.font82 {
font-size: 82%;
}
.font80 {
font-size: 80%;
}
.font75 {
font-size: 75%;
}
.font70 {
font-size: 70%;
}
.font65 {
font-size: 65%;
}
.font60 {
font-size: 60%;
}
.font50 {
font-size: 50%;
}
.trim {
margin-left: 0px;
margin-right: 0px;
broder: 0px;
padding: 0px;
}
.bold {
font-weight: bold;
}
.blur {
opacity: 0.20;
}
.statement {
color: #22A2C3;
}
.func {
color: #806332;
}
.str {
color: #EC2B24;
}
.filename {
color: #1A6840;
}
.codeFont {
font-size: 110%;
font-weight: bold;
}
.hiBold {
font-size: 110%;
font-weight: bold;
color: #EC2B24;
}
.markdown-body code { /* inline code */
font-family: Inconsolata, Consolas, Ubuntu Mono, Iosevka, Fantasque Sans Mono, mononoki, agave, Fira Code, ProggyVector, Menlo, Inconsolata, monospace;
font-size: 100%;
/* background-color: #EEEEEE; */
background-color: #E4E4E4;
}
.markdown-body pre code { /* code block */
font-family: Inconsolata, Consolas, Ubuntu Mono, Iosevka, Fantasque Sans Mono, mononoki, agave, Fira Code, ProggyVector, Menlo, Inconsolata, monospace;
font-size: 110%;
line-height: 16px;
}
.markdown-body {
max-width: 1500px;
}
.CodeMirror {
font-family: Inconsolata !important;
}
.borderlessTable {
border: 0;
}
.markdown-body table th, .markdown-body table td {
padding: 2px 3px 2px 5px !important;
// margin: 20px 30px 50px 10px !important;
border: 2px solid #ddd;
}
ul {
display: block;
/* list-style-type: disc; */
margin-block-start: 0.8em;
margin-block-end: 0.8em;
margin-inline-start: 0px;
margin-inline-end: 0px;
padding-inline-start: 15px;
padding: 0px;
}
.markdown-body ul, .markdown-body ol {
padding-left: 1em !important;
margin: 0 0 0 0;
}
</style>
Sign in with Wallet
Connect another wallet