I am running the following code (pandas 0.18.0, sqlalchemy 1.0.12):
skus = pd.read_sql_table('ads_skus', SQLALCHEMY_CONN, schema='a')
I receive the error:
ValueError: Table ads_skus not found
However, if I run:
i = inspect(SQLALCHEMY_CONN)
for table_name in i.get_table_names(schema='a'):
print(table_name)
where SQLALCHEMY is an Engine object. I see ads_skus in the list that is output. This looks like a bug if pandas is using the SQLAlchemy engine.
Note: I used the same function for a bunch of similar tables (which are also in the schema 'a'). They all worked - it only fails on this table.
Note 2: I noticed that "ads_skus" is the only table which has foreign keys to another schema. That seems to be the differentiating factor.
Comment From: jorisvandenbossche
@alexpetralia Can you try what engine.has_table(..) gives? (http://docs.sqlalchemy.org/en/rel_0_9/core/connections.html#sqlalchemy.engine.Engine.has_table)
Further, if you would be able to come up with a reproducible example (eg see if you can reproduce the issue when making a new table from some code mimicking the problematic table), that would help
Comment From: alexpetralia
Hi @jorisvandenbossche, here is the output:
CONNECTION.has_table('ads_skus', schema='a')
Out[3]: True
I will try the second step tomorrow.
Update: I also it on another machine. Same SQLAlchemy again shows the table but pandas cannot read it.
Comment From: jorisvandenbossche
Can you also try the following:
from sqlalchemy.schema import MetaData
meta = MetaData(engine, schema='a')
meta.reflect(only=['ads_skus'], views=True)
(this is possibly where the error is raised in the code). If this does not raise, can you also provide the full error traceback?
Comment From: alexpetralia
Very interesting:
from sqlalchemy.schema import MetaData
meta = MetaData(SQLALCHEMY_CONN, schema='a')
meta.reflect(only=['ads_skus'], views=True)
Traceback (most recent call last):
File "<ipython-input-9-fb619c55f525>", line 3, in <module>
meta.reflect(only=['ads_skus'], views=True)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 3655, in reflect
Table(name, self, **reflect_opts)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 416, in __new__
metadata._remove_table(name, schema)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 184, in reraise
raise value
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 411, in __new__
table._init(name, metadata, *args, **kw)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 484, in _init
self._autoload(metadata, autoload_with, include_columns)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 496, in _autoload
self, include_columns, exclude_columns
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1477, in run_callable
return callable_(self, *args, **kwargs)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 364, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\reflection.py", line 578, in reflecttable
exclude_columns, reflection_options)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\reflection.py", line 695, in _reflect_fk
**reflection_options
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 416, in __new__
metadata._remove_table(name, schema)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 184, in reraise
raise value
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 411, in __new__
table._init(name, metadata, *args, **kw)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 484, in _init
self._autoload(metadata, autoload_with, include_columns)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\sql\schema.py", line 496, in _autoload
self, include_columns, exclude_columns
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1477, in run_callable
return callable_(self, *args, **kwargs)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 364, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\reflection.py", line 563, in reflecttable
table_name, schema, **table.dialect_kwargs):
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\reflection.py", line 369, in get_columns
**kw)
File "<string>", line 2, in get_columns
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\reflection.py", line 54, in cache
ret = fn(self, con, *args, **kw)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\dialects\postgresql\base.py", line 2376, in get_columns
info_cache=kw.get('info_cache'))
File "<string>", line 2, in get_table_oid
File "C:\Anaconda3\lib\site-packages\sqlalchemy\engine\reflection.py", line 54, in cache
ret = fn(self, con, *args, **kw)
File "C:\Anaconda3\lib\site-packages\sqlalchemy\dialects\postgresql\base.py", line 2274, in get_table_oid
raise exc.NoSuchTableError(table_name)
NoSuchTableError: sku
'sku' is the foreign table in the public schema which this table 'ads_skus' references.
Comment From: jorisvandenbossche
Maybe it's a problem for sqlalchemy that this is in another schema? Anyway, this seems like an issue with sqalchemy, so best to raise it over there: https://bitbucket.org/zzzeek/sqlalchemy/issues
Comment From: alexpetralia
I think you are right - it looks like it is only looking within the a schema for its tables. Thank you for your help!
Comment From: jorisvandenbossche
@alexpetralia I am going to close this issue here for now (as it does not seem pandas can do anything about this). But thanks for reporting!
Comment From: alexpetralia
@jorisvandenbossche It looks like it actually may be an issue with pandas: https://bitbucket.org/zzzeek/sqlalchemy/issues/3716/sqlalchemy-not-finding-tables-in-two
Comment From: alexpetralia
Hey @jorisvandenbossche, I don't think the proposed solution solved the problem.
I've updated my read_sql_table() function to be:
con = _engine_builder(con)
if not _is_sqlalchemy_connectable(con):
raise NotImplementedError("read_sql_table only supported for "
"SQLAlchemy connectable.")
import sqlalchemy
from sqlalchemy.schema import MetaData
meta = MetaData(con)
try:
meta.reflect(only=[table_name], views=True, schema=schema)
except sqlalchemy.exc.InvalidRequestError:
raise ValueError("Table %s not found" % table_name)
pandas_sql = SQLDatabase(con, meta=meta)
table = pandas_sql.read_table(
table_name, index_col=index_col, coerce_float=coerce_float,
parse_dates=parse_dates, columns=columns, chunksize=chunksize)
if table is not None:
return table
else:
raise ValueError("Table %s not found" % table_name, con)
However, now no tables are successfully read (instead of just the one which failed previously), and instead I receive the error:
Traceback (most recent call last):
File "<ipython-input-1-efda3b5c9d21>", line 1, in <module>
runfile('/home/alex/Desktop/SVN_Development/advertising/uploader/main.py', wdir='/home/alex/Desktop/SVN_Development/advertising/uploader')
File "/usr/lib/python3/dist-packages/spyderlib/widgets/externalshell/sitecustomize.py", line 601, in runfile
execfile(filename, namespace)
File "/usr/lib/python3/dist-packages/spyderlib/widgets/externalshell/sitecustomize.py", line 80, in execfile
exec(compile(open(filename, 'rb').read(), filename, 'exec'), namespace)
File "/home/alex/Desktop/SVN_Development/advertising/uploader/main.py", line 29, in <module>
campaigns = pd.read_sql_table('ads_campaigns', SQLALCHEMY_CONN, schema='amazon')
File "/usr/local/lib/python3.4/dist-packages/pandas/io/sql.py", line 364, in read_sql_table
parse_dates=parse_dates, columns=columns, chunksize=chunksize)
File "/usr/local/lib/python3.4/dist-packages/pandas/io/sql.py", line 1127, in read_table
table = SQLTable(table_name, self, index=index_col, schema=schema)
File "/usr/local/lib/python3.4/dist-packages/pandas/io/sql.py", line 672, in __init__
self.table = self.pd_sql.get_table(self.name, self.schema)
File "/usr/local/lib/python3.4/dist-packages/pandas/io/sql.py", line 1285, in get_table
for column in tbl.columns:
AttributeError: 'NoneType' object has no attribute 'columns'
Comment From: alexpetralia
@jorisvandenbossche I also just implemented the SQLAlchemy bug fix. If I leave the pandas code unchanged, I get the ads_skus not found still, and if I update the code, I get the error traceback above. I still think this issue is unresolved - I'm not sure on whose end it's on though.
Regardless, I think the suggested sql commit above breaks the function because it no longer works for any of my tables.
Comment From: alexpetralia
My coworker just informed me of a solution:
skus = pd.read_sql('SELECT * FROM a.ads_skus;', SQLALCHEMY_CONN)
Voila! Thanks for all your help though.
Comment From: edrossy
Solution for me was to specify the schema as well.