SQLAlchemy를 사용하여 SQL보기를 만드는 방법은 무엇입니까?

SQLAlchemy를 사용하여 SQL보기를 만드는 방법은 무엇입니까?

---

SQLAlchemy로 SQL 뷰를 정의하는 "Pythonic"방식 ( "순수 SQL"쿼리 없음)이 있습니까?

---

업데이트 : 여기 에서 SQLAlchemy 사용 레시피도 참조 하세요.

내가 아는 한 (읽기 전용 비 구체화) 뷰를 만드는 것은 기본적으로 지원되지 않습니다. 그러나 SQLAlchemy 0.7에이 기능을 추가하는 것은 간단합니다 ( 여기 에서 제공 한 예제와 유사 함 ). 컴파일러 확장 을 작성하기 만하면 CreateView됩니다. 이 확장을 사용하면 다음을 작성할 수 있습니다 ( t열이있는 테이블 객체 라고 가정 id).

createview = CreateView('viewname', t.select().where(t.c.id>5))
engine.execute(createview)

v = Table('viewname', metadata, autoload=True)
for r in engine.execute(v.select()):
    print r

다음은 작동하는 예입니다.

from sqlalchemy import Table
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement

class CreateView(Executable, ClauseElement):
    def __init__(self, name, select):
        self.name = name
        self.select = select

@compiles(CreateView)
def visit_create_view(element, compiler, **kw):
    return "CREATE VIEW %s AS %s" % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )

# test data
from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.engine import create_engine
engine = create_engine('sqlite://')
metadata = MetaData(engine)
t = Table('t',
          metadata,
          Column('id', Integer, primary_key=True),
          Column('number', Integer))
t.create()
engine.execute(t.insert().values(id=1, number=3))
engine.execute(t.insert().values(id=9, number=-3))

# create view
createview = CreateView('viewname', t.select().where(t.c.id>5))
engine.execute(createview)

# reflect view and print result
v = Table('viewname', metadata, autoload=True)
for r in engine.execute(v.select()):
    print r

원하는 경우 방언을 전문화 할 수도 있습니다.

@compiles(CreateView, 'sqlite')
def visit_create_view(element, compiler, **kw):
    return "CREATE VIEW IF NOT EXISTS %s AS %s" % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )

---

stephan의 대답은 좋은 답변이며 대부분의 기반을 다룹니다.하지만 저를 만족시키지 못한 것은 나머지 SQLAlchemy (ORM, 자동 삭제 등)와의 통합 부족이었습니다. 몇 시간 동안 인터넷의 모든 구석에서 얻은 지식을 실험하고 결합한 후 다음을 생각해 냈습니다.

import sqlalchemy_views
from sqlalchemy import Table
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.ddl import DropTable


class View(Table):
    is_view = True


class CreateView(sqlalchemy_views.CreateView):
    def __init__(self, view):
        super().__init__(view.__view__, view.__definition__)


@compiles(DropTable, "postgresql")
def _compile_drop_table(element, compiler, **kwargs):
    if hasattr(element.element, 'is_view') and element.element.is_view:
        return compiler.visit_drop_view(element)

    # cascade seems necessary in case SQLA tries to drop 
    # the table a view depends on, before dropping the view
    return compiler.visit_drop_table(element) + ' CASCADE'

나는 sqlalchemy_views단지 일을 단순화하기 위해 패키지를 사용하고 있음을 유의하십시오 .

보기 정의 (예 : 테이블 모델과 같이 전역 적으로) :

from sqlalchemy import MetaData, text, Text, Column


class SampleView:
    __view__ = View(
        'sample_view', MetaData(),
        Column('bar', Text, primary_key=True),
    )

    __definition__ = text('''select 'foo' as bar''')

# keeping track of your defined views makes things easier
views = [SampleView]

뷰 매핑 (ORM 기능 활성화) :

앱을로드 할 때, 쿼리 전 및 DB 설정 후에 수행하십시오.

for view in views:
    if not hasattr(view, '_sa_class_manager'):
        orm.mapper(view, view.__view__)

보기 만들기 :

데이터베이스를 초기화 할 때 (예 : create_all () 호출 후) 수행하십시오.

from sqlalchemy import orm


for view in views:
    db.engine.execute(CreateView(view))

보기를 쿼리하는 방법 :

results = db.session.query(SomeModel, SampleView).join(
    SampleView,
    SomeModel.id == SampleView.some_model_id
).all()

이것은 당신이 기대하는 것을 정확하게 반환 할 것입니다 (각각 SomeModel 개체와 SampleView 개체가있는 개체 목록).

보기 삭제 :

SampleView.__view__.drop(db.engine)

It will also automatically get dropped during a drop_all() call.

This is obviously a very hacky solution but in my eyes it is the best one and cleanest one out there at the moment. I have tested it these past few days and have not had any issues. I'm not sure how to add in relationships (ran into problems there) but it's not really necessary, as demonstrated above in the query.

If anyone has any input, finds any unexpected issues, or knows a better way to do things, please do leave a comment or let me know.

This was tested on SQLAlchemy 1.2.6 and Python 3.6.

---

These days there's a PyPI package for that: SQLAlchemy Views.

From it's PyPI Page:

>>> from sqlalchemy import Table, MetaData
>>> from sqlalchemy.sql import text
>>> from sqlalchemy_views import CreateView, DropView

>>> view = Table('my_view', metadata)
>>> definition = text("SELECT * FROM my_table")

>>> create_view = CreateView(view, definition, or_replace=True)
>>> print(str(create_view.compile()).strip())
CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table

However, you asked for a no "pure SQL" query, so you probably want the definition above to be created with SQLAlchemy query object.

Luckily, the text() in the example above makes it clear that the definition parameter to CreateView is such a query object. So something like this should work:

>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
>>> from sqlalchemy.sql import select
>>> from sqlalchemy_views import CreateView, DropView

>>> metadata = MetaData()

>>> users = Table('users', metadata,
...     Column('id', Integer, primary_key=True),
...     Column('name', String),
...     Column('fullname', String),
... )

>>> addresses = Table('addresses', metadata,
...   Column('id', Integer, primary_key=True),
...   Column('user_id', None, ForeignKey('users.id')),
...   Column('email_address', String, nullable=False)
...  )

Here is the interesting bit:

>>> view = Table('my_view', metadata)
>>> definition = select([users, addresses]).where(
...     users.c.id == addresses.c.user_id
... )
>>> create_view = CreateView(view, definition, or_replace=True)
>>> print(str(create_view.compile()).strip())
CREATE OR REPLACE VIEW my_view AS SELECT users.id, users.name,
users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses 
WHERE users.id = addresses.user_id

---

SQLAlchemy-utils just added this functionality in 0.33.6 (available in pypi). It has views, materialized views, and it integrates with the ORM. It is not documented yet, but I am successfully using the views + ORM.

You can use their test as an example for both regular and materialized views using the ORM.

To create a view, once you install the package, use the following code from the test above as a base for your view:

class ArticleView(Base):
    __table__ = create_view(
        name='article_view',
        selectable=sa.select(
            [
                Article.id,
                Article.name,
                User.id.label('author_id'),
                User.name.label('author_name')
            ],
            from_obj=(
                Article.__table__
                    .join(User, Article.author_id == User.id)
            )
        ),
        metadata=Base.metadata
    )

Where Base is the declarative_base, sa is the SQLAlchemy package, and create_view is a function from sqlalchemy_utils.view.

---

I couldn't find an short and handy answer.

I don't need extra functionality of View (if any), so I simply treat a view as an ordinary table as other table definitions.

So basically I have a.py where defines all tables and views, sql related stuff, and main.py where I import those class from a.py and use them.

Here's what I add in a.py and works:

class A_View_From_Your_DataBase(Base):
    __tablename__ = 'View_Name'
    keyword = Column(String(100), nullable=False, primary_key=True)

Notably, you need to add the primary_key property even though there's no primary key in the view.

---

SQL View without pure SQL? You can create a class or function to implement a defined view.

function get_view(con):
  return Table.query.filter(Table.name==con.name).first()

참고URL : https://stackoverflow.com/questions/9766940/how-to-create-an-sql-view-with-sqlalchemy