A working code is given below:
"""
Connect to remote MySQL database and print today's data
"""
import logging
import pymysql
import paramiko
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder
import params
# Load the private key for SSH
pem_file = params.rsa_path + params.rsa_file
mypkey = paramiko.RSAKey.from_private_key_file(pem_file)
# ssh variables
ssh_host = params.remote_server_ip
ssh_port = params.remote_server_port
ssh_user = params.remote_server_username
localhost = params.private_server_ip
localport = params.private_server_port
# database variables
sql_username = params.sql_username
sql_password = params.sql_password
sql_database = params.sql_database
def dbQuery(q):
""" access the database over the SSH tunnel and execute the query """
with SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_username=ssh_user,
ssh_pkey=mypkey,
remote_bind_address=(localhost, 3306)) as tunnel:
try:
conn = pymysql.connect(host=localhost,
port=tunnel.local_bind_port,
user=sql_username,
passwd=sql_password,
db=sql_database,
cursorclass=pymysql.cursors.DictCursor)
cursor = conn.cursor()
cursor.execute(q)
data = cursor.fetchall()
finally:
conn.close()
return data
# these are the queries to get data for
TodayDateQuery = """
select CURDATE()
"""
def main():
""" Main entry point of the app """
print("get_current date")
dates = dbQuery(TodayDateQuery)
print(dates)
if __name__ == "__main__":
try:
main()
except Exception as e:
print("Application exited unexpectedly: ", e)
Login to remote database and mapping port to host
#!/bin/bash
ssh -i ~/.ssh/id_rsa pi@192.168.0.xxx -p 22 -L 3306:127.0.0.1:3306