pandas DataFrame.to_sql() function if_exists parameter not working

Question

When I try to pass the if_exists='replace' parameter to to_sql I get a programming error telling me the table already exists:

>>> foobar.to_sql('foobar', engine, if_exists=u'replace')
...
ProgrammingError: (ProgrammingError) ('42S01', "[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'foobar' in the database. (2714) (SQLExecDirectW)") u'\nCREATE TABLE foobar...

From the docs it sounds like this option should drop the table and recreate it, which is not the observed behavior. Works fine if the table does not exist already. Any ideas if this is a bug or I'm doing something wrong?

I'm using pandas 0.14 and sqlalchemy 0.8.3 and the enthought canopy python distro, and I'm connecting to SQL Server.

EDIT As per joris' comments:

>>>pd.__version__
Out[4]: '0.14.0'
>>>pd.io.sql.has_table('foobar', engine)
Out[7]: False
>>>foobar.to_sql('foobar', engine, if_exists=u'replace', index=False)
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-9-2f4ac7ed7f23> in <module>()
----> 1 foobar.to_sql('foobar', engine, if_exists=u'replace', index=False)

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\pandas\core\generic.pyc in to_sql(self, name, con, flavor, if_exists, index, index_label)
    948         sql.to_sql(
    949             self, name, con, flavor=flavor, if_exists=if_exists, index=index,
--> 950             index_label=index_label)
    951 
    952     def to_pickle(self, path):

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\pandas\io\sql.pyc in to_sql(frame, name, con, flavor, if_exists, index, index_label)
    438 
    439     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
--> 440                       index_label=index_label)
    441 
    442 

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\pandas\io\sql.pyc in to_sql(self, frame, name, if_exists, index, index_label)
    812         table = PandasSQLTable(
    813             name, self, frame=frame, index=index, if_exists=if_exists,
--> 814             index_label=index_label)
    815         table.insert()
    816 

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\pandas\io\sql.pyc in __init__(self, name, pandas_sql_engine, frame, index, if_exists, prefix, index_label)
    530             else:
    531                 self.table = self._create_table_statement()
--> 532                 self.create()
    533         else:
    534             # no data provided, read-only mode

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\pandas\io\sql.pyc in create(self)
    546 
    547     def create(self):
--> 548         self.table.create()
    549 
    550     def insert_statement(self):

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\schema.pyc in create(self, bind, checkfirst)
    614         bind._run_visitor(ddl.SchemaGenerator,
    615                             self,
--> 616                             checkfirst=checkfirst)
    617 
    618     def drop(self, bind=None, checkfirst=False):

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\base.pyc in _run_visitor(self, visitorcallable, element, connection, **kwargs)
   1477                                     connection=None, **kwargs):
   1478         with self._optional_conn_ctx_manager(connection) as conn:
-> 1479             conn._run_visitor(visitorcallable, element, **kwargs)
   1480 
   1481     class _trans_ctx(object):

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\base.pyc in _run_visitor(self, visitorcallable, element, **kwargs)
   1120     def _run_visitor(self, visitorcallable, element, **kwargs):
   1121         visitorcallable(self.dialect, self,
-> 1122                             **kwargs).traverse_single(element)
   1123 
   1124 

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\sql\visitors.pyc in traverse_single(self, obj, **kw)
    120             meth = getattr(v, "visit_%s" % obj.__visit_name__, None)
    121             if meth:
--> 122                 return meth(obj, **kw)
    123 
    124     def iterate(self, obj):

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\ddl.pyc in visit_table(self, table, create_ok)
     87                 self.traverse_single(column.default)
     88 
---> 89         self.connection.execute(schema.CreateTable(table))
     90 
     91         if hasattr(table, 'indexes'):

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\base.pyc in execute(self, object, *multiparams, **params)
    660                                                 object,
    661                                                 multiparams,
--> 662                                                 params)
    663         else:
    664             raise exc.InvalidRequestError(

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_ddl(self, ddl, multiparams, params)
    718             compiled,
    719             None,
--> 720             compiled
    721         )
    722         if self._has_events:

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    872                                 parameters,
    873                                 cursor,
--> 874                                 context)
    875 
    876         if self._has_events:

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1022                                         self.dialect.dbapi.Error,
   1023                                         connection_invalidated=self._is_disconnect),
-> 1024                                     exc_info
   1025                                 )
   1026 

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\util\compat.pyc in raise_from_cause(exception, exc_info)
    194         # the code line where the issue occurred
    195         exc_type, exc_value, exc_tb = exc_info
--> 196         reraise(type(exception), exception, tb=exc_tb)
    197 
    198 

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    865                                     statement,
    866                                     parameters,
--> 867                                     context)
    868         except Exception, e:
    869             self._handle_dbapi_exception(

C:\Users\AppData\Local\Enthought\Canopy\User\lib\site-packages\sqlalchemy\engine\default.pyc in do_execute(self, cursor, statement, parameters, context)
    322 
    323     def do_execute(self, cursor, statement, parameters, context=None):
--> 324         cursor.execute(statement, parameters)
    325 
    326     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (ProgrammingError) ('42S01', "[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'foobar' in the database. (2714) (SQLExecDirectW)") u'\nCREATE TABLE foobar (\n\tfactor TEXT NULL, \n\tnum_avail INTEGER NULL, \n\tpct_avail FLOAT NULL, \n\tmin FLOAT NULL, \n\tmax FLOAT NULL, \n\tptile1 FLOAT NULL, \n\tptile99 FLOAT NULL, \n\tpct_yday FLOAT NULL, \n\tdiff_yday FLOAT NULL, \n\tcorr_yday FLOAT NULL\n)\n\n' ()

Answer 1

This issue seem to have been fixed in 0.14.1 reference

Solution

Update your pandas