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:pw_db@127.0.0.1/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:pw_db@127.0.0.1:' \
    + 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()