Python用MySQLdb， pymssql 模組通過sshtunnel串連遠端資料庫

標籤：name   情境   adt   local   client   cli   mysqld   ssh   main   

轉載自 https://www.cnblogs.com/luyingfeng/p/6386093.html

安全起見，資料庫的訪問多半是要做限制的，所以就有一個直接的問題是，往往多數時候，在別的機器上（比如自己本地），是不能訪問資料庫的，給日常使用造成了很大不便。所以前幾天做了個需求，是希望在任何機器上都可以ssh到某台在資料庫白名單的機器上，然後訪問資料庫。

經人推薦，查詢了一個工具叫sshtunnel ，需要在你想要登入資料庫的機器上安裝。

sshtunnel的基本介紹 ： http://sshtunnel.readthedocs.io/en/latest/?badge=latest

這裡邊的圖2，正好是我們描述的情境，資料庫在遠程某台伺服器上，只能同在一起的另一台伺服器才能訪問，但是我們本地就需要ssh到這台伺服器上

 

但是這個裡邊涉及到的代碼是伺服器相關，遠端連線的是伺服器，我們需要的是遠程伺服器的資料庫，這個也一樣，只需要變更代碼中後半部分就可以了。

原代碼是下邊這樣：

 1 import paramiko 2 from sshtunnel import SSHTunnelForwarder 3  4 with SSHTunnelForwarder( 5     (REMOTE_SERVER_IP, 443), 6     ssh_username="", 7     ssh_pkey="/var/ssh/rsa_key", 8     ssh_private_key_password="secret", 9     remote_bind_address=(PRIVATE_SERVER_IP, 22),10     local_bind_address=(‘0.0.0.0‘, 10022)11 ) as tunnel:12     client = paramiko.SSHClient()13     client.load_system_host_keys()14     client.set_missing_host_key_policy(paramko.AutoAddPolicy())15     client.connect(‘127.0.0.1‘, 10022)16     # do some operations with client session17     client.close()18 19 print(‘FINISH!‘)

串連mysql資料庫

串連mysql資料庫的時候，看到網上有一位程式員已經實現了： python 使用mysqldb模組通過ssh隧道串連mysql

代碼如下：

 1 import MySQLdb 2 from sshtunnel import SSHTunnelForwarder 3  4 with SSHTunnelForwarder( 5          (‘sshhost.domain.com‘, 22),    #B機器的配置 6          ssh_password="sshpasswd", 7          ssh_username="sshusername", 8          remote_bind_address=(‘mysqlhost.domain.com‘, mysql.port)) as server:  #A機器的配置 9 10     conn = MySQLdb.connect(host=‘127.0.0.1‘,              #此處必須是是127.0.0.111                            port=server.local_bind_port,12                            user=‘user‘,13                            passwd=‘password‘,14                            db=‘dbname‘)

 然後接下來的查詢什麼的，直接寫在with那裡邊，與conn對齊就可以了。

對於我這邊來說有一個問題是，因為我們對於資料庫連接這一部分，往往是在一個單獨的函數裡，與其他資料庫的查詢插入刪除更新操作往往不在一起，這樣的話，with as 有個特點就是，離開這塊範圍，對象就被銷毀掉了，別的函數裡是沒法用的，也就會出現一種情況是，串連上了，但是對象又給銷毀掉了，結果查詢的時候直接顯示這個錯誤：OperationalError: (2006, ‘MySQL server has gone away‘)， 而網上查詢這個錯誤，多半說的是因為你查詢的 sql操作的時間過長，或者是傳送的資料太大 ，但是我這個地方實際上就是因為出了with as 的範圍，導致串連又給關閉掉了，所以出現這樣的結果。

關於 with as ，有篇文章寫得很詳盡。理解Python中的with…as…文法

所以我把上邊那個ssh代碼改掉了，像是sshtunnel文檔裡邊圖一所對應的代碼，一樣，將SSHTunnelForwarder出來的對象賦值給server，然後啟動server，然後進行一系列操作之後，再stop掉。

本來資料庫連接我們寫成了一個單獨的函數，改了之後，直接也還放在這個函數裡就好了，替代原來的connect語句。

 1 def connect(self): 2     ‘‘‘ 3     self.client = MySQLdb.connect(host=self.server, port=self.port, user=self.user, 4                                   passwd=self.password, db=self.database, 5                                   charset=self.charset) 6     # log.info(‘Connect to MySQL Server: ‘ + self.server) 7     ‘‘‘ 8  9     server = SSHTunnelForwarder(10             (‘sshhost.domain.com‘, 22),  # B機器的配置11             ssh_password=‘ssh_password‘,12             ssh_username=‘ssh_username‘,13             remote_bind_address=(‘mysqlhost.domain.com‘, mysql.port)14     )15     server.start()16 17     self.client = MySQLdb.connect(host=‘127.0.0.1‘,  # 此處必須是是127.0.0.118                                   port=server.local_bind_port,19                                   user=‘username‘,20                                   passwd=‘password‘,21                                   db=‘dbname‘)

然後在進行查詢更新刪除等操作的時候，先串連一下資料庫就好了，用self.client.

串連sqlserver資料庫

跟mysql的一致，但是db那裡要注意，SQLServer的是database, 然後是pymssql.connect就可以了，但是這個地方還要說我踩過的一個坑，我寫完sqlserver之後怎麼串連都連不上資料庫，後來才發現是版本的問題，我把本地SQLServer更新了之後就可以了。感覺版本是個大坑

Python用MySQLdb， pymssql 模組通過sshtunnel串連遠端資料庫