Pulling data to the template from an external database with django

Question

I'm going to attempting to build a web app where users can visit a url, login and view reports and other information. However the data for the reports are stored in an external database. It's a MySQL database which I'm going to have access to.

I've done a little research on google and not have much luck finding any examples. I've done a little reading into connecting to multiple databases - https://docs.djangoproject.com/en/dev/topics/db/multi-db/ So it looks i can connect to the database ok.

The next part is where I'm stuck. The data in the database is going to be updated all the time. I don't want to be able to edit the information neither do i want to be able to overwrite anything. I just want to be able to connect to the DB pull the information required and then view it through the template for user to be able to see. First of all because the data is being updated all the time, is this going to be a problem? (I hope not!)

Once I've connected to the database, what is the best to be able to pull out the data and then put it into a format that i can output to the template? Would i need to import the data in to models, then control with the view. Or would i need to convert the data with JSON or XML?

I'm fairly new to python / django, so any help would be much appreciated. If you need anymore info please ask and thanks in advance. :)

Answer 1

No problem! I do this all the time.

As far as the "don't edit or update the data", just don't add anything to your app that would update the data. Salem's suggestion about using permissions on the MySQL side is a good idea as well.

For retrieving the data, you have two options:

1) You can create Django models that correspond to your tables in the MySQL database. You can do this manually, or you can use the "inspectdb" command with manage.py to give yourself a good starting point. Then do something like this:

def myview(request):
  rows = MyModel.objects.using('mysql').all()
  return render_to_response("mytemplate.html", {"rows" : rows })

2) You can manage the connections and queries manually within your app. This is perfectly valid within a view:

def myview(request):
  conn = MySQLdb.connect("connection info here")
  try:
    cursor = conn.cursor()
    cursor.execute("select * from mytable")
    rows = cursor.fetchall()
  finally:
    conn.close()

  return render_to_response("mytemplate.html", {"rows" : rows})

finally -- Django is perfectly happy to use MySQL as a database. It might simplify things if your DBA will let Django create its tables right in the same database.