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