the Django Book Chapter 5 (Database)

Chapter 5 Database SQL in Django

MySQL for Python
http://www.djangoproject.com/r/python-mysql/

MVC Pattern
the Model-View-Controller (MVC) pattern of software architecture. 

1. “Model” refers to the data access layer
2. “View” refers to the part of the system that selects what to display and how to display it,
3. “Controller” refers to the part of the system that decides which view to use, depending on user input, accessing the model as needed.

• M, the data-access portion, is handled by Django’s database layer
• V, the portion that selects which data to display and how to display it, is handled by views and templates.
• C, the portion that delegates to a view depending on user input, is handled by the framework itself by following your URLconf and calling the appropriate Python function for the given URL (setting.py). (Handled by Django itself)

Because the “C” is handled by the framework itself and most of the excitement in Django happens in models, templates and views, Django has been referred to as an MTV framework. In the MTV development pattern,

• M stands for “Model,” the data access layer. This layer contains anything and everything about the data: how to access it, how to validate it, which behaviors it has, and the relationships between the data.
• T stands for “Template,” the presentation layer. This layer contains presentation-related decisions: how something should be displayed on a Web page or other type of document.
• V stands for “View,” the business logic layer. This layer contains the logic that access the model and defers to the appropriate template(s). You can think of it as the bridge between models and templates.

Within the mysite project directory, type this command to create a books app:

python manage.py startapp books

This command does not produce any output, but it does create a books directory within the mysite directory. Let’s look at the contents of that directory:

books/
    __init__.py
    models.py
    tests.py
    views.py

he first step in using this database layout with Django is to express it as Python code. In the models.py file that was created by thestartapp command, enter the following:

from django.db import models

class Publisher(models.Model):
    name = models.CharField(max_length=30)
    address = models.CharField(max_length=50)
    city = models.CharField(max_length=60)
    state_province = models.CharField(max_length=30)
    country = models.CharField(max_length=50)
    website = models.URLField()

class Author(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=40)
    email = models.EmailField()

class Book(models.Model):
    title = models.CharField(max_length=100)
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher)
    publication_date = models.DateField()

Let’s quickly examine this code to cover the basics. The first thing to notice is that each model is represented by a Python class that is a subclass of django.db.models.Model. The parent class, Model, contains all the machinery necessary to make these objects capable of interacting with a database – and that leaves our models responsible solely for defining their fields, in a nice and compact syntax. Believe it or not, this is all the code we need to write to have basic data access with Django.

'mysite.books' refers to the books app we’re working on. Each app in INSTALLED_APPS is represented by its full Python path – that is, the path of packages, separated by dots, leading to the app package.

Now that the Django app has been activated in the settings file, we can create the database tables in our database. First, let’s validate the models by running this command:

python manage.py validate

if your models are valid, run the following command for Django to generate CREATE TABLE statements for your models in the booksapp

python manage.py sqlall books

python manage.py syncdb

python manage.py dbshell  (not for sqlite)

Note that syncdb does not sync changes in models or deletions of models; if you make a change to a model or delete a model, and you want to update the database, syncdb will not handle that. 

>>> from books.models import Publisher
>>> p1 = Publisher(name='Apress', address='2855 Telegraph Avenue',
...     city='Berkeley', state_province='CA', country='U.S.A.',
...     website='http://www.apress.com/')
>>> p1.save()
>>> publisher_list = Publisher.objects.all()
>>> publisher_list
[<Publisher: Publisher object>, <Publisher: Publisher object>]

>>> from books.models import Publisher
>>> publisher_list = Publisher.objects.all()
>>> publisher_list
[<Publisher: Apress>, <Publisher: O'Reilly>]

Make sure any model you define has a __unicode__() method – not only for your own convenience when using the interactive interpreter, but also because Django uses the output of __unicode__() in several places when it needs to display objects.

Publisher.objects.filter(country="U.S.A.", state_province="CA")

Publisher.objects.filter(name__contains="press")

Publisher.objects.get(name="Apress")

• MultipleObjectsReturned: get() returned more than one Publisher
• DoesNotExist: Publisher matching query does not exist.

Publisher.objects.order_by("state_province", "address")

Publisher.objects.order_by("-name")

class Publisher(models.Model):
    name = models.CharField(max_length=30)
    address = models.CharField(max_length=50)
    city = models.CharField(max_length=60)
    state_province = models.CharField(max_length=30)
    country = models.CharField(max_length=50)
    website = models.URLField()

    def __unicode__(self):
        return self.name

    class Meta:
        ordering = ['name']

>>> Publisher.objects.filter(country="U.S.A.").order_by("-name")

>>> Publisher.objects.order_by('name')[0]

>>> Publisher.objects.order_by('name')[0:2]

>>> Publisher.objects.order_by('-name')[0]

>>> Publisher.objects.order_by('name')[-1] <<<<<NOT SUPPORTED

Update Multiple Objects in One Single Statement

Publisher.objects.filter(id=52).update(name='Apress Publishing')

 Publisher.objects.filter(country='USA').delete()

 Publisher.objects.all().delete()

Chapter 18 for importing data
done


two entity two relationships:

creator = models.ForeignKey(Users, null=True, related_name='creator')
assignee = models.ForeignKey(Users, null=True, related_name='assignee')

done