时间戳

首页 > 解决方案 > sqlalchemy.exc.InvalidRequestError：无法反映：请求的表在引擎中不可用

python - sqlalchemy.exc.InvalidRequestError：无法反映：请求的表在引擎中不可用

问题描述

我正在尝试使用这个简单的代码将excelxlsx数据推送到......mySQl Alchemy

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

但是我发现了这个错误

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)

所以任何人都可以解决这个问题......

谢谢...

我希望它足够清楚......

标签： pythonpandassqlalchemy

解决方案

将表名更改为小写对我有用。

推荐阅读