orm in django
TRANSCRIPT
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.)