sqlalchemy.exc.InvalidRequestError: Could not reflect: requested table(s) not available in Engine

Question

I am trying to push the excel xlsx data to mySQl Alchemy by using this simple code...

import pandas as pd
import os
import sqlalchemy


mydir = (os.getcwd()).replace('\\', '/') + '/'

# MySQL Connection
MYSQL_USER = 'xxxxxxx'
MYSQL_PASSWORD = 'xxxxxxxx'
MYSQL_HOST_IP = '127.0.0.1'
MYSQL_PORT = 3306
MYSQL_DATABASE = 'xlsx_test_db'

# connect db
engine = sqlalchemy.create_engine('mysql+mysqlconnector://' + MYSQL_USER + ':' + MYSQL_PASSWORD + '@' + MYSQL_HOST_IP + ':' + str(
    MYSQL_PORT) + '/' + MYSQL_DATABASE, echo=False)
engine.connect()

# reading and insert one file at a time
for file in os.listdir('.'):
    # only process excels files
    file_basename, extension = file.split('.')
    if extension == 'xlsx':

        df = pd.read_excel(r'' + mydir + 'MNM_Rotterdam_5_Daily_Details-20191216081027.xlsx', sheet_name='Report')
        df.to_sql(file_basename, con=engine, if_exists='replace')

But I found this error

Traceback (most recent call last):
  File "C:/Users/DELL/PycharmProjects/automateDB/myWatchDog.py", line 28, in <module>
    df.to_sql(file_basename, con=engine, if_exists='replace')
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\core\generic.py", line 2532, in to_sql
    dtype=dtype, method=method)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 460, in to_sql
    chunksize=chunksize, dtype=dtype, method=method)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 1173, in to_sql
    table.create()
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 577, in create
    self.pd_sql.drop_table(self.name, self.schema)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\pandas\io\sql.py", line 1222, in drop_table
    self.meta.reflect(only=[table_name], schema=schema)
  File "C:\Users\DELL\PycharmProjects\MyALLRefProf\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 3956, in reflect
    (bind.engine, s, ', '.join(missing)))
sqlalchemy.exc.InvalidRequestError: Could not reflect: requested table(s) not available in Engine(mysql+mysqlconnector://root:***@127.0.0.1:3306/xlsx_test_db): (MNM_Rotterdam_5_Daily_Details-20191216081027)

So any one could me to solve this...

Thank you...

I hope it would be clear enough....

Answer 1

Changing the table name to lower case worked for me.

Answer 2

if you have MySQL installed into windows you have to use lowercase for the name of tables.

Answer 3

I used upper case for if_exists = 'append' and it works but not for 'replace' . 'replace' only works with lower case, which makes no sense