def getAllTable(oper): dbconfig = { 'host': '120.92.224.183', 'port': 3306, 'user': '******', 'passwd': '5aRW7x8gFGNUB0dt', 'db': 'original_log_new', 'charset': 'utf8' } tableList = [] db = mysql.MySQL(dbconfig) tableName = 'center_userlogin_' + oper + "%" wanName = 'center_userlogin_51wan' + "%" if oper == '51': sql = "select table_name from information_schema.tables where table_name like '%s' and table_name not like '%s'" % ( tableName, wanName) else: sql = "show tables like '%s'" % tableName db.query(sql) result = db.fetchAllRows() for row in result: for colum in row: tableList.append(colum) db.close return tableList
def setUp(self): self.mysql = mysql.MySQL() self.session = self.mysql.session() mysql.drop_database() mysql.create_database() mysql.Base.metadata.create_all(self.mysql.engine)
def InLog(md5, data): try: db = mysql.MySQL() stime = time.strftime('%Y-%m-%d %H:%M:%S') value = [md5, stime, data] db.insert('insert into info_blind_log values(NULL,%s,%s,%s)', value) db.commit() db.close() except Exception, e: print Exception, ":", e db.close()
def start_requests(self): self.db = mysql.MySQL(DB_CONFIG['host'], DB_CONFIG['user'], DB_CONFIG['passwd'], DB_CONFIG['db'], DB_CONFIG['port'], DB_CONFIG['charset'], DB_CONFIG['timeout'], '') return [ scrapy.FormRequest("http://bbs.byr.cn/user/ajax_login.json", formdata=const.LOGIN_DATA, meta={'cookiejar': 1}, headers=self.headers, callback=self.logged_in) ]
def connectDB(): global db if db is None: settings = get_project_settings() db = mysql.MySQL({ 'host': settings.get('MYSQL_HOST'), 'port': settings.get('MYSQL_PORT'), 'user': settings.get('MYSQL_USER'), 'passwd': settings.get('MYSQL_PASSWD'), 'db': settings.get('MYSQL_DBNAME'), }) return db
def get_mysql(): cwd = os.getcwd() config = ConfigParser.ConfigParser() config.read("/Users/yangyuncai/test/crawl/config/global.conf") host = config.get("db", "host") port = config.get("db", "port") user = config.get("db", "user") password = config.get("db", "password") database = config.get("db", "database") db = mysql.MySQL(host, port, user, password) db.selectDb(database) return db
def getMysql(): cwd = os.getcwd() config = ConfigParser.ConfigParser() config.read(cwd + "/config/global.conf") # db db_host = config.get("db", "host") db_port = config.get("db", "port") db_user = config.get("db", "user") db_password = config.get("db", "password") db_database = config.get("db", "database") resource_db = mysql.MySQL(db_host, db_port, db_user, db_password) resource_db.selectDb(db_database) return resource_db
def getOperList(): dbconfig = { 'host': '120.92.224.183', 'port': 3306, 'user': '******', 'passwd': '5aRW7x8gFGNUB0dt', 'db': 'gm', 'charset': 'utf8' } operList = [] db = mysql.MySQL(dbconfig) sql = "select name from admin_oper" db.query(sql) result = db.fetchAllRows() for row in result: for colum in row: operList.append(colum) db.close() return operList
def select_info(self, tag=0): try: sql = mysql.MySQL(*self.cloud_db) if tag == 0: sql_string = "SELECT id, file_name, uri" + \ " FROM " + self.db_table + \ " WHERE xss_state LIKE 0 AND sql_state LIKE 0 LIMIT 1" if tag == 1: sql_string = "" ret_tuple = sql.get_value(sql_string) if ret_tuple is not None: sql_string = "UPDATE " + self.db_table + \ " SET xss_state = 1, sql_state = 1" + \ " WHERE id LIKE " + str(ret_tuple[0]) sql.insert_value(sql_string) return ret_tuple return None except Exception, e: log.output_log("[error] " + str(e)) return None
def insert_info_s(self, insert_info_list, tag=0): try: sql = mysql.MySQL(*self.cloud_db) if tag == 0: for insert_info in insert_info_list: vul_detail = insert_info[3].replace("'", "\\'") sql_string = "INSERT INTO " + self.db_table + "(scan_id, file_name, vul_type, vul_detail, vul_url) values(" + \ str(insert_info[0]) + ", '" + \ insert_info[1] + "', '" + \ insert_info[2] + "', '" + \ vul_detail + "', '" + \ insert_info[4] + "')" sql.insert_value_not_commit(sql_string) sql.commit() elif tag == 1: sql_string = '' sql.insert_value(sql_string) return True except Exception, e: log.output_log("[error] " + str(e)) return False
def gmBaobiao(request): dbconfig = { 'host': '120.92.224.183', 'port': 3306, 'user': '******', 'passwd': '5aRW7x8gFGNUB0dt', 'db': 'original_log_new', 'charset': 'utf8' } db = mysql.MySQL(dbconfig) operList = [] rDict = {} sTimeStamp = toTimeStamp(request.GET.get('starttime', '')) if not sTimeStamp: return render(request, "gmBaobiao.html") oper = request.GET.get('oper', '') if oper == 'all': operList = getOperList() elif oper == '': return render(request, "gmBaobiao.html") else: operList.append(oper) for oper in operList: sum = 0 tableList = getAllTable(oper) for table in tableList: sql = "select count(distinct(username)) from %s where time>=%s" % ( table, sTimeStamp) db.query(sql) result = db.fetchAllRows()[0][0] sum += result rDict[oper] = sum #print "%s:%s" %(oper,sum) #print rDict db.close() #print rDict return render_to_response('gmBaobiao.html', {'rDict': rDict})
def app(): app = flask.Flask("nandy-io-speech-api") app.mysql = mysql.MySQL() app.redis = redis.StrictRedis(host=os.environ['REDIS_HOST'], port=int(os.environ['REDIS_PORT'])) app.channel = os.environ['REDIS_CHANNEL'] if os.path.exists("/var/run/secrets/kubernetes.io/serviceaccount/token"): app.kube = pykube.HTTPClient(pykube.KubeConfig.from_service_account()) else: app.kube = pykube.HTTPClient(pykube.KubeConfig.from_url("http://host.docker.internal:7580")) api = flask_restful.Api(app) api.add_resource(Health, '/health') api.add_resource(PersonCL, '/person') api.add_resource(PersonRUD, '/person/<int:id>') api.add_resource(TemplateCL, '/template') api.add_resource(TemplateRUD, '/template/<int:id>') api.add_resource(AreaCL, '/area') api.add_resource(AreaRUD, '/area/<int:id>') api.add_resource(AreaA, '/area/<int:id>/<action>') api.add_resource(ActCL, '/act') api.add_resource(ActRUD, '/act/<int:id>') api.add_resource(ActA, '/act/<int:id>/<action>') api.add_resource(ToDoCL, '/todo') api.add_resource(ToDoRUD, '/todo/<int:id>') api.add_resource(ToDoA, '/todo/<int:id>/<action>') api.add_resource(RoutineCL, '/routine') api.add_resource(RoutineRUD, '/routine/<int:id>') api.add_resource(RoutineA, '/routine/<int:id>/<action>') api.add_resource(TaskA, '/routine/<int:routine_id>/task/<int:task_id>/<action>') return app
def run(st, mongocls, session_id): data = None st.text("Connect") container = st.beta_container() ds_selection = container.selectbox( "Data Sources", ['csv', 'txt', 'mysql', 'sql', 'parquet', 'folder']) if ds_selection == 'csv' or ds_selection == 'txt': upload_path = st.file_uploader('Upload Dataset', type=["csv", "txt"]) sub_container = st.beta_container() c1, c2, c3 = sub_container.beta_columns(3) header = c1.text_input("Header") separator = c2.text_input("Enter Seperator") if header == '': header = None else: header = eval(header) if separator == '': separator = ',' kwargs = c3.text_area("Additional Parameters (keyword arg format)") if kwargs == '': kwargs = None else: kwargs = eval(kwargs) connect_btn = st.button("Execute Query !!!") if connect_btn: if upload_path is None: st.warning("Select upload file!!!") else: if kwargs is None: data = load_data(path=upload_path, ds_type=ds_selection, header=header, sep=separator) #mongocls.delete_session({'session_id':session_id+'_df'}) #mongocls.write_session_info({'session_id':session_id+'_df','data_dict':data.to_dict("records")}) else: data = load_data(path=upload_path, ds_type=ds_selection, header=header, sep=separator) #mongocls.delete_session({'session_id':session_id+'_df'}) #mongocls.write_session_info({'session_id':session_id+'_df','data_dict':data.to_dict("records")}) elif ds_selection == 'parquet': upload_path = st.file_uploader('Upload Dataset', type=["parquet"]) connect_btn = st.button("Execute Query !!!") if connect_btn: if upload_path is None: st.warning("Select upload file!!!") else: data = load_data(path=upload_path, ds_type=ds_selection) #mongocls.delete_session({'session_id':session_id+'_df'}) #mongocls.write_session_info({'session_id':session_id+'_df','data_dict':data.to_dict("records")}) elif ds_selection == 'mysql': sub_container = st.beta_container() host = sub_container.text_input("Enter Host:", value='localhost') user = sub_container.text_input("Enter User:"******"Enter Password:"******"Enter port:", min_value=0, max_value=999999, step=1, value=3306) mysql_cls = mysql.MySQL(host=host, user=user, password=password, port=port) database_list = mysql_cls.get_data_from_RDS('SHOW DATABASES;') database = st.selectbox("Databases", database_list.Database.tolist()) mode = st.radio("Mode", ['Table', 'Query']) if database != '': if mode == 'Table': table_lists = mysql_cls.get_data_from_RDS( "SELECT table_name FROM information_schema.tables WHERE table_schema = '" + database + "';") table_selected = st.selectbox("Tables", table_lists.iloc[:, 0].tolist()) else: query = st.text_area("Enter your query") connect_btn = st.button("Execute Query !!!") if connect_btn: if mode == 'Table': data = load_data(path=None, ds_type=ds_selection, **{ 'mode': mode, 'class': mysql_cls, 'database': database, 'table_selected': table_selected }) # data = mysql_cls.get_data_from_RDS("Select * from "+database+"."+table_selected) st.success('Query executed successfully!!!') else: data = load_data(path=None, ds_type=ds_selection, **{ 'mode': mode, 'class': mysql_cls, 'query': query }) # data = mysql_cls.get_data_from_RDS(query) st.success('Query executed successfully!!!') #mongocls.delete_session({'session_id':session_id+'_df'}) #mongocls.write_session_info({'session_id':session_id+'_df','data_dict':data.to_dict("records")}) else: st.warning("Select a Database!!!") if data is not None: st.text('Data Loaded') mongocls.delete_session({'session_id': session_id + '_df'}) mongocls.write_session_info({ 'session_id': session_id + '_df', 'data_dict': data.to_dict("records") }) st.write(data.head()) #session.write_state_df(data, engine, session_id + '_df') return data else: st.warning('No data!!!')
def mysql_server(request): mysql_server = mysql.MySQL(base_dir) mysql_server.start() yield mysql_server mysql_server.stop()
#!/usr/bin/env python import mysql mysql.create_database() data = mysql.MySQL() mysql.Base.metadata.create_all(data.engine)
#-*-coding:utf-8-*- import datetime import web import mysql ldb = mysql.MySQL(dbase="hello") urls = ("/root/(.*)", "root", "/log/(.*)", "log", "/index", "index", "/add/$", "add", "/reply/$", "reply", "/delete/$", "delete", "/", "hello") app = web.application(urls, globals()) db = web.database(dbn="mysql", db="hello", user="******", pw="root") render = web.template.render("templates/", cache=False) class root: def GET(self, form): form = { 'id': u'', 'name': u'', 'passwd': u'', 'email': u'', 'sex': u'', 'result': '' } #return form return render.root(form) pass
def __init__(self): self.page_num = 1 self.total_num = None self.page_spider = page.Page() self.mysql = mysql.MySQL()
def main(): '''main ''' begin_time = datetime.datetime.strftime(datetime.datetime.today(), '%Y/%m/%d %H:%M:%S') path = '../data/club_autohome.csv' # /home/allfine/crontab/ logpath = '../log/club_autohome.log' # /home/allfine/crontab/ database = 'industrial_added_value' table = 'club_autohome' titles = ['club_autohome_id', 'int not null auto_increment', 'website', 'varchar(100)', 'title', 'varchar(100)', 'poster', 'varchar(100)', 'post_time', 'date', 'click_rate', 'int', 'reply_rate', 'int', 'update_time', 'datetime', 'note', 'varchar(30)'] columns = ['website', 'title', 'poster', 'post_time', 'click_rate', 'reply_rate', 'update_time'] num_row = 0 writefile.writecsv(columns, num_row=num_row, path=path, logpath=logpath) begin_date = datetime.date.today() - datetime.timedelta(1) end_date = datetime.date.today() - datetime.timedelta(1) sql = mysql.MySQL(passwd='171320', logpath=logpath) info = sql.usedatabase(database) # if info < 0: # return 0 sql.createtable(table, titles) maxpage = 10 exit_flag = False for i in range(0, maxpage): url = 'https://club.autohome.com.cn/bbs/forum-a-100001-'\ +str(i+1)+'.html?qaType=-1#pvareaid=101061' # 帖子网址 帖子标题 发帖人 发贴时间 点击量 回复量 最后回复时间 (其中,点击量 回复量因为异步加载,为空) reg = r'<dl class="list_dl".*?<a.*?href="(.*?)">(.*?)</a>.*?<a.*?>(.*?)</a><span class="tdate">(.*?)</span>.*?<span class="fontblue">(.*?)</span>.*?<span class="tcount">(.*?)</span>.*?<span class="ttime">(.*?)</span>.*?</dl>' print('正在爬取第'+str(i+1)+'页') items, info = webparse.parse(url, reg, logpath=logpath) if info < 0: continue for item in items: item = list(item) for j, ite in enumerate(item): item[j] = clear.filter_tags(ite.strip()) item_date = datetime.datetime.strptime(item[-1], '%Y-%m-%d %H:%M').date() if item_date < begin_date: if i == 0: continue else: exit_flag = True break if item_date > end_date: continue item[-1] = item[-1] + ':00' # 匹配:点击量 回复量 url1 = 'http://club.autohome.com.cn'+item[0] reg1 = r'<font id="x-views">(.*?)</font>.*?<font.*?>(.*?)</font>' items1, info = webparse.parse(url1, reg1, logpath=logpath) if info < 0: print('帖子获取失败'+str(url1)) item[4] = -1 item[5] = -1 num_row += 1 writefile.writecsv(item, num_row=num_row, path=path, logpath=logpath) sql.insertinto(table, item, columns=columns) else: item[4] = items1[0][0] item[5] = items1[0][1] num_row += 1 writefile.writecsv(item, num_row=num_row, path=path, logpath=logpath) sql.insertinto(table, item, columns=columns) if exit_flag: break num_sqlrow, info = sql.querymysql(table) sql.close() end_time = datetime.datetime.strftime(datetime.datetime.today(), '%Y/%m/%d %H:%M:%S') with open(logpath, 'a') as fla: fla.write('From '+begin_time+' to '+end_time+', we crawled '+str(num_row)+' rows'+'\n') fla.write('There are '+str(num_sqlrow)+' rows in mysql\t'+end_time+'\n') print('From '+begin_time+' to '+end_time+', we crawled '+str(num_row)+' rows') print('There are '+str(num_sqlrow)+' rows in mysql\t'+end_time)