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]:





'1.0.4'

Connecting

First, get the host and port for postgres.



In [2]:

    
import os

os.environ









    Out[2]:





{'TERM': 'xterm-color', 'JPY_PARENT_PID': '5', 'HOSTNAME': 'cc7fd8e83052', 'CLICOLOR': '1', 'PWD': '/notebooks', 'GIT_PAGER': 'cat', 'PATH': '/opt/conda/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin', 'HOME': '/', 'PAGER': 'cat'}



In [7]:

    
# set these according to docker container which runs postgres
pg_host = '192.168.59.103'
pg_port = '32768'



In [8]:

    
from sqlalchemy import create_engine
engine = create_engine(
    'postgresql://postgres@{}:{}/postgres'.format(pg_host, pg_port))

Declare a Mapping



In [9]:

    
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()



In [10]:

    
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 [11]:

    
User.__table__









    Out[11]:





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 [12]:

    
Base.metadata.create_all(engine)

Create an Instance of the Mapped Class



In [13]:

    
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')



In [14]:

    
ed_user.name









    Out[14]:





'ed'



In [15]:

    
ed_user.password









    Out[15]:





'edspassword'



In [16]:

    
str(ed_user.id)









    Out[16]:





'None'

Creating a Session



In [17]:

    
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)



In [18]:

    
session = Session()

Adding New Objects



In [19]:

    
session.add(ed_user)



In [20]:

    
our_user = session.query(User).filter_by(name='ed').first()



In [21]:

    
our_user
#<User(name='ed', fullname='Ed Jones', password='edspassword')>









    Out[21]:





<User(name='ed', fullname='Ed Jones', password='edspassword')>



In [22]:

    
ed_user is our_user
#True









    Out[22]:





True



In [23]:

    
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 [24]:

    
ed_user.password = 'f8s7ccs'



In [25]:

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









    Out[25]:





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



In [26]:

    
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[26]:





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



In [27]:

    
session.commit()



In [28]:

    
ed_user.id









    Out[28]:





1