如何在Django运行时为每条站点记录自动创建独立数据表

Got it, let's break down how to solve this problem effectively. Your worry about SQLite performance with 1000 sites writing data every second is totally justified—single-table scaling gets messy fast, especially with a year of historical data. Here's a step-by-step approach to implement per-site tables in Django without taking your server offline:

1. Define a Base Abstract Model for Site Data #

First, create an abstract model that holds all 40 fields your site data needs. This ensures every per-site table has an identical structure, so you don't have to repeat field definitions:

from django.db import models

class SiteDataAbstract(models.Model):
    timestamp = models.DateTimeField(auto_now_add=True)
    temperature = models.FloatField()
    humidity = models.FloatField()
    # Add your remaining 37 fields here (e.g., pressure, wind_speed, etc.)

    class Meta:
        abstract = True
        indexes = [
            models.Index(fields=['timestamp']),  # Critical for fast historical queries
        ]

2. Create a Site Registry Model #

This model tracks all your sites, and includes a helper method to dynamically fetch the corresponding data table model for any site:

class Site(models.Model):
    name = models.CharField(max_length=100, unique=True)
    # Add other site metadata fields (e.g., location, device_id, etc.)

    def get_data_model(self):
        """Dynamically generate the data model for this site's table"""
        from django.db import models
        
        class Meta:
            db_table = f'site_data_{self.id}'  # Table name like site_data_1, site_data_2
            managed = False  # Tell Django not to manage this table via migrations

        # Build the model class dynamically
        model_attrs = {
            '__module__': self.__module__,
            'Meta': Meta
        }
        return type(f'SiteData_{self.id}', (SiteDataAbstract,), model_attrs)

3. Use Django Signals to Auto-Create Tables on New Site #

Django signals let you trigger actions after a model is saved—perfect for auto-creating the data table when a new site is added, no downtime required:

from django.db.models.signals import post_save
from django.dispatch import receiver
from django.db import connection

@receiver(post_save, sender=Site)
def create_site_data_table(sender, instance, created, **kwargs):
    if created:
        # Generate the dynamic model for the new site
        data_model = instance.get_data_model()
        # Create the table using Django's schema editor
        with connection.schema_editor() as schema_editor:
            schema_editor.create_model(data_model)

4. Writing & Querying Data for a Site #

Once the table exists, you can easily write and query data using the dynamic model from the Site instance:

Writing Data

# Add a new site (this auto-creates its data table)
new_site = Site.objects.create(name="Warehouse_Site_001")

# Get the site's data model and save a reading
DataModel = new_site.get_data_model()
DataModel.objects.create(
    temperature=24.7,
    humidity=58.2,
    # Fill in the rest of your fields here
)

Querying Historical Data

import datetime

# Fetch the site
site = Site.objects.get(id=1)
DataModel = site.get_data_model()

# Get all data from the past year
last_year_data = DataModel.objects.filter(
    timestamp__gte=datetime.datetime.now() - datetime.timedelta(days=365)
)

Key Production Considerations #

• SQLite Concurrency: SQLite uses a single write lock, so while per-site tables reduce lock contention on one table, 1000 concurrent writes might still hit limits. Monitor this closely, and plan to migrate to PostgreSQL if you need higher long-term write throughput.
• Backup & Migrations: Since dynamic tables are marked managed=False, Django won't include them in migrations. Make sure your backup process captures all site_data_* tables.
• Legacy Data Migration: If you need to move existing single-table data to per-site tables, write a one-off batch script that runs during low traffic. Group old records by site and insert them into the corresponding new tables—this can be done without taking the server down.

内容的提问来源于stack exchange，提问作者Arvindh