MoSQL: More than SQL, but Less than ORM

** Please visit https://speakerdeck.com/mosky/mosql-more-than-sql-but-less-than-orm-at-pycon-apac-2013 for the newer slide. :)

It is the slides of the talk, "MoSQL: More than SQL, but Less than ORM", at PyCon TW 2013.

About MoSQL:

MoSQL is a Python library which lets you use common Python’s data structures to build SQLs, and provides a convenient model of result set.

http://mosql.mosky.tw/

Transcript

1. 1 MoSQL MoSQL Mosky Mosky
2. 2 More than SQL, but Less than ORM More than SQL, but Less than ORM MoSQL MoSQL
3. 3 Outline Outline • Why not SQL? Why not SQL? • Why ORM? Why ORM? • MoSQL MoSQL – SQL Builders SQL Builders – Model of Result Set Model of Result Set • Conclusion Conclusion
4. 4 Why not SQL? Why not SQL?
5. 5 SQL Syntax SQL Syntax • SELECT * FROM article; SELECT * FROM article; • SELECT * FROM article LIMIT 1; SELECT * FROM article LIMIT 1; • add “ ORDER BY created ”? add “ ORDER BY created ”? • add “ OFFSET 10 ”? add “ OFFSET 10 ”? • add “ GROUP BY author ”? add “ GROUP BY author ”? • Is “ UPDATE article WHERE title='SQL' SET Is “ UPDATE article WHERE title='SQL' SET title='ORM' ” correct? title='ORM' ” correct?
6. 6 !@#$% !@#$%
7. 7 SQL Injection SQL Injection • ') or '1'='1 ') or '1'='1 • ' or true; -- ' or true; -- • ' or 1=1; -- ' or 1=1; -- • ' or 2=2; -- ' or 2=2; -- • ' or 'str'='str'; -- ' or 'str'='str'; -- • … …
8. 8 It may be hacker friendly. It may be hacker friendly.
9. 9 SQL seems ancient, but ... SQL seems ancient, but ...
10. 10 using SQL is the using SQL is the FASTEST FASTEST way. way.
11. 11 Why ORM? Why ORM?
12. 12 ORM Syntax ORM Syntax class User(Base): class User(Base): __tablename__ = 'users' __tablename__ = 'users' name = Column(String) name = Column(String) fullname = Column(String) fullname = Column(String) password = Column(String) password = Column(String)
13. 13 ORM Syntax (cont.) ORM Syntax (cont.) >>> fake_user = User('fakeuser', 'Invalid', >>> fake_user = User('fakeuser', 'Invalid', '12345') '12345') >>> session.add(fake_user) >>> session.add(fake_user) >>> for row in session.query(User, >>> for row in session.query(User, User.name).all(): User.name).all(): ... print row.User, row.name ... print row.User, row.name
14. 14 hmmm … hmmm …
15. 15 SQL Injection SQL Injection • ' or true; -- ' or true; -- • ' or 1=1; -- ' or 1=1; -- • ' or 1=1; # ' or 1=1; # • ' or 1=1; /* ' or 1=1; /* • ') or '1'='1 ') or '1'='1 • … … • Safer Safer
16. 16 It's good! It's good!
17. 17 ORM seems modern, but ... ORM seems modern, but ...
18. 18 the most of ORMs are SLOW. the most of ORMs are SLOW.
19. 19 SQL < ______ < ORM SQL < ______ < ORM
20. 20 SQL < MoSQL < ORM SQL < MoSQL < ORM
21. 21 SQL Builders SQL Builders
22. 22 SQL Builders (cont.) SQL Builders (cont.) >>> from mosql.build import * >>> from mosql.build import * >>> >>> select('pycon') select('pycon') SELECT * FROM "pycon" SELECT * FROM "pycon" >>> select('pycon', >>> select('pycon', {'id': 'mosky'} {'id': 'mosky'}) ) SELECT * FROM "pycon" WHERE "id" = 'mosky' SELECT * FROM "pycon" WHERE "id" = 'mosky'
23. 23 SQL Builders (cont.) SQL Builders (cont.) >>> insert('pycon', >>> insert('pycon', {'yr': 2013, 'id': 'masky'} {'yr': 2013, 'id': 'masky'}) ) INSERT INTO "pycon" ("id", "yr") VALUES ('masky', 2013) INSERT INTO "pycon" ("id", "yr") VALUES ('masky', 2013) >>> update('pycon', >>> update('pycon', ... ... where={'id': 'masky'} where={'id': 'masky'}, , ... ... set ={'id': 'mosky'} set ={'id': 'mosky'} ... ) ... ) UPDATE "pycon" SET "id"='mosky' WHERE "id" = 'masky' UPDATE "pycon" SET "id"='mosky' WHERE "id" = 'masky'
24. 24 SQL Builders (cont.) SQL Builders (cont.) • insert(table, insert(table, set set, …) , …) • select(table, select(table, where where, …) , …) • update(table, update(table, where where, , set set, …) , …) • delete(table, delete(table, where where, …) , …) • ... ...
25. 25 If you like it, If you like it,
26. 26 sudo pip install mosql sudo pip install mosql
27. 27 Model of Result Set Model of Result Set
28. 28 Model: Configure Connection Model: Configure Connection import psycopg2.pool import psycopg2.pool from mosql.result import Model from mosql.result import Model pool = psycopg2.pool.SimpleConnectionPool(1, 5, pool = psycopg2.pool.SimpleConnectionPool(1, 5, database='mosky') database='mosky') class PostgreSQL(Model): class PostgreSQL(Model): getconn = pool.getconn getconn = pool.getconn putconn = pool.putconn putconn = pool.putconn
29. 29 Model: Set the Name of Table Model: Set the Name of Table class Person(PostgreSQL): class Person(PostgreSQL): table = 'person' table = 'person' >>> Person.select( >>> Person.select({'person_id': 'mosky'} {'person_id': 'mosky'}) ) {'name': ['Mosky Liu'], 'person_id': ['mosky']} {'name': ['Mosky Liu'], 'person_id': ['mosky']} >>> Person.where(person_id= >>> Person.where(person_id=('andy', 'mosky') ('andy', 'mosky')) ) {'name': ['Andy Warhol', 'Mosky Liu'], 'person_id': {'name': ['Andy Warhol', 'Mosky Liu'], 'person_id': ['andy', 'mosky']} ['andy', 'mosky']}
30. 30 Model: Make Queries Model: Make Queries Person. Person.select select({'person_id': 'mosky'}) ({'person_id': 'mosky'}) Person. Person.insert insert({'person_id': 'tina'}) ({'person_id': 'tina'}) Person. Person.update update( ( where={'person_id': 'mosky'}, where={'person_id': 'mosky'}, set ={'name' : 'Yiyu Liu'} set ={'name' : 'Yiyu Liu'} ) ) Person. Person.delete delete({'person_id': 'tina'}) ({'person_id': 'tina'})
31. 31 Model: Squash Columns Model: Squash Columns class Person(PostgreSQL): class Person(PostgreSQL): table = 'person' table = 'person' squashed = set(['person_id', 'name']) squashed = set(['person_id', 'name']) >>> Person.select({'person_id': 'mosky'}) >>> Person.select({'person_id': 'mosky'}) {'name': {'name': 'Mosky Liu' 'Mosky Liu', 'person_id': , 'person_id': 'mosky' 'mosky'} } >>> Person.where(person_id=('andy', 'mosky')) >>> Person.where(person_id=('andy', 'mosky')) {'name': {'name': 'Andy Warhol' 'Andy Warhol', 'person_id': , 'person_id': 'andy' 'andy'} }
32. 32 Model: Arrange Model: Arrange class Person(PostgreSQL): class Person(PostgreSQL): ... ... arrange_by = ('person_id', ) arrange_by = ('person_id', ) >>> for person in Person.arrange( >>> for person in Person.arrange({'person_id': {'person_id': ('andy', 'mosky')} ('andy', 'mosky')}): ): ... print person ... print person {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Mosky Liu', 'person_id': 'mosky'} {'name': 'Mosky Liu', 'person_id': 'mosky'}
33. 33 Model: Arrange (cont.) Model: Arrange (cont.) >>> for detail in >>> for detail in Detail Detail.arrange({'person_id': .arrange({'person_id': ('mosky', 'andy')}): ('mosky', 'andy')}): ... print detail ... print detail ... ... {'detail_id': [5], {'detail_id': [5], 'key': 'email', 'key': 'email', 'person_id': 'andy', 'person_id': 'andy', 'val': ['andy@gmail.com']} 'val': ['andy@gmail.com']} ... ...
34. 34 Model: Find Model: Find class Person(PostgreSQL): class Person(PostgreSQL): ... ... arrange_by = ('person_id', ) arrange_by = ('person_id', ) >>> for person in Person. >>> for person in Person.find find( (person_id=('andy', person_id=('andy', 'mosky') 'mosky')): ): ... print person ... print person {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Mosky Liu', 'person_id': 'mosky'} {'name': 'Mosky Liu', 'person_id': 'mosky'}
35. 35 Model: Identify a Row Model: Identify a Row class Person(PostgreSQL): class Person(PostgreSQL): ... ... ident_by = ('person_id', ) ident_by = ('person_id', )
36. 36 Model: Modification Model: Modification >>> p = Person.where(person_id='mosky') >>> p = Person.where(person_id='mosky') >>> >>> p['name'] = 'Yiyu Liu' p['name'] = 'Yiyu Liu' >>> >>> p.name = 'Yiyu Liu' p.name = 'Yiyu Liu' >>> p.save() >>> p.save() >>> d = >>> d = Detail Detail.where( .where(person_id='mosky', key='email' person_id='mosky', key='email') ) >>> >>> p['val'][0] = '<modified email>' p['val'][0] = '<modified email>' >>> >>> p.val[0] = '<modified email>' p.val[0] = '<modified email>' >>> p.save() >>> p.save()
37. 37 Model: Pop and Append Model: Pop and Append >>> d = Detail.where( >>> d = Detail.where(person_id='mosky', key='email' person_id='mosky', key='email') ) >>> >>> p.pop(-1) p.pop(-1) >>> >>> p.append({'val': '<new mail>'}) p.append({'val': '<new mail>'}) >>> p.save() >>> p.save()
38. 38 Model: Default Clauses Model: Default Clauses class Person(PostgreSQL): class Person(PostgreSQL): ... ... clauses = dict( clauses = dict( order_by=('person_id', ) order_by=('person_id', ) ) )
39. 39 Performance Performance • About About 4x 4x faster than SQLAlchemy. faster than SQLAlchemy. • Just a little bit slower than pure SQL. Just a little bit slower than pure SQL.
40. 40 Security Security • Security by default. Security by default. • Use escaping technique. Use escaping technique. • Prevent SQL injection from both value Prevent SQL injection from both value and identifier. and identifier. • Passed the tests from Passed the tests from sqlmap sqlmap at level=5 at level=5 and risk=3. and risk=3.
41. 41 Conclusion Conclusion • Easy-to-Learn Easy-to-Learn • Convenient Convenient • Faster Faster • Secure Secure • sudo pip install mosql sudo pip install mosql • http://mosql.mosky.tw/ http://mosql.mosky.tw/ • Welcome to fork! Welcome to fork!