Python 取 mysql 数据库操作

#!/bin/env python
#coding=utf-8
import os
import sys
import cgi
import cgitb
import re
import MySQLdb
from urllib import unquote
from comm import StrMD5
from comm import StrSqlHost
from comm import StrSqlUser
from comm import StrSqlPwsd
from comm import SessionLogin
from comm import SessionCheck

SIZE_PAGE = 10
print "Content-type:text/html\n\n"

try:
	form = cgi.FieldStorage()
	sess = form.getvalue('se')
	tasktype = form.getvalue('tasktype')
	account = form.getvalue('ac')
	role = form.getvalue('role')
	lang = form.getvalue('lang')
except:
	print "{\"Result\":false,\"ErrMsg\":\"system err\"}\n" 
	sys.exit()

client_ip = os.environ['REMOTE_ADDR']

if role < 0:
	role = "0"

if sess < 0:
	print "{\"Result\":false,\"ErrMsg\":\"no session\"}\n" 
	sys.exit()

try:
	conn = MySQLdb.connect(host = StrSqlHost(), user = StrSqlUser(), passwd = StrSqlPwsd())
except:
	print "{\"Result\":false,\"ErrMsg\":\"Database connection timeout(%d)\"}\n" %(sys._getframe().f_lineno, error)
	sys.exit()
	
error = SessionCheck(conn, sess, account)

if error < 0:
	conn.close()
	
	print "{\"Result\":false,\"ErrMsg\":\"system busy(%d/%d)\"}\n" %(sys._getframe().f_lineno, error)
	sys.exit()
elif error > 0:
	conn.close()

	print "{\"Result\":false,\"ErrMsg\":\"system timeout(%d)\"}\n" 	%(sys._getframe().f_lineno, error)
	sys.exit()

try:
	curs = conn.cursor()
except:
	conn.close()
	print "{\"Result\":false,\"ErrMsg\":\"system busy(%d)\"}\n" %(sys._getframe().f_lineno)
	sys.exit()


#lang
if tasktype=="10":
	sql = "UPDATE pavenas.manager SET lang=%s WHERE account='%s';" %(lang, account)	
	
	try:
		curs.execute(sql)
		conn.commit()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"no lang\"}"
		conn.close()
		sys.exit()
	
	print "{\"Result\":\"su\",\"ErrMsg\":\"}"
	curs.close()	
	conn.close()
	sys.exit()
	
#add	
if tasktype=="1":
	UserName = form.getvalue('UserName')
	name = form.getvalue('na')
	dept = form.getvalue('dept')
	phone = form.getvalue('phone')
	email = form.getvalue('email')	
	uower = form.getvalue('uower')	
	pwd = form.getvalue('pwd')	

#	if name :
#		name = urllib.unquote(name)
#	if dept :
#		dept = urllib.unquote(dept)	
	if dept<0 :
		dept = ""
	if phone<0 :
		phone = ""
	if email<0 :
		email = ""		

	pwd = pwd.strip()
	sql = "insert into pavenas.manager (account, pwd,name,role,dept,mobile,mail) values('%s', '%s', '%s', %s, '%s', '%s','%s');" %(MySQLdb.escape_string(UserName),MySQLdb.escape_string(pwd),MySQLdb.escape_string(name),uower,MySQLdb.escape_string(dept),MySQLdb.escape_string(phone),MySQLdb.escape_string(email))
	
	try:
		curs.execute(sql)
		conn.commit()
	except:
		#print e.pgerror
		if lang == "1":
			print "{\"Result\":false,\"ErrMsg\":\"已有同名账号！\"}"
		else: 
			print "{\"Result\":false,\"ErrMsg\":\"Has the same name accounts!\"}"

		curs.close()
		conn.close()

		sys.exit()

	print "{\"Result\":\"操作成功\",\"ErrMsg\":\"\"}"	
	
	#系统日志
	#str = "成功"
	#str = str.decode('utf-8')
	sql= "insert into pavenas.logs (account,ip,clas,oper,memo) values('%s', '%s', %s, 'add account(%s)', 'success')" %(MySQLdb.escape_string(account),MySQLdb.escape_string(client_ip), uower, MySQLdb.escape_string(UserName))
	try:
		curs.execute(sql)
		conn.commit()
	except:
		#print e.pgerror
		print "{\"Result\":false,\"ErrMsg\":\"Add system error log!\"}"

		curs.close()
		conn.close()
		sys.exit()	

	curs.close()
	conn.close()
	sys.exit()	

#删除	
if tasktype == "2":
	userid_array = form.getvalue('userid_array')
	if userid_array < 0:
		print "{\"Result\":false,\"ErrMsg\":\"Delete error!\"}"
		conn.close()
		sys.exit()	
	
	str = userid_array.split(',')

	len = len(str) - 1

	if len :
		i = 0
		while(i<len):	
			sql = "SELECT account from pavenas.manager where id=%s;" %(str[i])
			try:
				curs.execute(sql)
				results = curs.fetchall()
			except MySQLdb.Error, e:
				print "{\"Result\":false,\"ErrMsg\":\"Delete the system error log!\"}"
				conn.close()
				sys.exit()	
	

			account1 = results[0][0]	
				
			sql = "delete from pavenas.manager where id=%s;"  %(str[i])
			try:
				curs.execute(sql)
				conn.commit()
			except:
				print "{\"Result\":false,\"ErrMsg\":\"Delete the system error log!\"}"
				conn.close()
				sys.exit()	
	
					
			sql = "insert into pavenas.logs (account,ip,clas,oper,memo) values('%s', '%s', %s, 'del(%s)', 'success');" %(MySQLdb.escape_string(account), MySQLdb.escape_string(client_ip),role, MySQLdb.escape_string(account1))
					
			try:
				curs.execute(sql)
				conn.commit()
			except:
				print "{\"Result\":false,\"ErrMsg\":\"Delete the system error log!\"}"
				conn.close()
				sys.exit()
			i = i+1

		print "{\"Result\":\"删除成功\",\"ErrMsg\":\"\"}"
		curs.close()
		conn.close()
		sys.exit()
	else:
		print "{\"Result\":false,\"ErrMsg\":\"Id get wrong\"}"
		curs.close()	
		conn.close()
		sys.exit()

#用户信息
if tasktype == "4":
	userid = form.getvalue('userid')
	
	sql =  "SELECT * from pavenas.manager WHERE id = %s;" %(userid)
	
	try:
		curs.execute(sql)
		results = curs.fetchall()
	except MySQLdb.Error, e:
		print "{\"Result\":false,\"ErrMsg\":\"result error!\"}"
		conn.close()
		sys.exit()	
	
	
	UserName = results[0][1].encode('utf-8')
	name =  results[0][3].encode('utf-8')
	
	ouwer_sgin = results[0][4]
	if len(results[0][5]) <=0:
		DepartMent = ""
	else:
		DepartMent = results[0][5].encode('utf-8')
	if len(results[0][6])<=0:
		phone = ""
	else:
		phone = results[0][6].encode('utf-8') 
	if len(results[0][7])<=0:
		EmailAddress = ""
	else:
		EmailAddress = results[0][7].encode('utf-8')


	print "{\"UserName\":\"%s\",\"name\":\"%s\",\"uower\":%d,\"DepartMent\":\"%s\",\"phone\":\"%s\",EmailAddress:\"%s\"}" %(UserName, name, ouwer_sgin, DepartMent, phone, EmailAddress)
	
	curs.close()	
	conn.close()
	sys.exit()	

		
#修改
if tasktype == "3":
	userid = form.getvalue('userid')
	pwd_sgin = form.getvalue('pwd_sgin')
	name = form.getvalue('na')
	dept = form.getvalue('dept')	
	phone = form.getvalue('phone')
	email = form.getvalue('email')	
	uower = form.getvalue('uower')	
	pwd = form.getvalue('pwd')	

	if name < 0:
		name = ""	
	#else:
		#name = unquote(name)
	if dept < 0:
		dept = ""
	#else:
		#dept =unquote(dept)

	
	
	if phone < 0:
		phone = ""
	if email < 0:
		email = ""	

		
	if pwd_sgin == "1":
		pwd = pwd.strip()
		sql = "UPDATE pavenas.manager SET  pwd='%s',name='%s', dept='%s', mobile='%s',mail='%s' WHERE id=%s;" %(MySQLdb.escape_string(pwd),MySQLdb.escape_string(name).decode('utf-8'), MySQLdb.escape_string(dept).decode('utf-8'), MySQLdb.escape_string(phone),MySQLdb.escape_string(email), userid)
	else:
		sql = "UPDATE pavenas.manager SET name='%s', dept='%s', mobile='%s', mail='%s' WHERE id=%s;" %(MySQLdb.escape_string(name), MySQLdb.escape_string(dept), MySQLdb.escape_string(phone), MySQLdb.escape_string(email),  userid)

	try:
		curs.execute(sql)
		conn.commit()
	except :
		print "{\"Result\":false,\"ErrMsg\":\"result error1!\"}"
		conn.close()
		sys.exit()	

	print "{\"Result\":\"操作成功\",\"ErrMsg\":\"\"}"


	#系统日志				
	sql = "SELECT account from pavenas.manager where id=%s;" %(userid)	
	
	try:
		curs.execute(sql)
		results = curs.fetchall()		
	except:
		print "{\"Result\":false,\"ErrMsg\":\"change the system error log!\"}"
		conn.close()
		sys.exit()		
	
	account1 = results[0][0]
	sql = "insert into pavenas.logs (account,ip,clas,oper,memo) values('%s', '%s', %s, 'Modify account(%s)', 'success');" %(MySQLdb.escape_string(account), MySQLdb.escape_string(client_ip), role, MySQLdb.escape_string(account1))
	
	try:
		curs.execute(sql)
		conn.commit()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"change the system error log(1)!\"}"
		conn.close()
		sys.exit()	

	curs.close();
	conn.close()
	sys.exit()		

	
	

#用户信息修改
if tasktype == "5":
	username = form.getvalue('ac')
	name = form.getvalue('na')
	dept = form.getvalue('dept')
	phone = form.getvalue('phone')
	email = form.getvalue('email')	
		
	if dept < 0:
		dept = "";
	if phone < 0:
		phone = "";
	if email < 0:
		email = "";
	if dept < 0:
		dept = "";			

	sql =  "UPDATE pavenas.manager SET  name='%s', dept='%s', mobile='%s', mail='%s' WHERE account='%s';" %(   MySQLdb.escape_string(name), MySQLdb.escape_string(dept), MySQLdb.escape_string(phone), MySQLdb.escape_string(email), MySQLdb.escape_string(username))	

	try:
		curs.execute(sql)
		conn.commit()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"result error(%d)!\"}" %(sys._getframe().flineno)
		conn.close()
		sys.exit()		
	

	print "{\"Result\":\"操作成功\",\"ErrMsg\":\"\"}"


	#系统日志		
	sql = "insert into pavenas.logs (account,ip,clas,oper,memo) values('%s', '%s', %s, 'Modify account(%s)', 'success');" %(MySQLdb.escape_string(account), MySQLdb.escape_string(client_ip), role, MySQLdb.escape_string(username))

	try:
		curs.execute(sql)
		conn.commit()
	except:
		print  "{\"Result\":false,\"ErrMsg\":\"Modify the system error log(%s)!\"}" %(sys._getframe().flineno)
		conn.close()
		sys.exit()
		
	curs.close();	
	conn.close()
	sys.exit()	
	
	
	

	
	
if tasktype == "8":	
	try:
		curs.execute('select * from pavenas.manager where account = \'%s\';' %(account))
		row = curs.fetchall()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"result error(%d)!\"}" %(sys._getframe().flineno)
		curs.close()
		conn.close()
		sys.exit()	
	
	if len(row[0][3]) <= 0:
		usercount1 = "";
	else :
		usercount1 = row[0][3].encode('utf-8')	
	if len(row[0][5]) <= 0:
		DepartMent_user = "";
	else :
		DepartMent_user = (row[0][5]).encode('utf-8')	
	if len(row[0][6]) <= 0:
		phone_user = "";
	else :
		phone_user = row[0][6].encode('utf-8')	
	if len(row[0][7]) <= 0:
		EmailAddress_user = "";
	else :
		EmailAddress_user = row[0][7].encode('utf-8')
		

	print "{\"name_user\":\"%s\",\"DepartMent_user\":\"%s\",\"phone_user\":\"%s\",EmailAddress_user:\"%s\"}" %(usercount1, DepartMent_user, phone_user, EmailAddress_user)		
	
	curs.close()	
	conn.close()
	sys.exit()		
	

#用户密码修改
if tasktype == "6":	
	username = form.getvalue('ac')
	pwd = form.getvalue('pwd')
	pwd_yuan = form.getvalue('pwd_yuan')
	
	sql = "SELECT * from pavenas.manager WHERE account = '%s';" %(MySQLdb.escape_string(username))
	
	try:
		curs.execute(sql)
		results = curs.fetchall()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"result error!\"}"
		conn.close()
		sys.exit()		
	pwd = pwd.strip()
	pwd_old = results[0][2].encode('utf-8')
	pwd_yuan = StrMD5(pwd_yuan)

	if pwd_old != pwd_yuan:
		if lang == 1:
			print "{\"Result\":false,\"ErrMsg\":\"原密码错误！\"}"
		else:
			print "{\"Result\":false,\"ErrMsg\":\"The original password mistake!\"}"
		
		conn.close()
		sys.exit()
		

	sql = "UPDATE pavenas.manager SET pwd='%s' WHERE account='%s';" %(MySQLdb.escape_string(pwd), MySQLdb.escape_string(username))

	try:
		curs.execute(sql)
		conn.commit()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"result error\"}"
		conn.close()
		sys.exit()
	

	print "{\"Result\":\"操作成功\",\"ErrMsg\":\"\"}"

	#系统日志
	sql = "insert into pavenas.logs (account,ip,clas,oper,memo) values('%s', '%s', %s, 'Modify account(%s)Password', 'success');" %(MySQLdb.escape_string(account), MySQLdb.escape_string(client_ip), role, MySQLdb.escape_string(username))
	try:
		curs.execute(sql)
		conn.commit()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"Modify the system error log password!\"}"
		conn.close()
		sys.exit()	
	
	curs.close()
	conn.close()
	sys.exit()	
	
	
	
	
#过滤
if tasktype == "7":
	currpage = form.getvalue('page')
	guolv_account = form.getvalue('guolv_account')
	guolv_caoz = form.getvalue('guolv_caoz')
	guolv_role = form.getvalue('guolv_select')		
	
	if guolv_account < 0:
		guolv_account2 = ""
	else:
		guolv_account = unquote(guolv_account)
		guolv_account2 = guolv_account.replace("_", "\\_")
		guolv_account2 = guolv_account2.replace("%", "\\%")
		guolv_account2 = guolv_account2.replace("\\", "\\\\")	
		#guolv_account1 = " and account like '%%%s%%'" %(guolv_account2)
		#guolv_account1 = " and account like '%%%s%%'" %(MySQLdb.escape_string((guolv_account)))

	if guolv_caoz < 0 :
		guolv_caozuo2 = ""
		guolv_caoz = ""
	else:
		guolv_caoz = unquote(guolv_caoz)
		guolv_caozuo2 = guolv_caoz.replace("_", "\\_")
		guolv_caozuo2 = guolv_caozuo2.replace("%", "\\%")
		guolv_caozuo2 = guolv_caozuo2.replace("\\", "\\\\")
		#guolv_caozuo1 = " and name like '%%%s%%'" %(guolv_caozuo2)
		#guolv_caozuo1 = " and name like '%%%s%%'" %(MySQLdb.escape_string(guolv_caoz))


	guolv_role1 = "role=1" 
	
	row = 0

	sql = "select count(*) from pavenas.manager where role=1  and name like '%%%s%%' and account like '%%%s%%' and account!= 'admin';" %(guolv_caozuo2, guolv_account2)
	

	try:
		#curs.execute("select count(*) from pavenas.manager where %s %s %s and account!= 'admin';" %(guolv_role1, guolv_account1, guolv_caozuo1))
		curs.execute(sql)
	except:
		print "{\"Result\":false,\"ErrMsg\":\"result error(%d)\"}" %(sys._getframe().f_lineno)
		conn.close()
		sys.exit()	
		

#	if curs.rowcount < 1:
#		print "{\"total\":0,\"start\":0,\"row\":0,\"data\":[]}"
#		conn.close()
#		sys.exit()
		

	row = curs.fetchone()[0]
	if row == 0:
		print "{\"total\":0,\"start\":0,\"row\":0,\"data\":[]}"
		conn.close()
		sys.exit()
		
		
	if row > 0:
		left = row % SIZE_PAGE
		totalPage = row / SIZE_PAGE + (1 if left else 0)
		if left == 0:
			left = SIZE_PAGE

		if currpage < 0:
			currpage = 1
		elif int(currpage) > totalPage:
			currpage = totalPage
		else:
			currpage = int(currpage)

		if 	currpage == 0:
			currpage = 1
		if currpage != totalPage:
			left = SIZE_PAGE	
	

	#sql = "SELECT * from pavenas.manager where %s %s %s and account!= 'admin' order by id asc limit %u offset %u;" %(guolv_role1, guolv_account1, guolv_caozuo1, left, (currpage - 1) * SIZE_PAGE)
	
	sql = "SELECT * from pavenas.manager where role=1  and name like '%%%s%%' and account like '%%%s%%' and account!= 'admin' order by id asc limit %u offset %u;" %(guolv_caozuo2, guolv_account2, left, (currpage - 1) * SIZE_PAGE)	

	try:
		curs.execute(sql)
		results = curs.fetchall()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"result error\"}"
		conn.close()
		sys.exit()	
		
	total = 0
	result_str1 = ""
	result_str = ""
	if not results:
		print "{\"total\":0,\"start\":0,\"row\":0,\"data\":[]}\n"		
	else:
		for result in results: 
			result_id = result[0]
			#print "var result_id=%d;" %(result_id)
			result_account = result[1].encode('utf-8')
			#print "var result_account=\"%s\";" %(result_account)
			result_pwd = result[2].encode('utf-8')
			#print "var result_pwd=\"%s\";" %(result_pwd)
			result_name = result[3].encode('utf-8')
			#print "var result_name=\"%s\";" %(result_name)			
			result_role = result[4]
			#print "var result_role=\"%d\";" %(result_role)			
			result_state = result[8]
			#print "var result_state=\"%d\";" %(result_state)	
			result_lang = result[9]
			#print "var result_lang=\"%d\";" %(result_lang)	
			

			result_str = "{\"id\":%d,\"account\":\"%s\",\"name\":\"%s\",\"role\":1,\"lang\":%d,\"state\":%d}" %(result_id, result_account, result_name, result_lang, result_state)
			
			if total < 10:
				if total > 0:
					result_str1 = result_str1 + "," +result_str
				else:
					result_str1 = result_str
			
			total = total + 1
			

		print "{\"total\":%d,\"start\":%d,\"row\":10,\"data\":[%s]}"	%(row, (currpage - 1) * SIZE_PAGE, result_str1)			
		
	
	curs.close()
	conn.close()
	sys.exit()	
	


#分页
if tasktype == "9":
	currpage = form.getvalue('page')
	
	row = 0
	sql = "select count(*) from pavenas.manager where account!= 'admin';" 
	
	try:
		curs.execute(sql) 
	except:
		print "{\"Result\":false,\"ErrMsg\":\"result error\"}"%(sys._getframe().f_lineno)
		conn.close()
		sys.exit()			

	if curs.rowcount < 1:
		print "{\"total\":0,\"start\":0,\"row\":0,\"data\":[]}"
		conn.close()
		sys.exit()			
	
	row = curs.fetchone()[0]

	currpage = int(currpage)
	if row > 0:
		left = row % SIZE_PAGE
		totalPage = row / SIZE_PAGE + (1 if left else 0)
		if left == 0:
			left = SIZE_PAGE

		if currpage < 1:
			currpage = 1
		elif currpage > totalPage:
			currpage = totalPage

		if currpage != totalPage:
			left = SIZE_PAGE	
	

	sql = "SELECT * from pavenas.manager where account!= 'admin' order by id asc limit %u offset %u;" %(left, (currpage - 1) * SIZE_PAGE)
	
	try:
		curs.execute(sql)
		results = curs.fetchall()
	except:
		print "{\"Result\":false,\"ErrMsg\":\"result error\"}"
		conn.close()
		sys.exit()	
		
	total = 0
	result_str1 = ""
	result_str = ""
	if not results:
		print "{\"total\":0,\"start\":0,\"row\":0,\"data\":[]}\n"		
	else:
		for result in results: 
			result_id = result[0]
			#print "var result_id=%d;" %(result_id)
			result_account = result[1].encode('utf-8')
			#print "var result_account=\"%s\";" %(result_account)
			result_pwd = result[2].encode('utf-8')
			#print "var result_pwd=\"%s\";" %(result_pwd)
			result_name = result[3].encode('utf-8')
			#print "var result_name=\"%s\";" %(result_name)			
			result_role = result[4]
			#print "var result_role=\"%d\";" %(result_role)			
			result_state = result[8]
			#print "var result_state=\"%d\";" %(result_state)	
			result_lang = result[9]
			#print "var result_lang=\"%d\";" %(result_lang)	
			

			result_str = "{\"id\":%d,\"account\":\"%s\",\"name\":\"%s\",\"role\":1,\"lang\":%d,\"state\":%d}" %(result_id, result_account, result_name, result_lang, result_state)
			
			if total < 10:
				if total > 0:
					result_str1 = result_str1 + "," +result_str
				else:
					result_str1 = result_str
			
			total = total + 1
			

		print "{\"total\":%d,\"start\":%d,\"row\":10,\"data\":[%s]}"	%(row, (currpage - 1) * SIZE_PAGE,result_str1)			
		
	
	curs.close()
	conn.close()
	sys.exit()