Cacher is the code snippet organizer for pro developers

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
下面这段代码展示了一个 Object 的5中状态，以及在什么时候他们会转变成这些状态

参考: https://bwangel23.gitbooks.io/translate/content/SQLAlchemy/session-state-management.html
"""

from sqlalchemy import create_engine
from sqlalchemy import inspect
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://test:test@localhost/test')
Base = declarative_base()
Session = sessionmaker(bind=engine)

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32))

Base.metadata.create_all(engine)

session = Session()

love_user = User(name='xff')
insp = inspect(love_user)

print("transient: ", insp.transient)
session.add(love_user)
print("pending: ", insp.pending)
session.commit()
print("persistent: ", insp.persistent)

love = session.query(User).filter_by(name='xff').first()
insp2 = inspect(love)
print("persistent: ", insp2.persistent)
session.delete(love)
session.flush()
print("deleted: ", insp2.deleted)
session.commit()
print("detached: ", insp2.detached)

#!/usr/bin/env python3
# -*- coding: utf-8 -*-


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

# engine = create_engine('mysql+pymysql://test:test@localhost/test', echo=True)
engine = create_engine('mysql+pymysql://test:test@localhost/test')
Base = declarative_base()
Session = sessionmaker(bind=engine)

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    fullname = Column(String(32))
    password = Column(String(32))

    def __repr__(self):
        return "<User(name='{}', fullname='{}', password='{}')".format(
            self.name, self.fullname, self.password)

# 这里的创建语句的含义是，如果表已经存在的话，就不会再创建了
Base.metadata.create_all(engine)

session = Session()
# ed_user = User(name='ed', fullname='Ed Jones', password='edpassword')
# session.add(ed_user)
# session.commit()

ed_user = session.query(User).filter_by(id=1).first()
ed_user.name = 'Edwardo'

fake_user = User(name='fakeuser', fullname='Invalid', password='12345')
session.add(fake_user)

print(session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all())

session.rollback()
print(ed_user.name)
print(fake_user in session)

print(session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all())

#!/usr/bin/env python3
# -*- coding: utf-8 -*-


from sqlalchemy import create_engine, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker, aliased

engine = create_engine('mysql+pymysql://test:test@localhost/test')
Base = declarative_base()
Session = sessionmaker(bind=engine)

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    fullname = Column(String(32))
    password = Column(String(32))

    address = relationship("Address", order_by="Address.id", back_populates="user")

    def __repr__(self):
        return "<User(name='{}', fullname='{}', password='{}')".format(
            self.name, self.fullname, self.password)


class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email_address = Column(String(64), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    user = relationship("User", back_populates="address")

    def __repo__(self):
        return "<Address(email_address='%s')>" % self.email_address


# 这里的创建语句的含义是，如果表已经存在的话，就不会再创建了
Base.metadata.create_all(engine)

session = Session()

users = [
    User(name='ed', fullname='Ed Jones', password='edpassword'),
    User(name='xff', fullname='ffw', password='iloveyou'),
    User(name='michael', fullname='michael tsui', password='hahaha'),
    User(name='mama', fullname='mama', password='iloveyou'),
    User(name='mama', fullname='loving mama', password='iloveyou'),
]

session.add_all(users)  # add_all 可以用来一次添加多行
session.commit()

for item in session.query(User, User.name).all():
    print(item.User, item.name)

for instance in session.query(User).order_by(User.id):
    print(instance.id, instance.name, instance.fullname)

for instance in session.query(User.name.label('name_label')).all():
    print(instance.name_label)

# 这个别名不仅是在传入参数的别名，返回的 result 对象中也做了别名
user_alias = aliased(User, name='user_alias')

for row in session.query(user_alias, user_alias.name).all():
    print(row.user_alias, row.user_alias.name)

for row in session.query(User).order_by(User.id)[2:4]:
    print(row.id, row.name, row.password)

for row in session.query(User).filter_by(name='mama'):
    print(row.id, row.name, row.password)

# 注意这里的查询条件使用了一个普通的 Python 条件表达式
for row in session.query(User).filter(User.name=='mama'):
    print(row.fullname, row.password)

# 多次过滤查询，这里多个条件之间用了 AND 进行连接
for row in session.query(User).filter_by(fullname='mama').filter(User.name=='mama'):
    print(row.fullname, row.password)

# 这里使用几个特殊操作符
u11 = session.query(User).filter(User.id == 11).one()
print(u11.name, u11.fullname)
u1 = session.query(User).filter(User.id == 1).one_or_none()
print(u1)
query = session.query(User.id).filter(User.id == 11).order_by(User.id)
print(query.scalar())

# 这里使用了 NOT IN 操作符
us = session.query(User).filter(~User.name.in_(['ed', 'xff', 'michael'])).all()
print(us)

for user in session.query(User).all():
    session.delete(user)
session.commit()

# Session 执行 RAW SQL，这里使用的数据库驱动是PyMySQL
SQL = """
SELECT id as users_id, name FROM users where name = '{}'
""".format("ed")

ds = session.execute(SQL)

for item in ds.fetchall():
    print(item.users_id, item.name)

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
在下面的程序中，SQLalchemy声明了一个User表，同时创建了一个Session，向这个表添加和更新记录
"""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

# engine = create_engine('mysql+pymysql://test:test@localhost/test', echo=True)
engine = create_engine('mysql+pymysql://test:test@localhost/test')
Base = declarative_base()
Session = sessionmaker(bind=engine)

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    fullname = Column(String(32))
    password = Column(String(32))

    def __repr__(self):
        return "<User(name='{}', fullname='{}', password='{}')".format(
            self.name, self.fullname, self.password)

# 这里的创建语句的含义是，如果表已经存在的话，就不会再创建了
Base.metadata.create_all(engine)

session = Session()
ed_user = User(name='ed', fullname='Ed Jones', password='edpassword')
print(ed_user.id)   # User 的 id 在 add之前还是空的
session.add(ed_user)
our_user = session.query(User).filter_by(name='ed').first()
print(our_user, our_user is ed_user)    # 这里返回的两个 User 是相同的
session.commit()

session.add_all([
    User(name='xff', fullname='ffw', password='iloveyou'),
    User(name='michael', fullname='michael tsui', password='hahaha'),
    User(name='mama', fullname='mama', password='iloveyou'),
])  # add_all 可以用来一次添加多行
ed_user.password='p@ssw0rd'
print(session.dirty)    # dirty显示待 UPDATE 的行
print(session.new)  # new 显示待 INSERT 的行
session.commit()    # 下面是 commit 时输出的详细日志
"""
2016-11-08 21:03:02,305 INFO sqlalchemy.engine.base.Engine UPDATE users SET password=%(password)s WHERE users.id = %(users_id)s
2016-11-08 21:03:02,305 INFO sqlalchemy.engine.base.Engine {'password': 'p@ssw0rd', 'users_id': 21}
2016-11-08 21:03:02,306 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%(name)s, %(fullname)s, %(password)s)
2016-11-08 21:03:02,307 INFO sqlalchemy.engine.base.Engine {'name': 'xff', 'password': 'iloveyou', 'fullname': 'ffw'}
2016-11-08 21:03:02,308 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%(name)s, %(fullname)s, %(password)s)
2016-11-08 21:03:02,308 INFO sqlalchemy.engine.base.Engine {'name': 'michael', 'password': 'hahaha', 'fullname': 'michael tsui'}
2016-11-08 21:03:02,309 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%(name)s, %(fullname)s, %(password)s)
2016-11-08 21:03:02,310 INFO sqlalchemy.engine.base.Engine {'name': 'mama', 'password': 'iloveyou', 'fullname': 'mama'}
2016-11-08 21:03:02,311 INFO sqlalchemy.engine.base.Engine COMMIT
"""