def getSeries(self, response):
sel = Selector(response)
item = {}
item["brand"] = sel.xpath('//h2[@class="fn-left name"]/a/text()').extract()[0]
db = SimpleMysql(host=host, db=dbname, user=user, passwd=pswd)
fs = sel.xpath('//div[@class="carbradn-cont fn-clear"]/dl')
for f in fs:
item["factory"] = f.xpath("dt/a/text()").extract()[0]
ts = f.xpath('dd/div[@class="list-dl-name"]')
ss = f.xpath('dd/div[@class="list-dl-text"]')
for i in range(len(ts)):
item["cartype"] = ts[i].xpath("text()").extract()[0].replace(u"：", "")
temp1 = ""
temp2 = ""
qs = ss[i].xpath("a/text()").extract()
for q in qs:
if u"停售" in q:
temp1 += q.replace(u"(停售)", "").strip() + ","
else:
temp2 += q + ","
item["halts"] = temp1[:-1]
item["series"] = temp2[:-1]
db.insert(tablename, item)
self.logger.info(item)
def getSeries(self, response):
sel = Selector(response)
item = {}
item['brand'] = sel.xpath('//h2[@class="fn-left name"]/a/text()').extract()[0]
db = SimpleMysql(host = host, db = dbname, user = user, passwd = pswd)
fs = sel.xpath('//div[@class="carbradn-cont fn-clear"]/dl')
for f in fs:
item['factory'] = f.xpath('dt/a/text()').extract()[0]
ts = f.xpath('dd/div[@class="list-dl-name"]')
ss = f.xpath('dd/div[@class="list-dl-text"]')
for i in range(len(ts)):
item['cartype'] = ts[i].xpath('text()').extract()[0].replace(u'：', '')
temp1 = ''
temp2 = ''
qs = ss[i].xpath('a/text()').extract()
for q in qs:
if u'停售' in q:
temp1 += q.replace(u'(停售)', '').strip() + ','
else:
temp2 += q + ','
item['halts'] = temp1[:-1]
item['series'] = temp2[:-1]
db.insert(tablename, item)
self.logger.info(item)
def parsePrice(self, response):
sel = Selector(response)
trs = sel.xpath('//div[@class="carprice-cont"]/dl[@class="price-dl"]')
item = AutohomeAllPriceItem()
item['city'] = sel.xpath(
'//div[@class="breadnav"]/a[2]/text()').extract()[0]
item['dealer'] = sel.xpath(
'//div[@class="text-main"]/text()').extract()[0]
item['dealerid'] = sel.xpath(
'//li[@id="nav_0"]/a/@href').extract()[0].replace('/', '')
tmp = sel.xpath('//div[@class="brandtree-name"]')
tmps = ''
for t in tmp:
tmps += t.xpath('p[@class="text"]/text()').extract()[0] + ','
item['manu'] = tmps[:-1]
log.msg(item['city'] + ', ' + item['dealer'] + ', ' + item['manu'])
db = SimpleMysql(host='127.0.0.1:5029',
db='wholenetwork',
user='******',
passwd='')
for tr in trs:
item['brand'] = tr.xpath(
'dt[@class="fn-clear"]/div[@class="name"]/p/a/text()').extract(
)[0]
item['brandid'] = filt(
tr.xpath('dt[@class="fn-clear"]/div[@class="name"]/p/a/@href').
extract()[0], 'cn/', '/')
prices = tr.xpath('dd/table/tr')
for price in prices:
tmp = price.xpath('td[2]/p/text()').extract()
if not tmp: continue # filt th row
else: item['oprice'] = tmp[0]
item['oprice'] = item['oprice'].replace(u'万', '')
tmp = price.xpath(
'td[3]/div[@class="this-number red"]/a[1]/text()').extract(
)
if not tmp: tmp = price.xpath('td[3]/p/a/text()').extract()
item['price'] = tmp[0].replace(u'万', '').replace(u' ', '')
item['pubdate'] = price.xpath(
'td[5]/text()').extract()[0].replace(u' ', '').replace(
'\r\n', '')
tmp = price.xpath('td[1]/a/text()').extract()[0]
item['model'] = tmp[:tmp.find('<')]
item['modelid'] = filt(
price.xpath('td[1]/a/@href').extract()[0], 'spec_', '.')
if ISSAVE: db.insert('autohome_allprice', item)
if ISPOST:
tmb = doPost(API_ADDRESS, item)
log.msg('\t' + str(tmb['error']) + ', ' + tmb['msg'])
def parsePrice(self, response):
sel = Selector(response)
item = AutohomeAllPriceItem()
item["city"] = sel.xpath('//div[@class="breadnav"]/a[2]/text()').extract()[0]
item["dealer"] = sel.xpath('//div[@class="text-main"]/text()').extract()[0]
item["dealerid"] = sel.xpath('//li[@id="nav_0"]/a/@href').extract()[0].replace("/", "")
tmp = sel.xpath('//div[@class="brandtree-name"]')
tmps = ""
for t in tmp:
tmps += t.xpath('p[@class="text"]/text()').extract()[0] + ","
item["manu"] = tmps[:-1]
self.logger.info(u"经销商：" + item["dealer"] + u"，\t\t\t\t主营品牌：" + item["manu"])
trs = sel.xpath('//div[@class="carprice-cont"]/dl[@class="price-dl"]')
for tr in trs:
item["brand"] = tr.xpath('dt[@class="fn-clear"]/div[@class="name"]/p/a/text()').extract()[0]
item["brandid"] = filt(
tr.xpath('dt[@class="fn-clear"]/div[@class="name"]/p/a/@href').extract()[0], "cn/", "/"
)
item["cartype"] = tr.xpath('dt/div[@class="info"]/p[2]/text()').extract()[0]
db = SimpleMysql(host=host, db=dbname, user=user, passwd=pswd)
prices = tr.xpath("dd/table/tr")
for price in prices:
tmp = price.xpath("td[2]/p/text()").extract()
if not tmp:
continue # filt th row
item["oprice"] = tmp[0].replace(u"万", "")
tmp = price.xpath('td[3]/div[@class="this-number red"]/a/text()').extract()
if not tmp:
tmp = price.xpath("td[3]/p/a/text()").extract()
item["price"] = tmp[0].replace(u"万", "").strip()
tmp = price.xpath("td[1]/a/text()").extract()[0]
item["model"] = tmp[: tmp.find("<")]
item["modelid"] = filt(price.xpath("td[1]/a/@href").extract()[0], "spec_", ".")
db.insert(tablename, item)
def parsePrice(self, response):
sel = Selector(response)
trs = sel.xpath('//div[@class="carprice-cont"]/dl[@class="price-dl"]')
item = AutohomeAllPriceItem()
item['city'] = sel.xpath('//div[@class="breadnav"]/a[2]/text()').extract()[0]
item['dealer'] = sel.xpath('//div[@class="text-main"]/text()').extract()[0]
item['dealerid'] = sel.xpath('//li[@id="nav_0"]/a/@href').extract()[0].replace('/', '')
tmp = sel.xpath('//div[@class="brandtree-name"]')
tmps = ''
for t in tmp: tmps += t.xpath('p[@class="text"]/text()').extract()[0] + ','
item['manu'] = tmps[:-1]
log.msg(item['city'] + ', ' + item['dealer'] + ', ' + item['manu'])
db = SimpleMysql(host = '127.0.0.1:5029', db = 'wholenetwork', user = '******', passwd = '')
for tr in trs:
item['brand'] = tr.xpath('dt[@class="fn-clear"]/div[@class="name"]/p/a/text()').extract()[0]
item['brandid'] = filt(tr.xpath('dt[@class="fn-clear"]/div[@class="name"]/p/a/@href').extract()[0], 'cn/', '/')
prices = tr.xpath('dd/table/tr')
for price in prices:
tmp = price.xpath('td[2]/p/text()').extract()
if not tmp: continue # filt th row
else: item['oprice'] = tmp[0]
item['oprice'] = item['oprice'].replace(u'万','')
tmp = price.xpath('td[3]/div[@class="this-number red"]/a[1]/text()').extract()
if not tmp: tmp = price.xpath('td[3]/p/a/text()').extract()
item['price'] = tmp[0].replace(u'万','').replace(u' ','')
item['pubdate'] = price.xpath('td[5]/text()').extract()[0].replace(u' ','').replace('\r\n','')
tmp = price.xpath('td[1]/a/text()').extract()[0]
item['model'] = tmp[:tmp.find('<')]
item['modelid'] = filt(price.xpath('td[1]/a/@href').extract()[0], 'spec_', '.')
if ISSAVE: db.insert('autohome_allprice', item)
if ISPOST:
tmb = doPost(API_ADDRESS, item)
log.msg('\t' + str(tmb['error']) + ', ' + tmb['msg'])
def parsePrice(self, response):
sel = Selector(response)
item = AutohomeAllPriceItem()
item['city'] = sel.xpath('//div[@class="breadnav"]/a[2]/text()').extract()[0]
item['dealer'] = sel.xpath('//div[@class="text-main"]/text()').extract()[0]
item['dealerid'] = sel.xpath('//li[@id="nav_0"]/a/@href').extract()[0].replace('/', '')
tmp = sel.xpath('//div[@class="brandtree-name"]')
tmps = ''
for t in tmp: tmps += t.xpath('p[@class="text"]/text()').extract()[0] + ','
item['manu'] = tmps[:-1]
self.logger.info(u'经销商：' + item['dealer'] + u'，\t\t\t\t主营品牌：' + item['manu'])
trs = sel.xpath('//div[@class="carprice-cont"]/dl[@class="price-dl"]')
for tr in trs:
item['brand'] = tr.xpath('dt[@class="fn-clear"]/div[@class="name"]/p/a/text()').extract()[0]
item['brandid'] = filt(tr.xpath('dt[@class="fn-clear"]/div[@class="name"]/p/a/@href').extract()[0], 'cn/', '/')
item['cartype'] = tr.xpath('dt/div[@class="info"]/p[2]/text()').extract()[0]
db = SimpleMysql(host = host, db = dbname, user = user, passwd = pswd)
prices = tr.xpath('dd/table/tr')
for price in prices:
tmp = price.xpath('td[2]/p/text()').extract()
if not tmp: continue # filt th row
item['oprice'] = tmp[0].replace(u'万','')
tmp = price.xpath('td[3]/div[@class="this-number red"]/a/text()').extract()
if not tmp: tmp = price.xpath('td[3]/p/a/text()').extract()
item['price'] = tmp[0].replace(u'万','').strip()
tmp = price.xpath('td[1]/a/text()').extract()[0]
item['model'] = tmp[:tmp.find('<')]
item['modelid'] = filt(price.xpath('td[1]/a/@href').extract()[0], 'spec_', '.')
db.insert(tablename, item)
class SetData():
def __init__(self):
dbc = ini.get_items('MySQL')
self.db = SimpleMysql(host=dbc['host'],
db=dbc['db'],
user=dbc['user'],
passwd=dbc['passwd'])
def to_mysql(self, function, kwargs):
ini = Ini()
table = ini.get_value(function, 'targetTable')
if not table: return
result = self.db.insert(table, dict(kwargs))
logging.info('Complete sync - function: %s, table: %s, result: %s' %
(function, table, result))
# 完成保存时间
ini.set_updatedate(function)
class SetData():
def __init__(self):
dbc = ini.get_items('MySQL')
self.db = SimpleMysql(host=dbc['host'],
db=dbc['db'],
user=dbc['user'],
passwd=dbc['passwd'])
def to_mysql(self, function, kwargs, time, table):
if not table: return
l, id = self.db.IsExistsinSQL(table, dict(kwargs))
if l == 0:
result = self.db.insert(table, dict(kwargs))
elif l == 1:
result = self.db.update(table, dict(kwargs), id)
self.db.commit()
logging.info('Complete sync - function: %s, table: %s, result: %s' %
(function, table, result))
class LogDatabase:
def __init__(self, **keyVals):
# saving database parameters
self.dbParams = keyVals
# table information
self.tablePrefix = 'zipnish_'
self.tables = ['spans', 'annotations']
# connect to database
self.db = SimpleMysql(
host=keyVals['host'],
db=keyVals['db'],
user=keyVals['user'],
passwd=keyVals['passwd'],
keep_alive=keyVals['keep_alive']
)
self.__create_tables()
if 'truncate_tables' in keyVals and keyVals['truncate_tables'] == True:
self.truncateTables()
def __create_tables(self):
spans_table_query = "CREATE TABLE IF NOT EXISTS zipnish_spans " \
"(span_id BIGINT NOT NULL, " \
"parent_id BIGINT, " \
"trace_id BIGINT NOT NULL, " \
"span_name VARCHAR(255) NOT NULL, " \
"debug SMALLINT NOT NULL, " \
"duration BIGINT, " \
"created_ts BIGINT);"
span_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_id);"
trace_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(trace_id);"
span_name_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_name(64));"
created_ts_index = "ALTER TABLE zipnish_spans ADD INDEX(created_ts);"
annotations_table_query = "CREATE TABLE IF NOT EXISTS zipnish_annotations " \
"(span_id BIGINT NOT NULL, " \
"trace_id BIGINT NOT NULL, " \
"span_name VARCHAR(255) NOT NULL, " \
"service_name VARCHAR(255) NOT NULL, " \
"value TEXT, " \
"ipv4 INT, " \
"port INT, " \
"a_timestamp BIGINT NOT NULL, " \
"duration BIGINT);"
span_id_key = "ALTER TABLE zipnish_annotations ADD FOREIGN KEY(span_id) " \
"REFERENCES zipnish_spans(span_id) ON DELETE CASCADE;"
trace_id_indx = "ALTER TABLE zipnish_annotations ADD INDEX(trace_id);"
span_name_index = "ALTER TABLE zipnish_annotations ADD INDEX(span_name(64));"
value_index = "ALTER TABLE zipnish_annotations ADD INDEX(value(64));"
a_timestamp_index = "ALTER TABLE zipnish_annotations ADD INDEX(a_timestamp);"
queryes = [spans_table_query, span_id_index0, trace_id_index0,
span_name_index0, created_ts_index,
annotations_table_query, span_id_key, trace_id_indx,
span_name_index, value_index, a_timestamp_index]
stmt = "SHOW TABLES LIKE 'zipnish_%'"
cursor = self.db.query(stmt)
table_count = len(cursor.fetchall())
if table_count == 0:
for query in queryes:
self.db.query(query)
self.db.commit()
def getParams(self):
return self.dbParams
def getDB(self):
return self.conn
def insert(self, tableName, rows):
table = self.tablePrefix + tableName
if len(rows) > 0:
for row in rows:
self.db.insert(table, row)
self.db.commit()
# truncate data in tables related to our application
def truncateTables(self):
print 'Truncating Tables:'
if self.db is not None and self.db.is_open():
for tableName in self.tables:
# table prefix + table name
table = self.tablePrefix + tableName
print 'truncating table -> ' + table
# delete table, and commit changes to database
self.db.delete(table)
self.db.commit()
class Database:
"""Create database from script and handling data input"""
def __init__(self, db_host, db_user, db_passwd, db_name):
"""Initialize database"""
self.db = SimpleMysql(
host=db_host,
db=db_name,
user=db_user,
passwd=db_passwd,
keep_alive=True
)
self.db.query('SET NAMES utf8;')
self.db.query('SET CHARACTER SET utf8;')
self.db.query('SET character_set_connection=utf8;')
try:
self.db.getOne('user_table', ['username'])
print 'Database working'
except Exception:
print 'Create new database'
sql_file = open(
os.path.join(
os.path.dirname(
os.path.abspath(__file__)
),
'sqlscript.sql'
), 'r'
)
sql_query = ''
for line in sql_file:
if line == 'go\n':
self.db.query(sql_query)
sql_query = ''
else:
sql_query = sql_query + line
sql_file.close()
self.register_user('admin', 'admin', 'admin', 'secret')
self.commit()
def commit(self):
"""Commit"""
self.db.conn.commit()
def username_exist(self, username):
"""Validate username"""
return True if self.db.getOne(
'user_table',
['username'],
('username=%s', [username])
) else False
def register_user(self, first_name, last_name, username, raw_password):
"""Register player to the database with username
and encrypted password"""
if self.username_exist(username):
return False
algo = 'sha1'
salt = encryption.get_hexdigest(algo, str(random.random()),
str(random.random()))[:5]
hsh = encryption.get_hexdigest(algo, salt, raw_password)
password = '******'.format(algo, salt, hsh)
self.db.insert('user_table', {
'first_name': first_name,
'last_name': last_name,
'username': username,
'password': password,
'role': 'normal'
})
return True
def login(self, username, password):
"""Check user login data, return true if the
data is matched with database"""
user_password = self.db.getOne(
'user_table',
['password'],
('username=%s', [username])
)
if user_password:
algo, salt, hsh = user_password[0].split('$')
return True if hsh == encryption.get_hexdigest(algo,
salt, password) else False
else:
return False
def get_last_insert_id(self, table):
"""Return last insert id"""
return self.db.query(
'select max(id) from %s' % (table)
).fetchone()[0]
def get_no_of_elements(self, table):
"""Return the number of record in specific table"""
return self.db.query(
'select count(id) from %s' % (table)
).fetchone()[0]
def get_no_of_elements_filter(self, table, condition):
"""Return the number of record in specific table"""
return self.db.query(
'select count(id) from %s where %s' % (table, condition)
).fetchone()[0]
def get_database(self):
return self.db
def input_drug(self):
sql_file = open(
os.path.join(
os.path.dirname(
os.path.abspath(__file__)
),
'drug.txt'
), 'r'
)
for line in sql_file:
li = re.compile("--").split(line)
self.db.insert('drug_table',
{
'name': li[0],
'unit': li[1]
}
)
sql_file.close()
self.commit()
class LogDatabase:
def __init__(self, **keyVals):
# saving database parameters
self.dbParams = keyVals
# table information
self.tablePrefix = 'zipnish_'
self.tables = ['spans', 'annotations']
# connect to database
self.db = SimpleMysql(host=keyVals['host'],
db=keyVals['db'],
user=keyVals['user'],
passwd=keyVals['passwd'],
keep_alive=keyVals['keep_alive'])
self.__create_tables()
def __create_tables(self):
spans_table_query = "CREATE TABLE IF NOT EXISTS zipnish_spans " \
"(span_id BIGINT NOT NULL, " \
"parent_id BIGINT, " \
"trace_id BIGINT NOT NULL, " \
"span_name VARCHAR(255) NOT NULL, " \
"debug SMALLINT NOT NULL, " \
"duration BIGINT, " \
"created_ts BIGINT);"
span_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_id);"
trace_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(trace_id);"
span_name_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_name(64));"
created_ts_index = "ALTER TABLE zipnish_spans ADD INDEX(created_ts);"
annotations_table_query = "CREATE TABLE IF NOT EXISTS zipnish_annotations " \
"(span_id BIGINT NOT NULL, " \
"trace_id BIGINT NOT NULL, " \
"span_name VARCHAR(255) NOT NULL, " \
"service_name VARCHAR(255) NOT NULL, " \
"value TEXT, " \
"ipv4 INT, " \
"port INT, " \
"a_timestamp BIGINT NOT NULL, " \
"duration BIGINT);"
span_id_key = "ALTER TABLE zipnish_annotations ADD FOREIGN KEY(span_id) " \
"REFERENCES zipnish_spans(span_id) ON DELETE CASCADE;"
trace_id_index = "ALTER TABLE zipnish_annotations ADD INDEX(trace_id);"
span_name_index = "ALTER TABLE zipnish_annotations ADD INDEX(span_name(64));"
value_index = "ALTER TABLE zipnish_annotations ADD INDEX(value(64));"
a_timestamp_index = "ALTER TABLE zipnish_annotations ADD INDEX(a_timestamp);"
queryes = [
spans_table_query, span_id_index0, trace_id_index0,
span_name_index0, created_ts_index, annotations_table_query,
span_id_key, trace_id_index, span_name_index, value_index,
a_timestamp_index
]
stmt = "SHOW TABLES LIKE 'zipnish_%'"
cursor = self.db.query(stmt)
table_count = len(cursor.fetchall())
if table_count == 0:
for query in queryes:
self.db.query(query)
self.db.commit()
def get_params(self):
return self.dbParams
def insert(self, table_name, rows):
table = self.tablePrefix + table_name
for row in rows:
self.db.insert(table, row)
self.db.commit()
# -*- coding: UTF8 -*-
from random import randint
from simplemysql import SimpleMysql
if __name__ == '__main__':
db = SimpleMysql(
host="127.0.0.1",
db="test",
user="******",
port=3306,
passwd="",
keep_alive=True # try and reconnect timedout mysql connections?
)
#
db.insert("person", {'name': 'zhouyu', 'age': randint(1, 100)})
# print db.lastId()
# # db.commit()
db.delete("person", ("age between %s and %s", [18, 19]))
print db.lastQuery()
rows = db.getAll("person", ["id", "name", "age"], ("name=%s and age>%s", ["zhouyu", 10]))
print rows
for row in rows:
print row.id, row.name, row.age
# person = db.getOne("person", ['id', 'name', 'age'])
person2 = db.getAll("person", ['id', 'name', 'age'], None, ["id", "asc"])
#!/usr/bin/env python
# coding: utf-8
import sys
from simplemysql import SimpleMysql
if __name__ == '__main__':
reload(sys)
sys.setdefaultencoding('utf-8')
print sys.argv[1]
db = SimpleMysql(host='127.0.0.1',
user='******',
passwd='root',
db='databank')
for i in open(sys.argv[1]):
try:
t = i.strip('\r\n').split('\t')
r = {}
r['username'] = t[0]
r['password'] = t[1]
r['email'] = t[2]
db.insert('aipai', r)
except Exception, e:
continue
db.commit()
#!/usr/bin/env python
# coding: utf-8
import sys
from simplemysql import SimpleMysql
if __name__ == '__main__':
reload(sys)
sys.setdefaultencoding('utf-8')
print sys.argv[1]
db = SimpleMysql(host='127.0.0.1', user='******', passwd='root', db='databank')
for i in open(sys.argv[1]):
try:
t = i.strip('\r\n').split('\t')
r = {}
r['username'] = t[0]
r['password'] = t[1]
r['email'] = t[2]
db.insert('aipai', r)
except Exception, e:
continue
db.commit()
