Getting Started: SQLAlchemy & NuoDB

In a previous article, Jacob discussed the usage of the NuoDB python driver to harness the power of NuoDB within Python. This is awesome! However, while the python driver is fully capable, for many developers, interacting with a database with native SQL (1) poses security challenges as well as (2) breaks the mold with respect to object oriented abstraction & design.

In this article we are going to talk about SQLAlchemy, a popular object relational mapper (ORM) for Python and how one could utilize it to interact with NuoDB.

I am assuming a general familiarity and comfort with Python. Here’s the agenda:

  1. Introduction to Object Relational Mappers
  2. Installation of sqlalchemy_nuodb module
  3. Code examples

Let’s get started.

Introduction

Object Relational Mappers have been around for quite some time and are quite popular irrespective of the language. They exist primarily to make a developer’s life easier by abstracting away SQL into objects that can interact with the database. In addition, ORMs promotes object oriented encapsulation of data, provides an additional layer of security to the database and, in many cases, speeds up development.

So you are a Python developer who wants to create scalable cloud solutions, maybe the next Facebook web app that will process hundreds, possibly thousands of queries a second. You need your database to scale easily with demand while also maintaining performance (low latency) and you want to use a Python ORM. That’s a lot. I’m excited to announce an excellent solution, SQLAlchemy, the most popular Python based ORM combined with NuoDB, a scalable, high performance, elastic cloud database solution.

Lets get everything setup.

Installation

Before we can install the sqlalchemy_nuodb module, the sqlalchemy module must be installed on our system.

pip install sqlalchemy

Installation of sqlalchemy_nuodb can be done as follows:

pip install sqlalchemy_nuodb

That’s it. You now have the power of NuoDB and SQLAlchemy!

Now, let’s use it in a few examples

Code Samples

First we need to import the sqlalchemy_nuodb module

import sqlalchemy_nuodb

Next we need to do is create a mapper that is an object representation of the table in our NuoDB database. Assuming you are running the NuoDB quickstart database, let’s create a mapper for the `hockey` table.

In nuosql, this is how the hockey table is described:

SQL> show hockey;

	Found table HOCKEY in schema HOCKEY

		ID integer
		NUMBER integer
		NAME string
		POSITION string
		TEAM string

		Index: HOCKEY..PRIMARY_KEY
			ID
		Index: PLAYER_IDX
			NUMBER
			NAME
			TEAM

	Domain HOCKEY not found anywhere!

	Sequence HOCKEY not found anywhere!

We can leverage SQLAlchemy to represent the above hockey table as an object in the following way.

class Hockey(Base):
    __tablename__ = 'hockey'

    id          = Column(Integer, primary_key=True)
    number      = Column(Integer)
    name        = Column(String)
    position    = Column(String)
    team        = Column(String)
    
    def __init__(self, id, number, name, position, team):
        self.id         = id
        self.number     = number
        self.name       = name
        self.position   = position
        self.team       = team

That’s it! The Hockey class now maps to the Hockey table in NuoDB. Notice that we are mapping each of the `hockey` table columns to a Hockey class attribute. We also define the table this class maps to with __tablename__

Let’s put it all together in a working example to print out all the row entries in the Hockey table

import sqlalchemy_nuodb

from sqlalchemy.orm             import sessionmaker
from sqlalchemy                 import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy                 import Column, Integer, String

# perform simple query
engine = create_engine("nuodb://dba:goalie@localhost:48004/test?schema=hockey", echo=True)
    
# do same as above declaratively
Base = declarative_base()

# define hockey object
class Hockey(Base):
    __tablename__ = 'hockey'

    id          = Column(Integer, primary_key=True)
    number      = Column(Integer)
    name        = Column(String)
    position    = Column(String)
    team        = Column(String)
    
    def __init__(self, id, number, name, position, team):
        self.id         = id
        self.number     = number
        self.name       = name
        self.position   = position
        self.team       = team

    def __repr__(self):
        return "" % (self.id, self.number, self.name, self.position, self.team)

# bind to previously created engine & create a session
Session = sessionmaker(bind=engine)
session = Session()

# print each row
for row in session.query(Hockey).all():
    print row

Let’s update the #1 Fan, demonstrated in this working example

import sqlalchemy_nuodb

from sqlalchemy.orm             import sessionmaker
from sqlalchemy                 import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy                 import Column, Integer, String

# perform simple query
engine = create_engine("nuodb://dba:goalie@localhost:48004/test?schema=hockey", echo=True)
    
# do same as above declaratively
Base = declarative_base()

# define hockey object
class Hockey(Base):
    __tablename__ = 'hockey'

    id          = Column(Integer, primary_key=True)
    number      = Column(Integer)
    name        = Column(String)
    position    = Column(String)
    team        = Column(String)
    
    def __init__(self, id, number, name, position, team):
        self.id         = id
        self.number     = number
        self.name       = name
        self.position   = position
        self.team       = team

    def __repr__(self):
        return "" % (self.id, self.number, self.name, self.position, self.team)

# bind to previously created engine & create a session
Session = sessionmaker(bind=engine)
session = Session()

# print each row
for row in session.query(Hockey).all():
    print row

hockey = session.query(Hockey).get(24)
hockey.name = 'JOHN SMITH'

session.commit()

for row in session.query(Hockey).all():
    print row

Summary

Object relational mappers make it easy for developers to interact with SQL using objects and SQLAlchemy is arguably the most popular ORM in the Python community. We are excited to release this beta for people to build out amazing, scalable applications leveraging NuoDB.

For more information about SQLAlchemy, I highly recommend checking out their site, it is well documented and has great examples for doing more complex queries.

In addition, the first working sample described in this post is available on GitHub

Happy coding!

Aki
Anonymous's picture
<p>can this adapter be used

can this adapter be used with flask-sqlalchemy? (http://pythonhosted.org/Flask-SQLAlchemy/)
 

Akos (Aki) Balogh
Co-Founder, MarketMuse
http://www.marketmuse.co

@Aki, at first look I cant'

@Aki, at first look I cant' see any reason why the python driver wouldn't work with Flask-SQLAlchemy however, we don't specifically test that Flask extension. 

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.