class City:
id = ""
name = ""
sid = ""
db = MyDB()
def getIdCityFromName(self, name):
query = "SELECT * FROM city_province WHERE name LIKE '%s'" % (
'%' + name + '%')
self.db.query(query, (name))
data = self.db._db_cur.fetchone()
if data != None and data.count > 0:
return data[0]
return 0
def getIdProvinceFromCity(self, cityId, name):
self.db.query("set names utf8;", None)
query = "SELECT * FROM district WHERE city_id = %s AND name LIKE %s"
try:
self.db.query(query, (cityId, name))
data = self.db._db_cur.fetchone()
if data != None and data.count > 0:
return data[0]
#insert new district to city
return self.insertNewDistrict(cityId, name)
except UnicodeDecodeError as err:
print err
print self.db._db_cur._executed
#insert new district to city
return self.insertNewDistrict(cityId, name)
def insertNewDistrict(self, cityId, name):
alias = self.makeAlias(name)
self.db.query("set names utf8;", None)
try:
query = "INSERT INTO district (city_id,name,alias) VALUES (%s,%s,%s)"
self.db.query(query, (cityId, name, alias))
self.db._db_connection.commit()
return self.db.getLastId()
except UnicodeDecodeError as err:
print "insert district error: ", err
print self.db._db_cur._executed
return None
def makeAlias(self, name):
name = name.replace(u'đ', u'd')
try:
txt = slugify(name)
return txt
except UnicodeEncodeError as err:
print err
print name
return ""
# city = City()
# id = city.getIdCityFromName(u"Hồ Chí Minh")
# province = city.getIdProvinceFromCity(id, "Quận Phú Nhuận")
# print(province)
def cur():
print("Setting up")
db=MyDB()
conn=db.connect("server")
curs=conn.cursor()
yield curs
print("teardown DB") #pytest -v --capture=no
curs.close()
conn.close()
print("closed DB")
def fetch_database_urls():
db = MyDB()
query = "SELECT link, id FROM cs_sites"
db.query(query)
urls = {}
for item in list(db.fetchall()):
urls[item[1]] = item[0]
db.commit()
db.close()
return urls
class Tag:
id = ""
tag = ""
alias = ""
parentId = ""
patternTypeId = 3
status = 1
db = MyDB()
def getIdTagFromName(self, name, parentId):
self.db.query("set names utf8;", None)
alias = self.makeAlias(name)
query = "SELECT id FROM tag WHERE alias LIKE '%s'" % (alias)
# try:
self.db.query(query, None)
data = self.db._db_cur.fetchone()
if data != None:
return data[0]
#insert new district to city
return self.insertNewTag(name, parentId)
# except (UnicodeDecodeError,ProgrammingError) as err:
# print err
# print self.db._db_cur._executed
#insert new district to city
def insertNewTag(self, name, parentId):
alias = self.makeAlias(name)
self.db.query("set names utf8;", None)
try:
query = "INSERT INTO tag (tag,alias,parent_id,pattern_type_id) VALUES (%s,%s,%s,%s)"
self.db.query(query, (name, alias, parentId, self.patternTypeId))
self.db._db_connection.commit()
return self.db.getLastId()
except UnicodeDecodeError as err:
print "insert tag error: ", err
print self.db._db_cur._executed
return 0
def makeAlias(self, name):
name = name.replace(u'đ', u'd')
try:
txt = slugify(name)
return txt
except UnicodeEncodeError as err:
print err
print name
return ""
# tag = Tag()
# id = tag.getIdTagFromName(u"Hồ Chí Minh 13")
# print(id)
def read_db(db_info, table, key):
"""
从数据库中读取文本
:param db_info: 数据库信息 要求为字典格式
:param table: 表名
:param key:数据库字段
"""
sql_select = "select %s from %s" % (key, table)
with MyDB(db_info) as db:
db.execute(sql_select)
results = db.fetchall()
return results
class ObjectTag:
objectId = ""
objectType = 8
tagId = ""
db = MyDB()
def insertNewObjectTag(self, objectId, tagId):
self.db.query("set names utf8;", None)
try:
query = "INSERT INTO object_tag (object_id,object_type,tag_id) VALUES (%s,%s,%s)"
self.db.query(query, (objectId, self.objectType, tagId))
self.db._db_connection.commit()
return self.db.getLastId()
except UnicodeDecodeError as err:
print "insert object tag error: ", err
print self.db._db_cur._executed
return None
# tag = ObjectTag()
# id = tag.insertNewObjectTag(37,2)
# print(id)
def get_ip_data(self, ips):
self.my_db = MyDB(self.dbname, flavor='mysql')
self.my_util = MyUtil()
self.master = Master(self.logger)
ip_queue = Queue(len(ips) * 2 * 3)
geo_write_queue = Queue(len(ips) * 3)
rdap_write_queue = Queue(len(ips) * 3)
reporter_queue = Queue(len(ips) * 3)
router = {'rdap': rdap_write_queue, 'geo': geo_write_queue}
self.master.new_worker('readers', self.NUMBER_OF_READERS, self.ip_queue_thread_handler, (ip_queue, router))
self.master.new_worker('rdap writer', self.NUMBER_OF_WRITERS, self.db_writer, (rdap_write_queue, reporter_queue, 'rdap'))
self.master.new_worker('geo writer', self.NUMBER_OF_WRITERS, self.db_writer, (geo_write_queue, reporter_queue, 'geo'))
self.master.new_worker('simple reporter', self.NUMBER_OF_REPORTERS, self.simple_reporter, (reporter_queue, len(ips) * 2))
self.master.start()
for ip in ips:
ip_queue.put((ip, self.rdap_url, 'rdap'))
ip_queue.put((ip, self.geo_url, 'geo'))
self.master.shutdown()
def __init__(self):
self.db = MyDB()
from flask import Flask, request, jsonify
from flask_cors import CORS
from db import MyDB
from utils import readFile, validUser, tokenToUser
from MyJWT import MyJsonWebToken
import json, os, subprocess, signal, atexit
app = Flask(__name__)
CORS(app)
db = MyDB('api_db.json')
jwt = MyJsonWebToken()
process = ({})
@app.route('/', methods=['GET'])
def home():
return jsonify({'answer': 'pong'}), 200
@app.route('/create', methods=['post'])
def createUser():
user = request.json
if not validUser(user):
return jsonify({'answer': 'failure', 'toast': 'Input form invalid.'}), 400
ans = db.addUser(user)
if ans is not None:
return jsonify({'answer': 'failure', 'toast': ans}), 400
return jsonify({'answer': 'success'}), 201
def parse_page(json):
if json:
items = json.get('problemSetProblem')
# print(items)
problem_info = {
'problemId': items['id'],
'title': items['title'],
'type': items['type'],
'content': items['content'],
'points': items['score']
}
yield problem_info
if __name__ == '__main__':
db = MyDB()
table = 'problems'
sql = """CREATE TABLE IF NOT EXISTS problems (
problemId VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
type VARCHAR(64) NOT NULL,
content TEXT,
points INT NOT NULL,
score INT,
PRIMARY KEY (problemId))ENGINE=InnoDB DEFAULT CHARSET=utf8'
"""
db.create_table(sql)
get_id_list()
for id in id_list:
json = get_page(id)
problem_info_list = parse_page(json)
def init_db(self):
db = MyDB(host=self.db_host,port=self.db_port,username=self.db_user,password=self.db_pwd,database=self.db_db)
#print(dir(db))
return db
def setUp(self):
self.sqlite_db = MyDB('test')
self.mysql_db = MyDB('test', flavor='mysql')
self.mysql_db.curs.execute(create_sql)
class Host():
name = ""
description = ""
address = ""
phone = ""
image_profile = ""
status = 1
longtitude = ""
lattitude = ""
website = ""
alias = ""
typeId = ""
created_at = ""
creatorId = ""
tag = ""
districtId = ""
startTime = "08:00:00"
endTime = "21:00:00"
viewMap = 0
crawler = ""
suffixId = ""
db = MyDB()
listTagId = []
def parse(self, str):
data = str.split("[0h0]")
self.name = data[0]
self.address = data[1]
self.image_profile = data[2]
location = data[19].split(',')
self.lattitude = location[0]
self.longtitude = location[1]
self.suffixId = data[22]
self.alias = data[12]
self.crawler = data[22]
def parseContent(self, response):
hxs = Selector(text=response.body)
try:
self.phone = hxs.css('ul.textsdtdd li::text').extract()[0]
except IndexError:
pass
try:
self.website = hxs.css('p.topusc5_0::text').extract()[0]
except IndexError:
pass
# print(self.website)
rows = hxs.xpath(
'//div[@class="rdct_0"]/table/tr/td/b/text()').extract()
if len(rows) > 0:
time = rows[0]
if time:
try:
time = time.split('-')
t = time[0].strip()
dateObj = datetime.strptime(t, '%I:%M %p')
self.startTime = dateObj.strftime('%H:%M:%S')
# print self.startTime
t = time[1].strip()
dateObj = datetime.strptime(t, '%I:%M %p')
self.endTime = dateObj.strftime('%H:%M:%S')
# print self.endTime
except (ValueError, IndexError):
pass
self.listTagId = []
tag = Tag()
#khung gia: 2tr -10tr
rows = hxs.xpath('//div[@class="rdct_0"]/table/tr').extract()
for row in rows:
listTd = Selector(text=row).xpath('//td/p/text()').extract()
if len(listTd) > 0:
left = listTd[0]
listTd = Selector(text=row).xpath('//td/b/text()').extract()
right = listTd[0]
if left.find(u'giá') > 0:
self.tag = right
self.listTagId.append(tag.getIdTagFromName(
self.tag, 16339))
rows = hxs.xpath(
'//div[@class="rdct_0"]/table/tr/td/div/p[@class="imgtiddtt"]/text()'
).extract()
# print 'haha'
# print rows
for idx, row in enumerate(rows):
if row == u'Tiện ích':
xpath = '//div[@class="rdct_0"]/table/tr/td/div'
rr = hxs.xpath(xpath).extract()
rrr = Selector(text=rr[idx]).xpath(
'//p[@class="bleftdd_1"]/a/text()').extract()
# print rrr
for r in rrr:
t = r.strip()
if t != u'Khác':
self.tag += ',' + t
self.listTagId.append(tag.getIdTagFromName(t, 16359))
# print self.tag
rows = hxs.xpath(
'//div[@class="ndungleftdct"]/div[@class="ndleft_0"]/p/text()'
).extract()
if len(rows) > 0:
self.description = rows[0]
# print self.description
meta = response.meta
self.typeId = meta["typeId"]
cityId = meta["cityId"]
rows = hxs.xpath(
'//div[@class="rdct_0"]/p[@class="rdctfollow_0"]/span[@class="rdctfollow_5"]/text()'
).extract()
if len(rows) == 3:
district = rows[2][8:].strip()
city = City()
self.districtId = city.getIdProvinceFromCity(cityId, district)
# print "districtId=",self.districtId
# print self.districtId
def insertDB(self):
self.db.query("set names utf8;", None)
query = "INSERT INTO host (name,description,address,phone,image_profile,longtitude,lattitude,website,alias,type_id,tag,district_id,starttime,endtime,crawler,view_map)" \
" VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
self.db.query(
query, (self.name, self.description, self.address, self.phone,
self.image_profile, self.longtitude, self.lattitude,
self.website, self.alias, self.typeId, self.tag,
self.districtId, self.startTime, self.endTime,
self.suffixId, self.viewMap))
self.db.commit()
# print self.db._db_cur._executed
return self.db.getLastId()
except mysql.connector.Error as err:
print err
print self.db._db_cur._executed
return -1
def checkExisted(self):
query = "SELECT id FROM host WHERE alias LIKE '%s'" % (self.alias)
try:
self.db.query(query, None)
data = self.db._db_cur.fetchone()
if data != None:
return True
return False
except mysql.connector.Error as err:
print err
return False
def getItems(self):
query = "SELECT id,alias,crawler FROM host WHERE crawler != \"\" AND description LIKE '%s' ORDER BY `host`.`id` ASC" % (
"%đang cập nhật%")
print query
try:
self.db.query(query, None)
data = self.db._db_cur.fetchall()
if data != None:
return data
return False
except mysql.connector.Error as err:
print err
return False
def getDescription(self, response):
hxs = Selector(text=response.body)
listDescription = hxs.css('h2.prcbdd1::text').extract()
res = ""
if len(listDescription):
res = "\n".join(listDescription)
print res
return res
def updateDescription(self, id, description):
self.db.query("set names utf8;", None)
query = "UPDATE host SET description=%s WHERE id=%s"
try:
self.db.query(query, (description, id))
self.db.commit()
# print self.db._db_cur._executed
return True
except mysql.connector.Error as err:
print err
print self.db._db_cur._executed
return False
def getListHost(self, idx, limit):
query = "SELECT id,alias,crawler,tag FROM host WHERE crawler != \"\" ORDER BY `host`.`id` ASC LIMIT %s,%s" % (
idx, limit)
try:
self.db.query(query, None)
data = self.db._db_cur.fetchall()
if data != None:
return data
return False
except mysql.connector.Error as err:
print err
return False
def getKeyword(self, response):
meta = response.meta
id = meta['id']
txtTag = meta['tag']
hxs = Selector(text=response.body)
rows = hxs.xpath(
'//div[@class="rdct_0"]/table/tr/td/div/p[@class="imgtiddtt"]/text()'
).extract()
tag = Tag()
objectTag = ObjectTag()
listTagId = []
# list tag
for idx, row in enumerate(rows):
if row == u'Phục vụ các món':
tag.patternTypeId = 7
xpath = '//div[@class="rdct_0"]/table/tr/td/div'
rr = hxs.xpath(xpath).extract()
rrr = Selector(text=rr[idx]).xpath(
'//p[@class="bleftdd_1"]/a/text()').extract()
for r in rrr:
t = r.strip()
if t != u'Khác':
txtTag += ',' + t
listTagId.append(tag.getIdTagFromName(t, 19454))
if row == u'Phù hợp với mục đích':
tag.patternTypeId = 0
xpath = '//div[@class="rdct_0"]/table/tr/td/div'
rr = hxs.xpath(xpath).extract()
rrr = Selector(text=rr[idx]).xpath(
'//p[@class="bleftdd_1"]/a/text()').extract()
for r in rrr:
t = r.strip()
if t != u'Khác':
txtTag += ',' + t
listTagId.append(tag.getIdTagFromName(t, 0))
# print txtTag
# update tag object
for tagId in listTagId:
if tagId > 0:
# print id, tagId
objectTag.insertNewObjectTag(id, tagId)
self.updateKeyword(id, txtTag)
def updateKeyword(self, id, txtTag):
self.db.query("set names utf8;", None)
query = "UPDATE host SET tag=%s WHERE id=%s"
try:
self.db.query(query, (txtTag, id))
self.db.commit()
# print self.db._db_cur._executed
return True
except mysql.connector.Error as err:
print err
print self.db._db_cur._executed
return False
class Host:
name = ""
description = ""
address = ""
phone = ""
image_profile = ""
status = 1
longtitude = ""
lattitude = ""
website = ""
alias = ""
typeId = ""
created_at = ""
creatorId = ""
tag = ""
districtId = ""
startTime = "08:00:00"
endTime = "21:00:00"
viewMap = 0
crawler = ""
suffixId = ""
db = MyDB()
def parse(self, str):
data = str.split("[0h0]")
self.name = data[0]
self.address = data[1]
self.image_profile = data[2]
location = data[19].split(',')
self.lattitude = location[0]
self.longtitude = location[1]
self.suffixId = data[22]
self.alias = data[12]
def parseContent(self, response):
hxs = Selector(text=response.body)
try:
self.phone = hxs.css('ul.textsdtdd li::text').extract()[0]
except IndexError:
pass
try:
self.website = hxs.css('p.topusc5_0::text').extract()[0]
except IndexError:
pass
# print(self.website)
rows = hxs.xpath(
'//div[@class="rdct_0"]/table/tr/td/b/text()').extract()
if len(rows) > 0:
time = rows[0]
if time:
try:
time = time.split('-')
t = time[0].strip()
dateObj = datetime.strptime(t, '%I:%M %p')
self.startTime = dateObj.strftime('%H:%M:%S')
# print self.startTime
t = time[1].strip()
dateObj = datetime.strptime(t, '%I:%M %p')
self.endTime = dateObj.strftime('%H:%M:%S')
# print self.endTime
except (ValueError, IndexError):
pass
if len(rows) >= 3:
self.tag = rows[2].strip()
rows = hxs.xpath(
'//div[@class="rdct_0"]/table/tr/td/div/p[@class="bleftdd_1"]/a/text()'
).extract()
for row in rows:
self.tag += ',' + row.strip()
# print self.tag
rows = hxs.xpath(
'//div[@class="ndungleftdct"]/div[@class="ndleft_0"]/p/text()'
).extract()
if len(rows) > 0:
self.description = rows[0]
# print self.description
meta = response.meta
self.typeId = meta["typeId"]
cityId = meta["cityId"]
rows = hxs.xpath(
'//div[@class="rdct_0"]/p[@class="rdctfollow_0"]/span[@class="rdctfollow_5"]/text()'
).extract()
if len(rows) == 3:
district = rows[2][8:].strip()
city = City()
self.districtId = city.getIdProvinceFromCity(cityId, district)
print "districtId=", self.districtId
# print self.districtId
def insertDB(self):
self.db.query("set names utf8;", None)
query = "INSERT INTO host (name,description,address,phone,image_profile,longtitude,lattitude,website,alias,type_id,tag,district_id,starttime,endtime,crawler,view_map)" \
" VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
self.db.query(
query, (self.name, self.description, self.address, self.phone,
self.image_profile, self.longtitude, self.lattitude,
self.website, self.alias, self.typeId, self.tag,
self.districtId, self.startTime, self.endTime,
self.suffixId, self.viewMap))
self.db.commit()
# print self.db._db_cur._executed
return self.db.getLastId()
except mysql.connector.Error as err:
print err
print self.db._db_cur._executed
return -1
def checkExisted(self):
query = "SELECT id FROM host WHERE alias LIKE '%s'" % (self.alias)
try:
self.db.query(query, None)
data = self.db._db_cur.fetchone()
if data != None:
return True
return False
except mysql.connector.Error as err:
print err
return False
