Upgrade to Pro — share decks privately, control downloads, hide ads and more …

15-437 Database

ThierrySans
September 15, 2013

15-437 Database

ThierrySans

September 15, 2013
Tweet

More Decks by ThierrySans

Other Decks in Education

Transcript

  1. http://localhost/helloyou/ Step 1 - helloyou/ (the index page) helloyou/ <html>!

    <head ... Controller def index(request):! return ... Templates <html>! <head>! <title> ... HelloYou/views.py HelloYou/templates/HelloYou/index.html URL Dispatcher Project App (r'^helloyou/$' ... tsans/urls.py HelloYou/urls.py
  2. URL Dispatcher Project App (r'^sayhello$' ... http://localhost/helloyou/ Step 2 -

    helloyou/sayhello/ helloyou/sayhello?name=”cmu” “Hello CMU!” Controller def sayhello(request):! return ... Hello CMU! http://localhost/HelloYou/sayhello/ GET argument
  3. Controller def index(request):! return ... Templates <html>! <head>! <title> ...

    URL Dispatcher Project App (r'^webdirectory/$' Step 1 - webdirectory/ (the index page) Database img name url http:// Bart http:// http:// Lisa http:// webdirectory/ <html>! <head ...
  4. Controller def add(request):! return ... URL Dispatcher Project App (r'^add/$'

    Step 2 - webdirectory/add/ Database img name url http:// Bart http:// http:// Lisa http:// add/?name=... <html>! <head ... http:// Marge http://
  5. A quick migration ! 1.Create an application WebDirectory 2.Configure the

    URL dispatcher 
 (urls.py and WebDirectory/urls.py) 3.Create the template index.html! 4.Move the static files (JS, CSS and Media files)
  6. What we want to change 1.Create a database and a

    data model 2.Modify the index to retrieve all entries from the database 3.Create a function add that will add entries to the database
 (no longer done by Javascript)
  7. What is a relational database? A Database Management System is

    a software that provides • a way to organize data - Relational schema • a way to store data - Records • a way to access data - SQL language
  8. The relational schema • Tables are data records with attribute

    names and types • Each table has a primary key • Primary keys / foreign keys creates relations between tables
  9. Records • A record is a collection of attributes/values matching

    the table definition • The primary key value uniquely identify the record
  10. SQL Language SQL (Structured Query Language) is a programming language

    designed for managing data in a relational database. • create, alter, drop tables • insert, update and delete tuples • select tuples
  11. The database that we will use : SQLite ➡ Idea

    : storing the database in a normal file ๏ Less performant than “real” databases ✓ Much simpler to use and export

  12. Using the SQLite database ! DATABASES = {! 'default': {!

    'ENGINE': 'django.db.backends.sqlite3',! 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),! }! }! ! tsans/settings.py use SQLite 3 database path
  13. What do we want to record in the database? •

    An Entry is composed of an image URL a name a webpage URL
  14. Creating the data model Entry from django.db import models! !

    class Entry(models.Model):! image = models.CharField(max_length=200)! name = models.CharField(max_length=200)! webpage = models.CharField(max_length=200) WebDirectory/models.py With Django, we define a class representing the data structure • https://docs.djangoproject.com/en/1.7/ref/models/fields/
  15. Migrating the database https://docs.djangoproject.com/en/1.7/topics/migrations/ Create new migrations $ python manage.py

    makemigrations WebDirectory! Apply the migrations $ python manage.py migrate! ➡ Must be done every time you change the model
  16. Model vs Schema BEGIN;! CREATE TABLE "WebDirectory_entry" (! "id" integer

    NOT NULL PRIMARY KEY,! "image" varchar(200) NOT NULL,! "name" varchar(200) NOT NULL,! "webpage" varchar(200) NOT NULL! );! COMMIT; class Entry(models.Model):! image = models.CharField(max_length=200)! name = models.CharField(max_length=200)! webpage = models.CharField(max_length=200) Primary keys automatically generated
 (auto_increment)
  17. Playing with the model API $ python manage.py shell! !

    >>> from WebDirectory.models import Entry!
  18. Adding a record to the database >>> e = Entry(image=”http://example.com/pic1/”,\

    ! name=”Bart”,\! webpage=”htttp://example.com”)! >>> e.save()
  19. Fetching all records >>> Entry.objects.all()! [<Entry: Entry object>]! ! The

    model does not have a text representation of the object
  20. Create a representation >>> Entry.objects.all()! [<Entry: “Thierry”>]! ! class Entry(models.Model):!

    image = models.CharField(max_length=200)! name = models.CharField(max_length=200)! webpage = models.CharField(max_length=200)! ! def __unicode__(self):! return self.name WebDirectory/models.py
  21. Selecting records >>> Entry.objects.filter(id=1)! [<Entry: “Thierry”>]! ! >>> Entry.objects.filter(name=”Thierry”)! [<Entry:

    “Thierry”>]! ! >>> Entry.objects.filter(name__startswith=”Th”)! [<Entry: “Thierry”>]! ! >>> Entry.objects.filter(id=2)! []!
  22. Performing raw SQL queries in Django >>> for p in

    Entry.objects.raw(“SELECT id, name FROM WebDirectory_entry”): print p! “Thierry”
  23. How to define index Controller def index(request):! ... <html>! <head

    ... Database img name url http:// Bart http:// http:// Lisa http:// For each entry in the database ... Templates <html>! <head>! <title> ... ... create the corresponding HTML entry in the template webdirectory/
  24. Create the controller index WebDirectory/views.py from django.shortcuts import render! from

    WebDirectory.models import Entry! ! def index(request):! entry_list = Entry.objects.all()! return render(request,\! 'WebDirectory/index.html',\! {'entry_list': entry_list}) Fetch all entries in the database Call the template index.html
 with the list of all entries as argument
  25. Create the template WebDirectory/templates/WebDirectory/index.html <div id="directory">! {% if entry_list %}!

    {% for entry in entry_list %}! <div class="entry">! <div class="image"><img src="{{entry.image}}"/>! </div>! <div class="name">{{entry.name}}</div>! <div class="website">! <a href="{{entry.webpage}}">{{entry.name}}'s website</a>! </div>! </div>! {% endfor %}! {% else %}! <p>No entry.</p>! {% endif %}! </div>
  26. Database img name url http:// Bart http:// http:// Lisa http://

    How to define add <html>! <head ... Controller def add(request):! ...! ! def index(request):! ... 1) extract the arguments image - name - webpage add/?name=... 3) redirect to the (updated) index page 2) add the entry in the database http:// Marge http://
  27. Create the controller add WebDirectory/views.py from django.shortcuts import HttpResponseRedirect! from

    django.core.urlresolvers import reverse! from WebDirectory.models import Entry! ! def add(request):! e = Entry(image= request.GET['image'],\ ! name = request.GET['name'],\ ! webpage = request.GET['website'])! e.save() ! return HttpResponseRedirect(reverse('index')) Create the new entry Save the entry in the database redirect to the index page