<jupyter_start><jupyter_text>Maria SQL Data Exploration NotebookAn example notebook which can be used to perform data analysis using an MariaDB SQL client and Pandas dataframes.<jupyter_code>import json
import os
from datetime import datetime, timedelta

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import paramiko
import pymysql
import sqlfluff
from box import Box
from dateutil.relativedelta import relativedelta
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder

from cupyopt.schema import avro_schema, avro_schema_to_file, infer_df_avro_schema
from cupyopt.validation import df_avro_validate
"""
config_filepath found below should include yaml data similar to the following
<databasename>:
    ssh_hostname: str
    ssh_port: int
    ssh_username: str
    ssh_key: str
    sql_hostname: str
    sql_port: int
    sql_username: str
    sql_password: str
    database: str
"""

# filepath to configuration file in yaml format
config_filepath = "config.yaml"
database_name = "databasename"
# open configuration file containing relevant connection details for oracle database
config = Box.from_yaml(filename=config_filepath)[database_name]
# create paramiko private key for use with ssh cxn
pkey = paramiko.RSAKey.from_private_key_file(config["ssh_key"])
# inspired by following SO post -> https://stackoverflow.com/questions/21903411/enable-python-to-connect-to-mysql-via-ssh-tunnelling
def ssh_tunneled_sql_query(
    sql_stmt: str,
    config: Box,
    ssh_pkey: paramiko.pkey.PKey,
) -> pd.DataFrame:
    """
    Makes SSH-tunneled SQL Query, receiving and returning data as pd.DataFrame
    """
    with SSHTunnelForwarder(
        (config.ssh_hostname, config.ssh_port),
        ssh_username=config.ssh_username,
        ssh_pkey=pkey,
        remote_bind_address=(config.sql_hostname, config.sql_port),
    ) as tunnel:
        conn = pymysql.connect(
            host=config.sql_hostname,
            user=config.sql_username,
            passwd=<PASSWORD>.sql_password,
            port=tunnel.local_bind_port,
        )
        data = pd.read_sql_query(sql_stmt, conn)
        conn.close()

    return data
sql_stmt = """
select version()
"""
ssh_tunneled_sql_query(sql_stmt=sql_stmt, ssh_pkey=pkey, config=config)
sql_stmt = """
select *
from information_schema.schemata
order by schema_name;
"""
ssh_tunneled_sql_query(sql_stmt=sql_stmt, ssh_pkey=pkey, config=config)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07445077298616762, 'fraction_numerical': 0.0032546786004882017, 'mean_word_length': 4.357298474945534, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 9, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '153904', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 797, 'n_tokens_neox': 764, 'n_words': 202, 'path': 'code/data_acquisition_and_understanding/mariadb_sql_data_exploration_notebook.ipynb', 'repo_name': 'd33bs/data-endeavor-template', 'size': '2454'}