我正在嘗試將 python 資料幀加載到 mysql 中。它傳回錯誤「處理格式參數失敗；Python『時間戳記』無法轉換為 MySQL 類型」。我不確定它是關於什麼的。
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']) 
您可以使用 to_sql to_sql 使用 SQLAlchemy 並且 SQLAlchemy 支援 MySQL，因此下面的程式碼應該可以工作
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'])要查看更新的行，請使用以下程式碼：
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)