mosql: more than sql, but less than orm @ pycon apac 2013
DESCRIPTION
It is the slides of the talk, "MoSQL: More than SQL, but Less than ORM", at PyCon APAC 2013. It introduces MoSQL after v0.6. About MoSQL: MoSQL is a Python library which lets you use common Python’s data structures to build SQLs. http://mosql.mosky.tw/TRANSCRIPT
More than SQL, but Less than ORMMoSQL (after v0.6)
Mosky
2
MoskyI'm working at Pinkoi
COSCUP staff
Python trainer
2
MoskyI'm working at Pinkoi
COSCUP staff
Python trainer
Speaker at COSCUP 2013, PyCon TW 2013, PyCon JP 2012, PyCon TW 2012 ...
2
MoskyI'm working at Pinkoi
COSCUP staff
Python trainer
Speaker at COSCUP 2013, PyCon TW 2013, PyCon JP 2012, PyCon TW 2012 ...
http://mosky.tw/
2
Pinkoi.com Builds Design Ecosystem
for people to BUY / SELL / SHARE designs and to be INSPIRED.
Pinkoi.com Builds Design Ecosystem
Pinkoi はアジアで最も大きいデザインショッピングウェブサイトです。優秀なデザイナー達がお客さんのためにいつもPinkoiで一番新しいデザインを提供しています。早めに
あなた達に会いたいですね。お楽しみ!
Outline
5
Outline
Why not SQL? But ...
5
Outline
Why not SQL? But ...
Why ORM? But ...
5
Outline
Why not SQL? But ...
Why ORM? But ...
MoSQL
5
Outline
Why not SQL? But ...
Why ORM? But ...
MoSQL
The Usage, Performance, and Security
5
Outline
Why not SQL? But ...
Why ORM? But ...
MoSQL
The Usage, Performance, and Security
Demo
5
Why not SQL?
Hard to Use
8
Hard to UseSELECT * FROM article LIMIT 1;
8
Hard to UseSELECT * FROM article LIMIT 1;
add ORDER BY created?
8
Hard to UseSELECT * FROM article LIMIT 1;
add ORDER BY created?
add OFFSET 10?
8
Hard to UseSELECT * FROM article LIMIT 1;
add ORDER BY created?
add OFFSET 10?
add GROUP BY author?
8
Hard to UseSELECT * FROM article LIMIT 1;
add ORDER BY created?
add OFFSET 10?
add GROUP BY author?
UPDATE article WHERE title='SQL' SET title='ORM'?
8
Hard to Use
9
Hard to Use
Programming Error
9
Hard to Use
Programming Error
Programming Error
9
Hard to Use
Programming Error
Programming Error
Programming Error
9
Hard to Use
Programming Error
Programming Error
Programming Error!@#$
9
May Be Injected
10
May Be Injected
'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs)
10
May Be Injected
'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs)
Cracker can inject from value
10
May Be Injected
'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs)
Cracker can inject from value
or identifier, actually.
10
May Be Injected
'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs)
Cracker can inject from value
or identifier, actually.
DON'T copy the code here!
10
It seems bad! But ...
SQL ...
12
SQL ...
is fastest way to communicate with db,
12
SQL ...
is fastest way to communicate with db,
and everyone understands or learned it.
12
Why ORM?
Easy to Use
14
Easy to Use
class Person(Base): __tablename__ = 'person' person_id = Column(String, primary_key=True) name = Column(String) ...
14
Easy to Use
15
Easy to Use
mosky = Person('mosky', 'Mosky Liu')session.add(mosky)
15
Easy to Use
mosky = Person('mosky', 'Mosky Liu')session.add(mosky)
for person in session.query(Person).all(): print person.name, person.person_id
15
Easy to Use
mosky = Person('mosky', 'Mosky Liu')session.add(mosky)
for person in session.query(Person).all(): print person.name, person.person_id
Let you forget the ugly SQL so far.
15
SQL Injection Free
16
SQL Injection Free
Usually ORM guarantees it.
16
It seems good! But ...
ORM ...
18
ORM ...
is slower,
18
ORM ...
is slower,
and you need to learn it from scratch.
18
ORM ...
is slower,
and you need to learn it from scratch.
Sometimes it is just a black box.
18
SQL vs. ORMSQL ORM
Easy-to-Use V
Secure V
Easy-to-Learn V
Fast V
So ... MoSQL
The First Glance
21
The First Glance
from mosql.query import selectprint select('person')
21
The First Glance
from mosql.query import selectprint select('person')
-> SELECT * FROM "person"
21
Map is just condition
22
Map is just condition
select('person', { 'person_id': 'mosky'})
22
Map is just condition
select('person', { 'person_id': 'mosky'})
-> SELECT * FROM "person" WHERE "person_id" = 'mosky'
22
Sequence is just a list
23
Sequence is just a list
select('person', select=('name', ))
23
Sequence is just a list
select('person', select=('name', ))
-> SELECT "name" FROM "person"
23
Map is also a set-list
24
Map is also a set-list
insert('person', { 'person_id': 'mosky', 'name' : 'Mosky Liu'})
24
Map is also a set-list
insert('person', { 'person_id': 'mosky', 'name' : 'Mosky Liu'})
-> INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu')
24
Order doesn't matter
25
Order doesn't matter
update('person', where={'person_id': 'mosky'}, set ={'name' : 'Mosky Liu'},})
25
Order doesn't matter
update('person', where={'person_id': 'mosky'}, set ={'name' : 'Mosky Liu'},})
-> UPDATE "person" SET "name" = 'Mosky Liu' WHERE "person_id" = 'mosky'
25
Operator also works!
26
Operator also works!
select('person', { 'age >=': 20})
26
Operator also works!
select('person', { 'age >=': 20})
-> SELECT * FROM "person" WHERE "age" >= 20
26
All fromthe native data structures!
The Overview
28
The Overview
insert(table, set, ...)
28
The Overview
insert(table, set, ...)
select(table, where, ...)
28
The Overview
insert(table, set, ...)
select(table, where, ...)
update(table, where, set, ...)
28
The Overview
insert(table, set, ...)
select(table, where, ...)
update(table, where, set, ...)
delete(table, where, ...)
28
The Overview
insert(table, set, ...)
select(table, where, ...)
update(table, where, set, ...)
delete(table, where, ...)
...
28
If you like it,
sudo pip install mosql
Join is also available
31
Join is also available
select( 'person', {'person_id': 'mosky'}, joins=left_join('detail',using=('person_id',)))
31
Join is also available
select( 'person', {'person_id': 'mosky'}, joins=left_join('detail',using=('person_id',)))
-> SELECT * FROM "person" LEFT JOIN "detail" USING ("person_id") WHERE "person_id" = 'mosky'
31
A Partial Query
32
A Partial Query
fixed_args = {'table': 'person'}person_select = select.breed(fixed_args)person_select()
32
A Partial Query
fixed_args = {'table': 'person'}person_select = select.breed(fixed_args)person_select()
-> SELECT * FROM "person"
32
A Partial Query
fixed_args = {'table': 'person'}person_select = select.breed(fixed_args)person_select()
-> SELECT * FROM "person"
select('person')
32
A Partial Query
fixed_args = {'table': 'person'}person_select = select.breed(fixed_args)person_select()
-> SELECT * FROM "person"
select('person')
-> SELECT * FROM "person"
32
Performance
33
Performance
About 4x faster than SQLAlchemy.
33
Performance
About 4x faster than SQLAlchemy.
Just a little bit slower than pure SQL.
33
Security
34
Security
Security by default.
34
Security
Security by default.
Use escaping technique.
34
Security
Security by default.
Use escaping technique.
Prevent SQL injection from both value and identifier.
34
Security
Security by default.
Use escaping technique.
Prevent SQL injection from both value and identifier.
Passed the tests from sqlmap at level=5 and risk=3.
34
SQL vs. ORMSQL ORM
Easy-to-Use V
Secure V
Easy-to-Learn V
Fast V
SQL < ______ < ORMSQL ORM
Easy-to-Use V
Secure V
Easy-to-Learn V
Fast V
SQL < MoSQL < ORMSQL MoSQL ORM
Easy-to-Use V V
Secure V V
Easy-to-Learn V V
Fast V V
Demo
Demo
39
Demo
Arbitrary Query with Web
39
Demo
Arbitrary Query with Web
Serious Usage using Class
39
Demo
Arbitrary Query with Web
Serious Usage using Class
All the code are in the Github!
39
The End
The End
41
The EndMoSQL is ...
41
The EndMoSQL is ...
Easy-to-Use
41
The EndMoSQL is ...
Easy-to-Use
Easy-to-Learn
41
The EndMoSQL is ...
Easy-to-Use
Easy-to-Learn
Secure
41
The EndMoSQL is ...
Easy-to-Use
Easy-to-Learn
Secure
Fast
41
The EndMoSQL is ...
Easy-to-Use
Easy-to-Learn
Secure
Fast
sudo pip install mosql
41
The EndMoSQL is ...
Easy-to-Use
Easy-to-Learn
Secure
Fast
sudo pip install mosql
http://mosql.mosky.tw/
41