New issue 4344: Automap (reflection) fails on MySQL Chinook database
https://bitbucket.org/zzzeek/sqlalchemy/issues/4344/automap-reflection-fails-on-mysql-chinook
Thorsten Kampe:
* all software at their latest versions
* Database is MySQL 8.0.12 on Linux
* Client OS is Windows 10
Trying to automap the MySQL Chinook database results in an error (other RDBMSs work):
```
sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey
'Track.trackid' on table 'PlaylistTrack': table 'Track' has no column named 'trackid'
```
Python code:
```
import sqlalchemy as sa
from sqlalchemy.ext import automap
dbtype = 'mysql'
host = 'HOST'
password = 'PASSWORD'
db = 'Chinook'
sqlite_db = 'Chinook_Sqlite.sqlite'
conn_params = {'mssql': f'mssql+pymssql://sa:{password}@{host}/{db}',
'mysql': f'mysql+pymysql://root:{password}@{host}/{db}',
'oracle': f'oracle://chinook:{password}@{host}/xe',
'postgresql': f'postgres://postgres:{password}@{host}/{db}',
'sqlite': f'sqlite+pysqlite:///{sqlite_db}'}
Base = automap.automap_base()
engine = sa.create_engine(conn_params[dbtype])
Base.prepare(engine, reflect = True)
```
The full stack trace is
```
Traceback (most recent call last):
File ".\TEST.py", line 18, in <module>
Base.prepare(engine, reflect = True)
File "F:\PortableApps\Python\lib\site-packages\sqlalchemy\ext\automap.py", line 769, in prepare
lcl_m2m, rem_m2m, m2m_const = _is_many_to_many(cls, table)
File "F:\PortableApps\Python\lib\site-packages\sqlalchemy\ext\automap.py", line 875, in _is_many_to_many
fk_constraints[0].elements[0].column.table,
File "F:\PortableApps\Python\lib\site-packages\sqlalchemy\util\langhelpers.py", line 767, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File "F:\PortableApps\Python\lib\site-packages\sqlalchemy\sql\schema.py", line 1903, in column
tablekey, colname)
sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'Track.trackid' on table 'PlaylistTrack': table 'Track' has no column named 'trackid'
```
Code for the foreign key in question (from "Chinook_MySql.sql"):
```
CREATE TABLE `Track`
(
`TrackId` INT NOT NULL,
-- [...]
CONSTRAINT `PK_Track` PRIMARY KEY (`TrackId`)
);
ALTER TABLE `PlaylistTrack` ADD CONSTRAINT `FK_PlaylistTrackTrackId`
FOREIGN KEY (`TrackId`) REFERENCES `Track` (`TrackId`) ON DELETE NO ACTION ON UPDATE NO ACTION;
```
|
