"SELECT name FROM sqlite_master" error while uploading DataFrame using .to_sql()

Question

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)

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
– Shadow, Commented Sep 20, 2021 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
– Shadow, Commented Sep 20, 2021 at 10:55

Gord Thompson · Accepted Answer · 2021-09-20 11:43:18Z

1

.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()

answered Sep 20, 2021 at 11:43

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

"SELECT name FROM sqlite_master" error while uploading DataFrame using .to_sql()

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related