当前位置: 开发笔记 > 编程语言 > 正文

Python脚本之djangomysql记录主机性能数据到数据库web站点管理数据库及web显示命令执行结果

作者：手机用户2602884633 | 来源：互联网 | 2023-07-18 14:23

##############################################################环境[root@LVS1python]#catetcre

##############################################################环境

[root@LVS1 python]# cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.4 (Santiago)

You have new mail in /var/spool/mail/root

[root@LVS1 python]# python -V

Python 2.6.6

[root@LVS1 python]#

#############################################################安装paramiko

[root@LVS1 ~]# yum install gcc

[root@LVS1 ~]# yum install python-devel

[root@LVS1 ~]#tar -zxvf pycrypto-2.6.1.tar.gz#https://pypi.python.org/pypi/pycrypto

[root@LVS1 ~]#cd pycrypto-2.6.1

[root@LVS1 pycrypto-2.6.1]#python setup.py install

[root@LVS1 ~]#tar -zxvf paramiko-1.10.1.tar.gz#https://pypi.python.org/pypi/paramiko

[root@LVS1 ~]#cd paramiko-1.10.1

[root@LVS1 paramiko-1.10.1]# python setup.py install

[root@LVS1 demos]# python demo.py 192.168.1.10#测试

#############################################################安装django

[root@LVS1 python]# tar -zxvf Django-1.5.1.tar.gz

[root@LVS1 python]# cd Django-1.5.1

[root@LVS1 Django-1.5.1]# python setup.py install

[root@LVS1 Django-1.5.1]# cd django/bin/

[root@LVS1 bin]# ./django-admin.py startproject myweb

[root@LVS1 bin]# cd myweb

[root@LVS1 bin]# service iptables stop

[root@LVS1 myweb]# ./manage.py runserver 0.0.0.0:8000

#http://192.168.1.10:8000/

#############################################################安装python-MySQLdb

#yum install mysql-server

#service mysqld start

#chkconfig --level 345 mysqld on

#[root@LVS1 ~]# mysql -u root

#mysql> SET PASSWORD FOR ‘root‘@‘localhost‘ = PASSWORD(‘123456‘);

#mysql> show databases;

#mysql> use mysql;

#mysql> show tables;

mysql> create database Filesystem;

#mysql>quit

[root@LVS1 ~]#yum install MySQL-python

#########################################################将输出结果直接返回到页面上

[root@LVS1 bin]# cd myweb

[root@LVS1 myweb]# pwd

/tmp/python/Django-1.5.1/django/bin/myweb/myweb

vi view.py

from django.http import HttpResponse

import datetime,time,os

def hello(request):

return HttpResponse(‘hello my name is xk‘)

def current_time(request):

now=datetime.datetime.now()

html="It is now :%s"%now

return HttpResponse(html)

def cpu(request):

status=os.popen(‘top -bn 1‘).read()

html="

%s"%status
        return HttpResponse(html)
def hours_ahead(request,h):
        offset=int(h)
        dt=datetime.datetime.now() + datetime.timedelta(hours=offset)
        html="In %s hours later,It is %s"%(h,dt)
        return HttpResponse(html)
-------------------------------------------------
[root@LVS1 myweb]# vi urls.py
from django.conf.urls import patterns, include, url

# Uncomment the next two lines to enable the admin:
# from django.contrib import admin
# admin.autodiscover()
from myweb.view import hello,current_time,cpu,hours_ahead

urlpatterns = patterns(‘‘,
    # Examples:
    # url(r‘^$‘, ‘myweb.views.home‘, name=‘home‘),
    # url(r‘^myweb/‘, include(‘myweb.foo.urls‘)),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r‘^admin/doc/‘, include(‘django.contrib.admindocs.urls‘)),

    # Uncomment the next line to enable the admin:
    # url(r‘^admin/‘, include(admin.site.urls)),
      (r‘^hello/$‘,hello),
        (r‘^time/$‘,current_time),
        (r‘^cpu/$‘,cpu),
        (r‘^time/plus/(\d{1,2})/$‘,hours_ahead),
)

#http://192.168.1.10:8000/hello/
http://192.168.1.10:8000/time/
http://192.168.1.10:8000/cpu/
http://192.168.1.10:8000/time/plus/2/#返回当前时间加上2小时之后的时间
#########################################################利用模板显示输出结果到页面上
[root@LVS1 myweb]# pwd
/tmp/python/Django-1.5.1/django/bin/myweb/myweb
[root@LVS1 myweb]# tail -2000 view2.py
from django.shortcuts import render_to_response
import os
import paramiko
hosts=[‘192.168.1.10‘,‘192.168.1.10‘,‘192.168.1.11‘,‘192.168.1.10‘,‘192.168.1.11‘,‘192.168.1.13‘]
username=‘root‘
password=‘123456‘
port=22
d_usage={}
d_usage2={}
def disk(request):
        i=0
        for hostname in hosts:
                i=i+1
                if os.system(‘ping %s -c 1‘%hostname)==0:
                        paramiko.util.log_to_file(‘paramiko.log‘)
                        s = paramiko.SSHClient()
                        s.set_missing_host_key_policy(paramiko.AutoAddPolicy())
                        s.connect(hostname,port,username,password)
                        stdin,stdout,stderr=s.exec_command(‘df -kP‘)
                        d_usage[hostname+‘__%s‘%i]= stdout.read()
                        s.close()
                else:
                        d_usage2[hostname+‘__%s‘%i]=‘host Destination Host Unreachable‘
                        name={‘xk‘:[25,‘male‘],‘zq‘:[23,‘male‘],}
        sum1=len(d_usage)
        sum2=len(d_usage2)
        sum=sum1+sum2
        return render_to_response(‘disk.html‘,{"d_usage":d_usage,‘name‘:name,‘sum‘:sum,‘d_usage2‘:d_usage2,})
------------------------------------------------------------
[root@LVS1 myweb]# vi urls.py
from django.conf.urls import patterns, include, url

# Uncomment the next two lines to enable the admin:
# from django.contrib import admin
# admin.autodiscover()
from myweb.view2 import disk

urlpatterns = patterns(‘‘,
    # Examples:
    # url(r‘^$‘, ‘myweb.views.home‘, name=‘home‘),
    # url(r‘^myweb/‘, include(‘myweb.foo.urls‘)),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r‘^admin/doc/‘, include(‘django.contrib.admindocs.urls‘)),

    # Uncomment the next line to enable the admin:
    # url(r‘^admin/‘, include(admin.site.urls)),
(r‘^disk/$‘,disk),
)

------------------------------------------------------------
[root@LVS1 myweb]#mkdir templates
[root@LVS1 myweb]#vi /tmp/python/Django-1.5.1/django/bin/myweb/myweb/settings.py
TEMPLATE_DIRS = (
    # Put strings here, like "/home/html/django_templates" or "C:/www/django/templates".
    # Always use forward slashes, even on Windows.
    # Don‘t forget to use absolute paths, not relative paths.
    ‘/tmp/python/Django-1.5.1/django/bin/myweb/myweb/templates‘,
[root@LVS1 templates]# pwd
/tmp/python/Django-1.5.1/django/bin/myweb/myweb/templates
[root@LVS1 templates]# tail -1000 disk.html
 show disk usage
hosts:sum-- {{sum}}
{%for line in d_usage2.keys%}
        {{line}};
{%endfor%}
{%for line in d_usage.keys%}
        {{line}};
{%endfor%}



{% for ip,value in d_usage2.items %}
---------------------------------host{{ip}}----------------------------------
   {{value}}

{% endfor %}
{% for ip,value in d_usage.items %}
---------------------------------host{{ip}}----------------------------------
   {{value}}

{% endfor %}
-----------------------------------------------------------------------------
        {{name}}
####################################将主机文件系统、内存情况，cpu空闲率记录到MySQL数据库中
#注：先在MySQL数据库中创建好名为python的数据库，并赋给用户权限和密码
[root@LVS1 myweb]# pwd
/tmp/python/Django-1.5.1/django/bin/myweb/myweb
[root@LVS1 myweb]# vi settings.py
DATABASES = {
    ‘default‘: {
        ‘ENGINE‘: ‘django.db.backends.mysql‘, # Add ‘postgresql_psycopg2‘, ‘mysql‘, ‘sqlite3‘ or ‘oracle‘.
        ‘NAME‘: ‘Filesystem‘,                      # Or path to database file if using sqlite3.
        # The following settings are not used with sqlite3:
        ‘USER‘: ‘root‘,
        ‘PASSWORD‘: ‘123456‘,
        ‘HOST‘: ‘‘,                      # Empty for localhost through domain sockets or ‘127.0.0.1‘ for localhost through TC
P.
        ‘PORT‘: ‘‘,                      # Set to empty string for default.
    }
}
INSTALLED_APPS = (
    ‘django.contrib.auth‘,
    ‘django.contrib.contenttypes‘,
    ‘django.contrib.sessions‘,
    ‘django.contrib.sites‘,
    ‘django.contrib.messages‘,
    ‘django.contrib.staticfiles‘,
    # Uncomment the next line to enable the admin:
     ‘django.contrib.admin‘,
    # Uncomment the next line to enable admin documentation:
    # ‘django.contrib.admindocs‘,
        ‘pyweb‘
[root@LVS1 myweb]# pwd
/tmp/python/Django-1.5.1/django/bin/myweb
[root@LVS1 myweb]# ./manage.py startapp pyweb
[root@LVS1 pyweb]# pwd
/tmp/python/Django-1.5.1/django/bin/myweb/pyweb
[root@LVS1 pyweb]# tail -2000 models.py
from django.db import models

# Create your models here.
from django.db import models

class Filesystem(models.Model):
    ip = models.CharField(max_length=30)
    date_time = models.CharField(max_length=50)
    Filesystem = models.CharField(max_length=120)
    sum_kb = models.CharField(max_length=60)
    Used = models.CharField(max_length=30)
    Available = models.CharField(max_length=50)
    Capacity = models.CharField(max_length=60)
    Mounted_on = models.CharField(max_length=60)
    def __unicode__(self):
        return self.ip
class Men_Cpu(models.Model):
        ip = models.CharField(max_length=30)
        date_time = models.CharField(max_length=50)
        Men_sum_kb = models.CharField(max_length=40)
        Men_used = models.CharField(max_length=40)
        Men_free = models.CharField(max_length=40)
        Men_idle = models.CharField(max_length=40)
        Cpu_idle = models.CharField(max_length=40)
        def __unicode__(self):
            return self.ip
class Tablespace(models.Model):
    ip = models.CharField(max_length=30)
    date_time = models.CharField(max_length=50)
    TABLESPACE_NAME = models.CharField(max_length=120)
    SUMMARY = models.CharField(max_length=60)
    FREE = models.CharField(max_length=30)
    MAX_FREE_EXTENT = models.CharField(max_length=50)
    FREE_EXTENTS = models.CharField(max_length=60)
    USED = models.CharField(max_length=60)
    def __unicode__(self):
        return self.ip

#class Book(models.Model):
#    title = models.CharField(max_length=100)
#    authors = models.ManyToManyField(Author)
#    publisher = models.ForeignKey(Publisher)
#    publication_date = models.DateField()
#    country = models.CharField(defau="CN",max_length=50)#默认值为CN
#    def __unicode__(self):
#        return self.title
[root@LVS1 pyweb]# 
[root@LVS1 myweb]# pwd
/tmp/python/Django-1.5.1/django/bin/myweb
[root@LVS1 myweb]# python manage.py validate
0 errors found
[root@LVS1 myweb]# python manage.py sqlall pyweb
[root@LVS1 myweb]# python manage.py syncdb
---------------------------------------------------web站点管理上面的数据库
[root@LVS1 myweb]# pwd
/tmp/python/Django-1.5.1/django/bin/myweb/myweb
[root@LVS1 myweb]# vi settings.py
INSTALLED_APPS = (
    ‘django.contrib.auth‘,
    ‘django.contrib.contenttypes‘,
    ‘django.contrib.sessions‘,
    ‘django.contrib.sites‘,
    ‘django.contrib.messages‘,
    ‘django.contrib.staticfiles‘,
    # Uncomment the next line to enable the admin:
     ‘django.contrib.admin‘,
    # Uncomment the next line to enable admin documentation:
    # ‘django.contrib.admindocs‘,
        ‘pyweb‘
)
[root@LVS1 myweb]# vi urls.py
from django.conf.urls import patterns, include, url

# Uncomment the next two lines to enable the admin:
from django.contrib import admin
admin.autodiscover()


urlpatterns = patterns(‘‘,
    # Examples:
    # url(r‘^$‘, ‘myweb.views.home‘, name=‘home‘),
    # url(r‘^myweb/‘, include(‘myweb.foo.urls‘)),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r‘^admin/doc/‘, include(‘django.contrib.admindocs.urls‘)),

    # Uncomment the next line to enable the admin:
     url(r‘^admin/‘, include(admin.site.urls)),
       # (r‘^disk/$‘,disk),
)

[root@LVS1 pyweb]# tail -2000 admin.py
from django.contrib import admin
from pyweb.models import Filesystem,Men_Cpu,Tablespace 
class Filesystem_adin(admin.ModelAdmin):
        list_display=(‘ip‘,‘date_time‘,‘Filesystem‘,‘sum_kb‘,‘Used‘,‘Available‘,‘Capacity‘,‘Mounted_on‘)
        list_filter=(‘ip‘,‘date_time‘,)
        search_fields=(‘ip‘,‘date_time‘,‘Filesystem‘)
        ordering=(‘-date_time‘,)


class Men_Cpu_admin(admin.ModelAdmin):
        list_display=(‘ip‘,‘date_time‘,‘Men_sum_kb‘,‘Men_used‘,‘Men_free‘,‘Men_idle‘,‘Cpu_idle‘)
        list_filter=(‘ip‘,‘date_time‘,)
        search_fields=(‘ip‘,‘date_time‘,)
        ordering=(‘-date_time‘,)

class Tablespace_admin(admin.ModelAdmin):
        list_display=(‘ip‘,‘date_time‘,‘TABLESPACE_NAME‘,‘SUMMARY‘,‘FREE‘,‘MAX_FREE_EXTENT‘,‘FREE_EXTENTS‘,‘USED‘)
        list_filter=(‘ip‘,‘date_time‘,)
        search_fields=(‘ip‘,‘date_time‘,‘TABLESPACE_NAME‘)
        ordering=(‘-date_time‘,)

admin.site.register(Filesystem,Filesystem_adin)
admin.site.register(Men_Cpu,Men_Cpu_admin)
admin.site.register(Tablespace,Tablespace_admin)

#admin.site.register(Author)
#admin.site.register(Book)

[root@LVS1 myweb]# ./manage.py syncdb
[root@LVS1 myweb]#echo "python /tmp/python/Django-1.5.1/django/bin/myweb/manage.py runserver 0.0.0.0:8000  &>/tmp/dgangomyweb.txt &">>/etc/rc.local
http://192.168.1.10:8000/admin/#用户名和密码为第一次执行python manage.py syncdb时创建的
------------------------创建脚本（将主机文件系统、内存情况，cpu空闲率记录到MySQL数据库中）
[root@LVS1 pyweb]# tail -2000 /tmp/python/alldjango-mysql.py
#!/bin/usr/bin python
import os,datetime,paramiko
import tab,sys,multiprocessing,time
sys.path.append(‘/tmp/python/Django-1.5.1/django/bin/myweb‘)
os.environ[‘DJANGO_SETTINGS_MODULE‘] = ‘myweb.settings‘ 
from pyweb.models import Filesystem,Men_Cpu,Tablespace
#hosts=[‘192.168.1.10‘,‘192.168.1.11‘,‘192.168.1.13‘,‘192.168.1.200‘,‘192.168.1.11‘]
hosts=[‘192.168.1.10‘,‘192.168.1.11‘,‘192.168.1.13‘,‘192.168.1.10‘,‘192.168.1.200‘]
username=‘root‘
password=‘123456‘
port=22
time=datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S‘)
def run_cmd(ip):
        list=[]
        list0=[]
#       if os.system(‘ping %s -c 1 &>/dev/null‘%ip)==0:
        try:
                        paramiko.util.log_to_file(‘paramiko.log‘)
                        s = paramiko.SSHClient()
                        s.set_missing_host_key_policy(paramiko.AutoAddPolicy())
                        s.connect(ip,port,username,password)
                        stdin,stdout,stderr=s.exec_command(‘df -kP‘)
                        df= stdout.read().split(‘\n‘)
                        stdin,stdout,stderr=s.exec_command("free|grep Mem|awk ‘{print $2}‘")
                        list.append(stdout.read().strip())
                        stdin,stdout,stderr=s.exec_command("free|grep ‘buffers/‘|awk ‘{print $3}‘")
                        list.append(stdout.read().strip())
                        stdin,stdout,stderr=s.exec_command("free|grep ‘buffers/‘|awk ‘{print $4}‘")
                        list.append(stdout.read().strip())
                        list.append(‘%s‘%(float(list[2])/float(list[0])))
                        stdin,stdout,stderr=s.exec_command("vmstat 1 2|sed -n ‘4p‘|awk ‘{print $(NF-2)}‘")
                        list.append(stdout.read().strip())

                        try:
                                stdin,stdout,stderr=s.exec_command(‘sh /tmp/tablespace.sh‘)
                                list0=stdout.read().split(‘\n‘)
                                list0.pop(0)
                                list0.pop(0)
                                list0.pop(0)
                                list0.pop(-1)
                        except:
                                list0=[‘null  null  null  null  null   null‘]


                        s.close()
                        print ‘xxxx‘,ip
#       else:
        except:
                list=[‘null‘,‘null‘,‘null‘,‘null‘,‘null‘]
                df= ‘nul \n null null null  null null null \n‘.split(‘\n‘)
                list0=[‘null  null  null  null  null   null‘]
                print ‘butong‘,ip
        #time=datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S‘)
        df.pop(0)
        df.pop(-1)
        for line in df:
               list00=line.split()
               p1 = Filesystem(ip=‘%s‘%ip,date_time=‘%s‘%time,Filesystem=‘%s‘%list00[0], sum_kb=‘%s‘%list00[1],Used=‘%s‘%list00[2], Available=‘%s‘%list00[3], Capacity=‘%s‘%list00[4],Mounted_on=‘%s‘%list00[5])
               p1.save()

        p2 = Men_Cpu(ip=‘%s‘%ip,date_time=‘%s‘%time,Men_sum_kb=‘%s‘%list[0], Men_used=‘%s‘%list[1],Men_free=‘%s‘%list[2], Men_idle=‘%s‘%list[3], Cpu_idle=‘%s‘%list[4])
        p2.save()

        for list in list0:
                list=list.split()
                p3 = Tablespace(ip=‘%s‘%ip,date_time=‘%s‘%time,TABLESPACE_NAME=‘%s‘%list[0], SUMMARY=‘%s‘%list[1],FREE=‘%s‘%list[2], MAX_FREE_EXTENT=‘%s‘%list[3], FREE_EXTENTS=‘%s‘%list[4],USED=‘%s‘%list[5])
                p3.save()

p=multiprocessing.Pool(processes=10)
for hostname in hosts:
        p.apply_async(run_cmd,(‘%s‘%hostname,))
        print hostname
#time.sleep(240)
print len(hosts)
time2=datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S‘)
print time2
p.close()
p.join()
time3=datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S‘)
print time2
print time3
[root@LVS1 pyweb]# 
[root@LVS1 pyweb]# crontab -l
*/10 * * * * python /tmp/python/alldjango-mysql.py #每10分钟一次将主机文件系统、内存情况，cpu空闲率记录到MySQL数据库中

------------------------------------附：在装有oracle数据库的远程主机上创建查询表空间的脚本
[root@redhata ~]# vi /tmp/tablespace.sh 
#!/bin/bash
export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:$PATH
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/
sqlplus -S /nolog <
conn system/123456@orcl
set line 200;
set feedback off;
set pagesize 50000;
col member for a45;
select a.tablespace_name,a.summary,b.free,b.maxf "MAX_FREE_EXTENT",b.free_exts "FREE_EXTENTS",
    100-b.free/a.summary*100 "USED%"
        from
           (select tablespace_name,sum(bytes/1024/1024) "SUMMARY" from dba_data_files
               group by tablespace_name) a,
                   (select tablespace_name,sum(bytes/1024/1024) "FREE",max(bytes/1024/1024)
                      "MAXF" ,count(*) free_exts
                          from dba_free_space group by tablespace_name) b
                              where a.tablespace_name=b.tablespace_name
                                 order by 6 desc;
eof
                                 exit;
##########################################################################################
vi  /tmp/python/Django-1.5.1/django/bin/myweb/myweb/settings.py #修改时区
TIME_ZOnE= ‘Aisa/Shanghai‘
#########################################################################
vi /usr/lib/python2.6/site-packages/django/contrib/admin/templates/admin/base_site.html
{% extends "admin/base.html" %}
{% load i18n %}

{% block title %}{{ title }} | {% trans ‘主机性能记录系统‘ %}{% endblock %}

{% block branding %}
{% trans ‘主机性能记录系统‘ %}
{% endblock %}

{% block nav-global %}{% endblock %}
#####################################################
[root@LVS1 myweb# pwd
/tmp/python/Django-1.5.1/django/bin/myweb
[root@LVS1 myweb]# ./manage.py shell
In [2]: from pyweb.models import Publisher
In [3]: p1 = Publisher(name=‘shanghai‘, address=‘24242 chuansha road‘,city=‘ShangHai‘, state_province=‘CN‘, country=‘China‘,website=‘http://www.xxk.com/‘)                        
In [4]: p1.save()

In [5]: p1.name=‘hefei‘
In [6]: p1.save()
##################################################################

本文出自 “银河系|计算机网络” 博客，请务必保留此出处http://qqran.blog.51cto.com/10014850/1965836
Python脚本之django---mysql-记录主机性能数据到数据库-web站点管理数据库及web显示命令执行结果