Tag: MySQL

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