Python

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