这段python要怎么优化好呢,感觉执行的SQL一段一段的写,太多了,怎么优化?
#! /usr/bin/python
from future import division
import sys
import os
import mysql.connector
import time
import datetime
db = mysql.connector.connect(user='root',password='123456',host='192.168.0.1',database='test',charset='utf8')
today = time.strftime('%Y-%m-%d') + '-23'
cursor = db.cursor()
sqla = "select id from test.student"
cursor.execute(sqla)
row1 = cursor.fetchall()
data = list(row1)
for id in data:
cursor1 = db.cursor()
#new add user
sql1 = "select count(*) from test.account a where DATE_FORMAT(a.createtime,'%%Y-%%m-%%d')=curdate() and year='2010' and province = %s"
cursor1.execute(sql1,id)
row2 = cursor1.fetchone()
a = list(row2)
print a
cursor1 = db.cursor()
#total user
sql2 = "select count(*) from test.account a where year='2010' and province = %s"
cursor1.execute(sql2,id)
row3 = cursor1.fetchone()
b = list(row3)
cursor1 = db.cursor()
sql3 = "select count(*) from test.account a where last_sync=curdate() and year='2010' and province = %s"
cursor1.execute(sql3,id)
row4 = cursor1.fetchone()
c = list(row4)
cursor1 = db.cursor()
sql4 = "select count(*) from test.account where year='2010' and DATE_FORMAT(createtime,'%%Y-%%m-%%d')=date_sub(curdate(),interval 1 day) and last_sync=curdate() and province = %s"
cursor1.execute(sql4,id)
row5 = cursor1.fetchone()
d = list(row5)
cursor1 = db.cursor()
sql5 = "select count(*) from test.account where year='2010' and DATE_FORMAT(createtime,'%%Y-%%m-%%d')=date_sub(curdate(),interval 7 day) and last_sync=curdate() and province = %s"
cursor1.execute(sql5,id)
row6 = cursor1.fetchone()
e = list(row6)
cursor1 = db.cursor()
sql6 = "select count(*) from test.account where year='2010' and last_sync <= date_sub(curdate(),interval 21 day) and province = %s"
cursor1.execute(sql6,id)
row7 = cursor1.fetchone()
f = list(row7)
cursor1 = db.cursor()
sql7 = "select count(*) from test.account where year='2010' and last_sync >= date_sub(curdate(),interval 7 day) and province = %s"
cursor1.execute(sql7,id)
row8 = cursor1.fetchone()
g = list(row8)
cursor1 = db.cursor()
sql8 = ("insert into test.visitor(stat_date,id,new_user_num,updata_user_num,total_user_num,after_active_num,after_10_active_num,sleep_user_num,week_active_num) "
"values(%s,%s,%s,%s,%s,%s,%s,%s,%s) ")
cursor1.execute(sql8,(today,id[0],a[0],b[0],c[0],d[0],e[0],f[0],g[0]))
row9 = cursor1.fetchone()
db.commit()
else:
print "insert is completed"
##########################################update name########################################33
cursor2 = db.cursor()
sql80 = ("update test.visitor a inner join (select id,name from test.student) b on a.student_id=b.id "
"set a.name=b.name "
"where a.stat_date=concat(curdate(),'-23') ")
cursor2.execute(sql80)
row6 = cursor2.fetchone()
db.commit()
print "update is completed"