SQLAlchemy 通过 Paramiko SSH [英] SQLAlchemy through Paramiko SSH

问题描述

我在需要通过 SSH 访问的服务器上有一个数据库.现在我通过使用命令行获取数据来处理数据库.

I have a database on a server which I need to access through SSH. Right now I deal with the DB by using the command line to get the data.

import paramiko
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname='XX.XX.XX', username='user', password='pass', port = YYY)
query = "mysql -u " + username_sql + " -p" + password_sql +" dbb -e \"" + sql_query + "\""
ssh.exec_command(query.decode('string_escape'))
ssh.close()

有没有办法使用 SQLAlchemy 来提高效率，以便我可以直接使用 Pandas DataFrames?

Is there a way to do this with SQLAlchemy to be more efficient and so I can work with pandas DataFrames directly?

from sqlalchemy import create_engine
engine = create_engine(
       "mysql://username_sql:password_sql@localhost/dbb")

推荐答案

最简单的方法是运行到远程主机上 mysql 端口的 SSH 隧道.例如:

The easiest way to do this would be to run an SSH tunnel to the mysql port on the remote host. For example:

ssh -f [e-mail] -L 3307:mysql1.example.com:3306 -N

然后用SQLAlchemy在本地连接:

Then connect locally with SQLAlchemy:

engine = create_engine("mysql://username_sql:password_sql@localhost:3307/dbb")

如果你真的想使用paramiko，试试这个演示代码在 paramiko repo 或 sshtunnel 模块中.ssh 命令可能是最简单的方法，但您可以使用 autossh 在隧道关闭时重新启动隧道.

If you really want to use paramiko, try this demo code in the paramiko repo or the sshtunnel module. The ssh command might be the easiest method though.. and you can use autossh to restart the tunnel if it goes down.

这篇关于SQLAlchemy 通过 Paramiko SSH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！