Share
Embed
Transcript
40 slides
-
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 -
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 -
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 -
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) -
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() -
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” -
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 <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 -
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<image_id>\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