Tidak dapat memuatkan kerangka data python ke dalam mysql

Question

Saya cuba memuatkan kerangka data python ke dalam mysql. Ia mengembalikan ralat "Gagal memproses hujah format; 'cap masa' Python tidak boleh ditukar kepada jenis MySQL". Saya tidak pasti tentang apa itu.

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

Anda boleh menggunakan to_sql to_sql menggunakan SQLAlchemy dan SQLAlchemy menyokong MySQL, jadi kod di bawah sepatutnya berfungsi

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

Untuk melihat baris yang dikemas kini, gunakan kod berikut:

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)