I want to use metadata.reflect()
and generate the existing tables. Tried Method 1 and Method 2 in code block to generate the table. They both give the same error about the Mapper not being able to assemble any primary key columns for mapped table. I think it’s because of the <built-in function id>
field. How can I solve mapping issue?
What I’ve done:
- Read the package docs’ tutorial: https://flask-sqlalchemy.palletsprojects.com/en/3.1.x/models/
- I’ve checked the few other similar questions to this:
- getting sqlalchemy view reflection to work in flask-sqlalchemy
- how to reflect an existing table by using flask_sqlalchemy
- Asked ChatGPT
One suggested solution for the <built-in function id>
is to declare a primary key, but I thought my original CREATE TABLE
statement does that. Does it not? I’ve tried using __mapper_args__
on my own, but can’t figure out how to assign the shelf_id
or id
(I’ve tried both). The ultimate workaround seems to be to write out every table column, but the database isn’t finalized yet, so…
engine = create_engine(AppConfig.DBURI, echo=False)
meta = MetaData()
db_session = scoped_session(sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
def init_db(engine):
meta.init_db()
meta.create_all(bind=engine)
Base = declarative_base()
Base.metadata.reflect(bind=engine)
# Method 1
class Bookshelf(Base):
__table__ = Table("bookshelf",
Base.metadata,
Column('shelf_id', Integer),
autoload_with=engine,
extend_existing=True)
# Method 2
# class Bookshelf(Base):
# __table__ = Base.metadata.tables['bookshelf']
if __name__ == '__main__':
for table in Base.metadata.tables.values():
logging.debug(f"{table.name}")
for column in table.c:
logging.debug(f"{column.name}")
Errors:
raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Mapper Mapper[Bookshelf(bookshelf)] could not assemble any primary key columns for mapped table 'bookshelf'
If I run the same code above without the Table class, I get:
2024-05-31 11:42:23,355 [DEBUG] bookshelf.<module>, line 67: bookshelf
2024-05-31 11:42:23,356 [DEBUG] bookshelf.<module>, line 69: <built-in function id>
2024-05-31 11:42:23,357 [DEBUG] bookshelf.<module>, line 69: shelf_label
2024-05-31 11:42:23,357 [DEBUG] bookshelf.<module>, line 69: directory_name
2024-05-31 11:42:23,358 [DEBUG] bookshelf.<module>, line 69: url
The table was created separately by database_admin.py
.
CREATE TABLE IF NOT EXISTS bookshelf(
shelf_id INTEGER UNIQUE NOT NULL,
shelf_label TEXT UNIQUE NOT NULL ,
directory_name TEXT NOT NULL,
url TEXT NOT NULL,
creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_date DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (shelf_id),
UNIQUE(shelf_label, directory_name)
);
Also tried it with shelf_id INTEGER PRIMARY KEY UNIQUE NOT NULL
JIC.