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_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_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 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_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='******')
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
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='******')
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
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_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_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) == []
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
name = db.Column(db.String(20))
pets = db.relationship('Pet',backref='owner')
# 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()
#
p1 = db.query(Person).filter_by(name="p").first()
for i in p1.pets:
print i.name
spot4= db.query(Pet).filter_by(name="spot4").first()
print spot4.owner.name
#
# db.add(Pet(name="spot4",owner=p1))
# db.add(Pet(name="spot5",owner=p1))
# db.add(Pet(name="spot6",owner=p1))p1
#print d
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']
return ' {} {}'.format(self.roll_no, self.name)
#result = relationship("StudentCourse")
# , primaryjoin="and_(Student.roll_no==StudentCourse.roll_no,")
class StudentCourse(db.Model):
__tablename__ = "studentcourse"
id = db.Column(Integer, primary_key=True)
course_id = db.Column(Integer)
roll_no = db.Column(Integer, ForeignKey('student.roll_no'))
stud = relationship('Student')
def __repr__(self):
return ' {}'.format(self.course_id)
# result = relationship("Student", primaryjoin="and_(Student.roll_no==StudentCourse.roll_no, ")
db.create_all()
#qr = db.query(Student).select_from(StudentCourse).join(Student.roll_no).filter(Student.roll_no==StudentCourse.roll_no)
q1 = db.query(StudentCourse, Student).outerjoin(Student).all()
q2 = db.query(Student, StudentCourse).outerjoin(StudentCourse).all()
q3 = q1.union(q2)
# result = db.query(StudentCourse.course_id,Student.name,Student.age).filter(Student.roll_no == StudentCourse.roll_no).all()
for i in q3:
print i
def load_post_file(filename):
with open(filename, "r") as f:
d = f.read()
posts = "[" + d.replace("}{", "},{") + "]"
posts = json.loads(posts)
for p in posts:
"""domain --> list(len=6) --> label --> <type 'unicode'> --> final_value",
"domain --> list(len=6) --> prediction --> <type 'float'> --> final_value",
"leftright --> list(len=2) --> label --> <type 'unicode'> --> final_value",
"leftright --> list(len=2) --> prediction --> <type 'float'> --> final_value",
"manifestocode --> list(len=56) --> label --> <type 'unicode'> --> final_value",
"manifestocode --> list(len=56) --> prediction --> <type 'float'> --> final_value",
"numberComments --> <type 'unicode'> --> final_value",
"numberLikes --> <type 'unicode'> --> final_value",
"party --> <type 'unicode'> --> final_value",
"postId --> <type 'unicode'> --> final_value",
"text --> list(len=1) --> <type 'unicode'> --> final_value",
"timeStamp --> <type 'unicode'> --> final_value"]"""
db
db.create_all()
db.drop_all()
todos = db.query(ToDo).all()
age = db.Column(Integer)
def __repr__(self):
return ' {}'.format(self.name)
#result = relationship("StudentCourse")
# , primaryjoin="and_(Student.roll_no==StudentCourse.roll_no,")
class StudentCourse(db.Model):
__tablename__ = "studentcourse"
id = db.Column(Integer, primary_key=True)
course_id = db.Column(Integer)
roll_no = db.Column(Integer, ForeignKey('student.roll_no'))
stud = relationship('Student')
def __repr__(self):
return ' {}'.format(self.course_id)
# result = relationship("Student", primaryjoin="and_(Student.roll_no==StudentCourse.roll_no, ")
db.create_all()
result = db.query(Student, StudentCourse).outerjoin(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
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'*****@*****.**',
'addresses.2-email': u'*****@*****.**',
'addresses.3-email': u'*****@*****.**',
}
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'*****@*****.**',
'addresses.2-email': u'*****@*****.**',
'addresses.3-email': u'*****@*****.**',
}
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
__tablename__ = 'department'
id = db.Column(Integer, primary_key=True)
name = db.Column(String(30))
employee = relationship('Employee', backref='company', cascade="delete")
class Employee(db.Model):
__tablename__ = "employee"
id = db.Column(Integer, primary_key=True)
name = db.Column(String)
department_id = db.Column(Integer, ForeignKey('department.id'))
db.create_all()
db.delete(db.query(Department).filter_by(id=1).first())
db.commit()
# dc = Department(name="Comp")
# di=Department(name="It")
# db.add(dc)
# db.add(di)
# db.commit()
#
#
# emp1 = Employee(name="piyush", company=dc)
# emp2 = Employee(name="p1", company=di)
# emp3 = Employee(name="p2", company=di)
# emp4 = Employee(name="akshay", company=dc)
# db.add(emp2)
# db.add(emp3)
# db.add(emp4)
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
for user in db.query(Child):
print user.id, user.parent_id, user.child_attr
db.commit()
passive_updates=False)
class Employee(db.Model):
__tablename__ = "employee"
id = db.Column(Integer, primary_key=True)
name = db.Column(String)
department_id = db.Column(Integer,
ForeignKey('department.id', onupdate="cascade"))
db.create_all()
#working
object_to_be_updated = db.query(Department).filter_by(name="Comp").first()
object_to_be_updated.id = 22
db.commit()
#
#
# dc = Department(name="Comp")
# di=Department(name="It")
# db.add(dc)
# db.add(di)
# db.commit()
#
#
# emp1 = Employee(name="piyush", company=dc)
# emp2 = Employee(name="p1", company=di)
