Impossible de charger le dataframe Python dans MySQL

Question

J'essaie de charger un dataframe python dans MySQL. Il renvoie l'erreur "Échec du traitement de l'argument de format ; l'horodatage Python ne peut pas être converti en type MySQL". Je ne suis pas sûr de quoi il s'agit.

import pandas as pd
from datetime import date
import mysql.connector
from mysql.connector import Error
def priceStock(tickers):
  today = pd.to_datetime("today").strftime("%Y-%m-%d")
  for ticker in tickers:
    conn = mysql.connector.connect(host='103.200.22.212', database='analysis_stock', user='analysis_PhamThiLinhChi', password='Phamthilinhchi')
    
    new_record = stock_historical_data(ticker, '2018-01-01', today)
    new_record.insert(0, 'ticker', ticker)
    table = 'priceStock'
    cursor = conn.cursor()
        #loop through the data frame
    for i,row in new_record.iterrows():
    #here %s means string values 
        sql = "INSERT INTO " + table + " VALUES (%s,%s,%s,%s,%s,%s,%s)"

        #đoán chắc là do format time từ python sang sql ko khớp 
        cursor.execute(sql, tuple(row))
        print("Record inserted")
        # the connection is not auto committed by default, so we must commit to save our changes
        conn.commit()
priceStock(['VIC'])

Answer 1

Vous pouvez utiliser to_sql to_sql utilise SQLAlchemy et SQLAlchemy prend en charge MySQL, donc le code ci-dessous devrait fonctionner

import pandas as pd
from datetime import date
import mysql.connector
from mysql.connector import Error
import sqlalchemy

def priceStock(tickers):
  today = pd.to_datetime("today").strftime("%Y-%m-%d")
  for ticker in tickers:
    conn = sqlalchemy.create_engine('mysql+mysqlconnector://analysis_PhamThiLinhChi:Phamthilinhchi@103.200.22.212')
    # conn = mysql.connector.connect(host='103.200.22.212', database='analysis_stock', user='analysis_PhamThiLinhChi', password='Phamthilinhchi')
    
    new_record = stock_historical_data(ticker, '2018-01-01', today)
    new_record.insert(0, 'ticker', ticker)
    table = 'priceStock'
    # cursor = conn.cursor()
    new_record.to_sql(table, con=conn, if_exists='append')
        #loop through the data frame
    # for i,row in new_record.iterrows():
    # #here %s means string values 
    #     sql = "INSERT INTO " + table + " VALUES (%s,%s,%s,%s,%s,%s,%s)"

    #     #đoán chắc là do format time từ python sang sql ko khớp 
    #     cursor.execute(sql, tuple(row))
    #     print("Record inserted")
    #     # the connection is not auto committed by default, so we must commit to save our changes
    #     conn.commit()
priceStock(['VIC'])

Pour voir les lignes mises à jour, utilisez le code suivant :

from sqlalchemy import text
conn = sqlalchemy.create_engine('mysql+mysqlconnector://analysis_PhamThiLinhChi:Phamthilinhchi@103.200.22.212')
with conn.connect() as con:
   df = con.execute(text("SELECT * FROM priceStock")).fetchall()
   print(df)