Alexis G Alexis G - 5 months ago 46
SQL Question

SQLAlchemy through Paramiko 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()


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

Answer

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 user@XX.XX.XX.XX -L 3307:mysql1.example.com:3306 -N

Then connect locally with SQLAlchemy:

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

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.

Comments