python 片段:
1.excel操作
#!/usr/bin/python
# -*- coding: cp936 -*-
'''
get relation_tag from excel
'''
import xlrd
import MySQLdb
import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )
try:
#写库
conn = MySQLdb.connect(host="localhost",port=3307,user="user",passwd="pwd",db="db",charset="utf8")
cursor = conn.cursor()
isql = "insert into t_relation_tags(tag_id,rel_tag_id) (select id,-1 from t_tags where tag_name = %s)"
usql = "update t_relation_tags set rel_tag_id = (select ifnull(id,-1) from t_tags where tag_name = %s) where rel_tag_id = -1"
data = xlrd.open_workbook('.\20160229.xlsx')
table = data.sheets()[0]
nrows = table.nrows
for i in range(nrows):
tagName = (table.row_values(i)[0])
#参数2使用元组
cursor.execute(isql,(tagName,))
#conn.commit()
relTagName = (table.row_values(i)[1])
print usql %(relTagName)
cursor.execute(usql,(relTagName,))
conn.commit()
conn.close()
except MySQLdb.Error,e:
conn.close()
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
注意 cursor.execute(usql,(relTagName,))
python1.2.5第二个参数必须使用元组形式
2.redis
#!/usr/bin/python
'''
sync tags at first time
'''
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
raw_out = sys.stdout
flog=open('tag.log','aw')
sys.stdout=flog
import time
def log(*s):
print time.strftime('%Y-%m-%d %H:%M:%S'),
for ss in s:
print ss,
print
flog.flush()
import redis
import MySQLdb
#redis写库
HOST_NAME='localhost'
PORT=6379
#读库
conn = MySQLdb.connect(host="localhost",port=3307,user="user",passwd="pwd",db="db",charset="utf8")
cursor = conn.cursor()
pool = redis.ConnectionPool(host=HOST_NAME,port=PORT,db=0)
r = redis.Redis(connection_pool=pool)
def tag_catch():
sql = "select t.id,t.tag_name,count(tag_id) from t_tags t left join t_app_tags t2 on t.id = t2.tag_id group by t.id";
#print "sql"+sql
count = cursor.execute(sql)
return cursor.fetchall()
def cache_tags(tags):
for tag in tags:
dic = {"id":str(tag[0]),"tag":str(tag[1]),"count":str(tag[2])}
r.hmset("appinfo:tag:"+str(tag[0]),dic);
if __name__ == "__main__":
stime = time.time()
tags = tag_catch()
cache_tags(tags)
log("cache model infos takes:"+ str(time.time()-stime));