orm in django

ORM in DJANGOHoang Nguyen

[Agenda]ORM, What is?

and learn by hand

[OOP, What does this mean?]

[Welcome to of Objects]

Each object is a Lego piece

This Lego bear sculpture contains over 95,000 LEGO pieces and took over 1100 hours to construct together.

This amazing Lego airport was showcased in LegoCity, at Senayan City, Indonesia.

This machine has several playable features include functional powered treads for movement, full suspension and front and rear steering. A true masterpiece.

Do you love Lego, too?

if you are programmer {while (true) {

YOU OOP!}

}

We also need persistence

[How we do persistence]OOP languages provide object persistence

But it is fragile

Relational databases

Can store vast amounts of data in a structured way that allows for efficient storage, access, and search

NoSQL solutions

truly vast datasets, real time and concurrent

[so, What problem]

Table Column RowClass Object Attrib-ute

key – valuepairRelationMethod Inher-

itance

Associ-ation

public const string ConnectionString =

@"Data Source=.\Sqlexpress;Initial Catalog=Example;Integrated Security=SSPI;";

string sql = "select * from table where fname = '" + firstName + "' and lname

= '" + lastName + "'";

using (SqlConnection connection = new SqlConnection(ConnectionString)){

using (SqlCommand command = new SqlCommand(sql, connection)) {

connection.Open();

SqlDataReader reader = command.ExecuteReader();

while (reader.Read()) {

string output = "First Name: {0} \t Last Name: {1} \t Phone: {2}";

Console.WriteLine(output, reader["FirstName"], reader["LastName"],

reader["Telephone"]);

}

}

}

SELECT '%c%' as Chapter,(SELECT count(ticket.id) as Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status IN('new','assigned') ) AS 'New',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' ANDticket.status='document_interface' ) AS 'Document\ Interface',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' ANDticket.status='interface_development' ) AS 'Inter\face Development',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' ANDticket.status='interface_check' ) AS 'Interface C\heck',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' ANDticket.status='document_routine' ) AS 'Document R\outine',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' ANDticket.status='full_development' ) AS 'Full Devel\opment',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' ANDticket.status='peer_review_1' ) AS 'Peer Review O\ne',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%‘ AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' ANDticket.status='peer_review_2' ) AS 'Peer Review Tw\o',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='qa' ) AS 'QA',(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%‘ AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' ANDticket.status='closed' ) AS 'Closed',count(id) AS Total, ticket.id AS _idFROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticketWHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine'

I am programmer. I love OOP. I hate SQL.

You hear me. ORM is sexy.

[ORM, What is]Object - Relational Mapping

type systems object-oriented

virtual object database

bunch of code

provides a set of APIs which allows access databases in OO style

helps database become more transparent

[ORM, What is]

1994: TopLink for SmallTalk – the first ORM

2001 – 2002: Hibernate

Entity Framework and LINQ

ACTIVE RECORD CORE DATA

Gavin King

[Discussion]

ORM is the Vietnam of Computer Sciencehttp://blogs.tedneward.com/post/the-vietnam-of-computer-science/

https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/http://martinfowler.com/bliki/OrmHate.html

Trying to match two different world

How to work with ORM in Django

[ORM in Django]

Save for insert and update

Delete for, well, delete

And some methods to load records

[How to create a model]

Must inherit from the Model class

Model class inside the models package (django.db.models.Model)

Model add ORM methods, such as save() and delete()

Model add an objects collection as a property for querying data

Create Student Model

[How to create a model]

property_name = models.Type(fieldOptions)

Type is some Field class inside models (you can create and use customize field classes)

Each property_name exactly is an instance of some field class

Each Field class represent a column type, which tells to the database what kind of data to store.

fieldOptions is a set of field-specific arguments

[How to create a model]

[How to create string Field] property_name = models.CharField(fieldOptions)

property_name = models.TextField(fieldOptions)

max_length: integer number represent maximum number of characters

null: bool value to indicate if null value is allowed. False by default.

blank: bool value to indicate if empty string is allowed. False by default.

default: default value if none is provided

choices: enumeration

[How to create number Field]

property_name = models.BigIntegerField(fieldOptions)

property_name = models.IntegerField(fieldOptions)

property_name = models.SmallIntegerField(fieldOptions)

property_name = models.PositiveIntegerField(fieldOptions)

property_name = models.PositiveSmallIntegerField(fieldOptions)

property_name = models.FloatField(fieldOptions)

null: bool value to indicate if null value is allowed. False by default.

default: default value if none is provided

[How to create Boolean Field]

property_name = models.BooleanField(fieldOptions)

property_name = models.NullBooleanField(fieldOptions)

null: bool value to indicate if null value is allowed. False by default.

default: default value if none is provided

[How to create date - time Field]

property_name = models.DateField(fieldOptions)

property_name = models.TimeField(fieldOptions)

property_name = models.DateTimeField(fieldOptions)

auto_now: automatically set the field to now every time the object is saved. False by default. Note that the current date is always used; it’s not just a default value that you can override.

auto_now_add: automatically set the field to now when the object is first created.Note that the current date is always used; it’s not just a default value that you can override.

[Some special fields]

A CharField that checks that the value is a valid email address. max_length=254

A CharField that checks that the value is a valid url. max_length=200

A CharField that checks that the value is a valid IP address.

protocol: both, IPv4, IPv6

unpack_ipv4: to convert a packed address(::ffff:192.0.2.1) to ipv4 address (192.0.2.1)

[Some special fields]

upload_to: the string value will be appended to your MEDIA_ROOT path to form the location on the local filesystem where uploaded files will be stored. upload_to may also be a callable, such as a function

All that will be stored in your database is a path to the file (relative to MEDIA_ROOT). You can get absolute path use mug_shot.url

Inherits all attributes and methods from FileField, but also validates that the uploaded object is a valid image.

height_field=None, width_field=None

Read API reference for the other field types

[How to create a primary key]

Must be unique for each record

Typically has a name of id

Typically is a auto-generate number

models.CharField(primary_key=True)

models.IntField(primary_key=True)

models.AutoField(primary_key=True)

[Exercise 1]

first_name: is a string, length <= 30, not null, not blank

mid_name: is a string, length <= 50, nullable, blankable

last_name: is a string, length <=30, not null, not blank

birthday: for storing the date of birth

admission_day: for storing the date and time

gender: F or M

class_name: is a string, length <= 50

special: is a string, length <=100

class_term: is a integer number

How about relationships?

[How to create relationships]

ForeignKey

ManyToManyField

OneToOneField

[How to connect to database]

[How to connect to database] for details

SQLite

MySQL

Oracle

PostgreSQL

[How to sync between models and database]

Creates a package that contains all of the changes to be made on the database based on the code changes in the models

--name

Synchronizes the database state with the current set of models and migrations

--fake

Prints the SQL for the named migration

[How to CRUD in OO style]

[How to CRUD in OO style]

[How to CRUD in OO style]

objects

objects

objects

objects

[How to Retrieve in OO style]

lazy

QuerySet has two public properties: ordered and db

Two main kinds of functions

return an instance of QuerySet class: all, filter, exclude, annotate, orderby, distinct, …

get, count, first, last, Avg, Count, Max, Min, Sum, Variance, StdDev, …

Example with get, all functions

[How to Retrieve in OO style]

[How to Retrieve in OO style] values(‘property1’, ‘property2’, …) to select some properties

Lookup expression

Django is aware of your object structure => we can query data using the class structure we created.

Use two underscore to call function or deeper property

String funcs: exact, contains, startswith, endswith, regex,

Use i prefix to call insensitive string funcs

Number funcs: gt, gte, lt, lte, range, …

Use in for list and subquery statements

Limiting QuerySet: [5], [:5], [5:10]

Examples with get, filter and exclude

[How to Retrieve in OO style] How to make complex lookup expression

Chaining filters

Student.objects.filter(first_name__exact=‘Nguyen’).filter(last_name__exact=‘Hoang’)

Q() objects

&, |, ~

Q(first_name__exact=‘Nguyen’) & Q(last_name__exact=‘Hoang’)

Multiple update and delete

filters.delete()

filters.update(property1=value1, …)

filters.update(property1 = F(‘property1’) + 1, property2 = F(‘property3’) + 2)

“More like science. You grab this piece of library and you poke at it. You writeprograms that poke it and see what it does. And you say, ‘Can I tweak it to do thething I want?’”

As to why they chose Python as an alternative, Sussman joked that it was “latebinding” decision. Python has a ton of libraries that make it applicable to manytypes of projects that instructors might want to assign (like writing software tocontrol a robot.)