Code Sample, a copy-pastable example if possible
import mysql.connector
from sqlalchemy import create_engine
import pandas as pd
# So I don't have to type my password
engine = create_engine('mysql://',
creator=lambda: mysql.connector.connect(
option_files='/Users/simnim/.mylogin_ue.cnf',
buffered=True,
charset='utf8'
)
)
# Code to make df ...
df.to_sql('my_table', engine, schema='my_db', index=False, if_exists='replace')
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
<ipython-input-166-decc2798114f> in <module>()
----> 1 df.to_sql('my_table', engine, schema='my_db', index=False)
/usr/local/lib/python2.7/site-packages/pandas/core/generic.pyc in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
1199 sql.to_sql(self, name, con, flavor=flavor, schema=schema,
1200 if_exists=if_exists, index=index, index_label=index_label,
-> 1201 chunksize=chunksize, dtype=dtype)
1202
1203 def to_pickle(self, path):
/usr/local/lib/python2.7/site-packages/pandas/io/sql.pyc in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
468 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
469 index_label=index_label, schema=schema,
--> 470 chunksize=chunksize, dtype=dtype)
471
472
/usr/local/lib/python2.7/site-packages/pandas/io/sql.pyc in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
1145 if_exists=if_exists, index_label=index_label,
1146 schema=schema, dtype=dtype)
-> 1147 table.create()
1148 table.insert(chunksize)
1149 if (not name.isdigit() and not name.islower()):
/usr/local/lib/python2.7/site-packages/pandas/io/sql.pyc in create(self)
582
583 def create(self):
--> 584 if self.exists():
585 if self.if_exists == 'fail':
586 raise ValueError("Table '%s' already exists." % self.name)
/usr/local/lib/python2.7/site-packages/pandas/io/sql.pyc in exists(self)
570
571 def exists(self):
--> 572 return self.pd_sql.has_table(self.name, self.schema)
573
574 def sql_schema(self):
/usr/local/lib/python2.7/site-packages/pandas/io/sql.pyc in has_table(self, name, schema)
1173 self.connectable.dialect.has_table,
1174 name,
-> 1175 schema or self.meta.schema,
1176 )
1177
/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)
1656 try:
1657 rs = connection.execution_options(
-> 1658 skip_user_error_events=True).execute(st)
1659 have = rs.fetchone() is not None
1660 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_db.my_table' doesn't exist
########### More minimal version ###########
sql_builder = pandas.io.sql.pandasSQL_builder(engine, schema='my_db')
# This one should return True
sql_builder.has_table('table_that_exists')
Out[169]:
True
# And it did.
# This one should return False
sql_builder.has_table('table_that_doesnt_exist')
### Similar trace as above (omitted for length)
# Nope, it breaks
Problem description
to_sql should create the table if it does not exist instead of raising an error. has_table should return False instead of raising an error
Expected Output
False
Output of pd.show_versions()
In [3]: pd.show_versions()
INSTALLED VERSIONS
------------------
commit: None
python: 2.7.13.final.0
python-bits: 64
OS: Darwin
OS-release: 16.4.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: None.None
pandas: 0.19.2
nose: None
pip: 9.0.1
setuptools: 32.1.0
Cython: None
numpy: 1.12.0
scipy: 0.18.1
statsmodels: None
xarray: None
IPython: 5.2.1
sphinx: None
patsy: None
dateutil: 2.6.0
pytz: 2016.10
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: 2.0.0
openpyxl: None
xlrd: 1.0.0
xlwt: 1.2.0
xlsxwriter: None
lxml: None
bs4: 4.5.3
html5lib: 0.9999999
httplib2: 0.9.2
apiclient: 1.6.1
sqlalchemy: 1.1.5
pymysql: 0.7.10.None
psycopg2: 2.6.2 (dt dec pq3 ext lo64)
jinja2: 2.9.5
boto: 2.45.0
pandas_datareader: None
Comment From: jorisvandenbossche
@simnim Can you do what I asked on gitter to check if a table exists:
import pandas.io.sql
sql_builder = pandas.io.sql.pandasSQL_builder(engine, schema='my_db')
sql_builder.has_table('table_that_exists')
but with the table that did not yet exist?
And if that raises an error, can you try this with just plain sqlalchemy: engine.has_table(name, schema=..) ?
Comment From: simnim
@jorisvandenbossche I ran that bit of code from your snippet already (it's below the giant stack trace)
For the engine.has_table() : It has the same behavior. The function call returns true if it the table exists and the function call raises an error if the table doesn't exist.
So it looks like it's actually an SQLAlchemy or mysql-connector bug. Probably SQLAlchemy. I'll raise the issue with them.
Comment From: jorisvandenbossche
For the engine.has_table() : It has the same behavior. The function call returns true if it the table exists and the function call raises an error if the table doesn't exist.
Then it indeed looks rather a bug in sqlalchemy or the mysql-connector. In any case, in pandas we rely on the fact that that function returns True or False. Good to raise the issue there.
Comment From: jorisvandenbossche
Closing this as not a pandas issue then. Did you already file it at sqlalchemy or mysql-connector?
Comment From: simnim
I raised the issue on the SQLAlchemy IRC channel but nobody noticed. I'm going to raise it on their bitbucket page hopefully today.
Comment From: simnim
We sorted it out over on their bitbucket. Turns out instead of 'mysql://' I should have used 'mysql+mysqlconnector://'
Comment From: jorisvandenbossche
@simnim Thanks for letting it know