def readWebPage(self, urlString, depth=1, isExternal=False):
webPageData = self.db.websites.search(
filters=all(eq('address',
WebPage.parseUrl(urlString).string))).rows()
pageLinks = []
result = None
if len(webPageData) == 0:
return result
webPageData = webPageData[0]
pageId = webPageData[0]
depthData = self.db.session.search('depth',
all(eq('website_id', pageId)))
if len(depthData) > 0:
depth = depthData[0][0]
result = WebPage(url=webPageData[1],
depth=depth,
isExternal=isExternal)
query = self.db.execute(
'SELECT w.{0}, r.{0} from links join websites as w on links.{1} = w.id join websites as r on links.{2} = r.id WHERE w.id = {3};'
.format(self.db.websites.fields[1], self.db.links.fields[1],
self.db.links.fields[2], pageId))
for row in iter(query):
pageLinks.append(
WebPage(url=row[1], parent=result, depth=depth + 1))
result.links = pageLinks
return result
def insertRelation(self, parent, child):
if parent is None or child is None:
return
parentId = self.db.websites.search(
filters=all(eq('address', parent.url.string))).rows()[0][0]
childId = self.db.websites.search(
filters=all(eq('address', child.url.string))).rows()[0][0]
currentRecords = self.db.links.search(filters=all(
eq('website', parentId), eq('reference', childId))).rows()
if len(currentRecords) == 0:
self.db.links.append(website=parentId, reference=childId)
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 isInThisSession(self, page):
webPageData = self.db.session.search('websites.address',
filters=all(
eq('websites.address',
page.url.string))).rows()
if len(webPageData) > 0:
return True
return False
def wasPageVisited(self, page):
webPageData = self.db.websites.search('lastVisited',
filters=all(
eq('address',
page.url.string))).rows()
if len(webPageData) == 0:
return False
dateVisited = webPageData[0][0]
if dateVisited is None:
return False
return True
def appendSession(self, page):
pageId = self.db.websites.search('id',
filters=all(
eq('address',
page.url.string))).rows()
if len(pageId) == 0:
raise DbException(
"Trying to append website to session without having it in website table"
)
pageId = pageId[0][0]
try:
self.db.session.append(website_id=pageId, depth=page.depth)
except sqlite3.IntegrityError as err:
print "Invalid session data, cleaning session..."
self.clearSession()
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)
def insertWebpage(self, page, connection=False):
idDomain = None
dateVisited = None
if page.url.domain:
self.insertDomain(page.url.domain)
idDomain = self.getDomainId(page.url.domain)
if connection:
dateVisited = date(NOW)
try:
self.db.websites.append(address=page.url.string,
domain_id=idDomain,
connected=connection,
lastVisited=dateVisited)
self.insertRelation(page.parent, page)
except sqlite3.IntegrityError:
if connection:
self.db.websites.update(all(eq('address', page.url.string)),
connected=True,
lastVisited=dateVisited)
def getDomainId(self, name):
results = self.db.domains.search(filters=all(eq('name', name))).rows()
if len(results) > 0:
return results[0][0]
return None
