Python SQLAlchemy.query方法代码示例

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_query():
    db = SQLAlchemy(URI1)
    ToDo = create_test_model(db)
    db.create_all()

    db.add(ToDo('First', 'The text'))
    db.add(ToDo('Second', 'The text'))
    db.flush()

    titles = ' '.join(x.title for x in db.query(ToDo).all())
    assert titles == 'First Second'

    data = db.query(ToDo).filter(ToDo.title == 'First').all()
    assert len(data) == 1

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_multiple_databases():
    db1 = SQLAlchemy(URI1)
    db2 = SQLAlchemy(URI2)
    ToDo1 = create_test_model(db1)
    ToDo2 = create_test_model(db2)
    db1.create_all()
    db2.create_all()

    db1.add(ToDo1('A', 'a'))
    db1.add(ToDo1('B', 'b'))
    db2.add(ToDo2('Q', 'q'))
    db1.add(ToDo1('C', 'c'))
    db1.commit()
    db2.commit()

    assert db1.query(ToDo1).count() == 3
    assert db2.query(ToDo2).count() == 1

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_add_role():
    db = SQLAlchemy('sqlite:///:memory:')
    auth = authcode.Auth(SECRET_KEY, db=db, roles=True)
    User = auth.User
    Role = auth.Role
    db.create_all()
    user = User(login=u'meh', password='foobar')
    db.session.add(user)
    role = Role(name=u'loremipsum')
    db.session.add(role)
    db.session.commit()

    assert hasattr(auth, 'Role')
    assert hasattr(User, 'roles')

    # Add nonexistant role creates it
    user.add_role('admin')
    db.session.commit()
    assert user.has_role('admin')
    assert db.query(Role).count() == 2
    assert list(user.roles) == [Role.by_name('admin')]

    # Adding the same role does nothing
    user.add_role('admin')
    db.session.commit()
    assert user.has_role('admin')
    assert db.query(Role).count() == 2
    assert list(user.roles) == [Role.by_name('admin')]

    # Adding an existent role does not create a new one
    user.add_role('loremipsum')
    db.session.commit()
    assert user.has_role('loremipsum')

    result = sorted([role.name for role in user.roles])
    assert result == ['admin', 'loremipsum']
    assert db.query(Role).count() == 2

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_remove_role():
    db = SQLAlchemy('sqlite:///:memory:')
    auth = authcode.Auth(SECRET_KEY, db=db, roles=True)
    User = auth.User
    Role = auth.Role
    db.create_all()
    user = User(login=u'meh', password='foobar')
    db.session.add(user)
    db.session.commit()

    assert hasattr(auth, 'Role')
    assert hasattr(User, 'roles')

    user.add_role('admin')
    db.session.commit()
    assert user.has_role('admin')
    assert db.query(Role).count() == 1

    # Removed from user but not deleted
    user.remove_role('admin')
    db.session.commit()
    assert not user.has_role('admin')
    assert list(user.roles) == []
    assert db.query(Role).count() == 1

    # Removing a role it doesn't have does nothing
    user.remove_role('admin')
    db.session.commit()
    assert not user.has_role('admin')
    assert list(user.roles) == []
    assert db.query(Role).count() == 1

    # Removing a nonexistant role does nothing
    user.remove_role('foobar')
    db.session.commit()
    assert db.query(Role).count() == 1

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_model_helpers():
    db = SQLAlchemy()

    class Row(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(60), nullable=False)
        created_at = db.Column(db.DateTime, nullable=False,
                               default=datetime.utcnow)

    db.create_all()
    db.add(Row(name='a'))
    db.flush()
    row = db.query(Row).first()

    assert str(row) == '<Row>'
    assert dict(row)['name'] == 'a'

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_aggregated_query():
    db = SQLAlchemy(URI1)

    class Unit(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(60))
        price = db.Column(db.Integer)

    db.create_all()
    db.add(Unit(price=25))
    db.add(Unit(price=5))
    db.add(Unit(price=10))
    db.add(Unit(price=3))
    db.commit()

    res = db.query(db.func.sum(Unit.price).label('price')).first()
    assert res.price == 43

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def create_test_model():
    db = SQLAlchemy('sqlite://')

    class Item(db.Model):
        id = db.Column(db.Integer, primary_key=True)

    class Part(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        item_id = db.Column(db.Integer, db.ForeignKey(Item.id))
        item = db.relationship('Item', backref='parts')

    db.create_all()

    for i in range(1, 26):
        item = Item()
        db.add(item)
    db.commit()

    item = db.query(Item).first()
    for j in range(1, 26):
        db.add(Part(item=item))
    db.commit()

    return db, Item, Part

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_role_model_methods():
    db = SQLAlchemy('sqlite:///:memory:')
    auth = authcode.Auth(SECRET_KEY, db=db, roles=True)
    Role = auth.Role
    db.create_all()
    role = Role(name=u'admin')
    db.session.add(role)
    db.commit()

    assert Role.by_id(role.id) == role
    assert Role.by_id(33) is None

    assert Role.by_name(u'admin') == role
    assert Role.by_name(u'foobar') is None

    assert Role.get_or_create(u'admin') == role

    role2 = Role.get_or_create(u'owner')
    db.commit()
    assert role2 != role
    assert db.query(Role).count() == 2

    assert list(role.users) == []
    assert list(role2.users) == []

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_cascade_save():
    db = SQLAlchemy()

    class ModelA(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        a1 = db.Column(db.Unicode, nullable=False)
        a2 = db.Column(db.Unicode)

    class ModelB(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        b1 = db.Column(db.Unicode, nullable=False)
        b2 = db.Column(db.Unicode)

    db.create_all()

    class FormA(f.Form):
        _model = ModelA

        a1 = f.Text(validate=[f.Required])
        a2 = f.Text()

    class FormB(f.Form):
        _model = ModelB

        b1 = f.Text(validate=[f.Required])
        b2 = f.Text()

    class WrapForm(f.Form):
        wr = f.Text()
        fa = FormA()
        fb = FormB()

    data = {
        'wr': u'foo',
        'fa.a1': u'AAA1',
        'fa.a2': u'AAA2',
        'fb.b1': u'BBB1',
        'fb.b2': u'BBB2',
    }
    form = WrapForm(data)

    assert form.is_valid()
    form.save()
    db.commit()
    assert db.query(ModelA).count() == 1
    assert db.query(ModelB).count() == 1
    obja = db.query(ModelA).first()
    assert obja.a1 == data['fa.a1']
    assert obja.a2 == data['fa.a2']
    objb = db.query(ModelB).first()
    assert objb.b1 == data['fb.b1']
    assert objb.b2 == data['fb.b2']

    # Update
    data = {
        'wr': u'foo',
        'fa.a1': u'A1',
        'fa.a2': u'A2',
        'fb.b1': u'B1',
        'fb.b2': u'B2',
    }
    objs = {
        'fa': obja,
        'fb': objb
    }
    form = WrapForm(data, obj=objs)
    assert form.is_valid()
    form.save()
    db.commit()

    assert db.query(ModelA).count() == 1
    assert db.query(ModelB).count() == 1

    obja = db.query(ModelA).first()
    assert obja.a1 == data['fa.a1']
    assert obja.a2 == data['fa.a2']

    objb = db.query(ModelB).first()
    assert objb.b1 == data['fb.b1']
    assert objb.b2 == data['fb.b2']

# 需要导入模块: from sqlalchemy_wrapper import SQLAlchemy [as 别名]
# 或者: from sqlalchemy_wrapper.SQLAlchemy import query [as 别名]
def test_formset_model():
    db = SQLAlchemy()

    class User(db.Model):
        __tablename__ = 'users'
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String)

    class Address(db.Model):
        __tablename__ = 'addresses'
        id = db.Column(db.Integer, primary_key=True)
        email = db.Column(db.String)
        user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
        user = db.relationship(
            'User', backref=db.backref('addresses', lazy='dynamic')
        )

        def __repr__(self):
            return '<Address %s>' % (self.email,)

    db.create_all()

    class FormAddress(f.Form):
        _model = Address
        email = f.Text(validate=[f.ValidEmail])

        def __repr__(self):
            return '<FormAddress %s>' % (self.email.value,)

    class FormUser(f.Form):
        _model = User
        name = f.Text()
        addresses = f.FormSet(FormAddress, parent='user')

    # Save

    data = {
        'name': u'John Doe',
        'addresses.1-email': u'[email protected]',
        'addresses.2-email': u'[email protected]',
        'addresses.3-email': u'[email protected]',
    }
    form = FormUser(data)
    assert form.is_valid()
    user = form.save()
    db.commit()

    assert db.query(User).count() == 1
    assert db.query(Address).count() == 3
    addr = db.query(Address).first()
    assert addr.email == data['addresses.1-email']
    assert addr.user == user

    # Update

    user = db.query(User).first()
    data = {
        'name': u'Max Smart',
        'addresses.1-email': u'[email protected]',
        'addresses.2-email': u'[email protected]',
        'addresses.3-email': u'[email protected]',
    }
    form = FormUser(data, obj=user)
    assert form.is_valid()
    form.save()
    db.commit()

    assert user.name == data['name']
    assert db.query(Address).count() == 3
    addr = db.query(Address).first()
    assert addr.email == data['addresses.1-email']
    assert addr.user == user