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()