python · ssh · SQL | python 连接远程 SQL 数据库

python 连接本地 SQL 的 教程存档。

---

如果要连接远程的 SQL 数据库，需要先开一个 ssh 连接，在 ssh 连接里写 pymysql 的 connect 代码。

代码如下：

'''
pip install pymysql
pip install sshtunnel
'''

import pymysql
import pandas as pd
from sshtunnel import SSHTunnelForwarder


def get_all_data(cursor, table_name):
    cursor.execute("select * from " + table_name)
    results = cursor.fetchall()
    description = cursor.description
    # table head
    df = pd.DataFrame(data=results, columns=[item[0] for item in description])
    return df


if __name__ == '__main__':
    # 连接 ssh
    with SSHTunnelForwarder(
        ('10.10.87.7', 22), # 指定 ssh 登录的跳转机的 address，端口号
        ssh_username='ssh_guest', # 远程服务器的用户名，注意不是 DB 的用户名和密码
        ssh_password='password', # 远程服务器的密码
        remote_bind_address=('10.10.87.7', 3306), # 注意端口号不要加引号
        # local_bind_address=('127.0.0.1', 22) # 注意端口号不要加引号 # 13306
    ) as server:
        print('successfully connect ssh!')
        # 连接数据库
        try:
            with pymysql.connect(host='localhost',
                port=3306, # server.local_bind_port, 
                user='sql_username',
                password='password',
                database='database_wanna_read',
                connect_timeout=10,
            ) as conn:
                print('successfully connect sql!')
                df = get_all_data(conn.cursor(), table_name='cold_source_run_data_history')
                # 保存 excel
                df.to_excel('./cold_source_run_data_history.xls',
                    sheet_name='cold_source_run_data_history',
                    index=False)
                print('successfully save excel!')
                exit(0)
        except pymysql.err.OperationalError as e:
            print('mysql lost connection')