Using SSH Tunnel to access mysql database

Anvil Q&A
1

What I’m trying to do:
access my MySQL database from server code using pymysql and sshtunnel

What I’ve tried and what’s not working:
I keep getting “OperationalError: (2013, ‘Lost connection to MySQL server during query’)” when I try to first connect to the database even though the same code runs without hiccup on my personal machine in a few seconds max. I noticed the examples on anvil’s documentation don’t use ssh tunnel and wonder if that is the issue? I’m still new to this, but the example from the MySQL server I’m using has sshtunnel so that’s what I am trying.

Code Sample:

# code snippet
with sshtunnel.SSHTunnelForwarder(
      (ssh_host),
      ssh_username=ssh_username, ssh_password=ssh_password,
      remote_bind_address=(remote_address, 3306)
  ) as tunnel:
      connection = pymysql.connect(
        host=localhost,
        user=database_username,
        passwd=database_password,
        db=database_name,
        port=tunnel.local_bind_port
  )
2

Welcome to the Forum, @alextcates.

Just a hunch. Were you expecting the tunnel to remain open between Server calls?

Normally, code on the Server runs only long enough to service the last Server call. After that Server-side function returns, the Server-side program exits.

There are ways around that.

3

Thank you for the welcom and answer.

However, I dont think I am expecting the tunnel to remain open. I have that code snippet in a function so it should open the tunnel, connect, make the SQL call, then close the connection and tunnel before returning the results from the SQL call. It seems to crash while trying to make the first SQL call (rather than any subsequent call)

4

This may or may not be the answer, but the first thing I would do is attempt to convert the pymysql cursor object to a normalized python type in memory to make sure it does not require any lingering connection just to return the results you are asking for after you have already closed the connection.

MySQL does not actually have a cursor like other SQL dbs do (ex. postgresql ) , so pymysql will create a pythonic SQL standardized ‘cursor-like’ object for it that sometimes can act more like a generator object attached to a stream.

So what I would try first:

mysqlcursor = connection.cursor()

sql_query = 'FROM `your_table` SELECT *  LIMIT 5 '

mysqlcursor.execute(sql_query )

#turn the generator-like MySQL cursor object iterator into a list comprehension
mysqlcursor = [ row for row in mysqlcursor ]

#proceed with rest of your code/test as normal

If this does not work with a simple example then you definitely have an ssh tunnel connection issue and it is not some basic anvil server module behavior that behaves differently from your local machine. :cookie:

1 Like