Main Content

SQLAlchemy - first examples with a Python Object Relationship Mapping system

Archive - Originally posted on "The Horse's Mouth" - 2015-10-14 07:42:16 - Graham Ellis

Object to Relational Database mapping - in other words how to you take objects in your language such as Python and save them in database tables, and vice versa, without having to do all the detailed SQL coding yourself? Use on ORM system. An example in Python is SQLalchemy which connects to a variety of databases - and we have a couple of "demos" of it in action [here] and [here].

Here's sample output from the second of those - not a line of SQL in sight!

  WomanWithCat:flask grahamellis$ python alch002
  users
  <User(name='ed', fullname='Ed Jones', password='edspassword')>
  IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])
  IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>, <User(name='mary', fullname='Mary Contrary', password='xxg527')>, <User(name='fred', fullname='Fred Flinstone', password='blah')>])
  1. -----------------------
  IdentitySet([])
  IdentitySet([])
  2. -----------------------
  [<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]
  1. ======================================
  (u'ed', u'Ed Jones')
  (u'wendy', u'Wendy Williams')
  (u'mary', u'Mary Contrary')
  (u'fred', u'Fred Flinstone')
  (<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>, u'ed')
  (<User(name='wendy', fullname='Wendy Williams', password='foobar')>, u'wendy')
  (<User(name='mary', fullname='Mary Contrary', password='xxg527')>, u'mary')
  (<User(name='fred', fullname='Fred Flinstone', password='blah')>, u'fred')
  2. ======================================
  <User(name='wendy', fullname='Wendy Williams', password='foobar')>
  <User(name='mary', fullname='Mary Contrary', password='xxg527')>
  3. ======================================
  <User(name='ed', fullname='Ed Jones',password='f8s7ccs')>
  WomanWithCat:flask grahamellis$

But if we run it with echo on, you can see the underlying SQL - here's the end of the output with that switch thrown

  3. ======================================
  2015-10-14 07:37:47,723 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
  FROM users
  WHERE users.name = ? AND users.fullname = ?
  2015-10-14 07:37:47,723 INFO sqlalchemy.engine.base.Engine ('ed', 'Ed Jones')
  <User(name='ed', fullname='Ed Jones', password='f8s7ccs')>

There's a very great deal to this ORM, including the echoing back to the database of changes made in memory (commit and rollback) and a whole extra library of functions to perform a wide range of tasks. The examples here are just "get you started" in the concepts!