Есть ли "Pythonic" способ (я имею в виду, нет "чистого SQL" запроса) для определения представления SQL с помощью SQLAlchemy?
Как создать SQL-представление с SQLAlchemy?
Ответ 1
Обновление: См. также рецепт использования 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)
)
Ответ 2
Ответ стефана хороший и охватывает большинство баз, но что меня не удовлетворило, так это отсутствие интеграции с остальной частью SQLAlchemy (ORM, автоматическое удаление и т.д.). После нескольких часов экспериментов и объединения знаний из всех angularков интернета я придумал следующее:
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):
Делайте это при загрузке приложения, перед любыми запросами и после настройки БД.
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)
Он также автоматически удаляется во время вызова drop_all().
Это, очевидно, очень хакерское решение, но на мой взгляд, это лучшее и самое чистое решение на данный момент. Я проверял это в последние несколько дней, и у меня не было никаких проблем. Я не уверен, как добавить отношения (там возникли проблемы), но это не совсем необходимо, как показано выше в запросе.
Если у кого-либо есть какие-либо замечания, если они обнаруживают какие-либо неожиданные проблемы или знают, как лучше поступить, оставьте комментарий или дайте мне знать.
Это было проверено на SQLAlchemy 1.2.6 и Python 3.6.
Ответ 3
В эти дни для этого есть пакет PyPI: SQLAlchemy Views.
С этого 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
Однако вы запросили запрос "без SQL", поэтому вы, вероятно, хотите, чтобы описанный выше definition был создан с объектом запроса SQLAlchemy.
К счастью, text() в приведенном выше примере проясняет, что параметр definition для CreateView является таким объектом запроса. Так что-то вроде этого должно работать:
>>> 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)
... )
Вот интересный момент:
>>> 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
Ответ 4
SQLAlchemy-utils только что добавил эту функцию в 0.33.6 (доступно в pypi). Он имеет представления, материализованные представления и интегрируется с ORM. Это еще не задокументировано, но я успешно использую представления + ORM.
Вы можете использовать их тест в качестве примера как для обычных, так и для материализованных представлений с использованием ORM.
Чтобы создать представление, после установки пакета используйте следующий код из приведенного выше теста в качестве основы для своего представления:
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
)
Где Base - это declarative_base, sa - это пакет SQLAlchemy, а create_view - это функция из sqlalchemy_utils.view.
Ответ 5
Я не смог найти короткий и удобный ответ.
Мне не нужна дополнительная функциональность View (если есть), поэтому я просто воспринимаю представление как обычную таблицу как другие определения таблицы.
В общем, у меня есть a.py, где определяются все таблицы и представления, связанные с SQL вещи, и main.py, где я импортирую эти классы из a.py и использую их.
Вот что я добавляю в a.py и работает:
class A_View_From_Your_DataBase(Base):
__tablename__ = 'View_Name'
keyword = Column(String(100), nullable=False, primary_key=True)
Примечательно, что вам нужно добавить свойство primary_key, даже если в представлении нет первичного ключа.
Ответ 6
SQL View без чистого SQL? Вы можете создать класс или функцию для реализации определенного вида.
function get_view(con):
return Table.query.filter(Table.name==con.name).first()