Storing Data Thierry Sans 

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 definition • 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 file ๏ Less performant than “real” databases ✓ Much simpler to use and export
 

Configuring 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): firstname = models.CharField(max_length=200) img_url = models.URLField(max_length=200) w_url = models.URLField(max_length=200) api/models.py With Django, we define a class representing the data structure • https://docs.djangoproject.com/en/1.9/ref/models/fields/ 

Migrating the database https://docs.djangoproject.com/en/1.7/topics/migrations/ Create new migrations $ python manage.py makemigrations api 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 "api_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 api.models import Entry 

Adding a record to the database >>> e = Entry(img_url=”http://example.com/pic1/”,\ firtname=”Bart”,\ w_url=”htttp://example.com”) >>> e.save() 

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

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

Create a representation >>> Entry.objects.all() [] class Entry(models.Model): firstname = models.CharField(max_length=200) img_url = models.URLField(max_length=200) w_url = models.URLField(max_length=200) def __unicode__(self): return self.firstname api/models.py 

Selecting records >>> Entry.objects.filter(id=1) [] >>> Entry.objects.filter(firstname=”Thierry”) [] >>> Entry.objects.filter(firstname__startswith=”Th”) [] >>> Entry.objects.filter(id=2) [] 

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

Handling Files Thierry Sans 

MIME Types 

MIME types MIME (Multipurpose Internet Mail Extensions)
 is also known as the content type ➡ Define the format of a document exchanged on internet 
 (IETF standard) http://www.iana.org/assignments/media-types/index.html 

Examples of MIME types • text/html • text/css • text/javascript • image/jpeg - image/gif - image/svg - image/png (and so on) • application/pdf • application/json 

Example of how images are retrieved GET hello/bart/ http://localhost/HelloYou/ http://www.example.com//hello/bart/ GET images/bart.jpg MIME : text/html MIME : image/jpg 

Server Side
Saving and serving uploaded files 

Saving uploaded files Controller def add(request): ... Database img mime path image/png path image/jpg The image path and the mime type are stored in the database POST add/ uploads The image is stored in the upload directory Image 

Configuring Django about where to upload files # Absolute filesystem path to the directory that will hold user-uploaded files. # Example: "/home/media/media.lawrence.com/media/" MEDIA_ROOT = os.path.join(BASE_DIR, 'uploads/') tsans/settings.py 

The model class Entry(models.Model): # image = models.CharField(max_length=200) name = models.CharField(max_length=200) webpage = models.URLField(max_length=200)
 class Image(models.Model): entry = models.ForeignKey(Entry, unique=True) image = models.ImageField(upload_to='WebDirectory') mimeType = models.CharField(max_length=20) WebDirectory/models.py where to store uploaded files use FileField for generic files We need to record the MIME type (see serving uploaded file) entry foreign key 

Migrating the WebDirectory database $ python manage.py makemigrations WebDirectory $ python manage.py migrate 

Controller - saving uploaded files from django.views.decorators.csrf import csrf_exempt @csrf_exempt def add(request): e = Entry(name = request.POST['name'],\ webpage = request.POST['website']) e.save()
 i = Image(entry = e,\ image = request.FILES['file'],\ mimeType=request.FILES['file'].content_type) i.save()
 return HttpResponseRedirect(reverse('index')) WebDirectory/views.py get the file from the HTTP request Not secure but we will talk about security later get the MIME type 

Controller - serving uploaded files • How to serve these uploaded files? • Should we serve them as static files? 

Why not serving uploaded files as static files? Because we want to control • who can access these files • when to serve these files • how to serve these files 

A good way to serve uploaded files Have a method to control (controller) the file - getImage Reference the image using an identifier • automatically generated hash code • or database entry id (primary key in the database) 

How to define getImage Controller def getimage(request,imageid): ... GET getimage/345/ uploads Retrieve the image from the upload directory
 (this is done automatically by Django) Database img mime path image/png path image/jpg Get the image path and mime type from the database based on its id Return a HTTP response of the corresponding mime type Image 

Controller - serving uploaded files urlpatterns = patterns('', url(r'^$', views.index, name='index'), url(r'^add/$', views.add, name='add'), url(r'^getimage/(?P\d+)/$',\ views.getImage,\ name='getImage'), ) WebDirectory/urls.py 

Controller - serving uploaded files def getImage(request,image_id): i = Image.objects.get(id=image_id) return HttpResponse(i.image.read(), content_type=i.mimeType) WebDirectory/views.py get the file from the database return an HTTP response of the corresponding MIME type