engine.has_table() throws an error instead of returning False when a table does not exist
Hi there, I've got the following versions: SQLAlchemy==1.1.6 mysql-connector==2.1.4
When running the following code I get an error:
import mysql.connector
from sqlalchemy import create_engine
engine = create_engine('mysql://',
creator=lambda: mysql.connector.connect(
option_files='/Users/me/.mylogin.cnf'
)
)
engine.has_table('table_that_exists', schema='my_schema')
# -> returns True
engine.has_table('table_that_does_not_exist', schema='my_schema')
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
<ipython-input-18-c38b6b523753> in <module>()
----> 1 engine.has_table('table_that_does_not_exist',schema='my_schema')
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in has_table(self, table_name, schema)
2131
2132 """
-> 2133 return self.run_callable(self.dialect.has_table, table_name, schema)
2134
2135 def _wrap_pool_connect(self, fn, connection):
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in run_callable(self, callable_, *args, **kwargs)
2034 """
2035 with self.contextual_connect() as conn:
-> 2036 return conn.run_callable(callable_, *args, **kwargs)
2037
2038 def execute(self, statement, *multiparams, **params):
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in run_callable(self, callable_, *args, **kwargs)
1523
1524 """
-> 1525 return callable_(self, *args, **kwargs)
1526
1527 def _run_visitor(self, visitorcallable, element, **kwargs):
/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.pyc in has_table(self, connection, table_name, schema)
1659 try:
1660 rs = connection.execution_options(
-> 1661 skip_user_error_events=True).execute(st)
1662 have = rs.fetchone() is not None
1663 rs.close()
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
937 """
938 if isinstance(object, util.string_types[0]):
--> 939 return self._execute_text(object, multiparams, params)
940 try:
941 meth = object._execute_on_connection
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params)
1095 statement,
1096 parameters,
-> 1097 statement, parameters
1098 )
1099 if self._has_events or self.engine._has_events:
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
1187 parameters,
1188 cursor,
-> 1189 context)
1190
1191 if self._has_events or self.engine._has_events:
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
1394 )
1395 else:
-> 1396 util.reraise(*exc_info)
1397
1398 finally:
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
1180 statement,
1181 parameters,
-> 1182 context)
1183 except BaseException as e:
1184 self._handle_dbapi_exception(
/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
468
469 def do_execute(self, cursor, statement, parameters, context=None):
--> 470 cursor.execute(statement, parameters)
471
472 def do_execute_no_params(self, cursor, statement, context=None):
/usr/local/lib/python2.7/site-packages/mysql/connector/cursor.pyc in execute(self, operation, params, multi)
513 else:
514 try:
--> 515 self._handle_result(self._connection.cmd_query(stmt))
516 except errors.InterfaceError:
517 if self._connection._have_next_result: # pylint: disable=W0212
/usr/local/lib/python2.7/site-packages/mysql/connector/connection.pyc in cmd_query(self, query, raw, buffered, raw_as_string)
486 if not isinstance(query, bytes):
487 query = query.encode('utf-8')
--> 488 result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
489
490 if self._have_next_result:
/usr/local/lib/python2.7/site-packages/mysql/connector/connection.pyc in _handle_result(self, packet)
393 return self._handle_eof(packet)
394 elif packet[4] == 255:
--> 395 raise errors.get_exception(packet)
396
397 # We have a text result set
ProgrammingError: 1146 (42S02): Table 'my_schema.table_that_does_not_exist' doesn't exist
the ~/.mylogin.cnf' file is an unencrypted config file https://dev.mysql.com/doc/refman/5.7/en/option-files.html
Comments (8)
-
repo owner -
repo owner OK re: the internalerror issue, mysql.connector requires the "buffered" option to work with SQLAlchemy, normally passed in via the dialect. I can get past the issue with the option file as follows::
engine = create_engine( 'mysql+mysqlconnector://', creator=lambda: mysql.connector.connect( option_files='/home/classic/mylogin.cnf', buffered=True ) )
and then, I get False back, e.g. no bug. Similarly, the easier way use the option file is as a standard option:
engine = create_engine( "mysql+mysqlconnector:///?option_files=/home/classic/mylogin.cnf" )
also no bug here.
-
repo owner also no issue with ipython.
-
reporter Ah! You replied while I was typing, here's what I was going to say
- Makes no difference
- Nope, MacOS Sierra
- It works correctly. BUT Having the options file is nice because I don't need to deal with passwords in my code, any ugly config parsing nonsense, and no string formatting.
- But it'd like to use the options file and I don't see that supported in PyMySQL. I just bumped the issue thread for the feature request.
-
reporter Ah!!!! After I added '+mysqlconnector' it worked.
(And yeah I had the buffered=True, but I deleted that from my example code by mistake, good catch)
-
reporter Thanks!
Not sure how to work around dumb input like this. That's the only way it's failed me so far. Otherwise it's been flawless even without the
'+mysqlconnector'
Which is what made it extra confusing. -
repo owner oh, you didn't use the right dialect, I missed that :)
-
repo owner - changed status to resolved
we both missed the same bit in the code example ("mysqlconnector").
- Log in to comment
I can't reproduce with: 1. mysql-connector 2.1.4 or 2.1.5 2. not using the "option_files" parameter. 3. using mariadb instead of mysql
I'm not able to run with "option_files" because when I try to make a file by hand, I get "(mysql.connector.errors.InternalError) Unread result found", and this is a separate bug in mysql.connector, or perhaps because I'm on mariadb.
The stack trace here makes no sense because 1146 is the correct error code, we look into it and we return False if that's the error code. The only possibility is that the exception object being raised is incorrect, either from the wrong hierarchy or has the wrong structure (e.g. missing "errno" perhaps).
I'd suggest trying other things like 1. don't use ipython 2. is this windows? 3. what happens if you don't use the option_files thing? 4. use a more supportable driver like PyMySQL.