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+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.
