def get_degree():
f = open("./texts/degree.txt", 'w')
f.write(
'EXPLAIN: for a relationship A->B means B (child object) is derived from A (father object). in_degree means how many A could derive to a single B. Out_degree means how many B could be derived from a single A. If you look at object 19141 (http://www.thingiverse.com/thing:19141), the in_degree is 3 as it is derived from 3 objects, the out_degree is 2 as it derived to 2 objects. \n'
)
f.write('thing_id \tthing_url \tin_degree \tout_degree\n')
c = conn.cursor()
c.execute(sql_things, ())
for raw in c.fetchall():
thing_url = raw[0]
thing_id = thing_url.strip().replace('/thing:', '')
# in
c_in = conn.cursor()
c_in.execute(sql_degree_in % (thing_url), ())
degree_in = ''
for raw_in in c_in:
degree_in = str(raw_in[0])
if degree_in == '':
degree_in == '0'
c_in.close()
# out
c_out = conn.cursor()
c_out.execute(sql_degree_out % (thing_url), ())
degree_out = ''
for raw_out in c_out:
degree_out = str(raw_out[0])
if degree_out == '':
degree_out == '0'
c_out.close()
f.write('%s\t%s\t%s\t%s\n' %
(thing_id, thing_url, degree_in, degree_out))
print thing_url, thing_id, degree_in, degree_out
c.close()
f.close()
print "hello"
def get_degree():
f = open("./texts/degree.txt", 'w')
f.write('EXPLAIN: for a relationship A->B means B (child object) is derived from A (father object). in_degree means how many A could derive to a single B. Out_degree means how many B could be derived from a single A. If you look at object 19141 (http://www.thingiverse.com/thing:19141), the in_degree is 3 as it is derived from 3 objects, the out_degree is 2 as it derived to 2 objects. \n')
f.write('thing_id \tthing_url \tin_degree \tout_degree\n')
c = conn.cursor()
c.execute(sql_things, ())
for raw in c.fetchall():
thing_url = raw[0]
thing_id = thing_url.strip().replace('/thing:', '')
# in
c_in = conn.cursor()
c_in.execute(sql_degree_in%(thing_url), ())
degree_in = ''
for raw_in in c_in:
degree_in = str(raw_in[0])
if degree_in == '':
degree_in == '0'
c_in.close()
# out
c_out = conn.cursor()
c_out.execute(sql_degree_out%(thing_url), ())
degree_out = ''
for raw_out in c_out:
degree_out = str(raw_out[0])
if degree_out == '':
degree_out == '0'
c_out.close()
f.write('%s\t%s\t%s\t%s\n'%(thing_id, thing_url, degree_in, degree_out))
print thing_url, thing_id, degree_in, degree_out
c.close()
f.close()
print "hello"
def text_things():
thing_tags_list = text_tags()
f = open("./texts/texts_all.txt", 'w')
f.write(
'thing_id \tthing_url \tthing_title \tthing_tags \tthing_desc \tthing_instruction\n'
)
c = conn.cursor()
param = ()
sql = sql_all
c.execute(sql, param)
for raw in c.fetchall():
thing_id = raw[0]
thing_url = raw[1]
thing_title = raw[2].strip().encode('ascii', 'ignore').replace(
'\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
thing_desc = raw[3].strip().encode('ascii', 'ignore').replace(
'\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
thing_instruction = raw[4].strip().encode('ascii', 'ignore').replace(
'\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
#print thing_desc
if thing_url in thing_tags_list:
thing_tags = thing_tags_list[thing_url]
else:
thing_tags = ''
#print thing_url, "==========", raw
if thing_id == 15152 or thing_id == 15153: # be careful this line, I can not format and clean it, so be aware to manually delete it.
print thing_desc
print thing_instruction
print "======"
f.write('%s\t%s\t%s\t%s\t%s\t%s\t\n' %
(thing_id, thing_url, thing_title, thing_tags, thing_desc,
thing_instruction))
f.close()
c.close()
def derived_insert(x_url, y_url):
sql_i = 'INSERT OR IGNORE INTO derived_raw (x_url, y_url) VALUES (?, ?)'
c = conn.cursor()
param = (x_url, y_url,)
c.execute(sql_i, param)
conn.commit()
c.close()
def derived_insert(x_url, y_url):
sql_i = "INSERT OR IGNORE INTO derived_raw (x_url, y_url) VALUES (?, ?)"
c = conn.cursor()
param = (x_url, y_url)
c.execute(sql_i, param)
conn.commit()
c.close()
def derived_insert(thing_id, derived_url):
sql_i = 'INSERT INTO derived (thing_id, url) VALUES (?, ?)'
c = conn.cursor()
param = (thing_id, derived_url,)
c.execute(sql_i, param)
conn.commit()
c.close()
def derived_insert(thing_id, derived_url):
sql_i = "INSERT INTO derived (thing_id, url) VALUES (?, ?)"
c = conn.cursor()
param = (thing_id, derived_url)
c.execute(sql_i, param)
conn.commit()
c.close()
def text_things():
thing_tags_list = text_tags()
f = open("./texts/texts_all.txt", 'w')
f.write('thing_id \tthing_url \tthing_title \tthing_tags \tthing_desc \tthing_instruction\n')
c = conn.cursor()
param = ()
sql = sql_all
c.execute(sql, param)
for raw in c.fetchall():
thing_id = raw[0]
thing_url = raw[1]
thing_title = raw[2].strip().encode('ascii', 'ignore').replace('\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
thing_desc = raw[3].strip().encode('ascii', 'ignore').replace('\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
thing_instruction = raw[4].strip().encode('ascii', 'ignore').replace('\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
#print thing_desc
if thing_url in thing_tags_list:
thing_tags = thing_tags_list[thing_url]
else:
thing_tags = ''
#print thing_url, "==========", raw
if thing_id == 15152 or thing_id == 15153: # be careful this line, I can not format and clean it, so be aware to manually delete it.
print thing_desc
print thing_instruction
print "======"
f.write('%s\t%s\t%s\t%s\t%s\t%s\t\n'%(thing_id, thing_url, thing_title, thing_tags, thing_desc, thing_instruction))
f.close()
c.close()
def image_insert(thing_id, image_url, image_type):
sql_i = 'INSERT INTO gallery_image (thing_id, url, type) VALUES (?,?,?)'
param = (thing_id, image_url, image_type, )
#print param
c = conn.cursor()
c.execute(sql_i, param)
conn.commit()
c.close()
def description_insert(thing_id, description):
sql_i = "INSERT INTO description (thing_id, description) VALUES (?,?)"
param = (thing_id, description)
# print param
c = conn.cursor()
c.execute(sql_i, param)
conn.commit()
c.close()
def description_insert(thing_id, description):
sql_i = 'INSERT INTO description (thing_id, description) VALUES (?,?)'
param = (thing_id, description, )
#print param
c = conn.cursor()
c.execute(sql_i, param)
conn.commit()
c.close()
def init():
c = conn.cursor()
c.executescript(sql)
conn.commit()
c.execute('''SELECT * FROM SQLITE_MASTER ''')
tables = c.fetchall()
print "** tables total number:"+str(len(tables))
c.close()
def like_insert(thing_id, follower_url):
sql_i = 'INSERT OR IGNORE INTO like (thing_id, follower_url) VALUES (?, ?)'
c = conn.cursor()
param = (thing_id, follower_url,)
#print param
c.execute(sql_i, param)
conn.commit()
c.close()
def like_insert(thing_id, follower_url):
sql_i = "INSERT OR IGNORE INTO like (thing_id, follower_url) VALUES (?, ?)"
c = conn.cursor()
param = (thing_id, follower_url)
# print param
c.execute(sql_i, param)
conn.commit()
c.close()
def init():
c = conn.cursor()
c.executescript(sql)
conn.commit()
c.execute('''SELECT * FROM SQLITE_MASTER ''')
tables = c.fetchall()
print "** tables total number:" + str(len(tables))
c.close()
def image_insert(thing_id, image_url, image_type):
sql_i = "INSERT INTO gallery_image (thing_id, url, type) VALUES (?,?,?)"
param = (thing_id, image_url, image_type)
# print param
c = conn.cursor()
c.execute(sql_i, param)
conn.commit()
c.close()
def text_things_right():
c = conn.cursor()
sql = sql_thing
c.execute(sql, ())
for raw in c.fetchall():
thing_id = raw[0]
thing_url = raw[1]
thing_title = raw[2]
if not things.has_key(thing_id):
things[thing_id] = {}
things[thing_id]['thing_id'] = thing_id
things[thing_id]['thing_url'] = thing_url
things[thing_id]['thing_title'] = thing_title
sql = sql_desc
c.execute(sql, ())
for raw in c.fetchall():
thing_id = raw[0]
desc = raw[1]
things[thing_id]['thing_desc'] = desc
sql = sql_insc
c.execute(sql, ())
for raw in c.fetchall():
thing_id = raw[0]
insc = raw[1]
things[thing_id]['thing_insc'] = insc
print len(things)
thing_tags_list = text_tags()
f = open("./texts/texts_all.txt", 'w')
f.write(
'thing_id \tthing_url \tthing_title \tthing_tags \tthing_desc \tthing_instruction\n'
)
for tid in things:
thing_url = things[tid]['thing_url'].encode('utf-8')
thing_title = things[tid]['thing_title'].strip().encode(
'utf-8').replace('\r\n', ' ').replace('\n',
' ').replace('\t', ' ')
if things[tid].has_key('thing_desc'):
thing_desc = things[tid]['thing_desc'].strip().encode(
'utf-8').replace('\r\n', ' ').replace('\n',
' ').replace('\t', ' ')
else:
thing_desc = ''
if things[tid].has_key('thing_insc'):
thing_insc = things[tid]['thing_insc'].strip().encode(
'utf-8').replace('\r\n', ' ').replace('\n',
' ').replace('\t', ' ')
else:
thing_insc = ''
if thing_url in thing_tags_list:
thing_tags = thing_tags_list[thing_url].encode('utf-8')
else:
thing_tags = ''
print tid
f.write(
'%s\t%s\t%s\t%s\t%s\t%s\t\n' %
(tid, thing_url, thing_title, thing_tags, thing_desc, thing_insc))
f.close()
c.close()
def made_insert(x_url, y_url, made_time, made_author_url):
sql_i = 'INSERT OR IGNORE INTO made_raw (x_url, y_url, made_time, made_author_url) VALUES (?,?,?,?)'
c = conn.cursor()
param = (x_url, y_url, made_time, made_author_url, )
#print param
c.execute(sql_i, param)
conn.commit()
#print "*******"
c.close()
def made_insert(x_url, y_url, made_time, made_author_url):
sql_i = "INSERT OR IGNORE INTO made_raw (x_url, y_url, made_time, made_author_url) VALUES (?,?,?,?)"
c = conn.cursor()
param = (x_url, y_url, made_time, made_author_url)
# print param
c.execute(sql_i, param)
conn.commit()
# print "*******"
c.close()
def people_check(url):
sql_id = 'SELECT id FROM people WHERE url=?'
c = conn.cursor()
c.execute(sql_id, (url,))
people_id = c.fetchone()
#print "***", people_id
#print "***", people_id[0]
if people_id:
return people_id[0]
else:
name = ''
register_time = ''
response, content = fetch.fetchio_single(url)
#print content
if True:
#response, content = http.request(url, 'GET')
#if int(response['status']) == 200:
soup = BeautifulSoup(content)
lists = soup.findAll('div', attrs={'id':'user-meta'})
if lists:
#print lists[0].contents
name = lists[0].contents[1].contents[0]
#print name
#print lists[0].contents[3].contents
#print "======"
for l in lists[0].contents[3].contents:
if type(l) == Tag:
for r in l.contents:
if type(r) == Tag:
#print r.contents
temp = r.contents[0].strip('\n\t')
#print temp
t = re.findall('Registered on', temp)
if t:
register_time = temp.strip('Registered on')
#print register_time
break
#tag_number = tag_number.strip('()')
#print t
#print "****"
#print "======"
else:
print "user's profile is not existing:"+url
sql = 'INSERT INTO people (name, url, register_time) VALUES (?, ?, ?)'
param = (name, url, register_time, )
#print param
c.execute(sql, param)
conn.commit()
c.execute(sql_id, (url,))
people_id = c.fetchone()
if people_id:
return people_id[0]
c.close()
def people_check(url):
sql_id = "SELECT id FROM people WHERE url=?"
c = conn.cursor()
c.execute(sql_id, (url,))
people_id = c.fetchone()
# print "***", people_id
# print "***", people_id[0]
if people_id:
return people_id[0]
else:
name = ""
register_time = ""
response, content = fetch.fetchio_single(url)
# print content
if True:
# response, content = http.request(url, 'GET')
# if int(response['status']) == 200:
soup = BeautifulSoup(content)
lists = soup.findAll("div", attrs={"id": "user-meta"})
if lists:
# print lists[0].contents
name = lists[0].contents[1].contents[0]
# print name
# print lists[0].contents[3].contents
# print "======"
for l in lists[0].contents[3].contents:
if type(l) == Tag:
for r in l.contents:
if type(r) == Tag:
# print r.contents
temp = r.contents[0].strip("\n\t")
# print temp
t = re.findall("Registered on", temp)
if t:
register_time = temp.strip("Registered on")
# print register_time
break
# tag_number = tag_number.strip('()')
# print t
# print "****"
# print "======"
else:
print "user's profile is not existing:" + url
sql = "INSERT INTO people (name, url, register_time) VALUES (?, ?, ?)"
param = (name, url, register_time)
# print param
c.execute(sql, param)
conn.commit()
c.execute(sql_id, (url,))
people_id = c.fetchone()
if people_id:
return people_id[0]
c.close()
def thing_insert(url, status, name, author_url, created_time):# default value for status is 1 which is in finished status
sql_i = 'INSERT OR IGNORE INTO thing (url, status, name, author_url, created_time) VALUES (?,?,?,?,?)'
param = (url, status, name, author_url, created_time, )
#print param
c = conn.cursor()
c.execute(sql_i, param)
conn.commit()
sql = 'SELECT id FROM thing WHERE url=?'
param = (url, )
c.execute(sql, param)
thing_id = c.fetchone()
if thing_id:
return thing_id[0]
c.close()
def thing_people():
f = open("./texts/people.txt", 'w')
f.write('thing_id \tthing_url \tauthor_name \tthing_created_time \tthing_author_url\n')
c = conn.cursor()
c.execute(sql_things, ())
for raw in c.fetchall():
thing_url = raw[0]
thing_id = thing_url.strip().replace('/thing:', '')
thing_author_url = raw[1]
thing_author = thing_author_url.strip().replace('http://www.thingiverse.com/', '')
thing_time = raw[2]
f.write('%s\t%s\t%s\t%s\t%s\n'%(thing_id, thing_url, thing_author, thing_time, thing_author_url))
print thing_id, thing_url, thing_author, thing_time, thing_author_url
c.close()
f.close()
def text_things_right():
c = conn.cursor()
sql = sql_thing
c.execute(sql, ())
for raw in c.fetchall():
thing_id = raw[0]
thing_url = raw[1]
thing_title = raw[2]
if not things.has_key(thing_id):
things[thing_id] = {}
things[thing_id]['thing_id'] = thing_id
things[thing_id]['thing_url'] = thing_url
things[thing_id]['thing_title'] = thing_title
sql = sql_desc
c.execute(sql, ())
for raw in c.fetchall():
thing_id = raw[0]
desc = raw[1]
things[thing_id]['thing_desc'] = desc
sql = sql_insc
c.execute(sql, ())
for raw in c.fetchall():
thing_id = raw[0]
insc = raw[1]
things[thing_id]['thing_insc'] = insc
print len(things)
thing_tags_list = text_tags()
f = open("./texts/texts_all.txt", 'w')
f.write('thing_id \tthing_url \tthing_title \tthing_tags \tthing_desc \tthing_instruction\n')
for tid in things:
thing_url = things[tid]['thing_url'].encode('utf-8')
thing_title = things[tid]['thing_title'].strip().encode('utf-8').replace('\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
if things[tid].has_key('thing_desc'):
thing_desc = things[tid]['thing_desc'].strip().encode('utf-8').replace('\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
else:
thing_desc = ''
if things[tid].has_key('thing_insc'):
thing_insc = things[tid]['thing_insc'].strip().encode('utf-8').replace('\r\n', ' ').replace('\n', ' ').replace('\t', ' ')
else:
thing_insc = ''
if thing_url in thing_tags_list:
thing_tags = thing_tags_list[thing_url].encode('utf-8')
else:
thing_tags = ''
print tid
f.write('%s\t%s\t%s\t%s\t%s\t%s\t\n'%(tid, thing_url, thing_title, thing_tags, thing_desc, thing_insc))
f.close()
c.close()
def thing_insert(
url, status, name, author_url, created_time
): # default value for status is 1 which is in finished status
sql_i = "INSERT OR IGNORE INTO thing (url, status, name, author_url, created_time) VALUES (?,?,?,?,?)"
param = (url, status, name, author_url, created_time)
# print param
c = conn.cursor()
c.execute(sql_i, param)
conn.commit()
sql = "SELECT id FROM thing WHERE url=?"
param = (url,)
c.execute(sql, param)
thing_id = c.fetchone()
if thing_id:
return thing_id[0]
c.close()
def derived_x_to_y():
txt = open("./dot/derived_x_to_y.gv", "w")
txt.write("//%s\n" % (str(datetime.now())))
txt.write("digraph graphname {\n")
gexf = Gexf("Jianhua Shao", "Thingiver derivation mapping")
graph = gexf.addGraph("directed", "static", "derivation_x_y")
# attr_node = graph.addNodeAttribute('url', '', 'string')
attr_edge = graph.addEdgeAttribute("c_days", "", "string")
# sql = sql_derived_x_to_y
sql = sql_derived_x_to_y_created_time
param = ()
c = conn.cursor()
c.execute(sql, param)
for r in c.fetchall():
print r
x = r[1]
y = r[2]
x_ctime = r[3]
y_ctime = r[4]
x = x.strip("\n\t/thing:")
# x = int(x)
y = y.strip("\n\t/thing:")
# y = int(y)
# print type(x), type(y)
# print x, y
x_ctime = datetime.strptime(x_ctime, "%b %d, %Y")
y_ctime = datetime.strptime(y_ctime, "%b %d, %Y")
duration = (y_ctime - x_ctime).days
# print duration
dot_line = "\t{%s} -> {%s} [label=%s];\n" % (x, y, str(duration))
print dot_line
txt.write(dot_line)
n_x = graph.addNode(str(x), str(x))
n_y = graph.addNode(str(y), str(y))
# n_x.addAttribute(attr_node, 'string')
# n_y.addAttribute(attr_node, 'string')
e = graph.addEdge("%s_%s" % (str(x), str(y)), x, y)
e.addAttribute(attr_edge, str(duration))
# print e
c.close()
txt.write("}")
txt.close()
gexf_file = open("./dot/derived_x_to_y.gexf", "w")
gexf.write(gexf_file)
print "finish"
def derived_x_to_y():
txt = open('./dot/derived_x_to_y.gv', 'w')
txt.write('//%s\n' % (str(datetime.now())))
txt.write('digraph graphname {\n')
gexf = Gexf('Jianhua Shao', 'Thingiver derivation mapping')
graph = gexf.addGraph('directed', 'static', 'derivation_x_y')
#attr_node = graph.addNodeAttribute('url', '', 'string')
attr_edge = graph.addEdgeAttribute('c_days', '', 'string')
#sql = sql_derived_x_to_y
sql = sql_derived_x_to_y_created_time
param = ()
c = conn.cursor()
c.execute(sql, param)
for r in c.fetchall():
print r
x = r[1]
y = r[2]
x_ctime = r[3]
y_ctime = r[4]
x = x.strip('\n\t/thing:')
#x = int(x)
y = y.strip('\n\t/thing:')
#y = int(y)
#print type(x), type(y)
#print x, y
x_ctime = datetime.strptime(x_ctime, '%b %d, %Y')
y_ctime = datetime.strptime(y_ctime, '%b %d, %Y')
duration = (y_ctime - x_ctime).days
#print duration
dot_line = '\t{%s} -> {%s} [label=%s];\n' % (x, y, str(duration))
print dot_line
txt.write(dot_line)
n_x = graph.addNode(str(x), str(x))
n_y = graph.addNode(str(y), str(y))
#n_x.addAttribute(attr_node, 'string')
#n_y.addAttribute(attr_node, 'string')
e = graph.addEdge('%s_%s' % (str(x), str(y)), x, y)
e.addAttribute(attr_edge, str(duration))
#print e
c.close()
txt.write('}')
txt.close()
gexf_file = open('./dot/derived_x_to_y.gexf', 'w')
gexf.write(gexf_file)
print "finish"
def get_tree():
#print "hello"
c = conn.cursor()
param = ()
sql = sql_tree
c.execute(sql, param)
for row in c.fetchall():
#print row
thing_from = row[1]
thing_to = row[2]
#print thing_from
#print thing_to
tree_loop(tree_list, thing_from, thing_to)
#print tree_list
#print len(tree_list)
#tree_display(tree_list)
print "tree_list: %s"%(str(len(tree_list)))
return tree_list
def thing_check():
thing_unique = {}
c = conn.cursor()
c.execute(sql_derived_thing_all_x, ())
for raw in c.fetchall():
x = raw[0].strip().replace('/thing:', '')
if thing_unique.has_key(x):
thing_unique[x] = thing_unique[x] + 1
else:
thing_unique[x] = 1
c.execute(sql_derived_thing_all_y, ())
for raw in c.fetchall():
y = raw[0].strip().replace('/thing:', '')
if thing_unique.has_key(y):
thing_unique[y] = thing_unique[y] + 1
else:
thing_unique[y] = 1
print len(thing_unique)
def get_tree():
#print "hello"
c = conn.cursor()
param = ()
sql = sql_tree
c.execute(sql, param)
for row in c.fetchall():
#print row
thing_from = row[1]
thing_to = row[2]
#print thing_from
#print thing_to
tree_loop(tree_list, thing_from, thing_to)
#print tree_list
#print len(tree_list)
#tree_display(tree_list)
print "tree_list: %s" % (str(len(tree_list)))
return tree_list
def thing_check():
thing_unique = {}
c = conn.cursor()
c.execute(sql_derived_thing_all_x, ())
for raw in c.fetchall():
x = raw[0].strip().replace('/thing:', '')
if thing_unique.has_key(x):
thing_unique[x] = thing_unique[x]+1
else:
thing_unique[x] = 1
c.execute(sql_derived_thing_all_y, ())
for raw in c.fetchall():
y = raw[0].strip().replace('/thing:', '')
if thing_unique.has_key(y):
thing_unique[y] = thing_unique[y]+1
else:
thing_unique[y] = 1
print len(thing_unique)
def tag_insert(thing_id, tag_name):
sql_i = 'INSERT OR IGNORE INTO tag (name) VALUES (?)'
c = conn.cursor()
param = (tag_name, )
c.execute(sql_i, param)
conn.commit()
sql_i = 'SELECT id FROM tag WHERE name = ?'
param = (tag_name, )
c.execute(sql_i, param)
tag_id = c.fetchone()[0]
if tag_id:
sql_i = 'INSERT OR IGNORE INTO thing_tag (thing_id, tag_id) VALUES (?, ?)'
param = (thing_id, tag_id, )
#print param
c.execute(sql_i, param)
conn.commit()
else:
sys.error('tag is not exisitng in database file')
c.close()
def tag_insert(thing_id, tag_name):
sql_i = "INSERT OR IGNORE INTO tag (name) VALUES (?)"
c = conn.cursor()
param = (tag_name,)
c.execute(sql_i, param)
conn.commit()
sql_i = "SELECT id FROM tag WHERE name = ?"
param = (tag_name,)
c.execute(sql_i, param)
tag_id = c.fetchone()[0]
if tag_id:
sql_i = "INSERT OR IGNORE INTO thing_tag (thing_id, tag_id) VALUES (?, ?)"
param = (thing_id, tag_id)
# print param
c.execute(sql_i, param)
conn.commit()
else:
sys.error("tag is not exisitng in database file")
c.close()
def file_insert(thing_id, file_type, file_url, file_date, file_name, file_download):
sql_i = 'INSERT OR IGNORE INTO file_type (type) VALUES (?)'
c = conn.cursor()
param = (file_type, )
#print param
c.execute(sql_i, param)
conn.commit()
sql_i = 'SELECT id FROM file_type WHERE type=?'
param = (file_type, )
c.execute(sql_i, param)
type_id = c.fetchone()[0]
if type_id:
sql_i = 'INSERT INTO file (thing_id, date, type_id, download_count, url, name) VALUES (?, ?, ?, ?, ?, ?)'
param = (thing_id, file_date, type_id, file_download, file_url, file_name, )
#print param
c.execute(sql_i, param)
conn.commit()
else:
sys.error('file type is not correct in database')
c.close()
def license_insert(thing_id, license_url):
sql_i = "INSERT OR IGNORE INTO license (url) VALUES (?)"
c = conn.cursor()
param = (license_url,)
c.execute(sql_i, param)
conn.commit()
sql_i = "SELECT id FROM license WHERE url = ?"
param = (license_url,)
c.execute(sql_i, param)
license_id = c.fetchone()
if license_id and license_id[0]:
license_id = license_id[0]
sql_i = "INSERT OR IGNORE INTO thing_license (thing_id, license_id) VALUES (?,?)"
param = (thing_id, license_id)
# print param
c.execute(sql_i, param)
conn.commit()
else:
sys.error("license error in database")
c.close()
def license_insert(thing_id, license_url):
sql_i = 'INSERT OR IGNORE INTO license (url) VALUES (?)'
c = conn.cursor()
param = (license_url, )
c.execute(sql_i, param)
conn.commit()
sql_i = 'SELECT id FROM license WHERE url = ?'
param = (license_url, )
c.execute(sql_i, param)
license_id = c.fetchone()
if license_id and license_id[0]:
license_id = license_id[0]
sql_i = 'INSERT OR IGNORE INTO thing_license (thing_id, license_id) VALUES (?,?)'
param = (thing_id, license_id, )
#print param
c.execute(sql_i, param)
conn.commit()
else:
sys.error('license error in database')
c.close()
def file_insert(thing_id, file_type, file_url, file_date, file_name, file_download):
sql_i = "INSERT OR IGNORE INTO file_type (type) VALUES (?)"
c = conn.cursor()
param = (file_type,)
# print param
c.execute(sql_i, param)
conn.commit()
sql_i = "SELECT id FROM file_type WHERE type=?"
param = (file_type,)
c.execute(sql_i, param)
type_id = c.fetchone()[0]
if type_id:
sql_i = "INSERT INTO file (thing_id, date, type_id, download_count, url, name) VALUES (?, ?, ?, ?, ?, ?)"
param = (thing_id, file_date, type_id, file_download, file_url, file_name)
# print param
c.execute(sql_i, param)
conn.commit()
else:
sys.error("file type is not correct in database")
c.close()
def text_tags():
thing_tags_list = {}
c = conn.cursor()
param = ()
sql = sql_tags
c.execute(sql, param)
for raw in c.fetchall():
thing_id = raw[0]
thing_url = raw[1]
thing_tag = raw[2].strip().replace('/tag:', '')
#print thing_id, thing_tag
if thing_url in thing_tags_list:
#print thing_id, "ininiinininini"
thing_tags_list[thing_url] = thing_tags_list.pop(thing_url)+", "+thing_tag
else:
thing_tags_list[thing_url] = thing_tag
#print thing_id, thing_tags_list[thing_id]
c.close()
#for raw in thing_tags_list:
# print raw, thing_tags_list[raw]
return thing_tags_list
def text_tags():
thing_tags_list = {}
c = conn.cursor()
param = ()
sql = sql_tags
c.execute(sql, param)
for raw in c.fetchall():
thing_id = raw[0]
thing_url = raw[1]
thing_tag = raw[2].strip().replace('/tag:', '')
#print thing_id, thing_tag
if thing_url in thing_tags_list:
#print thing_id, "ininiinininini"
thing_tags_list[thing_url] = thing_tags_list.pop(
thing_url) + ", " + thing_tag
else:
thing_tags_list[thing_url] = thing_tag
#print thing_id, thing_tags_list[thing_id]
c.close()
#for raw in thing_tags_list:
# print raw, thing_tags_list[raw]
return thing_tags_list
def get_things_with_multiple_parents():
child_list = []
f = open('./tree/things_with_multiple_parents.txt', 'w')
txt = 'parents_count\tthing_to\n'
f.write(txt)
c = conn.cursor()
param = ()
sql = sql_child_with_multiple_parents
c.execute(sql, param)
for row in c.fetchall():
parents_count = row[0]
thing_to = row[1]
txt = '%s\t%s\n' % (str(parents_count), str(thing_to))
f.write(txt)
if parents_count > 1:
#print parents_count, thing_to
child_list.append(thing_to)
c.close()
f.close()
#print len(child_list)
t = list_no_duplicate(child_list)
print "child_list: %s" % (str(len(t)))
return t
def get_things_with_multiple_parents():
child_list = []
f = open('./tree/things_with_multiple_parents.txt', 'w')
txt = 'parents_count\tthing_to\n'
f.write(txt)
c = conn.cursor()
param = ()
sql = sql_child_with_multiple_parents
c.execute(sql, param)
for row in c.fetchall():
parents_count = row[0]
thing_to = row[1]
txt = '%s\t%s\n'%(str(parents_count), str(thing_to))
f.write(txt)
if parents_count > 1:
#print parents_count, thing_to
child_list.append(thing_to)
c.close()
f.close()
#print len(child_list)
t = list_no_duplicate(child_list)
print "child_list: %s"%(str(len(t)))
return t
def error_log(url, e):
sql_i = 'INSERT INTO TABLE error (url, error) VALUES (?,?)'
c = conn.cursor()
c.execute(sql_i, (url, e,))
conn.commit()
c.close()
def error_log(url, e):
sql_i = "INSERT INTO TABLE error (url, error) VALUES (?,?)"
c = conn.cursor()
c.execute(sql_i, (url, e))
conn.commit()
c.close()
def test():
sql = """ SELECT * FROM SQLITE_MASTER"""
c = conn.cursor()
c.execute(sql)
for r in c.fetchall():
print r
def test():
sql = ''' SELECT * FROM SQLITE_MASTER'''
c = conn.cursor()
c.execute(sql)
for r in c.fetchall():
print r
