如何在django中创建临时表而不丢失ORM？

I'm curious how to create a temporary table in django? (the database is mysql, a client requirement)

我很好奇如何在django中创建一个临时表？（数据库是mysql，客户端要求）

CREATE TEMPORARY TABLE somewhat_like_a_cache AS
(SELECT * FROM expensive_query_with_multiple_joins);
SELECT * FROM somewhat_like_a_cache LIMIT 1000 OFFSET X;

The reasoning behind this: The resultset is fairly large and I have to iterate it. The expensive query takes around 30 seconds. Without a temporary table I stress the database server for several hours. With the temporary table the expensive query is executed only once and iterating the temporary table in slices is cheap afterwards.

这背后的原因：结果集相当大，我必须迭代它。昂贵的查询大约需要30秒。没有临时表，我会强调数据库服务器几个小时。使用临时表，昂贵的查询只执行一次，然后在切片中迭代临时表是很便宜的。

This is no duplicate of How do I create a temporary table to sort the same column by two criteria using Django's ORM?. The author just wanted to sort by two columns.

这与使用Django的ORM如何创建临时表以按两个条件对同一列进行排序无关？作者只想按两列排序。

2 个解决方案

#1

I have came to this problem and I built a function to sync models to the database (adapted from the management script syncdb.

我遇到了这个问题，我构建了一个函数来将模型同步到数据库（改编自管理脚本syncdb）。

you can write your temporary models any where in your code or even generate the models in the run-time then call sync_models. and enjoy the ORM

您可以在代码中的任何位置编写临时模型，甚至可以在运行时生成模型，然后调用sync_models。并享受ORM

its database independent by the way and can be used with any django supported database backend

它的数据库独立，可以与任何django支持的数据库后端一起使用

from django.db import connection
from django.test import TestCase
from django.core.management.color import no_style
from importlib import import_module


def sync_models(model_list):
    '''
    Create the database tables for given models.
    '''
    tables = connection.introspection.table_names()
    seen_models = connection.introspection.installed_models(tables)
    created_models = set()
    pending_references = {}
    cursor = connection.cursor()
    for model in model_list:
        # Create the model's database table, if it doesn't already exist.
        sql, references = connection.creation.sql_create_model(model, no_style(), seen_models)
        seen_models.add(model)
        created_models.add(model)
        for refto, refs in references.items():
            pending_references.setdefault(refto, []).extend(refs)
            if refto in seen_models:
                sql.extend(connection.creation.sql_for_pending_references(refto, no_style(), pending_references))
        sql.extend(connection.creation.sql_for_pending_references(model, no_style(), pending_references))
        for statement in sql:
            cursor.execute(statement)
        tables.append(connection.introspection.table_name_converter(model._meta.db_table))

#2

I tested following in Postgresql (also using it rarely), but I do not think you will have any problem.

我在Postgresql中测试了以下（很少使用它），但我认为你不会有任何问题。

Django also offers you an api for executing custom SQL Queries (INSERT, UPDATE, CREATE, DROP etc) using connection. Here is the documentation about the usage

Django还为您提供了使用连接执行自定义SQL查询（INSERT，UPDATE，CREATE，DROP等）的API。以下是有关用法的文档

from django.db import connection, transaction
cursor = connection.cursor() #  create the cursor

cursor.execute("CREATE TEMPORARY TABLE test_table (code char(5)  PRIMARY KEY,
                                                   title varchar(40) NOT NULL);")
cursor.execute("INSERT into test_table (code, title) values ('ABVCD', 'This is my Title');")
cursor.execute("select * from test_table;") #  now select all data in our table
print cursor.fetchall() # and fetch them all
cursor.close()  # if you wish to close it

This is an example code I tested withj Postgresql 9.1, but you will not have any problem if you execute queries suitable to your DBMS version.

这是我使用Postgresql 9.1测试的示例代码，但如果执行适合您的DBMS版本的查询，则不会有任何问题。

Using TEMPORARY tables or creating normal tables and executing drop statement after you finished your job is up to you. But read the docs before you begin.

完成工作后使用TEMPORARY表或创建普通表并执行drop语句由您决定。但在开始之前阅读文档。

#1