15-437 Storing Data and Files

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

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() [<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): 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: “Thierry”>] >>> Entry.objects.filter(firstname=”Thierry”) [<Entry: “Thierry”>]
>>> Entry.objects.filter(firstname__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 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 ➡ Deﬁne 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 <html> <body> <img src=images/bart.jpg/>
</body> </html> 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 ﬁles

Saving uploaded ﬁles 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

Conﬁguring Django about where to upload ﬁles # 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 ﬁles 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 ﬁle 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 deﬁne 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 ﬁles urlpatterns = patterns('', url(r'^$', views.index,
name='index'), url(r'^add/$', views.add, name='add'), url(r'^getimage/(?P<image_id>\d+)/$',\ views.getImage,\ name='getImage'), ) WebDirectory/urls.py

Controller - serving uploaded ﬁles 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 ﬁle from the database return an HTTP response of the corresponding MIME type

15-437 Storing Data and Files

15-437 Storing Data and Files

More Decks by ThierrySans

Other Decks in Programming

Featured

Transcript