Integrating with a Legacy Database

Django’s database layer generates SQL schemas from Python code — but with a legacy database, you already have the SQL schemas. In such a case, you’ll need to create models for your existing database tables. For this purpose, Django comes with a tool that can generate model code by reading your database table layouts. This tool is called inspectdb, and you can call it by executing the command manage.py inspectdb.

Using inspectdb – The inspectdb utility introspects the database pointed to by your settings file, determines a Django model representation for each of your tables, and prints the Python model code to standard output. Here’s a walk-through of a typical legacy database integration process from scratch. The only assumptions are that Django is installed and that you have a legacy database.

 

• Create a Django project by running django-admin.pystartprojectmysite (where mysite is your project’s name). We’ll use mysite as the project name in this example.
• Edit the settings file in that project, mysite/settings.py, to tell Django what your database connection parameters are and what the name of the database is. Specifically, provide the DATABASE_NAME, DATABASE_ENGINE, DATABASE_USER, DATABASE_PASSWORD, DATABASE_HOST, and DATABASE_PORT Note that some of these settings are optional.
• Create a Django application within your project by running pythonmysite/manage.pystartappmyapp (where myapp is your application’s name). We’ll use myapp as the application name here.
• Run the command pythonmysite/manage.pyinspectdb. This will examine the tables in the DATABASE_NAME database and print the generated model class for each table. Take a look at the output to get an idea of what inspectdb can do.
• Save the output to the py file within your application by using standard shell output redirection:

python mysite/manage.py inspectdb > mysite/myapp/models.py

• Edit the mysite/myapp/models.py file to clean up the generated models and make any necessary customizations. We’ll give some hints for this in the next section.

Cleaning Up Generated Models – As you might expect, the database introspection isn’t perfect, and you’ll need to do some light cleanup of the resulting model code. Here are a few pointers for dealing with the generated models:

 

• Each database table is converted to a model class (i.e., there is a one-to-one mapping between database tables and model classes). This means that you’ll need to refactor the models for any many-to-many join tables into ManyToManyField
• Each generated model has an attribute for every field, including id primary key fields. However, recall that Django automatically adds an id primary key field if a model doesn’t have a primary key. Thus, you’ll want to remove any lines that look like this:

id = models.IntegerField(primary_key=True)

Not only are these lines redundant, but also they can cause problems if your application will be adding new records to these tables. The inspectdb command cannot detect whether a field is autoincremented, so it’s up to you to change this to AutoField, if necessary.

• Each field’s type (e.g., CharField, DateField) is determined by looking at the database column type (e.g., VARCHAR, DATE). If inspectdb cannot map a column’s type to a model field type, it will use TextField and will insert the Python comment ‘Thisfieldtypeisa’ next to the field in the generated model. Keep an eye out for that, and change the field type accordingly if needed.

If a field in your database has no good Django equivalent, you can safely leave it off. The Django model layer is not required to include every field in your table(s).

 

• If a database column name is a Python reserved word (such as pass, class, or for), inspectdb will append ‘_field’ to the attribute name and set the db_column attribute to the real field name (e.g., pass, class, or for).

For example, if a table has an INT column called for, the generated model will have a field like this:

for_field = models.IntegerField(db_column=’for’)

inspectdb will insert the Python comment ‘Field renamed because it was a Python reserved word.’ next to the field.

• If your database contains tables that refer to other tables (as most databases do), you might need to rearrange the order of the generated models so that models that refer to other models are ordered properly. For example, if model Book has a ForeignKey to model Author, model Author should be defined before model Book. If you need to create a relationship on a model that has not yet been defined, you can use the name of the model, rather than the model object itself.
• inspectdb detects primary keys for PostgreSQL, MySQL, and SQLite. That is, it inserts primary_key=True where appropriate. For other databases, you’ll need to insert primary_key=True for at least one field in each model, because Django models are required to have a primary_key=True
• Foreign-key detection only works with PostgreSQL and with certain types of MySQL tables. In other cases, foreign-key fields will be generated as IntegerField“s,assumingtheforeign-keycolumnwasan“INT

Back to Tutorial