python - SQLAlchemy 和 pandas 产生错误(engine.table_names 返回空列表)

Question

我的代码如下所示:

from sqlalchemy import create_engine
import pandas as pd

# load the CSV
df = pd.Series()
df['raw'] = pd.read_csv('./data/Iris.csv',index_col='Id')

# Connect to the mysql, and use database "datasets"
engine = create_engine('mysql://root:root@127.0.0.1')
engine.execute("USE Datasets") # select new db


# Write data
table_name = 'IRIS'
df['raw'].to_sql(table_name, engine, if_exists='append', index=False)

数据正确插入数据库，之后可以加载，但是命令报错:

> --------------------------------------------------------------------------- AttributeError                            Traceback (most recent call
> last) <ipython-input-6-cfb9b0f5c930> in <module>()
>       1 table_name = 'IRIS'
>       2 
> ----> 3 df['raw'].to_sql(table_name, engine, if_exists='append', index=False)
> 
> ~/anaconda3/lib/python3.6/site-packages/pandas/core/generic.py in
> to_sql(self, name, con, flavor, schema, if_exists, index, index_label,
> chunksize, dtype)    1360         sql.to_sql(self, name, con,
> flavor=flavor, schema=schema,    1361                   
> if_exists=if_exists, index=index, index_label=index_label,
> -> 1362                    chunksize=chunksize, dtype=dtype)    1363     1364     def to_pickle(self, path, compression='infer'):
> 
> ~/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in
> to_sql(frame, name, con, flavor, schema, if_exists, index,
> index_label, chunksize, dtype)
>     469     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
>     470                       index_label=index_label, schema=schema,
> --> 471                       chunksize=chunksize, dtype=dtype)
>     472 
>     473 
> 
> ~/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in
> to_sql(self, frame, name, if_exists, index, index_label, schema,
> chunksize, dtype)    1157                 table_names =
> engine.table_names(    1158                     schema=schema or
> self.meta.schema,
> -> 1159                     connection=conn,    1160                 )    1161             if name not in table_names:
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
> table_names(self, schema, connection)    2137             if not
> schema:    2138                 schema =
> self.dialect.default_schema_name
> -> 2139             return self.dialect.get_table_names(conn, schema)    2140     2141     def has_table(self, table_name, schema=None):
> 
> <string> in get_table_names(self, connection, schema, **kw)
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py
> in cache(fn, self, con, *args, **kw)
>      40     info_cache = kw.get('info_cache', None)
>      41     if info_cache is None:
> ---> 42         return fn(self, con, *args, **kw)
>      43     key = (
>      44         fn.__name__,
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/base.py
> in get_table_names(self, connection, schema, **kw)    1954            
> rp = connection.execute(    1955                 "SHOW FULL TABLES
> FROM %s" %
> -> 1956                 self.identifier_preparer.quote_identifier(current_schema))    1957    
> 1958             return [row[0]
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py in
> quote_identifier(self, value)    3021     3022         return
> self.initial_quote + \
> -> 3023             self._escape_identifier(value) + \    3024             self.final_quote    3025 
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py in
> _escape_identifier(self, value)    2999         """    3000 
> -> 3001         value = value.replace(self.escape_quote, self.escape_to_quote)    3002         if self._double_percents:   
> 3003             value = value.replace('%', '%%')
> 
> AttributeError: 'NoneType' object has no attribute 'replace'

我已经在 SQL 上启用了日志记录，并且日志说明如下:

Time                 Id Command    Argument
2018-01-12T18:13:43.116036Z    20 Query SET global log_output = 'file'
2018-01-12T18:13:44.291677Z    20 Query SET global general_log = on
2018-01-12T18:14:15.861927Z    19 Query DESCRIBE `IRIS`
2018-01-12T18:14:15.864129Z    19 Query rollback
2018-01-12T18:14:15.869620Z    19 Query INSERT INTO `IRIS` (`SepalLengthCm`, `SepalWidthCm`, `PetalLengthCm`, `PetalWidthCm`, `Species`) VALUES (5.1, 3.5, 1.4, 0.2, 'Iris-setosa'),(4.9, 3, 1.4, 0.2, 'Iris-setosa'),(4.7, 3.2, 1.3, 0.2, 'Iris-setosa'),(4.6, 3.1, 1.5, 0.2, 'Iris-setosa'),(5, 3.6, 1.4, 0.2, 'Iris-setosa'),(5.4, 3.9, 1.7, 0.4, 'Iris-setosa'),(4.6, 3.4, 1.4, 0.3, 'Iris-setosa'),(5, 3.4, 1.5, 0.2, 'Iris-setosa'),(4.4, 2.9, 1.4, 0.2, 'Iris-setosa'),(4.9, 3.1, 1.5, 0.1, 'Iris-setosa'),(5.4, 3.7, 1.5, 0.2, 'Iris-setosa'),(4.8, 3.4, 1.6, 0.2, 'Iris-setosa'),(4.8, 3, 1.4, 0.1, 'Iris-setosa'),(4.3, 3, 1.1, 0.1, 'Iris-setosa'),(5.8, 4, 1.2, 0.2, 'Iris-setosa'),(5.7, 4.4, 1.5, 0.4, 'Iris-setosa'),(5.4, 3.9, 1.3, 0.4, 'Iris-setosa'),(5.1, 3.5, 1.4, 0.3, 'Iris-setosa'),(5.7, 3.8, 1.7, 0.3, 'Iris-setosa'),(5.1, 3.8, 1.5, 0.3, 'Iris-setosa'),(5.4, 3.4, 1.7, 0.2, 'Iris-setosa'),(5.1, 3.7, 1.5, 0.4, 'Iris-setosa'),(4.6, 3.6, 1, 0.2, 'Iris-setosa'),(5.1, 3.3, 1.7, 0.5, 'Iris-setosa'),(4.8, 3.4, 1.9, 0.2, 'Iris-setosa'),(5, 3, 1.6, 0.2, 'Iris-setosa'),(5, 3.4, 1.6, 0.4, 'Iris-setosa'),(5.2, 3.5, 1.5, 0.2, 'Iris-setosa'),(5.2, 3.4, 1.4, 0.2, 'Iris-setosa'),(4.7, 3.2, 1.6, 0.2, 'Iris-setosa'),(4.8, 3.1, 1.6, 0.2, 'Iris-setosa'),(5.4, 3.4, 1.5, 0.4, 'Iris-setosa'),(5.2, 4.1, 1.5, 0.1, 'Iris-setosa'),(5.5, 4.2, 1.4, 0.2, 'Iris-setosa'),(4.9, 3.1, 1.5, 0.1, 'Iris-setosa'),(5, 3.2, 1.2, 0.2, 'Iris-setosa'),(5.5, 3.5, 1.3, 0.2, 'Iris-setosa'),(4.9, 3.1, 1.5, 0.1, 'Iris-setosa'),(4.4, 3, 1.3, 0.2, 'Iris-setosa'),(5.1, 3.4, 1.5, 0.2, 'Iris-setosa'),(5, 3.5, 1.3, 0.3, 'Iris-setosa'),(4.5, 2.3, 1.3, 0.3, 'Iris-setosa'),(4.4, 3.2, 1.3, 0.2, 'Iris-setosa'),(5, 3.5, 1.6, 0.6, 'Iris-setosa'),(5.1, 3.8, 1.9, 0.4, 'Iris-setosa'),(4.8, 3, 1.4, 0.3, 'Iris-setosa'),(5.1, 3.8, 1.6, 0.2, 'Iris-setosa'),(4.6, 3.2, 1.4, 0.2, 'Iris-setosa'),(5.3, 3.7, 1.5, 0.2, 'Iris-setosa'),(5, 3.3, 1.4, 0.2, 'Iris-setosa'),(7, 3.2, 4.7, 1.4, 'Iris-versicolor'),(6.4, 3.2, 4.5, 1.5, 'Iris-versicolor'),(6.9, 3.1, 4.9, 1.5, 'Iris-versicolor'),(5.5, 2.3, 4, 1.3, 'Iris-versicolor'),(6.5, 2.8, 4.6, 1.5, 'Iris-versicolor'),(5.7, 2.8, 4.5, 1.3, 'Iris-versicolor'),(6.3, 3.3, 4.7, 1.6, 'Iris-versicolor'),(4.9, 2.4, 3.3, 1, 'Iris-versicolor'),(6.6, 2.9, 4.6, 1.3, 'Iris-versicolor'),(5.2, 2.7, 3.9, 1.4, 'Iris-versicolor'),(5, 2, 3.5, 1, 'Iris-versicolor'),(5.9, 3, 4.2, 1.5, 'Iris-versicolor'),(6, 2.2, 4, 1, 'Iris-versicolor'),(6.1, 2.9, 4.7, 1.4, 'Iris-versicolor'),(5.6, 2.9, 3.6, 1.3, 'Iris-versicolor'),(6.7, 3.1, 4.4, 1.4, 'Iris-versicolor'),(5.6, 3, 4.5, 1.5, 'Iris-versicolor'),(5.8, 2.7, 4.1, 1, 'Iris-versicolor'),(6.2, 2.2, 4.5, 1.5, 'Iris-versicolor'),(5.6, 2.5, 3.9, 1.1, 'Iris-versicolor'),(5.9, 3.2, 4.8, 1.8, 'Iris-versicolor'),(6.1, 2.8, 4, 1.3, 'Iris-versicolor'),(6.3, 2.5, 4.9, 1.5, 'Iris-versicolor'),(6.1, 2.8, 4.7, 1.2, 'Iris-versicolor'),(6.4, 2.9, 4.3, 1.3, 'Iris-versicolor'),(6.6, 3, 4.4, 1.4, 'Iris-versicolor'),(6.8, 2.8, 4.8, 1.4, 'Iris-versicolor'),(6.7, 3, 5, 1.7, 'Iris-versicolor'),(6, 2.9, 4.5, 1.5, 'Iris-versicolor'),(5.7, 2.6, 3.5, 1, 'Iris-versicolor'),(5.5, 2.4, 3.8, 1.1, 'Iris-versicolor'),(5.5, 2.4, 3.7, 1, 'Iris-versicolor'),(5.8, 2.7, 3.9, 1.2, 'Iris-versicolor'),(6, 2.7, 5.1, 1.6, 'Iris-versicolor'),(5.4, 3, 4.5, 1.5, 'Iris-versicolor'),(6, 3.4, 4.5, 1.6, 'Iris-versicolor'),(6.7, 3.1, 4.7, 1.5, 'Iris-versicolor'),(6.3, 2.3, 4.4, 1.3, 'Iris-versicolor'),(5.6, 3, 4.1, 1.3, 'Iris-versicolor'),(5.5, 2.5, 4, 1.3, 'Iris-versicolor'),(5.5, 2.6, 4.4, 1.2, 'Iris-versicolor'),(6.1, 3, 4.6, 1.4, 'Iris-versicolor'),(5.8, 2.6, 4, 1.2, 'Iris-versicolor'),(5, 2.3, 3.3, 1, 'Iris-versicolor'),(5.6, 2.7, 4.2, 1.3, 'Iris-versicolor'),(5.7, 3, 4.2, 1.2, 'Iris-versicolor'),(5.7, 2.9, 4.2, 1.3, 'Iris-versicolor'),(6.2, 2.9, 4.3, 1.3, 'Iris-versicolor'),(5.1, 2.5, 3, 1.1, 'Iris-versicolor'),(5.7, 2.8, 4.1, 1.3, 'Iris-versicolor'),(6.3, 3.3, 6, 2.5, 'Iris-virginica'),(5.8, 2.7, 5.1, 1.9, 'Iris-virginica'),(7.1, 3, 5.9, 2.1, 'Iris-virginica'),(6.3, 2.9, 5.6, 1.8, 'Iris-virginica'),(6.5, 3, 5.8, 2.2, 'Iris-virginica'),(7.6, 3, 6.6, 2.1, 'Iris-virginica'),(4.9, 2.5, 4.5, 1.7, 'Iris-virginica'),(7.3, 2.9, 6.3, 1.8, 'Iris-virginica'),(6.7, 2.5, 5.8, 1.8, 'Iris-virginica'),(7.2, 3.6, 6.1, 2.5, 'Iris-virginica'),(6.5, 3.2, 5.1, 2, 'Iris-virginica'),(6.4, 2.7, 5.3, 1.9, 'Iris-virginica'),(6.8, 3, 5.5, 2.1, 'Iris-virginica'),(5.7, 2.5, 5, 2, 'Iris-virginica'),(5.8, 2.8, 5.1, 2.4, 'Iris-virginica'),(6.4, 3.2, 5.3, 2.3, 'Iris-virginica'),(6.5, 3, 5.5, 1.8, 'Iris-virginica'),(7.7, 3.8, 6.7, 2.2, 'Iris-virginica'),(7.7, 2.6, 6.9, 2.3, 'Iris-virginica'),(6, 2.2, 5, 1.5, 'Iris-virginica'),(6.9, 3.2, 5.7, 2.3, 'Iris-virginica'),(5.6, 2.8, 4.9, 2, 'Iris-virginica'),(7.7, 2.8, 6.7, 2, 'Iris-virginica'),(6.3, 2.7, 4.9, 1.8, 'Iris-virginica'),(6.7, 3.3, 5.7, 2.1, 'Iris-virginica'),(7.2, 3.2, 6, 1.8, 'Iris-virginica'),(6.2, 2.8, 4.8, 1.8, 'Iris-virginica'),(6.1, 3, 4.9, 1.8, 'Iris-virginica'),(6.4, 2.8, 5.6, 2.1, 'Iris-virginica'),(7.2, 3, 5.8, 1.6, 'Iris-virginica'),(7.4, 2.8, 6.1, 1.9, 'Iris-virginica'),(7.9, 3.8, 6.4, 2, 'Iris-virginica'),(6.4, 2.8, 5.6, 2.2, 'Iris-virginica'),(6.3, 2.8, 5.1, 1.5, 'Iris-virginica'),(6.1, 2.6, 5.6, 1.4, 'Iris-virginica'),(7.7, 3, 6.1, 2.3, 'Iris-virginica'),(6.3, 3.4, 5.6, 2.4, 'Iris-virginica'),(6.4, 3.1, 5.5, 1.8, 'Iris-virginica'),(6, 3, 4.8, 1.8, 'Iris-virginica'),(6.9, 3.1, 5.4, 2.1, 'Iris-virginica'),(6.7, 3.1, 5.6, 2.4, 'Iris-virginica'),(6.9, 3.1, 5.1, 2.3, 'Iris-virginica'),(5.8, 2.7, 5.1, 1.9, 'Iris-virginica'),(6.8, 3.2, 5.9, 2.3, 'Iris-virginica'),(6.7, 3.3, 5.7, 2.5, 'Iris-virginica'),(6.7, 3, 5.2, 2.3, 'Iris-virginica'),(6.3, 2.5, 5, 1.9, 'Iris-virginica'),(6.5, 3, 5.2, 2, 'Iris-virginica'),(6.2, 3.4, 5.4, 2.3, 'Iris-virginica'),(5.9, 3, 5.1, 1.8, 'Iris-virginica')
2018-01-12T18:14:15.879509Z    19 Query commit
2018-01-12T18:14:15.881477Z    19 Query rollback
2018-01-12T18:14:15.881695Z    19 Query rollback

我发现问题出在 SQLalchemy 试图获取当前数据库的名称时。是否可以修复错误或阻止 API 进行调用？我唯一的问题是程序因错误而崩溃，因为数据实际上在表中。

Answer 1

我发现问题的根本原因是 table_names 方法返回一个空列表。

len(engine.table_names())
0

我改变了这个:

# Connect to the mysql, and use database "datasets"
######### wrong part ##########
#engine = create_engine('mysql://root:root@127.0.0.1')
#engine.execute("USE Datasets") # select new db
######### Correct code ########
engine = create_engine('mysql+mysqlconnector://root:root@127.0.0.1/Datasets')

这样引擎就知道要从哪个数据库返回表。

table_names 的长度现在是正确的:

len(engine.table_names())
1