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')