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