1

I recently migrated the project and databases to another server. Both servers are ubuntu 20.04.

About the new database(MySql), I created the databse first and then loads the dumped file from the old databse. So basically the databses are the same.

However, when I tried to use sqlalchemy to reflect on the new database, it does not work and throws out errors like this:

> File "/Users/max/Dropbox/Python Code/ym/db_api.py", line 6, in
> <module>
>     from db import engine as ym_engine   File "/Users/max/Dropbox/Python Code/ym/db.py", line 7, in <module>
>     Base.metadata.reflect(engine)   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/sql/schema.py",
> line 4711, in reflect
>     Table(name, self, **reflect_opts)   File "<string>", line 2, in __new__   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py",
> line 298, in warned
>     return fn(*args, **kwargs)   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/sql/schema.py",
> line 601, in __new__
>     metadata._remove_table(name, schema)   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py",
> line 70, in __exit__
>     compat.raise_(   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/util/compat.py",
> line 207, in raise_
>     raise exception   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/sql/schema.py",
> line 596, in __new__
>     table._init(name, metadata, *args, **kw)   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/sql/schema.py",
> line 671, in _init
>     self._autoload(   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/sql/schema.py",
> line 706, in _autoload
>     conn_insp.reflect_table(   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py",
> line 794, in reflect_table
>     self._reflect_fk(   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py",
> line 947, in _reflect_fk
>     fkeys = self.get_foreign_keys(   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py",
> line 564, in get_foreign_keys
>     return self.dialect.get_foreign_keys(   File "<string>", line 2, in get_foreign_keys   File
> "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py",
> line 55, in cache
>     ret = fn(self, con, *args, **kw)   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/base.py",
> line 3124, in get_foreign_keys
>     self._correct_for_mysql_bugs_88718_96365(fkeys, connection)   File "/Users/max/opt/anaconda3/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/base.py",
> line 3203, in _correct_for_mysql_bugs_88718_96365
>     fkey["referred_table"] = rec["TABLENAME"] KeyError: 'TABLENAME'

And the code is below:

from sqlalchemy import MetaData, create_engine
from sqlalchemy.ext.declarative import declarative_base

DB_URL = 'mysql+pymysql://root:pass@server_ip:3306/mh'
engine = create_engine(DB_URL, echo=False)
Base = declarative_base()
Base.metadata.reflect(engine)

class Manga(Base):
    __table__ = Base.metadata.tables['cartoon_list']

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

Pleas note that, the same code above works for the old database. So, I am kind of lost on how to approach this problem. So, I am wondering what can I do now?

Floh
  • 745
  • 3
  • 16
sean k
  • 11
  • 2
  • [This](https://docs.sqlalchemy.org/en/14/dialects/mysql.html#reflection-of-foreign-key-constraints) might be relevant - what storage engine are you using? – snakecharmerb Apr 23 '22 at 10:09
  • @snakecharmerb I just checked. It's using the default InnoDB engine. Both databses are. – sean k Apr 23 '22 at 10:15
  • The [failing code](https://github.com/sqlalchemy/sqlalchemy/blob/63191fbef63ebfbf57e7b66bd6529305fc62c605/lib/sqlalchemy/dialects/mysql/base.py#L2917) relates to inconsistent casing of tables and schemas in foreign key definitions, so perhaps your database has some FK definitions that the code can't handle? Or are not present as suggested [here](https://stackoverflow.com/questions/65231167/keyerror-tablename-in-mysql-database-while-using-sqlalchemy#comment115335151_65231167)?. – snakecharmerb Apr 23 '22 at 10:30

0 Answers0