python - 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.

Answer 2

To make your access to the database "read only", I guess the best option is to create a limited used in the MySQL side with only SELECT:

GRANT SELECT ON target_database.* TO your_user@'your_host' IDENTIFIED BY 'your_password';

This will make sure that in any case an update/alter will succeed.

Usually you model your database tables as objects because this makes it easier to work with database records from Python and gives you some abstraction, but you can execute raw SQL queries if you feel this is the right thing to do.

Depending on how you want to present your data, you may need to convert it to something.

If your want to make your application more dynamic (for example, retrieving new data in 10 seconds intervals and presenting it to the user without refresh) you probably will need to convert it to some format more suitable to be used with AJAX, like JSON or XML (Django has some serialization tools ready to be used). If you just want a "static" application( ie: user clicks in a link/button and goes to a page where data is presented, and to be refreshed user has to refresh the page) you can use the objects as retrieved from the database in your view.

Answer 3

There is going to be no problem if the data is being updated.

Now for retrieving the data from database you first have to import the concerned model in your views

from app_name.models import model_name
def view_report(request):
    r_name=request.POST.get('r_name','default_value')
    r=model_name.objects.get(report_name=r_name)
    return render_to_response('url',{'r':r})

In your template

{{r.report_desc}}