Chapter 5, Django database layer

1. run postgresql on ubuntu: sudo -u postgre psql 

then input password

if you didn't set up user name, password or create db then follow the steps below:

sudo -u postgres createuser --superuser guangyi

then input the user name, like here, guangyi

sudo -u postgres psql

postgres=# \password guangyi

after inputting the new password

input:

sudo -u postgres createdb guangyi

input: 

psql

then you should see something like this:

psql(9.1.9)

try "help" for help

guangyi=# 

now we can write some SQL code to manage database, like

create database mydb;

so here we go. After doing all the steps above, we should have already set up the database. 

2. edit django settings.py file, database section. Need to edit "ENGINE", "NAME","USER", "PASSWORD" 4 section.

3. test configuration: run python manage.py shell 

when get into interpreter:

>>> from django.db import connection
>>> cursor = connection.cursor()

if nothing comes out after running those code, it means the connection is correct.

part2: First model

1. create a new app:

python manage.py startapp books

After that it will create a books directory with 4 python files in it. one of those 4 files is models.py

2. edit models

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()

Basic data access:

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()

When calling "save()", django then save the data into database.

the second way to do this is using objects.create（）:

p1 = Publisher.objects.create(name='Apress',
...     address='2855 Telegraph Avenue',
...     city='Berkeley', state_province='CA', country='U.S.A.',
...     website='http://www.apress.com/')

Adding Model String Representations

Make sure any model you define has a __unicode__() method 

Inserting and Updating Data

Filtering data:

Publisher.objects.filter(name='Apress', state='ca')

You can pass multiple argument into filter() 

>>> Publisher.objects.filter(name__contains="press")
[<Publisher: Apress>]

it's similar to:

SELECT id, name, address, city, state_province, country, website
FROM books_publisher
WHERE name LIKE '%press%';

The  filter()  examples above all returned a  QuerySet , which you can treat like a list

The get() example are different:

>>> Publisher.objects.get(name="Apress")
<Publisher: Apress>

it returns and can only return 1 object, if it get more than 2 object, there will arise an exception:

>>> Publisher.objects.get(country="U.S.A.")
Traceback (most recent call last):
    ...
MultipleObjectsReturned: get() returned more than one Publisher --
    it returned 2! Lookup parameters were {'country': 'U.S.A.'}

if the object does not exit:

>>> Publisher.objects.get(name="Penguin")
Traceback (most recent call last):
    ...
DoesNotExist: Publisher matching query does not exist.

The DoesNotExist exception is an attribute of the model’s class – Publisher.DoesNotExist. In your applications, you’ll want to trap these exceptions, like this:

try:
    p = Publisher.objects.get(name='Apress')
except Publisher.DoesNotExist:
    print "Apress isn't in the database yet."
else:
    print "Apress is in the database."

Ordering Data:

Publisher.objects.order_by()

>>> Publisher.objects.order_by("address")
[<Publisher: O'Reilly>, <Publisher: Apress>]

>>> Publisher.objects.order_by("state_province", "address")
 [<Publisher: Apress>, <Publisher: O'Reilly>]

Django can let us specify a default order in 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()

    def __unicode__(self):
        return self.name

    class Meta:
        ordering = ['name']

Slicing Data：

The return of Publisher.object.filter() or .object.all() is a list. so if want to slice data, regard the result as a list

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

Updating Multiple Objects in One Statement: update()

save() method updates all the columns 

>>> p = Publisher.objects.get(name='Apress')
>>> p.name = 'Apress Publishing'
>>> p.save()

but if we want to update only one column in an object, we'd better use update() method

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

use .id to get an instance's id

The update() method works on any QuerySet, which means you can edit multiple records in bulk. Here’s how you might change the country from 'U.S.A.' to USA in each Publisher record:

>>> Publisher.objects.all().update(country='USA')
2

The update() method has a return value – an integer representing how many records changed. In the above example, we got 2.

Deleting Objects: .delete()

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

if you want to delete all the data in a model, you have to add all() after .objects

>>> Publisher.objects.delete()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
AttributeError: 'Manager' object has no attribute 'delete'

But it’ll work if you add the all() method:

>>> Publisher.objects.all().delete()