3

I'm trying to do a simple upload of the dataframe to mysql database.

Which works fine if the database does not exist. But when I try to add new data to the database I get an error.

This is my code:

import sqlalchemy
db_username = 'root'
db_pasword = ''
db_ip       = '127.0.0.1'
db_name     = 'db'
con = sqlalchemy.create_engine('mysql+mysqlconnector://{0}:{1}@{2}/{3}'.format(db_username, db_pasword, db_ip, db_name))

df.to_sql(name='foo', con=con, if_exists='replace')

And this is the error:

---------------------------------------------------------------------------
InvalidRequestError                       Traceback (most recent call last)
<ipython-input-35-3d595e7cefaf> in <module>
----> 1 df.to_sql(name='foo', con=con, if_exists='replace')

/opt/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2710             chunksize=chunksize,
   2711             dtype=dtype,
-> 2712             method=method,
   2713         )
   2714 

/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
    516         chunksize=chunksize,
    517         dtype=dtype,
--> 518         method=method,
    519     )
    520 

/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
   1317             dtype=dtype,
   1318         )
-> 1319         table.create()
   1320         table.insert(chunksize, method=method)
   1321         if not name.isdigit() and not name.islower():

/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py in create(self)
    645                 )
    646             elif self.if_exists == "replace":
--> 647                 self.pd_sql.drop_table(self.name, self.schema)
    648                 self._execute_create()
    649             elif self.if_exists == "append":

/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py in drop_table(self, table_name, schema)
   1364         schema = schema or self.meta.schema
   1365         if self.has_table(table_name, schema):
-> 1366             self.meta.reflect(only=[table_name], schema=schema)
   1367             self.get_table(table_name, schema).drop()
   1368             self.meta.clear()

/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/schema.py in reflect(self, bind, schema, views, only, extend_existing, autoload_replace, resolve_fks, **dialect_kwargs)
   4247                     raise exc.InvalidRequestError(
   4248                         "Could not reflect: requested table(s) not available "
-> 4249                         "in %r%s: (%s)" % (bind.engine, s, ", ".join(missing))
   4250                     )
   4251                 load = [

InvalidRequestError: Could not reflect: requested table(s) not available in Engine(mysql+mysqlconnector://root:***@127.0.0.1/db): (foo)

I'd appreciate any help or advice.

notADeveloperrr
  • 101
  • 1
  • 10
  • 2
    Try using a lowercase version of whatever you're actually using instead of `'foo'` – OD1995 Jan 28 '20 at 12:13
  • Does this answer your question? [How to create a new table in a MySQL DB from a pandas dataframe](https://stackoverflow.com/questions/51236304/how-to-create-a-new-table-in-a-mysql-db-from-a-pandas-dataframe) – Giorgos Myrianthous Jan 28 '20 at 12:16

1 Answers1

0

This could result from a view with the same name that already exists in the db. I executed DROP VIEW <view_name> and the error was solved.

Yuv_c
  • 88
  • 6