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