**Jenis ralat: Tidak dapat mengulangi objek 'NoneType'**

Question

Saya mahu menggunakan terowong ssh untuk mengakses pangkalan data Mysql. Malangnya, seni bina kod ini nampaknya tidak menyokong format pertanyaan saya, kerana fungsi tersebut nampaknya tidak serasi dengan format pertanyaan saya.

/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

Pernyataan ringkas seperti (Pilih * Dari Jadual) berfungsi dengan sempurna.

Apakah yang perlu saya ubah dalam sintaks atau fungsi mysql untuk menjadikannya berfungsi?

Maaf, saya orang baru. Saya akan berterima kasih atas sebarang nasihat atau bantuan.

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()

Answer 1

Terdapat kesilapan menaip dalam talian df = run_query(...) anda.

Anda menggunakan koma; tetapi anda sepatutnya menggunakan %.

Itu

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))