def test_insert_update_delete(self):
# Assert Table.insert().
v1 = self.db.persons.insert(name=u"Kurt Gödel")
v2 = self.db.products.insert(name="pizza", price=10.0)
v3 = self.db.products.insert({"name": "garlic bread", "price": 3.0})
v4 = self.db.orders.insert(person=v1, product=v3)
self.assertEqual(v1, 1)
self.assertEqual(v2, 1)
self.assertEqual(v3, 2)
self.assertEqual(v4, 1)
self.assertEqual(self.db.persons.rows(), [(1, u"Kurt Gödel")])
self.assertEqual(self.db.products.rows(), [(1, u"pizza", 10.0),
(2, u"garlic bread", 3.0)])
self.assertEqual(self.db.orders.rows(), [(1, 1, 2)])
self.assertEqual(self.db.orders.count(), 1)
self.assertEqual(
self.db.products.xml.replace(' extra="auto_increment"', ""),
'<?xml version="1.0" encoding="utf-8"?>\n'
'<table name="products" fields="id, name, price" count="2">\n'
'\t<schema>\n'
'\t\t<field name="id" type="integer" index="primary" optional="no" />\n'
'\t\t<field name="name" type="string" length="100" />\n'
'\t\t<field name="price" type="float" default="0.0" />\n'
'\t</schema>\n'
'\t<rows>\n'
'\t\t<row id="1" name="pizza" price="10.0" />\n'
'\t\t<row id="2" name="garlic bread" price="3.0" />\n'
'\t</rows>\n'
'</table>')
# Assert transactions with commit=False.
if self.db.type == db.SQLITE:
self.db.orders.insert(person=v1, product=v2, commit=False)
self.db.rollback()
self.assertEqual(len(self.db.orders), 1)
self.db.orders.insert(person=v1, product=v2, commit=False)
# Assert Table.update().
self.db.products.update(2, price=4.0)
self.db.products.update(2, {"price": 4.5})
self.db.products.update(db.all(db.filter("name", "pi*")),
name="deeppan pizza")
self.assertEqual(self.db.products.rows(), [(1, u"deeppan pizza", 10.0),
(2, u"garlic bread", 4.5)])
# Assert Table.delete().
self.db.products.delete(db.all(db.filter("name", "deeppan*")))
self.db.products.delete(db.ALL)
self.db.orders.delete(1)
self.assertEqual(len(self.db.products), 0)
self.assertEqual(len(self.db.orders), 1)
print "pattern.db.Table.insert()"
print "pattern.db.Table.update()"
print "pattern.db.Table.delete()"
def test_insert_update_delete(self):
# Assert Table.insert().
v1 = self.db.persons.insert(name=u"Kurt Gödel")
v2 = self.db.products.insert(name="pizza", price=10.0)
v3 = self.db.products.insert({"name": "garlic bread", "price": 3.0})
v4 = self.db.orders.insert(person=v1, product=v3)
self.assertEqual(v1, 1)
self.assertEqual(v2, 1)
self.assertEqual(v3, 2)
self.assertEqual(v4, 1)
self.assertEqual(self.db.persons.rows(), [(1, u"Kurt Gödel")])
self.assertEqual(
self.db.products.rows(), [(1, u"pizza", 10.0), (2, u"garlic bread", 3.0)])
self.assertEqual(self.db.orders.rows(), [(1, 1, 2)])
self.assertEqual(self.db.orders.count(), 1)
self.assertEqual(self.db.products.xml.replace(' extra="auto_increment"', ""),
'<?xml version="1.0" encoding="utf-8"?>\n'
'<table name="products" fields="id, name, price" count="2">\n'
'\t<schema>\n'
'\t\t<field name="id" type="integer" index="primary" optional="no" />\n'
'\t\t<field name="name" type="string" length="100" />\n'
'\t\t<field name="price" type="float" default="0.0" />\n'
'\t</schema>\n'
'\t<rows>\n'
'\t\t<row id="1" name="pizza" price="10.0" />\n'
'\t\t<row id="2" name="garlic bread" price="3.0" />\n'
'\t</rows>\n'
'</table>'
)
# Assert transactions with commit=False.
if self.db.type == db.SQLITE:
self.db.orders.insert(person=v1, product=v2, commit=False)
self.db.rollback()
self.assertEqual(len(self.db.orders), 1)
self.db.orders.insert(person=v1, product=v2, commit=False)
# Assert Table.update().
self.db.products.update(2, price=4.0)
self.db.products.update(2, {"price": 4.5})
self.db.products.update(
db.all(db.filter("name", "pi*")), name="deeppan pizza")
self.assertEqual(self.db.products.rows(), [
(1, u"deeppan pizza", 10.0), (2, u"garlic bread", 4.5)])
# Assert Table.delete().
self.db.products.delete(db.all(db.filter("name", "deeppan*")))
self.db.products.delete(db.ALL)
self.db.orders.delete(1)
self.assertEqual(len(self.db.products), 0)
self.assertEqual(len(self.db.orders), 1)
print("pattern.db.Table.insert()")
print("pattern.db.Table.update()")
print("pattern.db.Table.delete()")
def userUpdate(self,userdict):
try:
q=self.db.usertable.search(fields=['id','mapid','userid'],
filters=all(filter("userid",userdict[0])))#
for row in q.rows():
a=row
if (a):
userdict.insert(0,a[1])
userup=tuple(userdict)
# self.db.usertable.update(a[0],userup)
self.db.usertable.update(a[0],name=userdict[2],province=userdict[3],city=userdict[4],
location=userdict[5],description=userdict[6],gender=userdict[7],
followersCount=userdict[8],friendsCount=userdict[9],
statusesCount=userdict[10],createdAt=userdict[11],
verified=userdict[12],verifiedType=userdict[13],biFollowersCount=userdict[14])
print "User "+str(userdict[1])+" has updated!"
else:
userdict.insert(0,-1)
userup=tuple(userdict)
self.db.usertable.append(userup)
# self.db.usertable.append(
# # mapid=userdict["mapid"],#用户映射id
# userid=userdict["userid"],#用户UID
# name=userdict["name"],#友好显示名称
# province=userdict["province"],#用户所在省级ID
# city=userdict["city"],# int 用户所在城市ID
# location=userdict["location"],# string 用户所在地
# description=userdict["description"],# string 用户个人描述
# gender=userdict["gender"],# string 性别，m：男、f：女、n：未知
# followersCount=userdict["followersCount"],# int 粉丝数
# friendsCount=userdict["friendsCount"],# int 关注数
# statusesCount=userdict["statusesCount"],# int 微博数
# createdAt=userdict["createdAt"],# string 用户创建（注册）时间
# verified=userdict["verified"],# STRING(10) 是否是微博认证用户，即加V用户，true：是，false：否
# verifiedType=userdict["verifiedType"],# int 暂未支持
# biFollowersCount=userdict["biFollowersCount"]# int 用户的互粉数
# )
print "user "+"User "+str(userdict[1])+" has appended!"
except IntegrityError:
print "User"+str(userdict['userid'])+" has existed!"
pass
except:
print "error when updating user recorder:"+str(userdict['userid'])+"!!!!"
for kk,vv in userdict.items():
print str(kk)+'='+str(vv)
exit(0)
def relationInsert(self,reldict):
try:
q=self.db.relationtable.search(fields=['id'],filters=all(filter("user_v1",reldict[0]),filter("user_v2",reldict[1])))
if (q.rows()):
for row in q.rows():
a=row
if (a):
self.db.relationtable.update(a[0],{reldict[-1]:0})
print "Relation between User "+str(reldict[0])+" and User "+str(reldict[1])+" has updated!"
else:
self.db.relationtable.append({'user_v1':reldict[0],'user_v2':reldict[1],reldict[-1]:0})
print "Relation between User "+str(reldict[0])+" and User "+str(reldict[1])+" has inserted!"
else:
self.db.relationtable.append({'user_v1':reldict[0],'user_v2':reldict[1],reldict[-1]:0})
print "Relation between User "+str(reldict[0])+" and User "+str(reldict[1])+" has inserted!"
except:
print "error when inserting relation recorder:"+str(reldict[0])+' and '+str(reldict[1])+"!!!!"
pass
exit(0)