notebook.community

Edit and run

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





In [2]:



    


from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=True)

Declare a Mapping





In [3]:



    


from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()



    











In [4]:



    


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



    


User.__table__



    


















    
Out[5]:








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



    


Base.metadata.create_all(engine)



    


















    






2015-05-30 00:33:04,334 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1










    






INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1










    






2015-05-30 00:33:04,336 INFO sqlalchemy.engine.base.Engine ()










    






INFO:sqlalchemy.engine.base.Engine:()










    






2015-05-30 00:33:04,341 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1










    






INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1










    






2015-05-30 00:33:04,343 INFO sqlalchemy.engine.base.Engine ()










    






INFO:sqlalchemy.engine.base.Engine:()










    






2015-05-30 00:33:04,348 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")










    






INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("users")










    






2015-05-30 00:33:04,350 INFO sqlalchemy.engine.base.Engine ()










    






INFO:sqlalchemy.engine.base.Engine:()










    






2015-05-30 00:33:04,356 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	fullname VARCHAR, 
	password VARCHAR, 
	PRIMARY KEY (id)
)












    






INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE users (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	fullname VARCHAR, 
	password VARCHAR, 
	PRIMARY KEY (id)
)












    






2015-05-30 00:33:04,359 INFO sqlalchemy.engine.base.Engine ()










    






INFO:sqlalchemy.engine.base.Engine:()










    






2015-05-30 00:33:04,362 INFO sqlalchemy.engine.base.Engine COMMIT










    






INFO:sqlalchemy.engine.base.Engine:COMMIT

Create an Instance of the Mapped Class





In [7]:



    


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



    











In [8]:



    


ed_user.name



    


















    
Out[8]:








'ed'

















In [9]:



    


ed_user.password



    


















    
Out[9]:








'edspassword'

















In [10]:



    


str(ed_user.id)



    


















    
Out[10]:








'None'

Creating a Session





In [11]:



    


from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)



    











In [12]:



    


session = Session()

Adding New Objects





In [13]:



    


session.add(ed_user)



    











In [14]:



    


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



    


















    






2015-05-30 00:33:12,589 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)










    






INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)










    






2015-05-30 00:33:12,592 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)










    






INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)










    






2015-05-30 00:33:12,595 INFO sqlalchemy.engine.base.Engine ('ed', 'Ed Jones', 'edspassword')










    






INFO:sqlalchemy.engine.base.Engine:('ed', 'Ed Jones', 'edspassword')










    






2015-05-30 00:33:12,599 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?
 LIMIT ? OFFSET ?










    






INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?
 LIMIT ? OFFSET ?










    






2015-05-30 00:33:12,601 INFO sqlalchemy.engine.base.Engine ('ed', 1, 0)










    






INFO:sqlalchemy.engine.base.Engine:('ed', 1, 0)

















In [15]:



    


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



    


















    
Out[15]:








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

















In [16]:



    


ed_user is our_user
#True



    


















    
Out[16]:








True

















In [17]:



    


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



    


ed_user.password = 'f8s7ccs'



    











In [19]:



    


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



    


















    
Out[19]:








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

















In [20]:



    


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








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



    


session.commit()



    


















    






2015-05-30 00:33:16,074 INFO sqlalchemy.engine.base.Engine UPDATE users SET password=? WHERE users.id = ?










    






INFO:sqlalchemy.engine.base.Engine:UPDATE users SET password=? WHERE users.id = ?










    






2015-05-30 00:33:16,076 INFO sqlalchemy.engine.base.Engine ('f8s7ccs', 1)










    






INFO:sqlalchemy.engine.base.Engine:('f8s7ccs', 1)










    






2015-05-30 00:33:16,078 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)










    






INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)










    






2015-05-30 00:33:16,080 INFO sqlalchemy.engine.base.Engine ('wendy', 'Wendy Williams', 'foobar')










    






INFO:sqlalchemy.engine.base.Engine:('wendy', 'Wendy Williams', 'foobar')










    






2015-05-30 00:33:16,082 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)










    






INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)










    






2015-05-30 00:33:16,084 INFO sqlalchemy.engine.base.Engine ('mary', 'Mary Contrary', 'xxg527')










    






INFO:sqlalchemy.engine.base.Engine:('mary', 'Mary Contrary', 'xxg527')










    






2015-05-30 00:33:16,086 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)










    






INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)










    






2015-05-30 00:33:16,088 INFO sqlalchemy.engine.base.Engine ('fred', 'Fred Flinstone', 'blah')










    






INFO:sqlalchemy.engine.base.Engine:('fred', 'Fred Flinstone', 'blah')










    






2015-05-30 00:33:16,090 INFO sqlalchemy.engine.base.Engine COMMIT










    






INFO:sqlalchemy.engine.base.Engine:COMMIT

















In [22]:



    


ed_user.id



    


















    






2015-05-30 00:33:16,364 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)










    






INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)










    






2015-05-30 00:33:16,368 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?










    






INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?










    






2015-05-30 00:33:16,370 INFO sqlalchemy.engine.base.Engine (1,)










    






INFO:sqlalchemy.engine.base.Engine:(1,)










    
Out[22]:








1

Content source: sbellem/docker-tutorial

Similar notebooks:

notebook.community | gallery | about