How to create a temporary table and not lose the ORM in django?

Question

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

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.

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.

Answer 1

I have came to this problem and I built a function to sync models to the database (adapted from the management script 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

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

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))