Not sure if this is a pandas bug or an upstream one, but here's an example of the bug (pandas-0.14.1, mariadb 10, sqlalchemy-0.9.4)
engine=create_engine('mysql://{username}:{password}@{host}/{database}?charset=utf8'.format(**db))
pandas.io.sql.read_sql('SELECT onlinetransactions.id FROM onlinetransactions LIMIT 1', engine) #Does what you'd expect
pandas.io.sql.read_sql('SELECT onlinetransactions.id as firstid FROM onlinetransactions LIMIT 1', engine) #Fails
The error you get back is:
C:\Anaconda\lib\site-packages\pandas\io\sql.pyc in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns)
421 coerce_float=coerce_float, parse_dates=parse_dates)
422
--> 423 if pandas_sql.has_table(sql):
424 pandas_sql.meta.reflect(only=[sql])
425 return pandas_sql.read_table(
C:\Anaconda\lib\site-packages\pandas\io\sql.pyc in has_table(self, name)
847
848 def has_table(self, name):
--> 849 return self.engine.has_table(name)
850
851 def get_table(self, table_name):
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in has_table(self, table_name, schema)
1757
1758 """
-> 1759 return self.run_callable(self.dialect.has_table, table_name, schema)
1760
1761 def raw_connection(self):
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in run_callable(self, callable_, *args, **kwargs)
1661 """
1662 with self.contextual_connect() as conn:
-> 1663 return conn.run_callable(callable_, *args, **kwargs)
1664
1665 def execute(self, statement, *multiparams, **params):
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in run_callable(self, callable_, *args, **kwargs)
1188
1189 """
-> 1190 return callable_(self, *args, **kwargs)
1191
1192 def _run_visitor(self, visitorcallable, element, **kwargs):
C:\Anaconda\lib\site-packages\sqlalchemy\dialects\mysql\base.pyc in has_table(self, connection, table_name, schema)
2274 try:
2275 try:
-> 2276 rs = connection.execute(st)
2277 have = rs.fetchone() is not None
2278 rs.close()
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in execute(self, object, *multiparams, **params)
710 """
711 if isinstance(object, util.string_types[0]):
--> 712 return self._execute_text(object, multiparams, params)
713 try:
714 meth = object._execute_on_connection
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_text(self, statement, multiparams, params)
859 statement,
860 parameters,
--> 861 statement, parameters
862 )
863 if self._has_events or self.engine._has_events:
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
945 parameters,
946 cursor,
--> 947 context)
948
949 if self._has_events or self.engine._has_events:
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
1106 self.dialect.dbapi.Error,
1107 connection_invalidated=self._is_disconnect),
-> 1108 exc_info
1109 )
1110
C:\Anaconda\lib\site-packages\sqlalchemy\util\compat.pyc in raise_from_cause(exception, exc_info)
183 exc_info = sys.exc_info()
184 exc_type, exc_value, exc_tb = exc_info
--> 185 reraise(type(exception), exception, tb=exc_tb)
186
187 if py3k:
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
938 statement,
939 parameters,
--> 940 context)
941 except Exception as e:
942 self._handle_dbapi_exception(
C:\Anaconda\lib\site-packages\sqlalchemy\engine\default.pyc in do_execute(self, cursor, statement, parameters, context)
433
434 def do_execute(self, cursor, statement, parameters, context=None):
--> 435 cursor.execute(statement, parameters)
436
437 def do_execute_no_params(self, cursor, statement, context=None):
C:\Anaconda\lib\site-packages\MySQLdb\cursors.pyc in execute(self, query, args)
203 del tb
204 self.messages.append((exc, value))
--> 205 self.errorhandler(self, exc, value)
206 self._executed = query
207 if not self._defer_warnings: self._warning_check()
C:\Anaconda\lib\site-packages\MySQLdb\connections.pyc in defaulterrorhandler(***failed resolving arguments***)
34 del cursor
35 del connection
---> 36 raise errorclass, errorvalue
37
38 re_numeric_part = re.compile(r"^(\d+)")
ProgrammingError: (ProgrammingError) (1103, "Incorrect table name 'SELECT onlinetransactions.id as firstid FROM onlinetransactions LIMIT 1'") 'DESCRIBE `SELECT onlinetransactions.id as firstid FROM onlinetransactions LIMIT 1`' ()
So it never gets as far as running the actual query, because it's tried to run a DESCRIBE query with ``` quotes which fails. i.e.
MariaDB [transactions]> DESCRIBE `SELECT onlinetransactions.id as firstid FROM onlinetransactions LIMIT 1`;
ERROR 1103 (42000): Incorrect table name 'SELECT onlinetransactions.id as firstid FROM onlinetransactions LIMIT 1'
But
MariaDB [transactions]> DESCRIBE SELECT onlinetransactions.id as firstid FROM onlinetransactions LIMIT 1;
+------+-------------+--------------------+-------+---------------+--------------------------------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+-------+---------------+--------------------------------+---------+------+----------+-------------+
| 1 | SIMPLE | onlinetransactions | index | NULL | ix_OnlineTransactions_Rpt_Year | 5 | NULL | 11485535 | Using index |
+------+-------------+--------------------+-------+---------------+--------------------------------+---------+------+----------+-------------+
1 row in set (0.00 sec)
Ok. So looking at the stacktrace, I reckon this is a pandas bug as it seems to be calling has_table on my sql query, which doesn't seem to make any sense?
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4