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 __init__(self):
self.db = SimpleMysql(host='127.0.0.1',
charset='utf8',
db='nissan_group',
user='******',
passwd='dndcadmin88*..',
autocommit=True,
keep_alive=False)
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 get_timestamp(timestamp_order):
ts = 0
db = SimpleMysql(**__DB_PARAMS__)
cursor = db.query("select created_ts from zipnish_spans "
"order by created_ts %s limit 1" % timestamp_order)
if cursor.rowcount:
ts = cursor.fetchone()[0]
else:
print "No timestamp found, default to 0."
cursor.close()
db.conn.close()
return ts
class Tom:
def __init__(self):
self.db = SimpleMysql(host='127.0.0.1',
charset='utf8',
db='nissan_group',
user='******',
passwd='dndcadmin88*..',
autocommit=True,
keep_alive=False)
def reg_tid(self, string):
string = string.strip()
if not string or (string.isdigit() and len(string) > 16): return None
found = re.findall(re.compile(r'\d{16}', re.IGNORECASE), string)
return found and found[-1] or None
def addAccountPageQuery(self, data):
try:
for d in data:
if d == 'merchant_out_order_no' and data[d]:
data['tid'] = self.reg_tid(data[d])
return self.db.insertOrUpdate(table='alipay_bill',
data=data,
keys=('iw_account_log_id'))
except Exception as e:
logging.error(e)
return 0
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 connect_mysql():
Container._db = SimpleMysql(
host="localhost",
db="platetype",
user="******",
passwd="plate",
keep_alive=True # try and reconnect timedout mysql connections?
)
Container._dbtype = 'mysql'
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))
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)
def parsePrice(self, response):
sel = Selector(response)
item = BitautoAllPriceItem()
item['city'] = filt(
sel.xpath('//div[@class="adress"]/text()').extract()[0], u'地址：',
u'市')
item['dealer'] = sel.xpath(
'//div[@class="info"]/h1/text()').extract()[0]
item['dealerid'] = filt(response.url, '.com/', '/')
db = SimpleMysql(host='127.0.0.1:5029',
db='wholenetwork',
user='******',
passwd='')
trs = sel.xpath('//div[@class="car_list"]')
for tr in trs:
tmp = tr.xpath('div/div[@class="car_top"]/h3/a')
item['brand'] = tmp.xpath('text()').extract()[0]
item['brandid'] = filt(
tmp.xpath('@href').extract()[0], 'cars_', '.html')
prices = tr.xpath('div/div[@class="car_price"]/table/tbody/tr')
for price in prices:
if not price.xpath('td'): continue # filt th rows
item['model'] = price.xpath('td[1]/a/@title').extract()[0]
item['modelid'] = filt(
price.xpath('td[1]/a/@href').extract()[0], 'price_detail/',
'.html')
item['oprice'] = price.xpath(
'td[2]/text()').extract()[0].replace(u' ', '').replace(
'\r\n', '').replace(u'万', '')
item['price'] = price.xpath(
'td[4]/a/text()').extract()[0].replace('\r\n', '').replace(
u' ', '').replace(u'万', '')
item['off'] = price.xpath(
'td[3]/em/text()').extract()[0].replace(
'\r\n', '').replace(u' ',
'').replace(u'万',
'').replace(u'↓', '')
if ISSAVE: doSave(db, item)
if ISPOST: doPost(API_ADDRESS, item)
np = sel.xpath('//div[@id="pager"]/a')
while np and (np[-1].xpath('text()').extract()[0] == u'下一页'):
url = np[-1].xpath('@href').extract()[0]
url = response.urljoin(url)
yield Request(url, self.parsePrice)
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()
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 Tom:
def __init__(self):
self.db = SimpleMysql(host='127.0.0.1', charset='utf8', db='nissan_group', user='******', passwd='dndcadmin88*..', autocommit=True, keep_alive=False)
def reg_tid(self, string):
string = string.strip()
if not string or (string.isdigit() and len(string)>16): return None
found = re.findall(re.compile(r'\d{16}', re.IGNORECASE), string)
return found and found[-1] or None
def addAccountPageQuery(self, data):
try:
for d in data:
if d == 'merchant_out_order_no' and data[d]:
data['tid'] = self.reg_tid(data[d])
return self.db.insertOrUpdate(table='alipay_bill', data=data, keys=('iw_account_log_id'))
except Exception as e:
logging.error(e)
return 0
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()
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()
#!/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()
def setUpClass(cls):
cls.simplemysql = SimpleMysql(lambda: sqlite3.connect(':memory:'),
DialectSQLite3())
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()
#!/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()
def __init__(self):
self.db = SimpleMysql(host='127.0.0.1', charset='utf8', db='nissan_group', user='******', passwd='dndcadmin88*..', autocommit=True, keep_alive=False)
def connect(host, db, user, passwd):
logging.info('Connecting to host ' + host + '...')
tmp = SimpleMysql(host=host, db=db, user=user, passwd=passwd)
if tmp: logging.info('Connected to host ' + host + '.')
return tmp
#!/usr/bin/env python
# -*- 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'])
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()
# scrapy crawl autohomeallpromotion -s JOBDIR=autohomeallpromotion
import sys, datetime, urllib, urllib2, json
from scrapy.spider import BaseSpider
from scrapy.selector import Selector
from scrapy.http import Request
from chebaba.items import AutohomeAllPromotionTitleItem
from simplemysql import SimpleMysql
from HTMLParser import HTMLParser
ISSAVE = False
ISPOST = False
NISSAN_ONLY = False
if ISSAVE:
db = SimpleMysql(host='127.0.0.1:5029',
db='wholenetwork',
user='******',
passwd='')
def doSave(item):
#return db.insert('autohome_allpromotiontitle', item)
return db.insertOrUpdate('autohome_allpromotiontitle', item,
['titleid', 'pubdate'])
def getBrands(array):
if not array: return None
brands = []
for a in array:
if a:
brands.append(a.extract())
def __init__(self):
dbc = ini.get_items('MySQL')
self.db = SimpleMysql(host=dbc['host'],
db=dbc['db'],
user=dbc['user'],
passwd=dbc['passwd'])
def u(s, encoding):
if isinstance(s, unicode): return s
return unicode(s, encoding)
def post(data):
f = urllib2.urlopen(
'',
urllib.urlencode(data)
)
return f.read()
if __name__ == '__main__':
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
conn = SimpleMysql(host="127.0.0.1", db='locoyspider', user='******', passwd='root')
results = conn.getAll("data_content_153", ['dealer', 'dealerid', 'modelid', 'model', 'price', 'oprice'])
a = open('r.txt', 'w')
for result in results:
data = {}
data['dealer'] = result[0].encode('utf-8')
data['dealerid'] = result[1].encode('utf-8')
data['modelid'] = result[2].encode('utf-8')
data['model'] = result[3].encode('utf-8')
data['price'] = result[4].encode('utf-8')
data['oprice'] = result[5].encode('utf-8')
r = post(data)
print r
a.write(r + '\n')
a.close()
def connect(host, db, user, passwd):
print '[INFO]', 'Connecting to host', host, '...'
tmp = SimpleMysql(host=host, db=db, user=user, passwd=passwd)
if tmp: print '[INFO]', 'Connected to host', host, '.'
return tmp
# -*- coding: utf-8 -*-
import scrapy
import re
import json
from scrapy.selector import Selector
from scrapy.http import Request
from simplemysql import SimpleMysql
def regx(patern, string):
regx = re.findall(re.compile(patern, re.IGNORECASE), string.strip())
return regx and regx[0] or None
_db = SimpleMysql(host='127.0.0.1', db='autohome', user='******', passwd='root', autocommit=True)
class CarSpider(scrapy.Spider):
name = "car"
allowed_domains = ['www.autohome.com.cn', 'k.autohome.com.cn']
start_urls = [
# 'http://www.autohome.com.cn/grade/carhtml/R.html',
'http://www.autohome.com.cn/grade/carhtml/'+C+'.html' for C in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
]
def parse(self, response):
# yield Request('http://k.autohome.com.cn/121/', self.parse_koubei)
# return
sel = Selector(response)
brands = sel.xpath('//dl')
item = {}
for brand in brands:
brand_name = brand.xpath('dt/div/a/text()').extract()
item['brand_name'] = brand_name and brand_name[0] or None
def getPrices(city, brand, dealer):
db = SimpleMysql(host='localhost', db='locoyspider', user='******', passwd='123456', keep_alive=True)
return db.getAll('data_content_151', ['modelname', 'model', 'oprice', 'price'], ('city=%s and brand=%s and dealer=%s', [city, brand, dealer]))
def setUpClass(cls):
cls.simplemysql = SimpleMysql(_connection_factory)
def setUpClass(cls):
cls.simplemysql = SimpleMysql(_connection_factory, DialectPostgres())
def run_sql(query, *args):
db = SimpleMysql(**__DB_PARAMS__)
db.query(query, args)
db.commit()
db.conn.close()
