In [1]:
import sqlalchemy
sqlalchemy.__version__
Out[1]:
In [25]:
from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.db', echo=False)
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(50))
fullname = Column(String(50))
password = Column(String(12))
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return '<User {}>'.format(self.fullname)
In [5]:
User.__table__
Out[5]:
In [40]:
Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)
In [7]:
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
ed_user.name
Out[7]:
In [8]:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
In [9]:
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)
In [11]:
our_user = session.query(User).filter_by(name='ed').first()
our_user
Out[11]:
In [12]:
ed_user is our_user
Out[12]:
In [13]:
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 [14]:
ed_user.password = 'f8s7ccs'
session.dirty
Out[14]:
In [15]:
session.new
Out[15]:
In [16]:
session.commit()
In [17]:
ed_user.id
Out[17]:
In [19]:
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[19]:
In [20]:
session.rollback()
ed_user.name
fake_user in session
Out[20]:
In [21]:
session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()
Out[21]:
In [26]:
for instance in session.query(User).order_by(User.id):
print(instance.name, instance.fullname)
In [27]:
for name, fullname in session.query(User.name, User.fullname):
print(name, fullname)
In [28]:
for row in session.query(User, User.name).all():
print(row.User, row.name)
In [29]:
for row in session.query(User.name.label('name_label')).all():
print(row.name_label)
In [31]:
from sqlalchemy.orm import aliased
user_alias = aliased(User, name='user_alias')
for row in session.query(user_alias, user_alias.name).all():
print(row.user_alias)
In [32]:
for u in session.query(User).order_by(User.id)[1:3]:
print(u)
In [33]:
for name, in session.query(User.name).filter_by(fullname='Ed Jones'):
print(name)
In [34]:
for name, in session.query(User.name).filter(User.fullname=='Ed Jones'):
print(name)
In [35]:
for user in session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones'):
print(user)
In [36]:
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={})>".format(self.email_address)
In [37]:
User.addresses = relationship("Address", order_by=Address.id, back_populates="user")
In [39]:
Base.metadata.create_all(engine)
In [41]:
jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
jack.addresses
Out[41]:
In [42]:
jack.addresses = [
Address(email_address='jack@google.com'),
Address(email_address='j25@yahoo.com')
]
In [43]:
jack.addresses[1]
Out[43]:
In [44]:
jack.addresses[1].user
Out[44]:
In [45]:
session.add(jack)
session.commit()
In [46]:
jack = session.query(User).filter_by(name='jack').one()
In [47]:
jack.addresses
Out[47]:
In [48]:
for u, a in session.query(User, Address)\
.filter(User.id==Address.user_id)\
.filter(Address.email_address=='jack@google.com')\
.all():
print(u, a)
In [49]:
session.query(User).join(Address)\
.filter(Address.email_address=='jack@google.com')\
.all()
Out[49]:
In [ ]: