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.
