MoSQL: More than SQL, but Less than ORM @ PyCon APAC 2013

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

1. More than SQL, but Less than ORM MoSQL (after v0.6)
2. Mosky 2
3. Mosky I'm working at Pinkoi 2
4. Mosky I'm working at Pinkoi COSCUP staff 2
5. Mosky I'm working at Pinkoi COSCUP staff Python trainer 2
6. Mosky I'm working at Pinkoi COSCUP staff Python trainer Speaker at COSCUP 2013, PyCon TW 2013, PyCon JP 2012, PyCon TW 2012 ... 2
7. Mosky I'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
8. Pinkoi.com   Builds  Design  Ecosystem for  people  to  BUY  /  SELL  /  SHARE  designs  and  to  be  INSPIRED.
9. Pinkoi.com   Builds  Design  Ecosystem Pinkoi  ͸ΞδΞͰ࠷΋େ͖͍σβΠϯγϣοϐϯά΢Σϒ αΠτͰ͢ɻ༏लͳσβΠφʔୡ͕͓٬͞ΜͷͨΊʹ͍ͭ ΋PinkoiͰҰ൪৽͍͠σβΠϯΛఏڙ͍ͯ͠·͢ɻૣΊʹ ͋ͳͨୡʹձ͍͍ͨͰ͢Ͷɻָ͓͠Έʂ
10. Outline 5
11. Outline Why not SQL? But ... 5
12. Outline Why not SQL? But ... Why ORM? But ... 5
13. Outline Why not SQL? But ... Why ORM? But ... MoSQL 5
14. Outline Why not SQL? But ... Why ORM? But ... MoSQL The Usage, Performance, and Security 5
15. Outline Why not SQL? But ... Why ORM? But ... MoSQL The Usage, Performance, and Security Demo 5
16. Doc: http://mosql.mosky.tw
17. Why not SQL?
18. Hard to Use 8
19. Hard to Use SELECT * FROM article LIMIT 1; 8
20. Hard to Use SELECT * FROM article LIMIT 1; add ORDER BY created? 8
21. Hard to Use SELECT * FROM article LIMIT 1; add ORDER BY created? add OFFSET 10? 8
22. Hard to Use SELECT * FROM article LIMIT 1; add ORDER BY created? add OFFSET 10? add GROUP BY author? 8
23. Hard to Use SELECT * FROM article LIMIT 1; add ORDER BY created? add OFFSET 10? add GROUP BY author? UPDATE article WHERE title='SQL' SET title='ORM'? 8
24. Hard to Use 9
25. Hard to Use Programming Error 9
26. Hard to Use Programming Error Programming Error 9
27. Hard to Use Programming Error Programming Error Programming Error 9
28. Hard to Use Programming Error Programming Error Programming Error !@#$ 9
29. May Be Injected 10
30. May Be Injected 'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs ) 10
31. May Be Injected 'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs ) Cracker can inject from value 10
32. May Be Injected 'WHERE ' + ' AND '.join( "%s = '%s'" for k, v in inputs ) Cracker can inject from value or identifier, actually. 10
33. 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
34. It seems bad! But ...
35. SQL ... 12
36. SQL ... is fastest way to communicate with db, 12
37. SQL ... is fastest way to communicate with db, and everyone understands or learned it. 12
38. Why ORM?
39. Easy to Use 14
40. Easy to Use class Person(Base): __tablename__ = 'person' person_id = Column(String, primary_key=True) name = Column(String) ... 14
41. Easy to Use 15
42. Easy to Use mosky = Person('mosky', 'Mosky Liu') session.add(mosky) 15
43. 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
44. 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
45. SQL Injection Free 16
46. SQL Injection Free Usually ORM guarantees it. 16
47. It seems good! But ...
48. ORM ... 18
49. ORM ... is slower, 18
50. ORM ... is slower, and you need to learn it from scratch. 18
51. ORM ... is slower, and you need to learn it from scratch. Sometimes it is just a black box. 18
52. SQL vs. ORM SQL ORM Easy-to-Use V Secure V Easy-to-Learn V Fast V
53. So ... MoSQL
54. The First Glance 21
55. The First Glance from mosql.query import select print select('person') 21
56. The First Glance from mosql.query import select print select('person') -> SELECT * FROM "person" 21
57. Map is just condition 22
58. Map is just condition select('person', { 'person_id': 'mosky' }) 22
59. Map is just condition select('person', { 'person_id': 'mosky' }) -> SELECT * FROM "person" WHERE "person_id" = 'mosky' 22
60. Sequence is just a list 23
61. Sequence is just a list select('person', select=('name', ) ) 23
62. Sequence is just a list select('person', select=('name', ) ) -> SELECT "name" FROM "person" 23
63. Map is also a set-list 24
64. Map is also a set-list insert('person', { 'person_id': 'mosky', 'name' : 'Mosky Liu' }) 24
65. 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
66. Order doesn't matter 25
67. Order doesn't matter update('person', where={'person_id': 'mosky'}, set ={'name' : 'Mosky Liu'}, }) 25
68. 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
69. Operator also works! 26
70. Operator also works! select('person', { 'age >=': 20 }) 26
71. Operator also works! select('person', { 'age >=': 20 }) -> SELECT * FROM "person" WHERE "age" >= 20 26
72. All from the native data structures!
73. The Overview 28
74. The Overview insert(table, set, ...) 28
75. The Overview insert(table, set, ...) select(table, where, ...) 28
76. The Overview insert(table, set, ...) select(table, where, ...) update(table, where, set, ...) 28
77. The Overview insert(table, set, ...) select(table, where, ...) update(table, where, set, ...) delete(table, where, ...) 28
78. The Overview insert(table, set, ...) select(table, where, ...) update(table, where, set, ...) delete(table, where, ...) ... 28
79. If you like it,
80. sudo pip install mosql
81. Join is also available 31
82. Join is also available select( 'person', {'person_id': 'mosky'}, joins=left_join('detail',using=('person_id',)) ) 31
83. 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
84. A Partial Query 32
85. A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args) person_select() 32
86. A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args) person_select() -> SELECT * FROM "person" 32
87. A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args) person_select() -> SELECT * FROM "person" select('person') 32
88. A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args) person_select() -> SELECT * FROM "person" select('person') -> SELECT * FROM "person" 32
89. Performance 33
90. Performance About 4x faster than SQLAlchemy. 33
91. Performance About 4x faster than SQLAlchemy. Just a little bit slower than pure SQL. 33
92. Security 34
93. Security Security by default. 34
94. Security Security by default. Use escaping technique. 34
95. Security Security by default. Use escaping technique. Prevent SQL injection from both value and identifier. 34
96. 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
97. SQL vs. ORM SQL ORM Easy-to-Use V Secure V Easy-to-Learn V Fast V
98. SQL < ______ < ORM SQL ORM Easy-to-Use V Secure V Easy-to-Learn V Fast V
99. SQL < MoSQL < ORM SQL MoSQL ORM Easy-to-Use V V Secure V V Easy-to-Learn V V Fast V V
100. Demo
101. Demo 39
102. Demo Arbitrary Query with Web 39
103. Demo Arbitrary Query with Web Serious Usage using Class 39
104. Demo Arbitrary Query with Web Serious Usage using Class All the code are in the Github! 39
105. The End
106. The End 41
107. The End MoSQL is ... 41
108. The End MoSQL is ... Easy-to-Use 41
109. The End MoSQL is ... Easy-to-Use Easy-to-Learn 41
110. The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure 41
111. The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure Fast 41
112. The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure Fast sudo pip install mosql 41
113. The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure Fast sudo pip install mosql http://mosql.mosky.tw/ 41