views:

27

answers:

2

Hi All,

I've been looking for a way to define database tables and alter them via a Django API.

For example, I'd like to be write some code which directly manipulates table DDL and allow me to define tables or add columns to a table on demand programmatically (without running a syncdb). I realize that django-south and django-evolution may come to mind, but I don't really think of these tools as tools meant to be integrated into an application and used by and end user... rather these tools are utilities used for upgrading your database tables. I'm looking for something where I can do something like:

class MyModel(models.Model):  # wouldn't run syncdb.. instead do something like below
    a = models.CharField()
    b = models.CharField()

model = MyModel()
model.create()  # this runs the create table (instead of a syncdb)

model.add_column(c = models.CharField())  # this would set a column to be added
model.alter()   # and this would apply the alter statement 

model.del_column('a')   # this would set column 'a' for removal 
model.alter()   # and this would apply the removal 

This is just a toy example of how such an API would work, but the point is that I'd be very interested in finding out if there is a way to programatically create and change tables like this. This might be useful for things such as content management systems, where one might want to dynamically create a new table. Another example would be a site that stores datasets of an arbitrary width, for which tables need to be generated dynamically by the interface or data imports. Dose anyone know any good ways to dynamically create and alter tables like this?

(Granted, I know one can do direct SQL statements against the database, but that solution lacks the ability to treat the databases as objects)

Just curious as to if people have any suggestions or approaches to this...

Joe

+1  A: 

Quickly off the top of my head..

Create a Custom Manager with the Create/Alter methods.

Rasiel
Thank you for your advice, Rasiel. I guess I was hoping that I wouldn't have to hard-code SQL into a manager or something like that. After all, if syncdb can issue CREATE statements, one can hope that it can be called manually. However, perhaps this use of SQL DDL in Django is something that most people just have no desire to do.
Joe J
+1  A: 

You can try and interface with the django's code that manages changes in the database. It is a bit limited (no ALTER, for example, as far as I can see), but you may be able to extend it. Here's a snippet from django.core.management.commands.syncdb.

for app in models.get_apps():
        app_name = app.__name__.split('.')[-2]
        model_list = models.get_models(app)
        for model in model_list:
            # Create the model's database table, if it doesn't already exist.
            if verbosity >= 2:
                print "Processing %s.%s model" % (app_name, model._meta.object_name)
            if connection.introspection.table_name_converter(model._meta.db_table) in tables:
                continue
            sql, references = connection.creation.sql_create_model(model, self.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, self.style, pending_references))
            sql.extend(connection.creation.sql_for_pending_references(model, self.style, pending_references))
            if verbosity >= 1 and sql:
                print "Creating table %s" % model._meta.db_table
            for statement in sql:
                cursor.execute(statement)
            tables.append(connection.introspection.table_name_converter(model._meta.db_table))

Take a look at connection.creation.sql_create_model. The creation object is created in the database backend relevant to the database you are using in your settings.py. All of them are under django.db.backends.

If you must have ALTER table, I think you can create your own custom backend that extends an existing one and adds this functionality. Then you can interface with it directly through a ExtendedModelManager you create.

OmerGertel
OmerGertel, thank you for this approach. I'll have to dig into it to see if I can extend it to do alters as you suggest. My dream would be to have Django (or at least my extension) be able to support "create, alter, truncate, etc". I think it would open up a whole new realm of uses for it. Thanks again for your comments and thoughts.
Joe J