Python “not all arguments converted during bytes formatting” in INSERT with SSHTunneling

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
Tom12
User
Beiträge: 48
Registriert: Mittwoch 17. Januar 2018, 17:38

Hallo zusammen,

ich habe folgendes Problem und die bekannten Stackoverflow Lösungen habe ich bereits getestet.

ich bekomme den Fehler "not all arguments converted during bytes formatting", die Anzahl der Platzhalter ist korrekt?
Was ist aber (Datenbank felder dürfen NULL sein) wenn ein DataFrame Feld wert NULL enthält wie geht Python-SQL damit um?

Bitte um Hilfe

Code: Alles auswählen

def query(q):
     with SSHTunnelForwarder(
          (host, 22),
          ssh_username=ssh_username,
          ssh_password=s_password,
          ssh_private_key=ssh_private_key,
          remote_bind_address=(localhost, 3306)
     ) as server:
          conn = db.connect(host=localhost,
          port=server.local_bind_port,
          user=user,
          passwd=password,
          db=database)
          return pd.read_sql_query(sql=q, con=conn)

def query2(q, s):
     with SSHTunnelForwarder(
          (host, 22),
          ssh_username=ssh_username,
          ssh_password=s_password,
          ssh_private_key=ssh_private_key,
          remote_bind_address=(localhost, 3306)
     ) as server:
          conn = db.connect(host=localhost,
          port=server.local_bind_port,
          user=user,
          passwd=password,
          db=database)
          return pd.read_sql_query(sql=q, con=conn, params=[s])

def getBilanz():
    df = query('SELECT symbol, currency FROM company')
    for i in range(len(df)) : 
        response = requests.get("https://financialmodelingprep.com/api/v3/balance-sheet-statement/" + df.loc[i, "symbol"] + "?period=quarter&limit=40&apikey=fweufjskdfjkasldjfklj")
        response = response.json()
        df_bil = pd.DataFrame.from_records(response)
        for ind in range(len(df_bil)):
            if df_bil.loc[ind, 'date'] > '01.01.2010':
#                val = [df_bil.loc[ind, "date"], df_bil.loc[ind, "symbol"], df_bil.loc[ind, "reportedCurrency"], df_bil.loc[ind, "fillingDate"], df_bil.loc[ind, "acceptedDate"], df_bil.loc[ind, "period"], df_bil.loc[ind, "cashAndCashEquivalents"], df_bil.loc[ind, "shortTermInvestments"], df_bil.loc[ind, "cashAndShortTermInvestments"], df_bil.loc[ind, "netReceivables"], df_bil.loc[ind, "inventory"], df_bil.loc[ind, "otherCurrentAssets"], df_bil.loc[ind, "totalCurrentAssets"], df_bil.loc[ind, "propertyPlantEquipmentNet"], df_bil.loc[ind, "goodwill"], df_bil.loc[ind, "intangibleAssets"], df_bil.loc[ind, "goodwillAndIntangibleAssets"], df_bil.loc[ind, "longTermInvestments"], df_bil.loc[ind, "taxAssets"], df_bil.loc[ind, "otherNonCurrentAssets"], df_bil.loc[ind, "totalNonCurrentAssets"], df_bil.loc[ind, "otherAssets"], df_bil.loc[ind, "totalAssets"], df_bil.loc[ind, "accountPayables"], df_bil.loc[ind, "shortTermDebt"], df_bil.loc[ind, "taxPayables"], df_bil.loc[ind, "deferredRevenue"], df_bil.loc[ind, "otherCurrentLiabilities"], df_bil.loc[ind, "totalCurrentLiabilities"], df_bil.loc[ind, "longTermDebt"], df_bil.loc[ind, "deferredRevenueNonCurrent"], df_bil.loc[ind, "deferredTaxLiabilitiesNonCurrent"], df_bil.loc[ind, "otherNonCurrentLiabilities"], df_bil.loc[ind, "totalNonCurrentLiabilities"], df_bil.loc[ind, "otherLiabilities"], df_bil.loc[ind, "totalLiabilities"], df_bil.loc[ind, "commonStock"], df_bil.loc[ind, "retainedEarnings"], df_bil.loc[ind, "accumulatedOtherComprehensiveIncomeLoss"], df_bil.loc[ind, "othertotalStockholdersEquity"], df_bil.loc[ind, "totalStockholdersEquity"], df_bil.loc[ind, "totalLiabilitiesAndStockholdersEquity"], df_bil.loc[ind, "totalInvestments"], df_bil.loc[ind, "totalDebt"], df_bil.loc[ind, "netDebt"], df_bil.loc[ind, "link"], df_bil.loc[ind, "finalLink"]]
                query2("INSERT INTO Bilanz(date, symbol, reportedCurrency, fillingDate, acceptedDate, period, cashAndCashEquivalents, shortTermInvestments, cashAndShortTermInvestments, netReceivables, inventory, otherCurrentAssets, totalCurrentAssets, propertyPlantEquipmentNet, goodwill, intangibleAssets, goodwillAndIntangibleAssets, longTermInvestments, taxAssets, otherNonCurrentAssets, totalNonCurrentAssets, otherAssets, totalAssets, accountPayables, shortTermDebt, taxPayables, deferredRevenue, otherCurrentLiabilities, totalCurrentLiabilities, longTermDebt, deferredRevenueNonCurrent, deferredTaxLiabilitiesNonCurrent, otherNonCurrentLiabilities, totalNonCurrentLiabilities, otherLiabilities, totalLiabilities, commonStock, retainedEarnings, accumulatedOtherComprehensiveIncomeLoss, othertotalStockholdersEquity, totalStockholdersEquity, totalLiabilitiesAndStockholdersEquity, totalInvestments, totalDebt, netDebt, link, finalLink) VALUES ( {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}, {29}, {30}, {31}, {32}, {33}, {34}, {35}, {36}, {37}, {38}, {39}, {40}, {41}, {42}, {43}, {44}, {45}, {46}, {47})", (df_bil.loc[ind, "date"], df_bil.loc[ind, "symbol"], df_bil.loc[ind, "reportedCurrency"], df_bil.loc[ind, "fillingDate"], df_bil.loc[ind, "acceptedDate"], df_bil.loc[ind, "period"], df_bil.loc[ind, "cashAndCashEquivalents"], df_bil.loc[ind, "shortTermInvestments"], df_bil.loc[ind, "cashAndShortTermInvestments"], df_bil.loc[ind, "netReceivables"], df_bil.loc[ind, "inventory"], df_bil.loc[ind, "otherCurrentAssets"], df_bil.loc[ind, "totalCurrentAssets"], df_bil.loc[ind, "propertyPlantEquipmentNet"], df_bil.loc[ind, "goodwill"], df_bil.loc[ind, "intangibleAssets"], df_bil.loc[ind, "goodwillAndIntangibleAssets"], df_bil.loc[ind, "longTermInvestments"], df_bil.loc[ind, "taxAssets"], df_bil.loc[ind, "otherNonCurrentAssets"], df_bil.loc[ind, "totalNonCurrentAssets"], df_bil.loc[ind, "otherAssets"], df_bil.loc[ind, "totalAssets"], df_bil.loc[ind, "accountPayables"], df_bil.loc[ind, "shortTermDebt"], df_bil.loc[ind, "taxPayables"], df_bil.loc[ind, "deferredRevenue"], df_bil.loc[ind, "otherCurrentLiabilities"], df_bil.loc[ind, "totalCurrentLiabilities"], df_bil.loc[ind, "longTermDebt"], df_bil.loc[ind, "deferredRevenueNonCurrent"], df_bil.loc[ind, "deferredTaxLiabilitiesNonCurrent"], df_bil.loc[ind, "otherNonCurrentLiabilities"], df_bil.loc[ind, "totalNonCurrentLiabilities"], df_bil.loc[ind, "otherLiabilities"], df_bil.loc[ind, "totalLiabilities"], df_bil.loc[ind, "commonStock"], df_bil.loc[ind, "retainedEarnings"], df_bil.loc[ind, "accumulatedOtherComprehensiveIncomeLoss"], df_bil.loc[ind, "othertotalStockholdersEquity"], df_bil.loc[ind, "totalStockholdersEquity"], df_bil.loc[ind, "totalLiabilitiesAndStockholdersEquity"], df_bil.loc[ind, "totalInvestments"], df_bil.loc[ind, "totalDebt"], df_bil.loc[ind, "netDebt"], df_bil.loc[ind, "link"], df_bil.loc[ind, "finalLink"]))

getBilanz()
Benutzeravatar
sparrow
User
Beiträge: 4164
Registriert: Freitag 17. April 2009, 10:28

Welche Datenbank verwendest du?
Warum benutz du nicht SQLAlchemy?
Bist du sicher, dass die Anzahl der Werte zur Anzahl der Felder passt? Zähl nochmal nach.
Zuletzt geändert von sparrow am Donnerstag 21. Januar 2021, 12:58, insgesamt 2-mal geändert.
Tom12
User
Beiträge: 48
Registriert: Mittwoch 17. Januar 2018, 17:38

Ich benutze MariaDB diese ist auf einem Webserver, ich nutze diese Methode da dies die einzige nach langem Testen war, über welche ich mit einem SSH-Tunnel die Datenbank erreicht habe

Felder sind korrekt!
Benutzeravatar
sparrow
User
Beiträge: 4164
Registriert: Freitag 17. April 2009, 10:28

Also bei deinem Code komme ich auf 47 Felder, 47 Werte und 48 Platzhalter.
Sirius3
User
Beiträge: 17711
Registriert: Sonntag 21. Oktober 2012, 17:20

query und query2 sind nicht nur schlechte Funktionsnamen, benutzen Unmengen globaler Variablen, sondern auch noch beide fast identisch. Auch möchte man nicht für jede Abfrage eine neue Datenbankverbindung und einen SQL-Tunnel aufbauen.
Niemand möchte sich eine 3000-Zeichen lange Zeile antun.
Über einen Index zu iterieren ist ein Anti-Pattern, weil man direkt über die Datensätze iterieren könnte.
Und query erwartet in Deinem Fall als params-Element mehr als eine einelementige Liste.
Tom12
User
Beiträge: 48
Registriert: Mittwoch 17. Januar 2018, 17:38

Sirius3 hat geschrieben: Donnerstag 21. Januar 2021, 13:08 query und query2 sind nicht nur schlechte Funktionsnamen, benutzen Unmengen globaler Variablen, sondern auch noch beide fast identisch. Auch möchte man nicht für jede Abfrage eine neue Datenbankverbindung und einen SQL-Tunnel aufbauen.
Niemand möchte sich eine 3000-Zeichen lange Zeile antun.
Über einen Index zu iterieren ist ein Anti-Pattern, weil man direkt über die Datensätze iterieren könnte.
Und query erwartet in Deinem Fall als params-Element mehr als eine einelementige Liste.
Dieses Script wird einmal ausgeführt und dann in den Untiefen des Explorers verschwinden, somit ist mir cleaner Code ziemlich egal.
Ich muss später noch pro Row berechnungen durchführen vorm Insert somit bietet es sich an über den Index zu iterieren, kann deinen Punkt nicht ganz nachvollziehen. Wo ist der unterschied mit index oder über Datensätze zu iterieren.
Dass mit dem SSH Tunnel ist ein guter Punkt, aus der performance sicht bietet es sich an diesen bestehen zu lassen, wenn er allerdings abbricht crasht das ganze Script?
Tom12
User
Beiträge: 48
Registriert: Mittwoch 17. Januar 2018, 17:38

sparrow hat geschrieben: Donnerstag 21. Januar 2021, 12:59 Also bei deinem Code komme ich auf 47 Felder, 47 Werte und 48 Platzhalter.
mit "alle Felder sind korrekt" meinte ich der Fehler besteht weiterhin :(
Benutzeravatar
__blackjack__
User
Beiträge: 13004
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@Tom12: *Dir* mag sauberer Code egal sein, aber Du willst ja das sich den jetzt andere anschauen um Fehler zu finden. Bei sauberem Code geht es ja nicht einfach nur um Schönheit/Ästhetik, sondern auch wie leicht kann man den lesen und verstehen.

Statt dem ``range(len(df))`` würde man über `df.iterrows()` iterieren. Das sind `collections.namedtuple`-Objeke mit den Daten von jeweils einer Zeile. Also sowas wie ``for record in df.iterrows():`` und dann kann man über ``record.symbol`` statt ``df.loc[i, "symbol"]`` auf die Element eines Datensatzes zugreifen.

Das ``if`` in der zweiten Schleife ist kaputt, weil man Datumsangaben in *der* Form auf diese Weise nicht sinnvoll vergleichen kann:

Code: Alles auswählen

In [6]: "05.02.1974" > "01.01.2010"                                             
Out[6]: True
Da muss man mit dem passenden Datentyp arbeiten, also beide Werte in ein `datetime.date`-Objekt wandeln, beziehungsweise bei `pandas` den entsprechenden kompatiblen Typ. Und dann gehört dieses ``if`` auch nicht *in* die Schleife wenn man schon Pandas verwendet. Da filtert man sich vorher die Werte aus dem `DataFrame`: ``df_bil[df_bil["date"] > datetime.date(2010, 1, 1)]``.
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
Benutzeravatar
__blackjack__
User
Beiträge: 13004
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

Nur noch mal damit Du es es auch glaubst, nicht per Hand nachgezählt, sondern per Programm:

Code: Alles auswählen

#!/usr/bin/env python3
from string import Formatter

from more_itertools import ilen
from redbaron import RedBaron

SOURCE = """query2("INSERT INTO Bilanz(date, symbol, reportedCurrency, fillingDate, acceptedDate, period, cashAndCashEquivalents, shortTermInvestments, cashAndShortTermInvestments, netReceivables, inventory, otherCurrentAssets, totalCurrentAssets, propertyPlantEquipmentNet, goodwill, intangibleAssets, goodwillAndIntangibleAssets, longTermInvestments, taxAssets, otherNonCurrentAssets, totalNonCurrentAssets, otherAssets, totalAssets, accountPayables, shortTermDebt, taxPayables, deferredRevenue, otherCurrentLiabilities, totalCurrentLiabilities, longTermDebt, deferredRevenueNonCurrent, deferredTaxLiabilitiesNonCurrent, otherNonCurrentLiabilities, totalNonCurrentLiabilities, otherLiabilities, totalLiabilities, commonStock, retainedEarnings, accumulatedOtherComprehensiveIncomeLoss, othertotalStockholdersEquity, totalStockholdersEquity, totalLiabilitiesAndStockholdersEquity, totalInvestments, totalDebt, netDebt, link, finalLink) VALUES ( {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}, {29}, {30}, {31}, {32}, {33}, {34}, {35}, {36}, {37}, {38}, {39}, {40}, {41}, {42}, {43}, {44}, {45}, {46}, {47})", (df_bil.loc[ind, "date"], df_bil.loc[ind, "symbol"], df_bil.loc[ind, "reportedCurrency"], df_bil.loc[ind, "fillingDate"], df_bil.loc[ind, "acceptedDate"], df_bil.loc[ind, "period"], df_bil.loc[ind, "cashAndCashEquivalents"], df_bil.loc[ind, "shortTermInvestments"], df_bil.loc[ind, "cashAndShortTermInvestments"], df_bil.loc[ind, "netReceivables"], df_bil.loc[ind, "inventory"], df_bil.loc[ind, "otherCurrentAssets"], df_bil.loc[ind, "totalCurrentAssets"], df_bil.loc[ind, "propertyPlantEquipmentNet"], df_bil.loc[ind, "goodwill"], df_bil.loc[ind, "intangibleAssets"], df_bil.loc[ind, "goodwillAndIntangibleAssets"], df_bil.loc[ind, "longTermInvestments"], df_bil.loc[ind, "taxAssets"], df_bil.loc[ind, "otherNonCurrentAssets"], df_bil.loc[ind, "totalNonCurrentAssets"], df_bil.loc[ind, "otherAssets"], df_bil.loc[ind, "totalAssets"], df_bil.loc[ind, "accountPayables"], df_bil.loc[ind, "shortTermDebt"], df_bil.loc[ind, "taxPayables"], df_bil.loc[ind, "deferredRevenue"], df_bil.loc[ind, "otherCurrentLiabilities"], df_bil.loc[ind, "totalCurrentLiabilities"], df_bil.loc[ind, "longTermDebt"], df_bil.loc[ind, "deferredRevenueNonCurrent"], df_bil.loc[ind, "deferredTaxLiabilitiesNonCurrent"], df_bil.loc[ind, "otherNonCurrentLiabilities"], df_bil.loc[ind, "totalNonCurrentLiabilities"], df_bil.loc[ind, "otherLiabilities"], df_bil.loc[ind, "totalLiabilities"], df_bil.loc[ind, "commonStock"], df_bil.loc[ind, "retainedEarnings"], df_bil.loc[ind, "accumulatedOtherComprehensiveIncomeLoss"], df_bil.loc[ind, "othertotalStockholdersEquity"], df_bil.loc[ind, "totalStockholdersEquity"], df_bil.loc[ind, "totalLiabilitiesAndStockholdersEquity"], df_bil.loc[ind, "totalInvestments"], df_bil.loc[ind, "totalDebt"], df_bil.loc[ind, "netDebt"], df_bil.loc[ind, "link"], df_bil.loc[ind, "finalLink"]))"""


def count_format_placeholders(text):
    return ilen(
        placeholder
        for placeholder in Formatter().parse(text)
        if placeholder[1] is not None
    )


def main():
    name_node, call_node = RedBaron(SOURCE)[0]
    assert name_node.value == "query2"
    sql_argument, values_argument = call_node
    print(
        "Anzahl Platzhalter:",
        count_format_placeholders(sql_argument.find("string").value),
    )
    print("      Anzahl Werte:", len(values_argument.value))


if __name__ == "__main__":
    main()
Ausgabe:

Code: Alles auswählen

Anzahl Platzhalter: 48
      Anzahl Werte: 47
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
Tom12
User
Beiträge: 48
Registriert: Mittwoch 17. Januar 2018, 17:38

Das Problem liegt wo anders, ich habe mir eine Testumgebung aufgebaut.

Insert per Python => Anfrage im PhPmyAdmin Logging => Programm wird ohne Fehler ausgeführt aber kein Eintrag in die Datenbank gemacht
Insert mit SSH (Putty) => Insert wird in der Datenbank gemacht (mit dem gleichen 1:1 gleichen Befehl wie in Python)
PhpMyAdmin SQL Terminal => Der Python mitgegebene SQL Befehl funktioniert auch hier perfekt.

FunFact: Wenn ich den INSERT Befehl durch einen SELECT in Python ersetze funktioniert dieser und ich bekomme die geforderten Daten geliefert... Verbindung funktioniert und wird ausgeführt nur INSERT geht nicht...
Meine letzte Vermutung ist das PhpMyAdmin erkennt das der Befehl von Python kommt und diesen Blockiert, für dies finde ich aber keine Einstellung.
Benutzeravatar
__blackjack__
User
Beiträge: 13004
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@Tom12: Wie kann man denn das INSERT aus Python woanders verwenden? Da musst Du dann ja die Werte von Hand eintragen anstelle der Platzhalter. Gehen diese Platzhalter überhaupt? Also in Python meine ich. Zeig mal die Stelle in der Dokumentation wo das steht.

Wobei mir gerade auffällt, dass ein `pandas.read_sql_query()` mit INSERT extrem falsch aussieht.

Und dann ist da auch noch die Anmerkung von Sirius3, dass `params` als eine Liste mit einem Element ganz sicher falsch ist.

Und etwas anderes als SQLite und SQLAlchemy dürfte sowieso nur ”zufällig” funktionieren. Die Pandas-Dokumentation ist da deutlich: „If a DBAPI2 object, only sqlite3 is supported.“
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
Tom12
User
Beiträge: 48
Registriert: Mittwoch 17. Januar 2018, 17:38

Ich habe es auf mysql.connector umgebaut mit dem zusatz commit() funktionierts nun auch... schon 20 mal gemacht und trotzdem vergessen.

Vielen Dank für die Hilfe!

Gruß Tom
Antworten