In Django, how to create tables from an SQL file when syncdb is run

Question

Hi, How do I make syncdb execute SQL queries (for table creation) defined by me, rather then generating tables automatically.

I'm looking for this solution as some particular models in my app represent SQL-table-views for a legacy-database table. So, I've created their SQL-views in my django-DB like this:

CREATE VIEW legacy_series AS SELECT * FROM legacy.series;

I have a reverse engineered model that represents the above view/legacytable. But whenever I run syncdb, I have to create all the views first by running sql scripts, otherwise syncdb simply creates tables for them (if a view is not found).

How do I make syncdb run the above mentioned SQL?

Answer 1

There are 2 possible approaches I know of to adapt your models to a legacy database table (without using views that is):

1) Run python manage.py inspectdb within your project. This will generate models for existing database tables, you can then continue to work with those.

2) Modify your tables with some specific settings. First of all you define the table name in your model by setting the db_table option in your meta options. Secondly you define for each field the column name to match your legacy database by setting the db_column option. Note there are other db_ options listed you possibly could use to match your legacy database.

If you really want the views approach an (ugly) workaround is possible, you can define custom sql commands per application model. This file is found in "application"/sql/"model".sql . Django will call this sql's after it created all tables. You can try to specify DROP statements for the generated tables followed by your view create statement in this file. Note that this will be a bit tricky for the tables with foreign keys as django guarantees no order of execution of these files (so stuffing all statements in one .sql will be the easiest way I think, I've never tried this before).

Answer 2

You could use unmanaged models for your reverse-engineered models, and initial SQL scripts for creating your views.

EDIT:

A bit more detailed answer. When you use unmanaged models, syncdb will not create your database tables for you, so you have to take care of it yourself. An important point is the table name, and how django maps Model classes to table names, I suggest you read the doc on that point.

Basically, your Series model will look like that :

class Series(models.Model):
    # model fields...
    ...

    class Meta:
        managed = False
        db_table = "legacy_series"

Then, you can put your SQL commands, in the yourapp/sql/series.sql file :

### yourapp/sql/series.sql
CREATE VIEW legacy_series AS SELECT * FROM legacy.series;

You can then syncdb as usual, and start using your legacy models.