Read data from MariaDB on remote server with SSH tunnel

Question

Following setup: On my local Windows machine I run Spyder with Python 3.5. Furthermore, I have a remote Ubuntu server with MariaDB on it. What I want do is loading data from the database into Spyder using an SSH tunnel and sqlalchemy.

The two solutions I'm looking at are:

(i) Use PuTTY for SSH tunnel to server as explained here and then in Spyder:

import mysql.connector
from sqlalchemy import create_engine

import pandas as pd

engine = create_engine('mysql+mysqlconnector://un_db:[email protected]/db')
dbconn = engine.connect()

rslt = dbconn.execute("SELECT * FROM table WHERE col1=x AND col2=y;")
df = pd.DataFrame(rslt.fetchall())
df.columns = rslt.keys()

This works well in terms of performance but I have opening PuTTY and building up the SSH tunnel as extra step in the process.

(ii) Use package sshtunnel, thus avoiding the PuTTY extra step:

from sshtunnel import SSHTunnelForwarder

import mysql.connector
from sqlalchemy import create_engine

import pandas as pd

server = SSHTunnelForwarder(
    (hostname, 22),
    ssh_username=un_server, \
    ssh_password=pw_server,
    remote_bind_address=('127.0.0.1', 3306))

server.start()

engine = create_engine('mysql+mysqlconnector://un_db:[email protected]:' \
    + str(server.local_bind_port) + '/db')
dbconn = engine.connect()

rslt = dbconn.execute("SELECT * FROM table WHERE col1=x AND col2=y;")
df = pd.DataFrame(rslt.fetchall())
df.columns = rslt.keys()

Building up the SSH tunnel works all fine (I think) but when I execute the query the IPython console in Spyder hangs.

Questions: Why does my use case work with PuTTY but not with package sshtunnel? And is there a difference between an SSH tunnel via PuTTY and one via package sshtunnel?

Answer 1

I switched drivers from mysql.connector to MySQLdb, with the new one also solution (ii) works now:

from sshtunnel import SSHTunnelForwarder
import MySQLdb as mdb
import pandas as pd

server = SSHTunnelForwarder(
    (hostname, 22),
    ssh_username=un_server, \
    ssh_password=pw_server,
    remote_bind_address=('127.0.0.1', 3306))

server.start()

con = mdb.connect('127.0.0.1', un_db, pw_db, port=server.local_bind_port)

df = pd.read_sql("SELECT * FROM table WHERE col1=x AND col2=y", con)

con.close()
server.stop()

Answer 2

You have two similar solutions.

(i) In this case you create a forwarding process. (not a python process)

(ii) In this case server.start() start new python forwarding thread. This thread does a similar work. But it is python thread and thread will work until it is stopped. Perhaps this is the reason for your problems.

Based on the problem I can suggest three solutions.

1. You can use sshtunnel to create forwarding process like PuTTY. From README: python -m sshtunnel -U username -P password -L :3306 -R 127.0.0.1:3306 -p 22 localhost

2. You can just use server.stop() for stopping sshtunnel forwarding thread.

3. You also can use with statement syntax for automatic stopping (sshtunnel#example-2)