Object Relational Mapping

Previous blog here.

Object Relational Mappers (ORM) are frameworks that bridges the gap between our classes in our object-oriented program and a relational database. The ORM will take the classes and create a database and tables based upon these. This means the programmer will not need to write their own SQL code for every database operation they want their program to do, hopefully leading to less bugs and a cleaner code.

Examples of ORMs are Hibernate (Java), Sequelize (node.js), Entity (C#) and SQLAlchemy (Python) which as I’m programming my backend in Python is what I will be using. I’m looking forward to the ORM will make my life easier and doing some heavy lifting for me.

To begin with as I already know some SQL and I first wanted to check my connection to the database and my ability to write to it. Using the PyMySQL docs I came up with this:

def add_venue(new_venue):
  connection = pymysql.connect(host=os.environ['DB_URL'],
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD'],
        db='innodb',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)
try:
  with connection.cursor() as cursor:
    sql = "INSERT INTO `venues` (`name`, `lat`, `lng`, `placeId`) VALUES (%s, %s, %s, %s)"
    cursor.execute(sql, (new_venue['name'], new_venue['lat'], new_venue['lng'], new_venue['placeId'] ))
    connection.commit()
  finally:
    connection.close()

Good news it worked! However writing out all the SQL statements for all my database operations was going to prove tiresome and open to errors and time wasted bug hunting. I persevered with this for a while but was starting to run into problems with type conversions and there was more work once a venue was saved to then get it’s id and update the object with that id. As I always planned to move to SQLAlchemy I thought I should just get on with it.

So I moved on and began to implement it in my program, again after consulting the docs I was able to create the class and tie it into SQLAlchemy’s db.Model.

class Venue(db.Model):
  __tablename__ = 'venues'
  placeId = db.Column(db.String(45), primary_key=True)
  name = db.Column(db.String(100), index=True)
  lat = db.Column(db.Float)
  lng = db.Column(db.Float)

Then once I have created the Venue object I can just:

db.session.add(venue)
db.session.commit()

That is all the code needed for me to save a new database entry and not having to write SQL. To return all the venues all I need to do is:

Venue.query.all()

All the venues will then be returned as Venue instances with no need to create them yourself from the data returned from the database. Ideal!

So far I have to say I’m a big fan of SQLAlchemy and it allows me to crack on with the to-do list and not have to worry about the persistence of my application.

Next up I learn about asynchronous tasks and Celery.