There are a few different points to make here: 1. SQLAlchemy requires mapped classes to have a primary key defined. This is because an instance of the mapped class corresponds to a row in the database. In order to make changes to that row (UPDATE, DELETE etc.), SQLAlchemy needs to be able to target it uniquely, so it needs a set of values that uniquely identify a row. Normally that's the primary key, although you can use any combination of columns you like as long as they guarantee uniqueness.
2. View reflection doesn't pick up primary keys, so if you want to map a class to a view, you need to explicitly tell SQLAlchemy which columns you want to treat as primary keys. 3. If you want to alter the definition of a Table object inside a Metadata, you have to use the extend_existing flag. This is an "explicit vs implicit" design decision. Now that you've given your Table a primary key, and automap has mapped a class to it, I think you should be fine. Simon On Thu, Feb 14, 2019 at 8:28 AM <[email protected]> wrote: > > i fiddled around with this. here are some interessting perceptions. > > defining the primary_key manually yields this, which is totally irrational to > me. the missing view that i am after is already defined somewhere? pffff... > > >>> my_view = Table("web_view", metadata, Column("NODE_ID", Integer, > >>> primary_key=True), autoload=True) > Traceback (most recent call last): > File "<stdin>", line 1, in <module> > File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/schema.py", line > 439, in __new__ > "existing Table object." % key) > sqlalchemy.exc.InvalidRequestError: Table 'web_view' is already defined for > this MetaData instance. Specify 'extend_existing=True' to redefine options > and columns on an existing Table object. > > so i did what it requires (extend_existing=True)...it seems it gets imported > > >>> my_view = Table("web_view", metadata, Column("NODE_ID", Integer, > >>> primary_key=True), autoload=True, extend_existing=True) > >>> my_view > Table('web_view', > MetaData(bind=Engine(mysql://aix_reg:***@localhost/aix_registry)), > Column('NODE_ID', Integer(), table=<web_view>, primary_key=True, > nullable=False), Column('NODE', VARCHAR(length=256), table=<web_view>), > Column('LOCATION', VARCHAR(length=256), table=<web_view>), > Column('IS_CLUSTER', VARCHAR(length=256), table=<web_view>), > Column('MAN_SYS_NAME', VARCHAR(length=256), table=<web_view>), > Column('HAS_CICS', VARCHAR(length=256), table=<web_view>), Column('OS_LEVEL', > VARCHAR(length=256), table=<web_view>), Column('HAS_COBOL', > VARCHAR(length=256), table=<web_view>), Column('HAS_ORACLE', > VARCHAR(length=256), table=<web_view>), Column('MEM_VALUE', > VARCHAR(length=256), table=<web_view>), Column('HAS_TUXEDO', > VARCHAR(length=256), table=<web_view>), Column('STORAGE_VALUE', > VARCHAR(length=256), table=<web_view>), Column('IS_LPM', VARCHAR(length=256), > table=<web_view>), Column('HAS_SAP', VARCHAR(length=256), table=<web_view>), > Column('CPU_VALUE', VARCHAR(length=256), table=<web_view>), > Column('CLUSTER_RGS', VARCHAR(length=256), table=<web_view>), > Column('HA_LEVEL', VARCHAR(length=256), table=<web_view>), > Column('CLUSTER_NODES', VARCHAR(length=256), table=<web_view>), > Column('CLUSTER_NAME', VARCHAR(length=256), table=<web_view>), > Column('CPU_POOL', VARCHAR(length=256), table=<web_view>), > Column('AME_FACTOR', VARCHAR(length=256), table=<web_view>), > Column('SYS_PROFILE', VARCHAR(length=256), table=<web_view>), > Column('INFO_MAIL', VARCHAR(length=256), table=<web_view>), > Column('HOSTNAME', VARCHAR(length=256), table=<web_view>), Column('OS_TYPE', > VARCHAR(length=256), table=<web_view>), Column('Java6_64', > VARCHAR(length=256), table=<web_view>), Column('Java7_64', > VARCHAR(length=256), table=<web_view>), Column('Java8_64', > VARCHAR(length=256), table=<web_view>), Column('Java5_64', > VARCHAR(length=256), table=<web_view>), Column('Java8', VARCHAR(length=256), > table=<web_view>), Column('Java5', VARCHAR(length=256), table=<web_view>), > Column('Java14', VARCHAR(length=256), table=<web_view>), Column('Java7', > VARCHAR(length=256), table=<web_view>), Column('Java71', VARCHAR(length=256), > table=<web_view>), Column('Java71_64', VARCHAR(length=256), > table=<web_view>), Column('Java14_64', VARCHAR(length=256), > table=<web_view>), Column('Java6', VARCHAR(length=256), table=<web_view>), > Column('UPTIME', VARCHAR(length=256), table=<web_view>), Column('IP', > VARCHAR(length=256), table=<web_view>), Column('IP_LONG', > VARCHAR(length=256), table=<web_view>), Column('CLUSTER_NODENAME', > VARCHAR(length=256), table=<web_view>), Column('RG_SERVICE_IP_LONG', > VARCHAR(length=256), table=<web_view>), Column('HAS_SNA', > VARCHAR(length=256), table=<web_view>), schema=None) > > and the view apears as a class... > > >>> from sqlalchemy.ext.automap import automap_base > >>> Base = automap_base(metadata=metadata) > >>> Base.prepare() > >>> print(Base.classes.keys()) > ['attribs', 'dyn_table', 'web_view', 'nodes', 'entries'] > > > i have grave doubts that this is intended behaviour. not sure how to proceed > from here. > > On Thursday, February 14, 2019 at 8:54:03 AM UTC+1, [email protected] wrote: >> >> >> maybe a "primary key" issue? >> >> from the docs: >> >> sually, it’s desired to have at least a primary key constraint when >> reflecting a view, if not foreign keys as well. View reflection doesn’t >> extrapolate these constraints. >> >> Use the “override” technique for this, specifying explicitly those columns >> which are part of the primary key or have foreign key constraints: >> >> >> >> my_view = Table("some_view", metadata, >> Column("view_id", Integer, primary_key=True), >> Column("related_thing", Integer, >> ForeignKey("othertable.thing_id")), >> autoload=True >> ) >> >> >> >> >> On Thursday, February 14, 2019 at 8:48:40 AM UTC+1, [email protected] >> wrote: >>> >>> ok, so we are back to the central question. the output below indictates >>> that only "tables" are reflected and "views" are ignored. i like to think >>> i am not the first who wants to use mysql views with sqla? is this really >>> such a difficult task? >>> >>> >>> >>> print(Base.classes.keys()) >>> ['attribs', 'dyn_table', 'nodes', 'entries'] >>> >>> >>> >>> >>> >>> On Wednesday, February 13, 2019 at 4:35:22 PM UTC+1, Simon King wrote: >>>> >>>> On Wed, Feb 13, 2019 at 3:22 PM Simon King <[email protected]> wrote: >>>> > >>>> > On Wed, Feb 13, 2019 at 3:13 PM <[email protected]> wrote: >>>> > > >>>> > > did not work out very well,...god, this stuff gives some good headache! >>>> > > >>>> > > >>> from sqlalchemy.ext.automap import automap_base >>>> > > >>> Base = automap_base(metadata=metadata) >>>> > > >>> Base.prepare() >>>> > > >>> WebView = Base.classes.web_view >>>> > > Traceback (most recent call last): >>>> > > File "<stdin>", line 1, in <module> >>>> > > File >>>> > > "/usr/lib64/python2.7/site-packages/sqlalchemy/util/_collections.py", >>>> > > line 212, in __getattr__ >>>> > > raise AttributeError(key) >>>> > > AttributeError: web_view >>>> > >>>> > >>>> > What does "print(Base.classes)" report at this point? >>>> >>>> Sorry, that should probably have been "print(Base.classes.keys())" >>>> >>>> > >>>> > >>>> > > >>>> > > On Wednesday, February 13, 2019 at 4:01:25 PM UTC+1, Simon King wrote: >>>> > >> >>>> > >> It looks like you are mixing up Table objects (which are part of >>>> > >> SQLAlchemy Core) with mapped classes (part of SQLAlchemy ORM). >>>> > >> >>>> > >> The objects in metadata.tables are instances of the Table class, and >>>> > >> each correspond to a table or view in the database. The ORM allows you >>>> > >> to define classes which are mapped to those Tables, such that each >>>> > >> instance of the mapped class corresponds to a *row* in the table. >>>> > >> Flask-admin works with mapped classes (which it calls "models"), not >>>> > >> Table instances. >>>> > >> >>>> > >> Since you are reflecting the database anyway, the quickest way for you >>>> > >> to get up-and-running might be to use the Automap extension: >>>> > >> >>>> > >> https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html >>>> > >> >>>> > >> It doesn't look like "Base.prepare()" accepts an argument telling it >>>> > >> to reflect views, so you might actually need a combination of the two >>>> > >> approaches, something like this (untested): >>>> > >> >>>> > >> engine = create_engine(app.config.get('SQLALCHEMY_DATABASE_URI')) >>>> > >> metadata = MetaData(bind=engine) >>>> > >> metadata.reflect(views=True, autoload=True) >>>> > >> >>>> > >> Base = automap_base(metadata=metadata) >>>> > >> Base.prepare() >>>> > >> >>>> > >> Nodes = Base.classes.nodes >>>> > >> Attributes = Base.classes.attribs >>>> > >> Entries = Base.classes.entries >>>> > >> WebView = Base.classes.web_view >>>> > >> >>>> > >> >>>> > >> Hope that helps, >>>> > >> >>>> > >> Simon >>>> > >> >>>> > >> On Wed, Feb 13, 2019 at 2:19 PM <[email protected]> wrote: >>>> > >> > >>>> > >> > i already spent 3 days trying to map one stupid mysql view and i >>>> > >> > cant get it to work. >>>> > >> > >>>> > >> > in the python interpreter it works... >>>> > >> > >>>> > >> > Python 2.7.5 (default, May 31 2018, 09:45:54) >>>> > >> > [GCC 4.8.5 20150623 (Red Hat 4.8.5-28)] on linux2 >>>> > >> > Type "help", "copyright", "credits" or "license" for more >>>> > >> > information. >>>> > >> > >>> SQLALCHEMY_DATABASE_URI = >>>> > >> > >>> 'mysql://aix_reg:blalblabla@localhost/aix_registry' >>>> > >> > >>> from flask import Flask, request >>>> > >> > >>> from flask_sqlalchemy import SQLAlchemy >>>> > >> > >>> from sqlalchemy import String, Enum, create_engine, MetaData, >>>> > >> > >>> Table, Column, Integer >>>> > >> > >>> from sqlalchemy.orm import sessionmaker >>>> > >> > >>> from sqlalchemy.ext.declarative import declarative_base >>>> > >> > >>> from flask_admin.contrib import sqla >>>> > >> > >>> from flask_admin import Admin, expose, BaseView >>>> > >> > >>> from flask_admin.contrib.sqla import ModelView >>>> > >> > >>> from flask_admin.model.template import EndpointLinkRowAction, >>>> > >> > >>> LinkRowAction >>>> > >> > >>> Base = declarative_base() >>>> > >> > >>> engine = create_engine(SQLALCHEMY_DATABASE_URI) >>>> > >> > >>> metadata = MetaData(bind=engine) >>>> > >> > >>> Base.metadata = metadata >>>> > >> > >>> metadata.reflect(views=True, autoload=True) >>>> > >> > >>> WebView = metadata.tables['web_view'] >>>> > >> > >>> WebView >>>> > >> > Table('web_view', >>>> > >> > MetaData(bind=Engine(mysql://aix_reg:***@localhost/aix_registry)), >>>> > >> > Column('NODE_ID', INTEGER(display_width=11), table=<web_view>, >>>> > >> > server_default=DefaultClause(<sqlalchemy.sql.elements.TextClause >>>> > >> > object at 0x3fff7aace890>, for_update=False)), Column('NODE', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('LOCATION', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('IS_CLUSTER', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('MAN_SYS_NAME', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('HAS_CICS', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('OS_LEVEL', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('HAS_COBOL', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('HAS_ORACLE', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('MEM_VALUE', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('HAS_TUXEDO', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('STORAGE_VALUE', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('IS_LPM', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('HAS_SAP', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('CPU_VALUE', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('CLUSTER_RGS', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('HA_LEVEL', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('CLUSTER_NODES', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('CLUSTER_NAME', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('CPU_POOL', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('AME_FACTOR', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('SYS_PROFILE', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('INFO_MAIL', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('HOSTNAME', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('OS_TYPE', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java6_64', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java7_64', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java8_64', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java5_64', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java8', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java5', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java14', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java7', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java71', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java71_64', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java14_64', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('Java6', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('UPTIME', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('IP', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('IP_LONG', >>>> > >> > VARCHAR(length=256), table=<web_view>), Column('CLUSTER_NODENAME', >>>> > >> > VARCHAR(length=256), table=<web_view>), >>>> > >> > Column('RG_SERVICE_IP_LONG', VARCHAR(length=256), >>>> > >> > table=<web_view>), Column('HAS_SNA', VARCHAR(length=256), >>>> > >> > table=<web_view>), schema=None) >>>> > >> > >>>> > >> > >>>> > >> > >>>> > >> > but in the flask app no way >>>> > >> > >>>> > >> > Base = declarative_base() >>>> > >> > engine = create_engine(app.config.get('SQLALCHEMY_DATABASE_URI')) >>>> > >> > metadata = MetaData(bind=engine) >>>> > >> > Base.metadata = metadata >>>> > >> > metadata.reflect(views=True, autoload=True) >>>> > >> > >>>> > >> > Nodes = metadata.tables['nodes'] >>>> > >> > Attributes = metadata.tables['attribs'] >>>> > >> > Entries = metadata.tables['entries'] >>>> > >> > WebView = metadata.tables['web_view'] >>>> > >> > >>>> > >> > session = sessionmaker(bind=engine) >>>> > >> > >>>> > >> > >>>> > >> > admin = Admin(app, name='AIX Registry', template_mode='bootstrap3') >>>> > >> > admin.add_view(AixAdmin(Nodes, session, 'Overview')) >>>> > >> > >>>> > >> > root@lpgaixmgmtlx01:/root/flask/aix_registry>python run.py >>>> > >> > Traceback (most recent call last): >>>> > >> > File "run.py", line 3, in <module> >>>> > >> > from app import app >>>> > >> > File "/root/flask/aix_registry/app/__init__.py", line 18, in >>>> > >> > <module> >>>> > >> > from app import views >>>> > >> > File "/root/flask/aix_registry/app/views.py", line 55, in <module> >>>> > >> > admin.add_view(AixAdmin(Nodes, session, 'Overview')) >>>> > >> > File >>>> > >> > "/usr/lib/python2.7/site-packages/flask_admin/contrib/sqla/view.py", >>>> > >> > line 329, in __init__ >>>> > >> > menu_icon_value=menu_icon_value) >>>> > >> > File >>>> > >> > "/usr/lib/python2.7/site-packages/flask_admin/model/base.py", line >>>> > >> > 812, in __init__ >>>> > >> > menu_icon_value=menu_icon_value) >>>> > >> > File "/usr/lib/python2.7/site-packages/flask_admin/base.py", line >>>> > >> > 192, in __init__ >>>> > >> > self.endpoint = self._get_endpoint(endpoint) >>>> > >> > File >>>> > >> > "/usr/lib/python2.7/site-packages/flask_admin/model/base.py", line >>>> > >> > 825, in _get_endpoint >>>> > >> > return self.model.__name__.lower() >>>> > >> > AttributeError: 'Table' object has no attribute '__name__' >>>> > >> > >>>> > >> > >>>> > >> > so how can i do this highly frustrating task correctly? >>>> > >> > >>>> > >> > -- >>>> > >> > SQLAlchemy - >>>> > >> > The Python SQL Toolkit and Object Relational Mapper >>>> > >> > >>>> > >> > http://www.sqlalchemy.org/ >>>> > >> > >>>> > >> > To post example code, please provide an MCVE: Minimal, Complete, >>>> > >> > and Verifiable Example. See http://stackoverflow.com/help/mcve for >>>> > >> > a full description. >>>> > >> > --- >>>> > >> > You received this message because you are subscribed to the Google >>>> > >> > Groups "sqlalchemy" group. >>>> > >> > To unsubscribe from this group and stop receiving emails from it, >>>> > >> > send an email to [email protected]. >>>> > >> > To post to this group, send email to [email protected]. >>>> > >> > Visit this group at https://groups.google.com/group/sqlalchemy. >>>> > >> > For more options, visit https://groups.google.com/d/optout. >>>> > > >>>> > > -- >>>> > > SQLAlchemy - >>>> > > The Python SQL Toolkit and Object Relational Mapper >>>> > > >>>> > > http://www.sqlalchemy.org/ >>>> > > >>>> > > To post example code, please provide an MCVE: Minimal, Complete, and >>>> > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>> > > description. >>>> > > --- >>>> > > You received this message because you are subscribed to the Google >>>> > > Groups "sqlalchemy" group. >>>> > > To unsubscribe from this group and stop receiving emails from it, send >>>> > > an email to [email protected]. >>>> > > To post to this group, send email to [email protected]. >>>> > > Visit this group at https://groups.google.com/group/sqlalchemy. >>>> > > For more options, visit https://groups.google.com/d/optout. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
