pandas to_sql fails when using if_exists

Question

I am trying to use the if_exists pandas to_sql arguments with sqlalchemy and i cannot seem to get it to work

versions

sqlalchemy version 1.2.12
pandas version 0.23.4

python 3.5.2

engine = create_engine(postgress_connection_string, echo=True)
df1 = pd.DataFrame([['Abe','1','True'],['Ben','2','True'],['Charlie','3','True']], columns=['Name','Number','Other'])

df1.to_sql('df_tbl',engine) #works if table doesn't exist, else fails
df1.to_sql('df_tbl',engine, if_exists='replace') #fails with error message

error: AttributeError: 'Connection' object has no attribute '_engine'

debug output

--------------------------------------------------------------------------- AttributeError Traceback (most recent call last) in 7 df_rides_stops.rename(index=str, columns={'orig_ad_id':'ad_id', 'orig_stay_time':'stay_time','orig_arrival_utc':'arrival_utc'},inplace=True) 8 df_rides_stops.head() ----> 9 df_rides_stops.to_sql('rides',engine, if_exists='replace') 10

~/dev/Ride/qgis3/lib/python3.5/site-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype) 2128 sql.to_sql(self, name, con, schema=schema, if_exists=if_exists, 2129
index=index, index_label=index_label, chunksize=chunksize, -> 2130 dtype=dtype) 2131 2132 def to_pickle(self, path, compression='infer',

~/dev/Ride/qgis3/lib/python3.5/site-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype) 448 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index, 449 index_label=index_label, schema=schema, --> 450 chunksize=chunksize, dtype=dtype) 451 452

~/dev/Ride/qgis3/lib/python3.5/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype) 1124
if_exists=if_exists, index_label=index_label, 1125
schema=schema, dtype=dtype) -> 1126 table.create() 1127 table.insert(chunksize) 1128 if (not name.isdigit() and not name.islower()):

~/dev/Ride/qgis3/lib/python3.5/site-packages/pandas/io/sql.py in create(self) 563 raise ValueError("Table '%s' already exists." % self.name) 564 elif self.if_exists == 'replace': --> 565 self.pd_sql.drop_table(self.name, self.schema) 566 self._execute_create() 567 elif self.if_exists == 'append':

~/dev/Ride/qgis3/lib/python3.5/site-packages/pandas/io/sql.py in drop_table(self, table_name, schema) 1173 schema = schema or self.meta.schema 1174 if self.has_table(table_name, schema): -> 1175 self.meta.reflect(only=[table_name], schema=schema) 1176 self.get_table(table_name, schema).drop() 1177 self.meta.clear()

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in reflect(self, bind, schema, views, only, extend_existing, autoload_replace, **dialect_kwargs) 3961 for name in load: 3962 try: -> 3963 Table(name, self, **reflect_opts) 3964 except exc.UnreflectableTableError as uerr: 3965
util.warn("Skipping table %s: %s" % (name, uerr))

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in new(cls, *args, **kw) 455 except: 456 with util.safe_reraise(): --> 457 metadata._remove_table(name, schema) 458 459 @property

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py in exit(self, type_, value, traceback) 64 self._exc_info = None # remove potential circular references 65 if not self.warn_only: ---> 66 compat.reraise(exc_type, exc_value, exc_tb) 67 else: 68 if not compat.py3k and self._exc_info and self._exc_info[1]:

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause) 247 if value.traceback is not tb: 248 raise value.with_traceback(tb) --> 249 raise value 250 251 else:

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in new(cls, *args, **kw) 450 metadata._add_table(name, schema, table) 451 try: --> 452 table._init(name, metadata, *args, **kw) 453 table.dispatch.after_parent_attach(table, metadata) 454 return table

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in _init(self, name, metadata, *args, **kwargs) 532 self._autoload( 533 metadata, autoload_with, --> 534 include_columns, _extend_on=_extend_on) 535 536 # initialize all the column, etc. objects. done after reflection to

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in _autoload(self, metadata, autoload_with, include_columns, exclude_columns, _extend_on) 545 autoload_with.dialect.reflecttable, 546 self, include_columns, exclude_columns, --> 547 _extend_on=_extend_on 548 ) 549 else:

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in run_callable(self, callable_, *args, **kwargs) 1543 1544
""" -> 1545 return callable_(self, *args, **kwargs) 1546 1547 def _run_visitor(self, visitorcallable, element, **kwargs):

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/engine/default.py in reflecttable(self, connection, table, include_columns, exclude_columns, **opts) 387 def reflecttable( 388 self, connection, table, include_columns, exclude_columns, **opts): --> 389 insp = reflection.Inspector.from_engine(connection) 390 return insp.reflecttable( 391 table, include_columns, exclude_columns, **opts)

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py in from_engine(cls, bind) 132 """ 133 if hasattr(bind.dialect, 'inspector'): --> 134 return bind.dialect.inspector(bind) 135 return Inspector(bind) 136

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py in init(self, conn) 2026 2027 def init(self, conn): -> 2028 reflection.Inspector.init(self, conn) 2029 2030 def get_table_oid(self, table_name, schema=None):

~/dev/Ride/qgis3/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py in init(self, bind) 101 # set the engine 102 if hasattr(bind, 'engine'): --> 103 self.engine = bind._engine 104 else: 105 self.engine = bind

AttributeError: 'Connection' object has no attribute '_engine'

pandas to_sql fails when using if_exists

versions

debug output

Answers (1)

Related Questions