python連接mysql，teradata，DB2資料庫

02-08

python語言靈活，短小精悍。資料庫是保存數據的重要載體，但結構化查詢語言SQL，表達能力較弱。如果能通過python連接資料庫，通過python腳本能實現一些自動化,批量繁雜的SQL增刪改查和數據監控等。

下面簡單的介紹一下

python連接mysql
python連接teradata
python連接DB2
使用pyodbc

本篇文章適用於Windows系統離線安裝參考，有網的情況下線直接`pip install xxx`。沒有網要搗騰很久。

python連接mysql

首先需要安裝MySQLdb模塊。

下載地址：MySQL-python

下載之後解壓進入setup.py文件目錄，cmd命令行執行如下語句安裝：

python setup.py install

在有網的環境下，直接使用命令 pip install MySQLdb 進行安裝。這裡注意 MySQLdb大小寫要嚴格區分，包括import導入模塊，不然會報錯。

在python環境下，執行import MySQLdb後沒有報錯，說明安裝成功了。

通過如下代碼說明簡單的python連接及增刪改查：

import MySQLdbconn= MySQLdb.connect( host=localhost, port = 3306, user=root, passwd=yourpaswd, db =dbname, )#建立連接cur = conn.cursor()#創建游標#創建數據表cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")#插入一條數據cur.execute("insert into student values(2,Tom,3_2class,9)")#修改查詢條件的數據cur.execute("update student set class=3_1 class where name = Tom")#刪除查詢條件的數據cur.execute("delete from student where age=9")cur.close()# 關閉游標conn.commit()# 提交事務# 有插入操作時，必須提交事務才能生效。也可以在執行多條SQL語句後提交，提高效率。conn.close()#關閉連接。

實際生產中使用最多的是查詢，因為沒有增刪改的許可權啊。

Python查詢Mysql使用下列方法獲取查詢記錄。

fetchone()：取下一個查詢結果記錄。
fetchmang(n)：返回查詢結果中n條記錄。
fetchall()：接收全部的返回結果行。
rowcount：返回執行execute()方法後影響的行數。

需要說明的是執行res = cur.execute(sel_sql_str)返回的是查詢結果記錄數。那麼可以通過如下代碼說明怎麼便利查詢的結果：

re = cur.execute(sqltext)for i in range(re): row = cur.fetchone() print rowcnt = cur.execute(sqltext)for row in fetchmane(cnt): print rowcur.execute(sqltext)for row in cur.fetchall(): print row

獲得的`row`是一個元組(tuple)，可以通過`row[i]`索引具體第幾個元素。

python連接teradata

首先需要下載驅動模塊：

teradata 15.10.0.21

這個驅動模塊適用於wPython3.4+/2.7+, Windows/Linux/Mac,32/64 bit。也不依賴其他一些庫，親測有效。

有網的情況下直接執行命令安裝：

pip install teradata

連接使用情況和連接mysql類似。具體可以參考Teradata Python Module文檔說明

文檔里也說明了怎麼設置配置文件，保存連接信息，不用每次連接都輸入connect賬戶信息。

python每次連接teradata資料庫都會保持連接會話log信息。可以在腳本目錄查看。

下面給出一個helloworld程序參考，連接teradata，根據日期查詢表tab_A中記錄數，並保持到本地。

#encoding=utf-8import teradataudaExec = teradata.UdaExec(appName=HELLOAPP, version=1.0,logConsole=False)session = udaExec.connect(method="odbc", system="192.168.0.1",username="root", password="yourpasswd")sql = "sel dt,count(1) from tab_A group by dt order by dt"f1 = open(test1.txt, w)for row in session.execute(sql): #print(str(row[0]),str(row[1]) f1.writelines([str(row[0]),:,str(row[1]), ])f1.close()

有3中遍歷訪問row方式,下面3行輸出的結果相同。

import teradataudaExec = teradata.UdaExec()with udaExec.connect("${dataSourceName}") as session:for row in session.execute("""SELECT InfoKey AS name, InfoData as val FROM DBC.DBCInfo"""): print(row[0] + ": " + row[1]) print(row["name"] + ": " + row["val"]) print(row.name + ": " + row.val)

python連接DB2

首先安裝驅動模塊：

下載ibm_db模塊，下載地址Python Package Index 解壓後得到imb_db目錄

下載db2驅動模塊：clidriver 解壓後得到clidriver目錄

將clidriver複製到ibm_db目錄後，進入ibm_db目錄，執行`python setup.py install`即可。

如果執行import ibm_db沒有報錯，就是安裝成功了。

需要注意的是ibm_db有兩種執行方式

# 1、採用立即執行的方式,不支持?佔位符,只能採取拼接字元串的方式,且字元串類型的數據一定要有單引號sql = "SELECT * FROM TAB_USER WHERE MOBILE = %s;"%"13911111111"stmt = ibm_db.exec_immediate(conn, sql)result = ibm_db.fetch_both(stmt) # 提取結果print(result)# 2、採用預編譯的方式,支持 ? 佔位符sql = "SELECT * FROM TAB_USER WHERE MOBILE= ?"sql_args = ("13911111111",)stmt = ibm_db.prepare(conn, sql) # 預編譯ibm_db.execute(stmt, sql_args) # 執行result = ibm_db.fetch_row(stmt)print(result)

下面代碼給出了一個連接資料庫，及插入表內容例子。

import ibm_db# 連接字元串connStr = "DATABASE=TEST;HOSTNAME=192.168.0.1;PORT=50000;PROTOCOL=TCPIP;UID=username;PWD=password;"conn = Nonetry: # 連接資料庫 conn = ibm_db.connect(connStr, "", "") # 關閉自動提交 ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) # 以插入語句為例,刪除和更新只需要替換語句即可 sql = "insert into tab_A values(%s, %s)" % (1, "Jet") # 執行SQL語句 stmt = ibm_db.exec_immediate(conn, sql) # 獲取受影響的行數 rows = ibm_db.num_rows(stmt) # 提交事務 ibm_db.commit(conn)except Exception as ex: # 回滾事務 ibm_db.rollback(conn)finally: # 關閉資料庫連接 ibm_db.close(conn)

ibm_db沒辦法一次提取多行,提取多行數據,需要循環提取。提取查詢結果有四種方式:

# 1.先調用fetch_row(stmt),有結果返回true否則返回false,再調用ibm_db.result(stmt, col_idx)提取結果ibm_db.fetch_row(stmt)result = ibm_db.result(stmt, 0)#2.調用fetch_both(stmt)提取一行,返回的結果是一個字典,取值可以用列名或索引 result["MOBILE"]或 result[0]result = ibm_db.fetch_both(stmt)3.調用fetch_assoc(stmt)提取一行,返回的是一個字典,取值只能用列明 result["MOBILE"]result = ibm_db.fetch_assoc(stmt)#4.調用fetch_tuple(stmt)提取一行,返回的是一個元組,取值只能用索引 result[0]result = ibm_db.fetch_tuple(stmt)

使用pyodbc

當然也可以使用通用的python ODBC模塊來連接資料庫，但是在沒有網的情況下，缺少依賴庫的環境下安裝失敗。pyodbc使用和MySQLdb模塊很類似。這裡只是簡單給出一個使用例子:循環列印表tab_A中記錄。

import pyodbc dsn="driver={IBM DB2 ODBC DRIVER};database=%s;hostname=%s;port=%s;protocol=tcpip;"%("bidb","99.12.74.244","50000") connStr = pyodbc.connect(dsn+"uid=wmsadm;pwd=wmsadm;"); cursor = connStr.cursor() cursor.execute("select * from tab_A") row = cursor.fetchone() while row: (col1,col2)= (row[0],row[1]) row = cursor.fetchone() print col1,col2cursor.close() connStr.close()

如有錯誤，歡迎指正。

微信公眾號：DataCode 首發，記錄數據科學的實踐中的一些總結。

歡迎關注！