Basic SQLAlchemy
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 calledengine
.- 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 theengine
; 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 theBase
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 theautoincrement
. See below.] - The
inquiry_date
is set to current date/ time. [This will be done with thedefault
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 theactivate
method. See below.] is_active
is set totrue
. [This will be done in theactivate
method. See below.]
-
The active member can unsubscribe (deactivate).
- The
inactive_date
is set to current date/ time. [This will be done in thedeactivate
method. See below.] is_active
is set tofalse
. [This will be done in thedeactivate
method. See below.]
- The
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.
- About
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 usingautoincrement
to create themember_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
- returns the first column of the first result ex:
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 byactive_date
- the results will be sorted in the order specified. ex:
-
.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 byactive_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
- the results will be grouped by the criterion. returns a set, based on the columns indicated. ex:
-
.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