1

Context: I'd like to send a concatenated data frame (I joined several dataframes from individual stock data) into a MySQL database, however, I can't seem to create a table and send the data there

Problem: When I run this code df.to_sql(name='stockdata', con=con, if_exists='append', index=False) (source: Writing a Pandas Dataframe to MySQL), I keep getting this error: pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting.

I'm new to MySQL as well so any help is very welcome! Thank you

from __future__ import print_function
import pandas as pd
from datetime import date, datetime, timedelta
import numpy as np
import yfinance as yf
import mysql.conector
import pymysql as pymysql
import pandas_datareader.data as web
from sqlalchemy import create_engine
import yahoo_fin.stock_info as si



######################################################
# PyMySQL configuration

user = '...'
passw = '...'
host = '...'
port = 3306
database = 'stockdata'

con.cursor().execute("CREATE DATABASE IF NOT EXISTS {0} ".format(database))


con = pymysql.connect(host=host,
                      port=port,
                      user=user,
                      passwd=passw,
                      db=database,
                      charset='utf8')

df.to_sql(name='stockdata', con=con, if_exists='append', index=False)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    The error has nothing to do with mysql, your code launches a query that works in sqlite only, therefore something went wrong with passing the database connection to the pandas dataframe. – Shadow Sep 20 '21 at 10:48
  • My guess is that you are using a recent version of pandas that requires sqlalchemy connection object as the connection parameter. – Shadow Sep 20 '21 at 10:55
  • Thank you! Working now :) –  Sep 20 '21 at 14:12

1 Answers1

0

.to_sql() expects the second argument to be either a SQLAlchemy Connectable object (Engine or Connection) or a DBAPI Connection object. If it is the latter then pandas assumes that it is a SQLite connection.

You need to use SQLAlchemy to create an engine object

engine = create_engine("mysql+pymysql://…")

and pass that to to_sql()

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418