I wanted to use an ssh tunnel to access a Mysql database. Unfortunately the architecture of this code doesn't seem to support the format of my query, as the function doesn't seem to be compatible with the format of my query.
/Users/peter/PycharmProjects/MySqlLocalhostTest/venv/lib/python3.10/site-packages/paramiko/transport.py:236: CryptographyDeprecationWarning: Blowfish has been deprecated
"class": algorithms.Blowfish,
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/code.py", line 90, in runcode
exec(code, self.locals)
File "<input>", line 96, in <module>
File "<input>", line 65, in run_query
File "/Users/peter/PycharmProjects/MySqlLocalhostTest/venv/lib/python3.10/site-packages/pandas/io/sql.py", line 399, in read_sql_query
return pandas_sql.read_query(
File "/Users/peter/PycharmProjects/MySqlLocalhostTest/venv/lib/python3.10/site-packages/pandas/io/sql.py", line 2081, in read_query
columns = [col_desc[0] for col_desc in cursor.description]
TypeError: 'NoneType' object is not iterable
simple statements like (Select * From Table) work perfectly.
What would I have to change either in the mysql syntax or the function to make this work?
sorry for being a noob. I would appreciate any advice or help.
import pandas as pd
import pymysql
import logging
import sshtunnel
from sshtunnel import SSHTunnelForwarder
ssh_host = '172.xxx.xxx.xxx'
ssh_username = 'root'
ssh_password = 'xxxxxxx'
database_username = 'root'
database_password = 'xxxxxxxx'
database_name = 'analysis-db'
localhost = '127.0.0.1'
def open_ssh_tunnel(verbose=False):
"""Open an SSH tunnel and connect using a username and password.
:param verbose: Set to True to show logging
:return tunnel: Global SSH tunnel connection
"""
if verbose:
sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
global tunnel
tunnel = SSHTunnelForwarder(
(ssh_host, 22),
ssh_username=ssh_username,
ssh_password=ssh_password,
remote_bind_address=('127.0.0.1', 3306)
)
tunnel.start()
def mysql_connect():
"""Connect to a MySQL server using the SSH tunnel connection
:return connection: Global MySQL database connection
"""
global connection
connection = pymysql.connect(
host='127.0.0.1',
user=database_username,
passwd=database_password,
db=database_name,
port=tunnel.local_bind_port
)
def run_query(sql):
"""Runs a given SQL query via the global database connection.
:param sql: MySQL query
:return: Pandas dataframe containing results
"""
return pd.read_sql_query(sql, connection)
def mysql_disconnect():
"""Closes the MySQL database connection.
"""
connection.close()
def close_ssh_tunnel():
"""Closes the SSH tunnel connection.
"""
tunnel.close
day_of_test = "24.01.22"
gold_close = 1000
sap_close= 1000
bonds_close= 1000
btc_est= 1000
btc_actual= 1000
actual_difference = 1000
open_ssh_tunnel()
mysql_connect()
query = ("INSERT INTO `ML1`(`day_of_test`, `gold_close`, `sap_close`, `bonds_close`, `btc_est`, `btc_actual`,`actual_difference`) VALUES (%s, %s, %s, %s, %s, %s, %s);"%
(day_of_test, gold_close, sap_close, bonds_close, btc_est, btc_actual, actual_difference))
df = run_query(query)
print(df)
df.head()
mysql_disconnect()
close_ssh_tunnel()
CodePudding user response:
You have a typo in your df = run_query(...)
line.
You used a comma; but you should use a %.
i.e.
df = run_query("INSERT INTO `ML1`(`day_of_test`, `gold_close`, `sap_close`, `bonds_close`, `btc_est`, `btc_actual`,`actual_difference`) VALUES (%s, %s, %s, %s, %s, %s, %s);" %
(day_of_test, gold_close, sap_close, bonds_close, btc_est, btc_actual, actual_difference))