Object Relational Tutorial

Version Check

A quick check to verify that we are on at least version 1.0 of SQLAlchemy


In [1]:
import sqlalchemy

sqlalchemy.__version__


Out[1]:
'0.9.9'

Connecting

First, get the host and port for postgres.


In [2]:
import os

os.environ


Out[2]:
{'DB_PORT_5432_TCP_PORT': '5432', 'DB_PORT': 'tcp://172.17.0.25:5432', 'DB_1_ENV_LANG': 'en_US.utf8', 'DB_1_ENV_PG_MAJOR': '9.4', 'DB_ENV_PG_VERSION': '9.4.1-1.pgdg70+1', 'DOCKERCOMPOSE_DB_1_ENV_PG_VERSION': '9.4.1-1.pgdg70+1', 'PATH': '/opt/conda/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin', 'DB_1_ENV_PGDATA': '/var/lib/postgresql/data', 'DOCKERCOMPOSE_DB_1_PORT_5432_TCP_PORT': '5432', 'TERM': 'xterm-color', 'DOCKERCOMPOSE_DB_1_ENV_PG_MAJOR': '9.4', 'DB_ENV_PG_MAJOR': '9.4', 'DB_1_PORT_5432_TCP_ADDR': '172.17.0.25', 'DB_PORT_5432_TCP_PROTO': 'tcp', 'DOCKERCOMPOSE_DB_1_PORT_5432_TCP_PROTO': 'tcp', 'DB_ENV_LANG': 'en_US.utf8', 'HOME': '/', 'DB_1_NAME': '/dockercompose_notebook_1/db_1', 'DB_ENV_PGDATA': '/var/lib/postgresql/data', 'JPY_PARENT_PID': '6', 'DOCKERCOMPOSE_DB_1_ENV_PGDATA': '/var/lib/postgresql/data', 'DOCKERCOMPOSE_DB_1_PORT_5432_TCP': 'tcp://172.17.0.25:5432', 'DB_PORT_5432_TCP_ADDR': '172.17.0.25', 'DB_NAME': '/dockercompose_notebook_1/db', 'GIT_PAGER': 'cat', 'DOCKERCOMPOSE_DB_1_NAME': '/dockercompose_notebook_1/dockercompose_db_1', 'DB_1_PORT_5432_TCP_PROTO': 'tcp', 'DB_PORT_5432_TCP': 'tcp://172.17.0.25:5432', 'DB_1_PORT': 'tcp://172.17.0.25:5432', 'HOSTNAME': '62f31a665198', 'DB_1_PORT_5432_TCP_PORT': '5432', 'DOCKERCOMPOSE_DB_1_PORT': 'tcp://172.17.0.25:5432', 'CLICOLOR': '1', 'PWD': '/notebooks', 'DB_1_ENV_PG_VERSION': '9.4.1-1.pgdg70+1', 'DOCKERCOMPOSE_DB_1_PORT_5432_TCP_ADDR': '172.17.0.25', 'PAGER': 'cat', 'DB_1_PORT_5432_TCP': 'tcp://172.17.0.25:5432', 'DOCKERCOMPOSE_DB_1_ENV_LANG': 'en_US.utf8'}

In [3]:
host = os.environ['DB_PORT_5432_TCP_ADDR']
port = os.environ['DB_PORT_5432_TCP_PORT']

In [4]:
from sqlalchemy import create_engine
engine = create_engine(
    'postgresql://postgres@{}:{}/postgres'.format(host, port))

Declare a Mapping


In [5]:
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

In [6]:
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)

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
            self.name, self.fullname, self.password)

Create a Schema


In [7]:
User.__table__


Out[7]:
Table('users', MetaData(bind=None), Column('id', Integer(), table=<users>, primary_key=True, nullable=False), Column('name', String(), table=<users>), Column('fullname', String(), table=<users>), Column('password', String(), table=<users>), schema=None)

In [8]:
Base.metadata.create_all(engine)

Create an Instance of the Mapped Class


In [9]:
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')

In [10]:
ed_user.name


Out[10]:
'ed'

In [11]:
ed_user.password


Out[11]:
'edspassword'

In [12]:
str(ed_user.id)


Out[12]:
'None'

Creating a Session


In [13]:
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

In [14]:
session = Session()

Adding New Objects


In [15]:
session.add(ed_user)

In [16]:
our_user = session.query(User).filter_by(name='ed').first()

In [17]:
our_user
#<User(name='ed', fullname='Ed Jones', password='edspassword')>


Out[17]:
<User(name='ed', fullname='Ed Jones', password='edspassword')>

In [18]:
ed_user is our_user
#True


Out[18]:
True

In [19]:
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')])

In [20]:
ed_user.password = 'f8s7ccs'

In [21]:
session.dirty
#IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])


Out[21]:
IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])

In [22]:
session.new  
#IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>,
#<User(name='mary', fullname='Mary Contrary', password='xxg527')>,
#<User(name='fred', fullname='Fred Flinstone', password='blah')>])


Out[22]:
IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>, <User(name='mary', fullname='Mary Contrary', password='xxg527')>, <User(name='fred', fullname='Fred Flinstone', password='blah')>])

In [23]:
session.commit()

In [24]:
ed_user.id


Out[24]:
1