Python with MySQLdb, pymssql module connects remote database via Sshtunnel

Reprinted from Https://www.cnblogs.com/luyingfeng/p/6386093.html

Security, access to the database is mostly limited, so there is a direct problem is that often, on other machines (such as their own local), is not able to access the database, to daily use caused great inconvenience. So a few days ago to do a demand, is that on any machine can ssh to a database on the white list of the machine, and then access the database.

Recommended by the People, query a tool called Sshtunnel, need to be installed on the machine you want to log on to the database.

Basic introduction of Sshtunnel: Http://sshtunnel.readthedocs.io/en/latest/?badge=latest

The side of the figure 2, exactly the scenario we described, the database on a remote server, can only be together with another server to access, but we need to SSH to this server locally

But the code involved in this is server-related, remote connection is the server, we need a remote server database, this is the same, only need to change the code in the second half of the.

The original code is below this:

 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 session
17     client.close()
18 
19 print(‘FINISH!‘)

Connect to MySQL Database

When you connect to the MySQL database, you see a programmer on the Internet that has implemented it: Python uses the MySQLdb module to connect to MySQL via the SSH tunnel

The code is as follows:

1 import MySQLdb
  2 from sshtunnel import SSHTunnelForwarder
  3
  4 with SSHTunnelForwarder (
  5 (‘sshhost.domain.com’, 22), #B machine configuration
  6 ssh_password = "sshpasswd",
  7 ssh_username = "sshusername",
  8 remote_bind_address = (‘mysqlhost.domain.com’, mysql.port)) as server: #A machine configuration
  9 
10 conn = MySQLdb.connect (host = ‘127.0.0.1’, #here must be 127.0.0.1
11 port = server.local_bind_port,
12 user = ‘user’,
13 passwd = ‘password’,
14 db = ‘dbname’)

Then the next query what, directly written in with there side, and Conn alignment on it.

One problem for my side is that because we are in a separate function for the database connection part, it is often not together with the query Insert delete Update operation from other databases, so with the feature of the with as is that the object is destroyed when you leave the scope. Other functions can not be used, there will be a situation is, the connection, but the object is destroyed, the results of the query directly display this error: Operationalerror: (2006, ' MySQL server has gone away '), and online query This error, mostly because you query the SQL operation is too long, or the data sent is too large, but I this place is actually because of the scope of the with AS, resulting in the connection and closed off, so the result occurs.

There is an article written in detail about with AS. Understand the with...as in Python ... Grammar

So I changed the SSH code above, like the Sshtunnel document inside the corresponding code, the same, the Sshtunnelforwarder out of the object assigned to the server, and then start the server, and then a series of operations, and then stop off.

Originally the database connection we wrote a separate function, after the change, the direct also put in this function is good, replace the original connect statement.

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 machine configuration
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’, # here must be 127.0.0.1
18 port = server.local_bind_port,
19 user = ‘username’,
20 passwd = ‘password’,
21 db = ‘dbname’) 

Then in the query update delete operations, the first connection to the database is good, with self.client.

Connect to SQL Server database

Consistent with MySQL, but DB has to be aware that SQL Server is database, Then it is pymssql.connect, but this place also said I stepped on a pit, I finished writing SQL Server after the connection is not connected to the database, later found that the version of the problem, I have updated the local SQL Server can be. The feeling version is a big pit.

Python with MySQLdb, pymssql module connects remote database via Sshtunnel