read_sql delegation dependent on occurence of 'select' in query

[sweettuse]

if 'select' in sql.lower():

there's a bug in read_sql line 377 in pandas.io.sql. the problem is that if your query doesn't contain the word 'select', it will not be run. so if you want to exec a stored proc, e.g., it doesn't work.

however, if you override this check, then it executes stored procs successfully.

this used to work in 13.1.

pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 2.7.6.final.0
python-bits: 64
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 45 Stepping 7, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None

pandas: 0.14.0
nose: 1.3.0
Cython: 0.20.1
numpy: 1.8.0
scipy: 0.13.3
statsmodels: 0.5.0
IPython: 2.0.0
sphinx: 1.2.1
patsy: 0.2.1
scikits.timeseries: None
dateutil: 1.5
pytz: 2013b
bottleneck: None
tables: 3.1.0
numexpr: 2.3.1
matplotlib: 1.3.1
openpyxl: 1.8.2
xlrd: 0.9.2
xlwt: 0.7.5
xlsxwriter: 0.5.2
lxml: 3.3.1
bs4: 4.3.1
html5lib: None
bq: None
apiclient: None
rpy2: None
sqlalchemy: 0.9.4
pymysql: None
psycopg2: None

[jorisvandenbossche]

I don't know if it is really a bug, but certainly a restriction of the current implementation. But you are right this this restriction can cause a regression from existing code that worked in 0.13.

Full explanation: as stated in the docs and the docstring (http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql), read_sql is just a wrapper around the actual functions read_sql_query and read_sql_table (since the sqlalchemy refactor).
So if you certainly want a query, the recommended functions (starting from 0.14) is actually read_sql_query to be explicit.

But I agree that this restriction in the delegation in read_sql (https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L377) is a problem, and not thinking about stored procedures was an oversight. I thought I first had an implementation without this if 'select' in .., but I can't remember exactly the reason why in the end I thought this was needed. I should look at that again.

Thanks for the report!

[jorisvandenbossche]

I found the reason, my commit here: 165b604 (reason I mentioned there: "changed way to delegate read_sql/table without using has_table for backwards compatibility (because this gave an error when uqing a mysql connection but not specifying the flavor)"). So it was changed to keep the deprecated mysql legacy working. But there should be another way to circumvent this.

[jorisvandenbossche]

@sweettuse For now, you can indeed add --select or something like that, or use read_sql_query?