15-437 Database

Storing Data Thierry Sans

Last time - Hello You

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

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

Today - The Web Directory

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

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://

The (Django) Web Directory

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)

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)

Relational Databases (in 5min)

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

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

Records • A record is a collection of attributes/values matching
the table deﬁnition • The primary key value uniquely identify the record

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

The database that we will use : SQLite ➡ Idea
: storing the database in a normal ﬁle ๏ Less performant than “real” databases ✓ Much simpler to use and export 

Conﬁguring Django

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

Web Directory Creating the model

What do we want to record in the database? •
An Entry is composed of an image URL a name a webpage URL

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 deﬁne a class representing the data structure • https://docs.djangoproject.com/en/1.7/ref/models/ﬁelds/

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

Mapping between the Django model and the SQL database

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)

Using the data model

Playing with the model API $ python manage.py shell! !
>>> from WebDirectory.models import Entry!

Adding a record to the database >>> e = Entry(image=”http://example.com/pic1/”,\
! name=”Bart”,\! webpage=”htttp://example.com”)! >>> e.save()

Modifying a record >>> e.name = “Thierry”! >>> e.save()

Fetching all records >>> Entry.objects.all()! [<Entry: Entry object>]! ! The
model does not have a text representation of the object

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

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)! []!

Performing raw SQL queries in Django >>> for p in
Entry.objects.raw(“SELECT id, name FROM WebDirectory_entry”): print p! “Thierry”

Web Directory Creating the views

How to deﬁne 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/

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

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>

Database img name url http:// Bart http:// http:// Lisa http://
How to deﬁne 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://

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

15-437 Database

15-437 Database

More Decks by ThierrySans

Other Decks in Education

Featured

Transcript