Unable to load python dataframe into mysql

Question

I'm trying to load a python dataframe into mysql. It returns the error "Failed processing format argument; Python 'timestamp' cannot be converted to MySQL type". I'm not sure what it's about.

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

You can use to_sql to_sql using SQLAlchemy and SQLAlchemy supports MySQL, so the code below should work

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

To view updated rows, use the following code:

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)