background
Install support library
Connect Mysql
Custom query function
backgroundIf you need to access the remote server Mysql database , But it's time to Mysql Database for security period , Security is set to allow only local connections （ That is, you need to log in to the server to use ）, Other remote connections are not directly accessible , And the corresponding port has also been modified , Then it needs to be based on ssh To connect to the database . This method connects the database to Navicat Internal interface based on ssh Same connection .
Navicat
Connect to database
Install support libraryIf you want to connect Mysql, Installation is required first pymysql
pip install pymysqlThe installation is based on ssh The library of sshtunnel
pip install sshtunnel # Current latest 0.3.1 edition It is recommended to install the latest sshtunnel library , The old version library has some bug
Connect Mysqlbe based on ssh Connect Mysql You can see sshtunnel Documents , There are 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 Successful connection ') conn = pymysql.connect(host='127.0.0.1', port=server.local_bind_port, user='root', database='data', charset='utf8') print('mysql Database connection successful ') cursor = conn.cursor() ... # Get data operation , Omit here cursor.close() conn.close() Custom query function The above connection can be encapsulated as a function , Convenient for use elsewhere
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 Successful connection ') conn = pymysql.connect(host='127.0.0.1', port=server.local_bind_port, user='root', database='data', charset='utf8') print('mysql Database connection successful ') cursor = conn.cursor() print(' Cursor acquisition succeeded ') try: print(f' Execute query statement ：{sql} Parameters ：{args}') cursor.execute(sql,args) print(' Data query successful ') conn.commit() print(' Transaction submitted successfully ') datas = cursor.fetchall() success = True except: print(' Data query failed ') datas = None success = False print(' Closing database connection ') cursor.close() conn.close() return datas, successBe careful ：
When using databases ,conn.commit()、cursor.close()、conn.close() These must be standardized , To prevent unnecessary bug
It is recommended to use this method when passing in parameters cursor.execute(sql,args), prevent sql The risk of Injection
Relevant reference ：
Python load txt Data garbled problem upgraded solution
Python The files are packed into exe Executable program
That's all Python be based on ssh Remote connection Mysql Details of database operation , More about Python ssh Remote connection Mysql Please pay attention to other relevant articles of software development network ！
