Note: This tutorial is based off the official SQLAlchemy ORM tutorial.
In [31]:
import sqlalchemy
# Connect to the database...
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)
The ORM utilizes classes inheriting from the SQLAlchemy base class.
In [32]:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Classes have RDBMS table attributes:
In [33]:
from sqlalchemy import Column, Integer, String
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
# Defines to_string() representation
def __repr__(self):
return "<User(name='%s', fullname='%s', password='%s')>" % (
self.name, self.fullname, self.password)
SQLAlchemy will set up your tables for you...
In [34]:
Base.metadata.create_all(engine)
In [35]:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
Once you have your tables create you can then start creating objects.
In [36]:
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)
ed_user
Out[36]:
When you do a query SQLAlchemy, your uncommited changes are flushed to the db (within a transaction). SQLAlchemy then requeries the database in order to gain the value of the objects primary key column.
In [37]:
our_user = session.query(User).filter_by(name='ed').first()
Since both of end_user and our_user have the same primary key the are mapped to the same object.
In [38]:
ed_user is our_user
Out[38]:
Multiple objects can be added to the session and objects can be modified.
In [39]:
session.add_all([
User(name='wendy', fullname='Wendy Williams', password='foobar'),
User(name='mary', fullname='Mary Contrary', password='xxg527'),
User(name='fred', fullname='Fred Flinstone', password='blah')])
ed_user.password = 'f8s7ccs'
Finally, the transaction can be commited. The remaining changes are flushed to the database.
In [40]:
session.commit()
Flush remaining changes (inserts and updates)
UPDATE users SET password='f8s7ccs' WHERE users.id = 1
INSERT INTO users (name, fullname, password) VALUES ('wendy', 'Wendy Williams', 'foobar')
INSERT INTO users (name, fullname, password) VALUES ('mary', 'Mary Contrary', 'xxg527')
INSERT INTO users (name, fullname, password) VALUES (fred', 'Fred Flinstone', 'blah')
Finally, commit the changes
COMMIT
In SQLAlchemy all database operations are performed within a transaction.
For example we can edit and query for objects:
In [41]:
ed_user.name = 'Edwardo'
fake_user = User(name='fakeuser', fullname='Invalid', password='12345')
session.add(fake_user)
session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()
Out[41]:
We can do a rollback and our changes are reset.
In [42]:
session.rollback()
Our objects are reset to their pervious states and our uncommited objects are removed.
In [43]:
ed_user.name
Out[43]:
In [44]:
fake_user in session
Out[44]:
In [45]:
for instance in session.query(User).order_by(User.id):
print(instance)
In [46]:
for name, fullname in session.query(User.name, User.fullname):
print(name, fullname)
Here’s an overview of some common operators used by the filter() fucntion:
In [47]:
for user in session.query(User).filter(User.name == 'ed'):
print(user)
In [48]:
for user in session.query(User).filter(User.name != 'ed'):
print(user)
In [49]:
for user in session.query(User).filter(User.name.like('%ed%')):
print(user)
In [50]:
for user in session.query(User).filter(User.name.in_(['ed', 'wendy', 'jack'])):
print(user)
In [51]:
for user in session.query(User).filter(~User.name.in_(['ed', 'wendy', 'jack'])):
print(user)
In [52]:
for user in session.query(User).filter(User.name == 'ed', User.fullname == 'Ed Jones'):
print(user)
In [53]:
from sqlalchemy import or_
for user in session.query(User).filter(or_(User.name == 'ed', User.name == 'wendy')):
print(user)
Multiple classes can be created and linked together using class relations.
In [54]:
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User", back_populates="addresses")
def __repr__(self):
return "<Address(email_address='%s')>" % self.email_address
Once primary and foreign keys are established, classes can be connected together via relationships.
In [55]:
User.addresses = relationship("Address", order_by=Address.id, back_populates="user")
Base.metadata.create_all(engine) # Flush schema changes to the DBMS.
In the above mappings:
user.addresses maps to a user's list of address objects.address.user maps to an address's parent user object.We create a "Jack" object and it initially has no addresses.
In [56]:
jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
jack.addresses
Out[56]:
We then add some address objects.
In [57]:
jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]
In [58]:
jack.addresses[1]
Out[58]:
We see that the "Jack" object's first address's user parameter refers back to the "Jack" object.
In [59]:
jack.addresses[1].user
Out[59]:
When we add "Jack" to the session and commit, the two address objects of "Jack" are also committed in a cascading fashion.
In [60]:
session.add(jack)
session.commit()
jack = session.query(User).filter_by(name='jack').one()
jack.addresses
Out[60]: