Table of Contents
Background
Install support library
Connecting to Mysql
Since Define query function
Home Database Mysql Tutorial How to connect to Mysql database remotely using Python based on ssh

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

May 27, 2023 pm 04:07 PM
mysql python 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.

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

Navicat

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

Connect to database

Install support library

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

pip install pymysql
Copy after login
  • Install the ssh-based library sshtunnel

pip install sshtunnel    #当前最新 0.3.1版
Copy after login

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()
Copy after login

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
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to download deepseek Xiaomi How to download deepseek Xiaomi Feb 19, 2025 pm 05:27 PM

How to download deepseek Xiaomi

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

How to optimize MySQL query performance in PHP?

Google AI announces Gemini 1.5 Pro and Gemma 2 for developers Google AI announces Gemini 1.5 Pro and Gemma 2 for developers Jul 01, 2024 am 07:22 AM

Google AI announces Gemini 1.5 Pro and Gemma 2 for developers

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

How to use MySQL backup and restore in PHP?

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into a MySQL table using PHP?

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

How to fix mysql_native_password not loaded errors on MySQL 8.4

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

How to use MySQL stored procedures in PHP?

How do you ask him deepseek How do you ask him deepseek Feb 19, 2025 pm 04:42 PM

How do you ask him deepseek

See all articles