20

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. :)

JDavies
  • 2,730
  • 7
  • 34
  • 54

3 Answers3

29

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.

Chris Curvey
  • 9,738
  • 10
  • 48
  • 70
  • Thanks for all of your replies, gives me enough information to go on. Would i need to setup a new app to pull the information into a new models.py file? Also when the data is pulled into the models, would the information update everyime i refreshed the page as if were being displayed on the template? – JDavies May 17 '13 at 10:58
  • You don't *need* a separate app for the MySQL classes, but you are welcome to create it. I'm looking at my code right now, and I have my remote stuff set up as sub-modules of models. (myapp/models, myapp/models/somethingelse, myapp/models/anotherthing). That's because in my case, I have items in myapp/models that wrap instances from "somethingelse" and "otherthing". You could also create a new app to segregate it, or you could just prefix all the names. – Chris Curvey May 17 '13 at 12:49
  • WRT data updates: If you use something like the examples above, then the answer is "yes". The query will get executed every time and be up-to-the moment. You'd have to add your own caching if you don't want that behavior. – Chris Curvey May 17 '13 at 12:51
  • Ok thanks, i'll try adding the models into my existing app for testing purposes then. I'm going to setup the DB in my settings file, how would i connect to the external DB? Would i just add the IP where the DB is hosted? – JDavies May 17 '13 at 15:12
  • see https://docs.djangoproject.com/en/dev/topics/db/multi-db/. there's an example right there. keep a 'default' entry for your main database, and another entry (the example uses 'users') for your external db – Chris Curvey May 17 '13 at 15:21
  • Thanks again, sorry i can't see how you setup the connection to the external DB? Would you mind pasting some of your code as an example? Without giving away any private info of course :) – JDavies May 17 '13 at 18:03
  • I agree with the solution given, but how can we ORM queries to the tables? Is there any or do we have to use raw queries only? – Vaibhav Kakade Jun 29 '21 at 10:22
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.

Salem
  • 12,808
  • 4
  • 34
  • 54
  • Is there a way i can test the connection to the external DB? Like run a script as if i were using a GUI? – JDavies May 20 '13 at 14:32
  • Yes, you can use something like `from django.db import connections; c=connections["external_db"].cursor()`. You can wrap this in a try/except block (if you don't get any exception connection was successfull). Also check [this](http://stackoverflow.com/a/6894615/1205368). – Salem May 20 '13 at 15:01
  • Really nice detail. Does it stand true with new version of Django 1.10, is there a better sleek way to do it? I would rather have my external database defined in settings.py. – nomad Mar 29 '17 at 17:09
1

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}}
sawan gupta
  • 188
  • 1
  • 8