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 AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

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)

Python: Automation, Scripting, and Task Management Python: Automation, Scripting, and Task Management Apr 16, 2025 am 12:14 AM

Python excels in automation, scripting, and task management. 1) Automation: File backup is realized through standard libraries such as os and shutil. 2) Script writing: Use the psutil library to monitor system resources. 3) Task management: Use the schedule library to schedule tasks. Python's ease of use and rich library support makes it the preferred tool in these areas.

Golang vs. Python: Concurrency and Multithreading Golang vs. Python: Concurrency and Multithreading Apr 17, 2025 am 12:20 AM

Golang is more suitable for high concurrency tasks, while Python has more advantages in flexibility. 1.Golang efficiently handles concurrency through goroutine and channel. 2. Python relies on threading and asyncio, which is affected by GIL, but provides multiple concurrency methods. The choice should be based on specific needs.

Python vs. JavaScript: The Learning Curve and Ease of Use Python vs. JavaScript: The Learning Curve and Ease of Use Apr 16, 2025 am 12:12 AM

Python is more suitable for beginners, with a smooth learning curve and concise syntax; JavaScript is suitable for front-end development, with a steep learning curve and flexible syntax. 1. Python syntax is intuitive and suitable for data science and back-end development. 2. JavaScript is flexible and widely used in front-end and server-side programming.

MySQL's Role: Databases in Web Applications MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

How to use VSCode How to use VSCode Apr 15, 2025 pm 11:21 PM

Visual Studio Code (VSCode) is a cross-platform, open source and free code editor developed by Microsoft. It is known for its lightweight, scalability and support for a wide range of programming languages. To install VSCode, please visit the official website to download and run the installer. When using VSCode, you can create new projects, edit code, debug code, navigate projects, expand VSCode, and manage settings. VSCode is available for Windows, macOS, and Linux, supports multiple programming languages ​​and provides various extensions through Marketplace. Its advantages include lightweight, scalability, extensive language support, rich features and version

What is the difference between vscode and pycharm What is the difference between vscode and pycharm Apr 15, 2025 pm 11:54 PM

The main differences between VS Code and PyCharm are: 1. Extensibility: VS Code is highly scalable and has a rich plug-in market, while PyCharm has wider functions by default; 2. Price: VS Code is free and open source, and PyCharm is paid for professional version; 3. User interface: VS Code is modern and friendly, and PyCharm is more complex; 4. Code navigation: VS Code is suitable for small projects, and PyCharm is more suitable for large projects; 5. Debugging: VS Code is basic, and PyCharm is more powerful; 6. Code refactoring: VS Code is basic, and PyCharm is richer; 7. Code

Which one is better, vscode or visual studio Which one is better, vscode or visual studio Apr 15, 2025 pm 08:36 PM

Depending on the specific needs and project size, choose the most suitable IDE: large projects (especially C#, C) and complex debugging: Visual Studio, which provides powerful debugging capabilities and perfect support for large projects. Small projects, rapid prototyping, low configuration machines: VS Code, lightweight, fast startup speed, low resource utilization, and extremely high scalability. Ultimately, by trying and experiencing VS Code and Visual Studio, you can find the best solution for you. You can even consider using both for the best results.

Golang vs. Python: Key Differences and Similarities Golang vs. Python: Key Differences and Similarities Apr 17, 2025 am 12:15 AM

Golang and Python each have their own advantages: Golang is suitable for high performance and concurrent programming, while Python is suitable for data science and web development. Golang is known for its concurrency model and efficient performance, while Python is known for its concise syntax and rich library ecosystem.

See all articles