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)
