DeeKras.com

Basic SQLAlchemy

Sat 20 December 2014

Filed under Python

First, why SQLAlchemy is powerful.

There's a whole set of features of SQLAlchemy listed in their docs. I have found that using SQLAlchemy instead of SQLlite has made it much easier to access the data, since data is saved as a class. And writing methods and queries on that data is simpler than straight up SQL.

And now to jump right in...

The imports:

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

To set things up:

#at the global level

db = 'sqlite:///membership.db'  #your database

engine = create_engine(db) 
Base = declarative_base()

Session = sessionmaker(bind=engine)
session = Session() 

#create tables

Base.metadata.create_all(engine)

Some explanations:

These are all set at the global level.

db = 'sqlite:///membership.db'

  • naming the database and where it resides
  • you can name it anything. db is good too.

engine = create_engine(db)

  • the engine is the 'home base' for the database - it contains the path to the database and some information about the dialect (there are other arguments too).
  • create_engine() creates a new Engine instance called engine.
  • you can name it anything. engine is convention.
  • (Much more about engines)

Base = declarative_base()

  • the declarative creates a new base class which all classes based on it will inherit. (See below when we create the Member class.)
  • you can name it anything. Base is convention.
  • (Much more about declaratives)

Session = sessionmaker(bind=engine)

  • the session is a 'holding zone' for all the conversations between the program and the database.
  • sessionmaker(bind=engine) creates the Session object. And binds it to the engine; in this way the session knows how to connect to the database it is working with.
  • you can name it anything. Session is convention.
  • (Much more about sessions)

session = Session()

  • create the 'session'
  • you can name it anything. *NOTE if you are also working with Flask which has a session object, things may get confusing; name this something else.

Base.metadata.create_all(engine)

  • after the tables are created, still need to create it in the engine
  • we are creating the tables in the 'engine' and linking it to be saved as the metadata of the Base class. -(metadata is the structure of the data - ie: the fields and types )

Here's a little example using the very basics of SQLAlchemy.

A quick background to what this little program is about: This program is a webform. (At this point, using regular Flask forms. Will refactor to use WTForms.)

  • A new user inquires about membership.

    • She fills in some basic info about herself.
    • The member_id is automatically set. [This will be done with the autoincrement. See below.]
    • The inquiry_date is set to current date/ time. [This will be done with the default set to current time. See below.]

  • The inquirer becomes a member (activate).

    • She adds some more info (password, etc.).
    • The active_date is set to current date/ time. [This will be done in the activate method. See below.]
    • is_active is set to true. [This will be done in the activate method. See below.]

  • The active member can unsubscribe (deactivate).

    • The inactive_date is set to current date/ time. [This will be done in the deactivate method. See below.]
    • is_active is set to false. [This will be done in the deactivate method. See below.]

So now, let's set up the Class:

class Member(Base):
    __tablename__ = 'members'

    member_id = Column(Integer, primary_key=True, autoincrement=True)
    inquiry_date = Column(DateTime, default=datetime.now())
    active__date = Column(DateTime, nullable=True)
    inactive__date = Column(DateTime, nullable=True)
    last_name = Column(String)
    first_name = Column(String)
    email_address = Column(String)
    password = Column(String)
    is_active = Column(Boolean, default=False)

The Class

  • The class is based on the Base object declared above.
  • It must contain the __tablename__.
  • And at least one column that is the primary key, in this example, it is member_id.
    • About primary_key: A primary key is unique. That is no other row in that column has the same data. It is the field that identifies the row as unique. In this example, each member has a unique member_id.

The Fields

  • The syntax is: field_name = Column(args, kwargs)
  • There are several different data types (ex: DateTime, String, Boolean, Float, Integer, Text and others. Must import each type that will be used, as we did above.)
  • (Much more about columns and data types)
  • There are several args that can be used (ex: default, autoincrement, index, nullable and others)
  • Much more about the rest of the args

And now to add some methods to the Member class:

    def add_to_db(self):
        session.add(self)  

    def activate(self):
        self.active_date = datetime.now()
        self.is_active = True

    def deactivate(self):
        self.inactive_date = datetime.now()
        self.is_active = False

session.add(self):

  • This method will (attempt to) create a new row in the database and add the data to that row. The primary_key must be unique. In this case, it is unique, since we are using autoincrement to create the member_id.
  • We can use add_all([list of new rows]) to create several rows at once.)

VERY IMPORTANT! Later when we actually call any of these methods that make changes (additions, changes or deletions) to the database, we will also have to commit() it, or the data will not be saved. session.commit().

So for example, the user is on the 'I'm interested' page and enters her last name, first name and email address and then hits 'submit'. The function might be something like this:

def add_inquiry():

    last_name = request.form['last_name']
    first_name = request.form['first_name']
    email_address = request.form['email_address']

    Member(last_name=last_name, 
           first_name=first_name, 
           email_address=email_address).add_to_db()

    session.commit()

Explanations

This is when the user comes to the website for the first time and is interested. She enters her last name, first name and email address and then 'submit'.

When 'submit' is hit, the fun begins. - the data is collected from the form (request.form['field']) - an instance of the Member class is created with these fields. - AND the member_id is created; remember it was set as autoincrement. - AND the inquiry_date is set to now(); that is its default. - the add_to_db() method is called. This will add all these fields as a new row in the table. - LASTLY and IMPORTANTLY, this row which is sitting in the 'holding zone' of the session gets a commit(). If the session is not committed, the data will sit there until it is committed or lost when a new session begins.

Using queries

It is interesting to see your data. For that we use queries. Of course, there are lots of other uses for queries. Here is a super simple query:

def display_members():
    members = session.query(Member).all()
    for member in members:
        print member.email_address, member.is_active, member.last_name, 
                member.active_date, member.cell_phone

The basic syntax for a query in SQLAlchemy is: session.query(whatever you are searching) *This will need a 'return results' argument at the end. See below.

  • session is the same 'session' that we declared at the very beginning.

  • You can search through a class session.query(Member).

  • Or you can list a few fields session.query(Member.last_name, Member.first_name).

There are many different arguments for the query.

Return results

At the very end - which indicates how many results to return - you can use:

  • .all()

    • returns all results. As a list of tuples.

  • .first()

    • returns the first result. As a tuple.
    • if there were no results, returns None.

  • .limit(limit)

    • returns the 'limit' number of rows. As a list of tuples.
    • ex: limit(3) will return 3

  • .one()

    • returns exactly one result. As a tuple.
    • if there are more results than 1, raises MultipleResultsFound
    • if there were no results, raises NoResultFound

  • .scalar()

    • returns the first column of the first result ex: session.query(Item.id, Item.name).scalar() will return just the value of Item.id (the first column)
    • if there are more results than 1, raises MultipleResultsFound
    • if there were no results, returns None

Other more common arguments are:

  • .filter() or .filter_by()

    • criterion to filter results
    • filter uses SQL expressions and is more pythonic. So uses == (double equal). ex: .filter(Member.last_name == 'Silver')
    • filter_by uses keyword expressions. So uses = (single equal). ex: .filter_by(Member.last_name = 'Silver')
    • use a comma to add more criteria, ex: .filter(Member.last_name == 'Silver', Member.first_name = 'Mark')

  • .order_by()

    • the results will be sorted in the order specified. ex: .order_by(Member.active_date) will be sorted by active_date

  • .group_by

    • the results will be grouped by the criterion. returns a set, based on the columns indicated. ex: .group_by(Member.active_date) will group by active_date and return one row for each active date in the table, even if there are many of the same active date in the table

  • .count()

    • returns a count (the number) of rows this query would return

  • And then there are a whole set of different types of 'joins' for working with 2 tables that have a relationship. Will explore these in a separate post about relationships between tables.

Another method to look at (uses a simple query)

``` def deactivate(): email_address = request.form['email_address'] 

member = session.query(Member).\
                     filter_by(Member.email_address=email_address).\
                     One()

member.deactivate()
session.commit()

Explanations

This is when the user wants to unsubscribe. She enters her email and clicks 'unsubscribe'.

It all happens when 'unsubscribe' is clicked. - get the email address - the query then searches for the ONE record where Member.email_address is equal to the email address from the form. - and then calls the deactivate method from the Member class. - and finally commits it. If the session is not committed, the data will sit there until it is committed or lost when a new session begins.

That's the very very basic. It gets much more interesting and useful, when we start adding relationships between the tables. And making the queries a bit more complex. More next time.

Some very basics about how it works.

Some very important points here

Comments

DeeKras.com © Dee Kras Powered by Pelican and Twitter Bootstrap. Icons by Font Awesome and Font Awesome More