SQLAlchemy核心创建临时表AS [英] SQLAlchemy Core CREATE TEMPORARY TABLE AS

问题描述

我正在尝试在SQLAlchemy Core中使用PostgreSQL CREATE TEMPORARY TABLE foo AS SELECT ... 查询。我浏览了所有文档，但没有找到执行此操作的方法。

I'm trying to use the PostgreSQL CREATE TEMPORARY TABLE foo AS SELECT... query in SQLAlchemy Core. I've looked through the docs but don't see a way to do this.

我有一个SQLA语句对象。如何根据其结果创建临时表？

I have a SQLA statement object. How do I create a temporary table from its results?

推荐答案

这是我想出的。请告诉我这是否是错误的方法。

This is what I came up with. Please tell me if this is the wrong way to do it.

from sqlalchemy.sql import Select
from sqlalchemy.ext.compiler import compiles


class CreateTableAs(Select):
    """Create a CREATE TABLE AS SELECT ... statement."""

    def __init__(self, columns, new_table_name, is_temporary=False,
            on_commit_delete_rows=False, on_commit_drop=False, *arg, **kw):
        """By default the table sticks around after the transaction. You can
        change this behavior using the `on_commit_delete_rows` or
        `on_commit_drop` arguments.

        :param on_commit_delete_rows: All rows in the temporary table will be
        deleted at the end of each transaction block.
        :param on_commit_drop: The temporary table will be dropped at the end
        of the transaction block.
        """
        super(CreateTableAs, self).__init__(columns, *arg, **kw)

        self.is_temporary = is_temporary
        self.new_table_name = new_table_name
        self.on_commit_delete_rows = on_commit_delete_rows
        self.on_commit_drop = on_commit_drop


@compiles(CreateTableAs)
def s_create_table_as(element, compiler, **kw):
    """Compile the statement."""
    text = compiler.visit_select(element)
    spec = ['CREATE', 'TABLE', element.new_table_name, 'AS SELECT']

    if element.is_temporary:
        spec.insert(1, 'TEMPORARY')

    on_commit = None

    if element.on_commit_delete_rows:
        on_commit = 'ON COMMIT DELETE ROWS'
    elif element.on_commit_drop:
        on_commit = 'ON COMMIT DROP'

    if on_commit:
        spec.insert(len(spec)-1, on_commit)

    text = text.replace('SELECT', ' '.join(spec))
    return text

这篇关于SQLAlchemy核心创建临时表AS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！