Simon, thank you for your kind help.
working excerpt:
Base = declarative_base()
engine = create_engine(app.config.get('SQLALCHEMY_DATABASE_URI'))
metadata = MetaData(bind=engine)
Base.metadata = metadata
# load tables
metadata.reflect(autoload=True)
# load view, as standard reflection ignores views for whatever reason
web_view = Table("web_view", metadata, Column("NODE_ID", Integer,
primary_key=True), autoload=True)
# automap
Base = automap_base(metadata=metadata)
Base.prepare()
# set up relations
Nodes = Base.classes.nodes
Attributes = Base.classes.attribs
Entries = Base.classes.entries
WebView = Base.classes.web_view
print colored("# Mapped SQLAlchemy classes #", 'yellow')
print colored(','.join(Base.classes.keys()), 'green')
print colored("# ------------------------- #", 'yellow')
Session = sessionmaker(bind=engine)
session = Session()
On Thursday, February 14, 2019 at 10:59:19 AM UTC+1, Simon King wrote:
>
> 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 <christia...@itsv.at <javascript:>>
> 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, christia...@itsv.at
> 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, christia...@itsv.at
> 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 <si...@simonking.org.uk>
> wrote:
> >>>> >
> >>>> > On Wed, Feb 13, 2019 at 3:13 PM <christia...@itsv.at> 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 <christia...@itsv.at> 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 sqlalchemy+...@googlegroups.com.
> >>>> > >> > To post to this group, send email to sqlal...@googlegroups.com.
>
> >>>> > >> > 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 sqlalchemy+...@googlegroups.com.
> >>>> > > To post to this group, send email to sqlal...@googlegroups.com.
> >>>> > > 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 sqlalchemy+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
