Commands and Instructions to manipulate databases using Django

ref: http://www.djangobook.com/en/2.0/chapter05.html

Commands:

To Validate the model:

python manage.py validate

To Show the SQL that will be generate

python manage.py sqlall <model>

To Create the Database:

python manage.py syncdb

Instructions:

Create a new registry:
Create a new object using a model class, for instance (class Publisher) and call the function save( ).

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

Getting the generated ID:
After save an object, you can get the id using the id property of object:

>>> p1.id
52    # this will differ based on your own data

where p1 is the object

Getting all rows in the table:

>>> publisher_list = Publisher.objects.all()
>>> publisher_list
[<Publisher: Publisher object>, <Publisher: Publisher object>]

Adding the model string representations (like toString( )):
Add the function def __unicode__(self) into the model

class Publisher(models.Model):
    name = models.CharField(max_length=30)
    address = models.CharField(max_length=50)

    def __unicode__(self):
        return self.name

Updating a registry:
First of all, the object needs to have a link with the database, and it occurs after you save the object into database or retrieve it. So, you can change the parameters of the object and call the function save( )

>>> p1.name = 'Apress Publishing'
>>> p1.save()

Filtering data: (returning QuerySet)
- Filtering by an argument (field):

>>> Publisher.objects.filter(name='Apress')
[<Publisher: Apress>]

- You can pass multiple arguments into filter() to narrow down things further:

>>> Publisher.objects.filter(country="U.S.A.", state_province="CA")
[<Publisher: Apress>]

- You can use the argument with __contains to create a similar LIKE operation of SQL:

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

Many other types of lookups are available, including icontains (case-insensitive LIKE), startswith and endswith, andrange (SQL BETWEEN queries)


Retrieving  single objects:
- Filtering by an argument (field):

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

- if this query return more than one result, it will cause an expection ( MultipleObjectsReturned )

- if this query returns no objects also causes an exception ( DoesNotExist ), you can use the structure below to trap the DoesNotExist exception:

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:

- Ordering by argument using order_by( )

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

- Ordering by more than one argument using order_by( )

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

- Reverse Ordering using order_by. Uses the '-' sign in front of the argument

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

- Change the default model ordering

class Publisher(models.Model):
    name = models.CharField(max_length=30)
    state_province = models.CharField(max_length=30)
    website = models.URLField()

    def __unicode__(self):
        return self.name

    class Meta:
        ordering = ['name']

Filter and Ordering data:

>>> Publisher.objects.filter(country="U.S.A.").order_by("-name")
[<Publisher: O'Reilly>, <Publisher: Apress>]

Slicing Data
You can get only selected rows using the Python's standard list slicing syntax ([start:end]:
-Only the first object:

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

- Using Range:

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

- Limit of Objects
>>> Publisher.objects.order_by('name')[:7]