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

MoSQL: More than SQL, but Less than ORM

D16bc1f94b17ddc794c2dfb48ef59456?s=47 Mosky
May 25, 2013

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/

D16bc1f94b17ddc794c2dfb48ef59456?s=128

Mosky

May 25, 2013
Tweet

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!