公司内部通常使用跳板机连接到远程数据库,需要 ssh 权限,程序本身不能直接连接 db,在写一些查询工具的时候会很麻烦,还好强大的python 有相关的模块可以轻易实现。
python使用跳板机访问数据库 mysql
安装python模块sshtunnel
pip install sshtunnel
pip install pymysql
代码
from sshtunnel import SSHTunnelForwarder
import pymysql
import pymysql.cursors
pymysql.install_as_MySQLdb()
db_host = "101.2.1.22"
db_port = 3306
db_user = "stat_query"
db_password = "pggXNBviboEfl@hk"
pk_file = "/Users/admin/***.pem"
with SSHTunnelForwarder(
('跳板机地址', 端口),
ssh_username="username",
ssh_password="password",
ssh_pkey=pk_file,
remote_bind_address=(db_host, db_port)
) as tunnel:
conn = pymysql.connect(host='127.0.0.1',
port=tunnel.local_bind_port,
user=db_user,
password=db_password,
db='rentcar',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
cur.execute("select customer_id, phone from table where customer_id>" + str(max_id) + " and customer_id < " + str(max_id + 10000) + " and phone like '% %' ")
rows = cur.fetchall()
# print(rows)
try:
for row in rows:
print(str(row['customer_id']) + ", " + row['phone'])
except Exception as e:
print("error" + e)
conn.close()
连接建完以后就可以进行其他操作了
网友评论