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 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 __init_tweet(self, game):
content = self.__compose_tweet(game)
is_doubleheader = self.__checkIfDoubleHeader(self._date)
game_num = game['@game_nbr']
game_id = str(game['@id'])
game_start = dateutil.parser.parse(game['@start']).replace(tzinfo = dateutil.tz.tzutc())
# new_time = game_start.replace(tzinfo = dateutil.tz.tzutc())
time_tweeted = datetime.datetime.now(timezone('America/Chicago'))-datetime.timedelta(hours=5)
if self._environment == 'production':
db = MyDB()
db.db_cur.execute("INSERT INTO tweets (content, is_doubleheader, game_num, game_id, game_start, time_tweeted) VALUES (%s, %s, %s, %s, %s, %s)", (content, is_doubleheader, game_num, game_id,game_start, time_tweeted))
db.finish_query()
twitter = self.__twitter_connect()
twitter.PostUpdate(content)
def tokenToUser(db: MyDB, jwt: MyJsonWebToken, token: str):
ans, username = jwt.decode(token)
if not ans:
return ans, username # user = message of why it didn't work.
user = db.getUser(username)
if user is None:
return False, "Token invalid." # user not found.
return True, user
def check_existence_of_new_location_and_add_it(self, data_location):
#DB CONNECTION
db = MyDB()
location_exist = False
for row in data_location:
if row.loc_intitule == self.new_location_box.text:
print("This Location already exists")
location_exist = True
if location_exist == False:
#selected_ent_jur =
idEntJur= self.ent_jur_listbox.ent_jur_listview.adapter.selection[0].id_jur_ent
add_permission_query = "INSERT INTO `Permission`;"
get_permission_id_query = """SELECT P.idPermission AS idP
FROM Permission P
ORDER BY P.idPermission DESC
LIMIT 1;"""
add_location_query = """INSERT INTO `Location` (idLocation, intitule, idEntJur)
VALUES (%d,%s,%d);"""
#try:
db.query(add_permission_query,[])
db.query(get_permission_id_query,[])
id_permission_data = db.db_fetchone()
parameters_query = [id_permission_data,self.new_location_box.text,idEntJur]
db.query(add_location_query,parameters_query)
db.commit()
#except:
#db.rollback()
self.new_location_box.text = ""
self.location_listbox.location_listview.adapter.data = self.location_listbox.get_location_list()
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 __did_tweet(self, day, game):
db = MyDB()
db.db_cur.execute("SELECT * FROM tweets;")
tweets_list = db.db_cur.fetchall()
db.finish_query()
# if checkIfDoubleHeader(getXMLData(createURLFromDate(day))):
tweeted = False
if self.__didCardinalsLose(game):
for entry in tweets_list:
if entry['game_id'] == game['@id']:
# if self.__parse_date(entry['game_start']) == self.__parse_date(day):
tweeted = True
if tweeted:
print "there was already a tweet for this game"
else:
print "No tweet yet"
return tweeted
class TestDB(unittest.TestCase):
def setUp(self):
self.sqlite_db = MyDB('test')
self.mysql_db = MyDB('test', flavor='mysql')
self.mysql_db.curs.execute(create_sql)
def test_create_sqlite(self):
self.assertTrue(os.path.exists('test.db'))
def test_mysql_create_db(self):
self.mysql_db.curs.execute('show databases')
databases = [x[0] for x in self.mysql_db.curs.fetchall()]
self.assertTrue('test' in databases)
def test_mysql_create_tables(self):
self.mysql_db.curs.execute('use test')
self.mysql_db.curs.execute('show tables')
tables = [x[0] for x in self.mysql_db.curs.fetchall()]
self.assertTrue('geo' in tables)
self.assertTrue('rdap' in tables)
def test_construct_insert_query(self):
sql, cols = self.mysql_db.construct_insert_query('unittest')
insert, duplicate = sql
self.assertEquals(cols, ['id', 'field1', 'field2', 'field3'])
self.assertEquals(
insert, 'INSERT INTO unittest (id,field1,field2,field3) VALUES ')
self.assertEquals(
duplicate,
'ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2), field3 = VALUES(field3)'
)
def test_query_to_string(self):
sql, cols = self.mysql_db.construct_insert_query('unittest')
queries = [(1, 'asdf', 60, '2017/05/04 15:34'),
(2, 'merp', 28, '1988/11/11 20:10')]
string = self.mysql_db.query_to_string('unittest', queries)
self.assertEquals(
string,
'''(1,"asdf",60,'2017-05-04 15:34:00'),(2,"merp",28,'1988-11-11 20:10:00')'''
)
def test_multi_insert(self):
sql, cols = self.mysql_db.construct_insert_query('unittest')
queries = [(1, 'asdf', 60, '2017/05/04 15:34'),
(2, 'merp', 28, '1988/11/11 20:10')]
try:
self.mysql_db.multi_insert('unittest', sql, queries)
except:
self.fail
def tearDown(self):
os.remove('test.db')
self.sqlite_db.curs.close()
self.sqlite_db.curs.close()
self.mysql_db.curs.execute('drop database test')
self.mysql_db.curs.close()
self.mysql_db.conn.close()
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
def get_ent_jur_list(self):
#DB CONNECTION
db = MyDB()
#Execute la requete SQL
get_all_legal_entities_query = "SELECT *FROM EntiteJuridique;"
try:
db.query(get_all_legal_entities_query,[])
db.commit()
except:
db.rollback()
#On obtient une matrice
legal_entities_data = db.db_fetchall()
# Liste d'entite Juridique
data_ent_jur = ListEntJur(legal_entities_data)
return data_ent_jur
def get_location_list(self):
#DB CONNECTION
db = MyDB()
#Execute la requete SQL
get_all_location_query = "SELECT * FROM Location;"
try:
db.query(get_all_location_query,[])
db.commit()
except:
db.rollback()
#On obtient une matrice
location_data = db.db_fetchall()
# Liste d'entite Juridique
data_location = ListLocationFromFetch(location_data)
return data_location
def check_login(self, *args):
user_login = self.login_box.text
user_password = self.password_box.text
# DB CONNECTION
db = MyDB()
login_query = "SELECT * FROM utilisateur WHERE login =%s AND password = %s"
parameters_query =[user_login,user_password]
try:
db.query(login_query,parameters_query)
db.commit()
except:
db.rollback()
login_data = db.db_fetchall()
if login_data:
user_logged = User(user_login)
self.dispatch('on_right_id')
else:
self.login_box.focus = True
self.remove_widget(self.pan_screen)
self.error_box.text = "Wrong credentials"
# create an animation object. This object could be stored
# and reused each call or reused across different widgets.
# += is a sequential step, while &= is in parallel
animation = Animation(x=(0), t='out_bounce')
# apply the animation on the button, passed in the "instance" argument
# Notice that default 'click' animation (changing the button
# color while the mouse is down) is unchanged.
animation.start(self.login_area)
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_module_available(self):
###################Looking into Module#####################
#DB CONNECTION
db = MyDB()
modules_query = """SELECT m.intitule, o.intitule
FROM module AS m , outil AS o, utilisateur AS u, utilisateuroutil AS uo
WHERE u.login =%s AND uo.idUtilisateur = u.idUtilisateur
AND uo.idOutil = o.idOutil AND o.idModule = m.idModule"""
user_logged = User()
parameters_query =[user_logged.login]
try:
db.query(modules_query,parameters_query)
db.commit()
except:
db.rollback()
modules_data = db.db_fetchall()
return modules_data
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 check_existence_of_new_ent_jur_and_add_it(self, data_ent_jur):
#DB CONNECTION
db = MyDB()
entity_exist = False
for row in data_ent_jur:
if row.ent_name == self.new_legal_entity_box.text:
print("This Legal Entity already exists")
entity_exist = True
if entity_exist == False:
add_legal_entity_query = "INSERT INTO `entitejuridique` (`intitule`) VALUES (%s) ;"
parameters_query = [str(self.new_legal_entity_box.text)]
try:
db.query(add_legal_entity_query,parameters_query)
db.commit()
except:
db.rollback()
self.new_legal_entity_box.text = ""
self.list_ent_jur_box.ent_jur_listview.adapter.data = self.list_ent_jur_box.get_ent_jur_list()
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)
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
def __init__(self):
self.db = MyDB()
def setUp(self):
self.sqlite_db = MyDB('test')
self.mysql_db = MyDB('test', flavor='mysql')
self.mysql_db.curs.execute(create_sql)
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
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
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
