Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Mosky Liu
September 15, 2013

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/

Mosky Liu

September 15, 2013
Tweet

More Decks by Mosky Liu

Other Decks in Programming

Transcript

  1. Mosky I'm working at Pinkoi COSCUP staff Python trainer Speaker

    at COSCUP 2013, PyCon TW 2013, PyCon JP 2012, PyCon TW 2012 ... 2
  2. 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
  3. Pinkoi.com   Builds  Design  Ecosystem for  people  to  BUY  /

     SELL  /  SHARE  designs  and  to  be  INSPIRED.
  4. Outline Why not SQL? But ... Why ORM? But ...

    MoSQL The Usage, Performance, and Security 5
  5. Outline Why not SQL? But ... Why ORM? But ...

    MoSQL The Usage, Performance, and Security Demo 5
  6. Hard to Use SELECT * FROM article LIMIT 1; add

    ORDER BY created? add OFFSET 10? 8
  7. Hard to Use SELECT * FROM article LIMIT 1; add

    ORDER BY created? add OFFSET 10? add GROUP BY author? 8
  8. 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
  9. May Be Injected 'WHERE ' + ' AND '.join( "%s

    = '%s'" for k, v in inputs ) 10
  10. May Be Injected 'WHERE ' + ' AND '.join( "%s

    = '%s'" for k, v in inputs ) Cracker can inject from value 10
  11. May Be Injected 'WHERE ' + ' AND '.join( "%s

    = '%s'" for k, v in inputs ) Cracker can inject from value or identifier, actually. 10
  12. 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
  13. SQL ... is fastest way to communicate with db, and

    everyone understands or learned it. 12
  14. Easy to Use class Person(Base): __tablename__ = 'person' person_id =

    Column(String, primary_key=True) name = Column(String) ... 14
  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
  16. 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
  17. ORM ... is slower, and you need to learn it

    from scratch. Sometimes it is just a black box. 18
  18. Map is just condition select('person', { 'person_id': 'mosky' }) ->

    SELECT * FROM "person" WHERE "person_id" = 'mosky' 22
  19. 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
  20. 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
  21. Operator also works! select('person', { 'age >=': 20 }) ->

    SELECT * FROM "person" WHERE "age" >= 20 26
  22. 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
  23. A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args)

    person_select() -> SELECT * FROM "person" select('person') 32
  24. A Partial Query fixed_args = {'table': 'person'} person_select = select.breed(fixed_args)

    person_select() -> SELECT * FROM "person" select('person') -> SELECT * FROM "person" 32
  25. 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
  26. SQL < MoSQL < ORM SQL MoSQL ORM Easy-to-Use V

    V Secure V V Easy-to-Learn V V Fast V V
  27. The End MoSQL is ... Easy-to-Use Easy-to-Learn Secure Fast sudo

    pip install mosql http://mosql.mosky.tw/ 41