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")
Paramiko is a Python library that makes a connection with a remote device through SSh. Paramiko is using SSH2 as a replacement for SSL to make a secure connection between two devices. It also supports the SFTP client and server model.
function sqlalchemy. create_engine(url, **kwargs) Create a new Engine instance. The standard calling form is to send the URL as the first positional argument, usually a string that indicates database dialect and connection arguments: engine = create_engine("postgresql://scott:tiger@localhost/test")
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 [email protected] -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.
Just swap the (host, port) of the server with postgres:
from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel
server = SSHTunnelForwarder(
(<'your host'>, <host port>),
ssh_username=<"os remote username">,
ssh_pkey=<'path/to/key.pem'>, # or ssh_password.
remote_bind_address=(<'postgres db host'>, <'postgres db port'>))
server.start()
connection_data = 'postgresql://{user}:{password}@{host}:{port}/{db}'.format(user=<'postgres user'>,
password=<'postgres password'>,
host=server.local_bind_host,
port=server.local_bind_port,
db=<'postgres db name'>)
engine = create_engine(connection_data)
# Do your queries
server.stop()
In case there is anyone who's interested in connecting to a remote Postgresql database via SSH and wants to load data into a pandas DataFrame here is how to do it.
Suppose we have installed a postgresql database on a remote server, to which we can ssh by the following parameters.
SSH parameters:
10.0.0.101
22
(default port for SSH)my_username
my_password
Database parameters:
5432
(postgresql default port)db
postgres_user
(default username is postgres
)postgres_pswd
(default password is an empty string)MY_TABLE
Now, we want to connect to this database on our end and load data into a pandas DataFrame:
from sshtunnel import SSHTunnelForwarder
from sqlalchemy import create_engine
import pandas as pd
server = SSHTunnelForwarder(
('10.0.0.101', 22),
ssh_username="my_username",
ssh_password="my_password",
remote_bind_address=('127.0.0.1', 5432)
)
server.start()
local_port = str(server.local_bind_port)
engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format("postgres_user", "postgres_pswd", "127.0.0.1", local_port, "db"))
dataDF = pd.read_sql("SELECT * FROM \"{}\";".format("MY_TABLE"), engine)
server.stop()
You could use the SSHTunnel library as follows:
from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel
from sqlalchemy.orm import sessionmaker #Run pip install sqlalchemy
with SSHTunnelForwarder(
('10.160.1.24', 22), #Remote server IP and SSH port
ssh_username = "<usr>",
ssh_password = "<pwd>",
remote_bind_address=('127.0.0.1', 5432)
) as server:
server.start() #start ssh sever
print 'Server connected via SSH'
#connect to PostgreSQL
local_port = str(server.local_bind_port)
engine = create_engine('postgresql://<db_user>:<db_pwd>@127.0.0.1:' + local_port +'/<db_name>')
Session = sessionmaker(bind=engine)
session = Session()
print 'Database session created'
#test data retrieval
test = session.execute("SELECT * FROM <table_name>")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With