read_sql routes sql query as table name snowflake-sqlalchemy

[norton120]

Code Sample

with snowflake-sqlalchemy installed:

from sqlalchemy import create_engine
import pandas as pd

CONN_STRING='snowflake://user:password@account/database'
engine=create_engine(CONN_STRING)

conn=engine.connect()
sql= 'SELECT COUNT(*) FROM "DATABASE_NAME"."SCHEMA_NAME"."TABLE_NAME"'
##this errors (stack below)
r=pd.read_sql(sql)

## this does not 
r=pd.read_sql_query(sql)

stack:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/site-packages/pandas/io/sql.py", line 420, in read_sql
    pandas_sql.meta.reflect(only=[sql])
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 4261, in reflect
    "in %r%s: (%s)" % (bind.engine, s, ", ".join(missing))
sqlalchemy.exc.InvalidRequestError: Could not reflect: requested table(s) not available in Engine(snowflake://ethan:***@yxxxxxx.us-east-1/REDACTED_DB_NAME): (SELECT COUNT(*) FROM "REDACTED_DB_NAME"."REDACTED_DB_SCHEMA"."REDACTED_DB_TABLE")

Problem description

read_sql attempts to read sql queries as tables when running against snowflake. Calling read_sql_query is a workaround.

looks like this line:

   try:
        _is_table_name = pandas_sql.has_table(sql)
    except Exception:
        # using generic exception to catch errors from sql drivers (GH24988)
        _is_table_name = False

is incorrectly determining statements to be table names.

Expected Output

pd dataframe result of query.

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit : None python : 3.7.6.final.0 python-bits : 64 OS : Linux OS-release : 4.19.76-linuxkit machine : x86_64 processor : byteorder : little LC_ALL : None LANG : C.UTF-8 LOCALE : en_US.UTF-8

pandas : 0.25.3
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.0
pip : 19.3.1
setuptools : 44.0.0
Cython : None
pytest : 5.3.0
hypothesis : None
sphinx : 2.3.1
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.4 (dt dec pq3 ext lo64)
jinja2 : 2.10.3
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.4.1
sqlalchemy : 1.3.12
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None

[mgsnuno]

sql= 'SELECT COUNT(*) FROM DATABASE_NAME.SCHEMA_NAME.TABLE_NAME'
r=pd.read_sql(sql, con=engine)  # works without `"`

can this help? https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html

Still the behavior might be improved to handle cases like this.

what I cannot make it to work is:

r=pd.read_sql_table('DATABASE_NAME.SCHEMA_NAME.TABLE_NAME', con=engine)  # Could not reflect error

Edit:

import dask.dataframe as dd

dd.read_sql_table('DATABASE_NAME.SCHEMA_NAME.TABLE_NAME', connection_string, index_col='index_col')  # works

it might be related to (like you mentioned)

pandas/pandas/io/sql.py

Lines 415 to 419 in 5c37a0b

	try:
	_is_table_name = pandas_sql.has_table(sql)
	except Exception:
	# using generic exception to catch errors from sql drivers (GH24988)
	_is_table_name = False