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 bugIt is recommended to use this method when passing in parameters
cursor.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!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

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

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



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 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 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.

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.

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

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

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 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.
