def _get_flask_app(roles=False, **kwargs):
db = SQLAlchemy('sqlite:///:memory:')
auth = authcode.Auth(
SECRET_KEY, db=db, roles=roles, password_minlen=3, **kwargs)
User = auth.User
db.create_all()
user = User(login=u'meh', password='******')
db.add(user)
user2 = User(login=u'foo', password='******')
db.add(user2)
db.commit()
app = Flask('test')
app.secret_key = os.urandom(32)
app.testing = True
@app.route('/protected/')
@auth.protected()
def protected():
return u'Welcome'
authcode.setup_for_flask(auth, app)
auth.session = {}
return auth, app, user
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
def test_flask_sqlalchemy_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 ToDo.query.all())
assert titles == 'First Second'
data = ToDo.query.filter(ToDo.title == 'First').all()
assert len(data) == 1
def test_custom_templates():
db = SQLAlchemy('sqlite:///:memory:')
options = {
'template_sign_in': 'sign-in.html',
'template_sign_out': 'sign-out.html',
'template_reset': 'reset-password.html',
'template_reset_email': 'reset-password-email.html',
'template_change_password': '******',
}
inbox = []
def send_email(user, subject, msg):
inbox.append(msg)
auth = authcode.Auth(SECRET_KEY, db=db, **options)
User = auth.User
db.create_all()
user = User(login=u'meh', password='******')
db.add(user)
db.commit()
custom_templates = os.path.join(
os.path.dirname(__file__),
'custom_templates'
)
app = Flask('test', template_folder=custom_templates)
app.secret_key = os.urandom(32)
app.testing = True
authcode.setup_for_flask(auth, app, send_email=send_email)
auth.session = {}
client = app.test_client()
resp = client.get(auth.url_sign_in)
assert resp.data == b'OK SIGN IN'
resp = client.get(auth.url_reset_password)
assert resp.data == b'OK RESET PASSWORD'
data = dict(login=user.login, _csrf_token=auth.get_csrf_token())
resp = client.post(auth.url_reset_password, data=data)
assert inbox[0] == 'OK RESET PASSWORD EMAIL'
auth.login(user)
resp = client.get(auth.url_change_password)
assert resp.data == b'OK CHANGE PASSWORD'
url = '{0}?_csrf_token={1}'.format(auth.url_sign_out, auth.get_csrf_token())
resp = client.get(url)
assert resp.data == b'OK SIGN OUT'
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'
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'
def test_formset_missing_objs():
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 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')
user = User(name=u'John Doe')
db.add(user)
a1 = Address(email=u'*****@*****.**', user=user)
db.add(a1)
a2 = Address(email=u'*****@*****.**', user=user)
db.add(a2)
a3 = Address(email=u'*****@*****.**', user=user)
db.add(a3)
db.commit()
print([(a.id, a.email) for a in user.addresses])
data = {
'name': u'Jane Doe',
'formaddress.1-email': u'*****@*****.**',
'formaddress.3-email': u'*****@*****.**',
'formaddress.4-email': u'*****@*****.**',
}
form = FormUser(data, user)
assert form.is_valid()
assert form.addresses.missing_objs == [a2]
def test_reconfigure(tmpdir):
db = SQLAlchemy(URI1, echo=False)
class CustomQuery(BaseQuery):
some_attr = 1
class Model(db.Model):
id = db.Column(db.Integer, primary_key=True)
db.create_all()
db.add(Model())
db.commit()
tmp_db_file = tmpdir.mkdir('test_reconfigure').join('db.sqlite')
uri = 'sqlite:///%s' % tmp_db_file.strpath.replace('\\', '/')
db.reconfigure(uri=uri, echo=True, query_cls=CustomQuery)
assert not Model.__table__.exists(db.engine)
assert (uri, True) == db.connector._connected_for
assert isinstance(db.query(Model), CustomQuery)
assert db.query(Model).some_attr == 1
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
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
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
def test_missing_fields_obj():
db = SQLAlchemy()
class Contact(db.Model):
id = db.Column(db.Integer, primary_key=True)
subject = db.Column(db.Unicode)
db.create_all()
class ContactForm(f.Form):
_model = Contact
subject = f.Text()
contact = Contact(subject=u'foobar')
db.add(contact)
db.commit()
form = ContactForm({}, contact)
new_contact = form.save()
assert new_contact.subject == u'foobar'
form = ContactForm({'subject': u''}, contact)
new_contact = form.save()
assert new_contact.subject == u''
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
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
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
def __repr__(self):
return ' {}'.format(self.course_id)
# result = relationship("Student", primaryjoin="and_(Student.roll_no==StudentCourse.roll_no, ")
db.create_all()
u1 = Student(roll_no=4306,
name="Akshay",
address="Solapur",
phone="78952146",
age=21)
s1 = StudentCourse(id=44, course_id=4, roll_no=9)
db.add(u1)
db.add(s1)
db.commit()
#
# #result = db.query(StudentCourse,Student).outerjoin(Student).all()
#
# result= db.query(Student).join(StudentCourse).all()
#
# #result = db.query(StudentCourse.course_id,Student.name,Student.age).filter(Student.roll_no == StudentCourse.roll_no).all()
#
# for i in result:
# print i
#
#
#
name = db.Column(String(20))
class Child(db.Model):
__tablename__ = "child"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
child_attr = db.Column(String(20))
parent = relationship('Parent')
db.create_all()
p1 = Parent(name="Akshay")
db.add(p1)
c1 = Child(child_attr="Child1")
c1.parent_id = p1.id
db.add(c1)
p2 = Parent(name="Piyush")
db.add(p2)
c2 = Child(child_attr="Child2")
c2.parent_id = p2.id
db.add(c2)
for user in db.query(Parent):
print user.name, '\t', user.id
# backref creates a virtual coloumn owner which takes Person object
# we can do personobject.pets.name
# we can do petobject.owner.name
# pet table will get virtual coloumn called as owner
class Pet(db.Model):
__tablename__ = "pet"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
owner_id = db.Column(db.Integer, db.ForeignKey('person.id'))
db.create_all()
piyush1 = Person(name="piyush1")
db.add(Person(name="piyush1"))
db.add(Person(name="piyush2"))
db.commit()
db.add(Pet(name="spot1", owner_id=piyush1.id))
db.add(Pet(name="spot2", owner_id=piyush1.id))
print "wefwfe"
db.commit()
#
# db.add(Pet(name="spot4",owner=p1))
# db.add(Pet(name="spot5",owner=p1))
# db.add(Pet(name="spot6",owner=p1))p1
#print d
