How to connect to Mysql database remotely using Python based on ssh

Background

If you need to access the Mysql database of the remote server, but for the security period of the Mysql database, the security measures are set to only allow local connections (that is, you need to log in to the server to use it), otherwise Remote connections cannot be accessed directly, and the corresponding port has been modified, so you need to connect to the database based on ssh. The method of connecting to the database is similar to the interface connection in Navicat through SSH.

Navicat

Connect to database

Install support library

• If you want to connect to Mysql, you first need to install pymysql

pip install pymysql

• Install the ssh-based library sshtunnel

pip install sshtunnel    #当前最新 0.3.1版

It is recommended to install the latest sshtunnel library. The old version library has some bugs

Connecting to Mysql

Connecting Mysql based on ssh can check the sshtunnel documentation, which contains some cases

with SSHTunnelForwarder(
        ('192.168.1.1', 2222),
        ssh_password='123456',
        ssh_username='root',
        remote_bind_address=('127.0.0.1', 3306)) as server:
    print('SSH连接成功')
    conn = pymysql.connect(host='127.0.0.1',
                           port=server.local_bind_port,
                           user='root',
                           database='data',
                           charset='utf8')
    print('mysql数据库连接成功')
    cursor = conn.cursor()
    ...  #获取数据操作，此处省略
    cursor.close()
    conn.close()

Since Define query function

You can encapsulate the above connection into a function to facilitate use in other places

def mysql_ssh(sql,args=None):
    with SSHTunnelForwarder(
            ('192.168.1.1', 2222),
            ssh_password='123456',
            ssh_username='root',
            remote_bind_address=('127.0.0.1', 3306)) as server:
        print('SSH连接成功')
        conn = pymysql.connect(host='127.0.0.1',
                               port=server.local_bind_port,
                               user='root',
                               database='data',
                               charset='utf8')
        print('mysql数据库连接成功')
        cursor = conn.cursor()
        print('游标获取成功')
        try:
            print(f'执行查询语句：{sql}  参数：{args}')
            cursor.execute(sql,args)
            print('数据查询成功')
            conn.commit()
            print('事务提交成功')
            datas = cursor.fetchall()
            success = True
        except:
            print('数据查询失败')
            datas = None
            success = False
        print('正在关闭数据库连接')
        cursor.close()
        conn.close()
    return datas, success

Notes:

• When using the database, conn.commit(), cursor.close(), conn.close() must be used in a standardized manner to prevent inaccuracies. Necessary bug

• It is recommended to use this method when passing in parameterscursor.execute(sql,args) to prevent the risk of sql injection

The above is the detailed content of How to connect to Mysql database remotely using Python based on ssh. For more information, please follow other related articles on the PHP Chinese website!