python查询帝国cms文章总量

# -*- coding: utf-8 -*-
import pymysql
import time
import json


def runtotal(dbhost,username,password,dbname,tbpre=''):
	print('[running]','链接数据库',dbname)
	class_tbnamer = []
	conn = pymysql.connect(
		host=dbhost,
		user=username,
		password=password,
		database=dbname,
		charset='utf8',
		cursorclass = pymysql.cursors.SSDictCursor,
		)
	cursor = conn.cursor()

	print('[running]','获得存放内容数据表名')
	# 获得存放内容数据表
	tbnamer = []
	rows = cursor.execute('show tables')
	for d in cursor.fetchall():
		for k,tb in d.items():
			# print(tb)
			nr = tb.split('_')
			if len(nr) != 4:
				continue
			# 识别数据表
			if nr[1]=='ecms' and nr[3]=='index':
				if tbpre and nr[0]!=tbpre:continue
				tbnamer.append(tb)	

	total = 0
	print('[running]','获得存放内容数据表名：',len(tbnamer))
	for tbname in tbnamer:
		cursor.execute('select count(*) from %s;'%tbname)
		count = cursor.fetchone()
		count = count['count(*)'] if count else 0
		print('[running]',tbname,count)
		total += count

	print('[running]','断开数据库',dbname)
	cursor.close()
	conn.close()
	return total

def runtotals(d,username,password,dbhost='localhost',filename=''):
	if not d:
		return
	print('[running]','开始运行',len(d))
	data = []
	for v in d :
		domain = v['domain']
		dbname = v['dbname']
		if not 'tbpre' in v.keys():v['tbpre'] = ''
		tbpre = v['tbpre']

		total = runtotal(dbhost,username,password,dbname,tbpre)
		print('[running]',domain,total)
		data.append({
			'domain':domain,
			'value':total,
			})
		print('')

	print('[running]',json.dumps(data))
	if filename:
		print('[running]','执行保存文件',filename)
		# 保存新数据
		with open(filename, 'w', encoding='utf-8') as filejson:
			filejson.write(json.dumps(data))

if __name__ == '__main__':

	date = time.strftime("%Y%m%d",time.localtime(int(time.time())))
	filename = 'count.%s.json'%date
	username = 'root'
	password = '2008'
	d =[
				{'domain':'www.baidu.com','dbname':'baidu','tbpre':'phome'},
				{'domain':'m.qq.com','dbname':'qq'},
	]
	runtotals(d,username,password,filename=filename)

	# 脚本说明：
	# 	python3.6版本
	# 	需要安装pymysql模块 pip install pymysql
	# 	默认查询帝国cms表，不通用其他cms，如有需要修改识别数据表即可
	# 	暂不支持分表，可修改识别

	# 	parama  d   array   查询域名数组
	# 		d =[
	# 			{'domain':'www.baidu.com','dbname':'baidu','tbpre':'phome'},
	# 			{'domain':'m.qq.com','dbname':'qq'},
	# 		]
	# 		domain	对应域名	必须
	# 		dbname 	对应数据库名	必须
	# 		tbpre		对应表前缀	可选
	# 	parama  username	string  数据库用户名
	# 	parama  password	string  数据库用户密码
	# 	parama  dbhost	string  数据库主机地址 默认 localhost
	# 	parama  filename  string  保存文件名称

	# by 薛一 
	# 2018-07-13