def findByTitleDate(self,title,created_at):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select id from %s where title = '%s' and created_at= '%s'" %(tbname,title,created_at))
return n.fetchAll()
def findById(self,v):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select j.id, j.title,j.content,j.view_count,j.created_at,j.category_id,c.title ctitle from %s j left join category c on j.category_id=c.category_id where j.id = %s" %(tbname,v))
return n.fetchAll()
def findByTitle(self,v):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
n.query("select name from %s where title = %s" %(tbname,v))
return n.fetchAll()
def prev_next(self,type,joke,order):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
# 按日期
if type=="prev" and order=="date":
sql = "select * from {0} where content!='' and id> {1} and created_at>='{2}'".format(tbname,joke['id'],joke['created_at'])
orders = " order by created_at asc,id asc limit 1"
if type=="next" and order=="date":
sql = "select * from {0} where content!='' and id< {1} and created_at<='{2}'".format(tbname,joke['id'],joke['created_at'])
orders = " order by created_at desc,id desc limit 1"
#按分类
if type=="prev" and order=="cate":
sql = "select * from {0} where content!='' and id> {1} and created_at>='{2}' and category_id={3}".format(tbname,joke['id'],joke['created_at'],joke['category_id'])
orders = " order by created_at asc,id asc limit 1"
if type=="next" and order=="cate":
sql = "select * from {0} where content!='' and id< {1} and created_at<='{2}' and category_id={3}".format(tbname,joke['id'],joke['created_at'],joke['category_id'])
orders = " order by created_at desc,id desc limit 1"
print "".join([sql,orders])
n.query("".join([sql,orders]))
joke = n.fetchRow()
return joke
def list(self,page, limit, cid,menu):
n = MySQL()
currRow = (int(page)-1)*int(limit)
n.selectDb('joke')
tbname = 'joke'
print "currRow:"+str(currRow)
conditions = (tbname,)
sqlCount = "SELECT FOUND_ROWS() c"
sql = "select j.id, j.title,j.view_count,j.created_at,c.title ctitle from %s j left join category c on j.category_id=c.category_id where 1=1 and j.content!='' "
if(cid):
print "cid:"+cid
sql += " AND j.category_id = %s"
conditions = conditions + (cid ,)
print "conditions:"+str(conditions)
conditions = conditions+(currRow,limit)
if(menu=="latest"):
sql += " order by j.created_at desc,j.id desc "
elif(menu=="hot"):
sql += " order by j.view_count desc,j.id desc "
sql += " limit %s,%s"
print "conditions:"+str(conditions)
sql = sql % conditions
print sql
n.query(sql)
ret = n.fetchAll()
n.query(sqlCount)
# totalCount = n.fetchAll()
totalCount = self.findCount(cid)
print "totalCount:"
print totalCount
print len(ret)
return ret,totalCount
def findAll(self):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.query("select name,features from similar_images")
return n.fetchAll()
def findByName(self,name):
n = MySQL()
n.selectDb('images')
tbname = 'similar_images'
n.query("select name,features from similar_images where name='"+name+"'")
return n.fetchRow()
def addViewCount(self,id):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
sql = "update {0} set view_count = view_count+1".format(tbname)
n.query(sql)
# n.update(tbname, { 'view_count': "view_count+1"}, "id='"+id+"'")
n.commit()
def findAll(self):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
# n.query("select name from %s where updated_at <'2016-06-27 00:00:00'" %(tbname) )
# n.query("select name from %s where is_face!=1 " %(tbname) )
n.query("select * from %s " %(tbname) )
return n.fetchAll()
def arrange(dbconfig):
db = MySQL(dbconfig)
sql = "select * from cinema_movie_info_tab"
db.query(sql)
list = []
results = db.fetchAllRows()
for row in results:
print row
list.append(row)
return list
def findCount(sefl,cid):
n = MySQL()
n.selectDb('joke')
tbname = 'joke'
conditions = (tbname,)
sqlCount = "SELECT count(id) c from %s where content!=''"
if(cid):
sqlCount += " AND category_id = %s"
conditions = conditions+(cid ,)
sqlCount = sqlCount % conditions
n.query(sqlCount)
totalCount = n.fetchAll()
return totalCount[0]['c']
def insert_data(data):
n=MySQL()
sql = "select id from urls_crawled where url='"+data['url']+"';"
result = n.query(sql)
if result == 0:
try:
n.insert('urls_crawled',data)
except:
return 200
n.commit()
def login():
data = request.json
if not data:
return jsonify({'error': 'JSON expected'}), 400
if 'username' not in data:
return jsonify({'error': 'missing key \'username\''}), 400
if 'password' not in data:
return jsonify({'error': 'missing key \'password\''}), 400
try:
db = MySQL(*db_data)
except Exception as e:
raise Exception('Database Error')
usr = db.query('SELECT password FROM itrago WHERE username = "******"'.format(data["username"]))
if usr is not None and usr.get("password") == hashlib.sha1(data['password'].encode()).hexdigest():
return jsonify({'msg': msg}), 200
else:
return jsonify({'error': 'wrong credentials'}), 400
def login():
data = request.json
if not data:
return jsonify({'error': 'JSON expected'}), 400
if 'username' not in data:
return jsonify({'error': 'missing key \'username\''}), 400
if 'password' not in data:
return jsonify({'error': 'missing key \'password\''}), 400
try:
db = MySQL(*db_data)
except Exception as e:
raise Exception('Database Error')
usr = db.query('SELECT password FROM itrago WHERE username = "******"'.format(
data["username"]))
if usr is not None and usr.get("password") == hashlib.sha1(
data['password'].encode()).hexdigest():
return jsonify({'msg': msg}), 200
else:
return jsonify({'error': 'wrong credentials'}), 400
logDir = "./log/"
sqlData="/tmp/data"
execNum = 0
dbconfig = {'host':dbconf.g_resultDBHost,
'port':dbconf.g_resultDBPort,
'user':dbconf.g_resultDBUser,
'passwd':dbconf.g_resultDBPwd,
'db':dbconf.g_resultDBName,
'charset':'utf8'}
db = MySQL(dbconfig)
#sql1 = "select tried_num from import_status_record where cid = '%s' and curr_sql_file like '%s';"%(cLists[0],'%'+sqlDate+'%')
#execNum=db.query(sql1)
#sql = "select count(*) from available_CIDs;"
sql2 = "select * from available_CIDs;"
cidsNum = db.query(sql2)
if __name__ == "__main__":
try:
#saveout = sys.stdout
#saveerr = sys.stderr
#f = open("log/import_record"+time.strftime("%m%d_%H%M%S"),"w")
#sys.stdout = f
#sys.stderr = f
#os.system('./stat.sh ' + "config")
start = time.strftime("%Y-%m-%d %H:%M:%S")
work_manager = WorkManager(cidsNum,4)
work_manager.wait_allcomplete()
end = time.strftime("%Y-%m-%d %H:%M:%S")
print "threads start times: ",start
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
sys.path.insert(0, 'lib')
from mysql import MySQL
import ConfigParser
import logging
config = ConfigParser.ConfigParser()
config.read('config/config.cfg')
db_config = config._sections['db']
db_config.pop('__name__')
a = MySQL(**db_config)
# sql = "show tables"
sql = "insert into url (post_id, url, status) values ('11', 'http://www.baidu.com', '0')"
a.query(sql)
a.commit()
# aa = a.get_all(sql)
# print aa
conn = MySQL()
conn.selectDb('twitter3')
#本地主机
#myhost = "107.191.118.80"
myhost = "127.0.0.1"
myuser = "******"
mypw = ""
myconn=MySQL(myhost,myuser,mypw)
myconn.selectDb('twitter')
#获取popularity相关数据
#retweet数目和favorite数目
sql_get_stauts="select distinct * from statusUpdate2 order by 'updatetime' desc"
results_1=conn.query(sql_get_stauts)
print u"查询条数："+str(results_1)
res_status_1 = conn.fetchAll()
for row in res_status_1:
status_id_1=row['status_id']
retweet_1=row['retweet_count']
favorite_1=row['favorite_count']
print "status:"+status_id_1
print "retweet:"+retweet_1
print "favorite:"+favorite_1
sql_check_1="select * from popularity where status_id='%s'"%(status_id_1)
check_1=myconn.query(sql_check_1)
res_1=myconn.fetchAll()
for i in res_1:
myretweet_1=i['retweet']
myfavorite_1=i['favorite']
