β

python 查询 Mysql 并输出到文本

运维日志 1097 阅读

学习Python后写的第二个脚本,逻辑有点乱,等以后在优化!

#!/usr/bin/env python
'''
author:wenmin
Created on 2013-4-23
'''
 
import MySQLdb
 
class MySQLHelper:
    #配置数据库信息并连接
    def __init__(self,host="****",user="****",password="****",port=****,charset="utf8"):
        self.host=host
        self.user=user
        self.password=password
        self.port=port
        self.charset=charset
        try:
            self.conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.password,port=self.port)
            self.conn.set_character_set(self.charset)
            self.cur=self.conn.cursor()
        except MySQLdb.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
    #取出需要统计的数据库名称
    def db_name(self):
        un_db_name = ['information_schema','cz','ecshop','edutone','gz','mysql','newparent','parent','performance_schema','test','xx','yyhd']
        name = []
        try:
            self.cur.execute('show databases')
            for row in self.cur.fetchall():
                for i in row:
                    if i not in un_db_name:
                        name.append(i)
            return name
        except MySQLdb.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
    #指定查询的数据库名称
    def selectDb(self,db):
        try:
            self.conn.select_db(db)
        except MySQLdb.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
    #查询用户数
    def user_quantity(self):
        try:
            self.cur.execute('select count(distinct phone) from pc_user')
            for row in self.cur.fetchall():
                return row[0]
        except MySQLdb.Error as e:
            print("Mysql Error:%s\n" %(e))
    #查询用户详细信息
    def user_details(self,db):
        try:
            self.cur.execute('select a.phone,a.parents_name,a.student_name,a.type,c.grade_name,b.class_name,a.sex,"'+str(db)+'"from pc_user a,pc_class b,pc_grade c where a.class_id=b.id and a.grade_id=c.id group by a.phone')
            #for row in self.cur.fetchall():
            #   return row
            s = self.cur.fetchall()
            return s
        except MySQLdb.Error as e:
            print("Mysql Error:%s\n" %(e))
    #查询议案数
    def monion_quantity(self):
        try:
            self.cur.execute('select count(distinct id) from pc_motions')
            for row in self.cur.fetchall():
                return row[0]
        except MySQLdb.Error as e:
            print("Mysql Error:%s\n" %(e))
    #查询有效议案
    def monion_details(self):
        try:
            self.cur.execute('select `motion_id`,count(*) from pc_motion_voterec group by motion_id having count(*)>5')
           # for row in self.cur.fetchall():
           #     return row
            s = self.cur.fetchall()
            return s
        except MySQLdb.Error as e:
            print("Mysql Error:%s\n" %(e))
 
    def close(self):
        self.cur.close()
        self.conn.close()
 
if __name__ == '__main__':
    school_db_name = MySQLHelper()
    school = school_db_name.db_name()
    for i in school:
        file = open('jwh/%s' % i,'w')
        file.write("================================================\n")
        d_name = str(i)
        i = MySQLHelper()
        i.selectDb(d_name)
        file.write("user_quantity:"+str(i.user_quantity())+"\n")
        file.write("================================================\n")
        s=i.user_details(d_name)
        for p in s:
            for m in p:
                file.write(str(m)+'  ')
            file.write("\n")
        file.write("================================================\n")
        file.write("monion_quantity:"+str(i.monion_quantity())+"\n")
        file.write("================================================\n")
        l=i.monion_details()
        for p in l:
            for s in p:
                file.write(str(s)+"  ")
            file.write("\n")
        i.close()
        file.close()
    school_db_name.close()
作者:运维日志
记录技术点滴 分享生活智慧
原文地址:python 查询 Mysql 并输出到文本, 感谢原作者分享。