开发者社区> 技术小甜> 正文

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

简介:
+关注继续查看

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

[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="<pre>%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

<html>

<body>

<center> show disk usage</center></br>

hosts:sum-- `sum`</br>

{%for line in d_usage2.keys%}

     <font color=red>   `line`;</font>

{%endfor%}</br>

{%for line in d_usage.keys%}

        `line`;

{%endfor%}</br></br></br></br>

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

---------------------------------host`ip`----------------------------------

<font color=red><pre>   `value`</pre></font>


{% endfor %}

{% for ip,value in d_usage.items %}

---------------------------------host`ip`----------------------------------

<pre>   `value`</pre>


{% endfor %}

-----------------------------------------------------------------------------</br>

        `name`

</body>

</html>

####################################将主机文件系统、内存情况,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 <<eof

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'

#########################################################################

Django-1.5.1版本

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 %}

<h1 id="site-name">{% trans '主机性能记录系统' %}</h1>

{% endblock %}


{% block nav-global %}{% endblock %}

------------------------------------------------------

Django-1.9.13版本

python -c "import sys;sys.path=sys.path[1:];import django;print(django.__path__)"#找到源文件目录

cd /usr/local/lib/python3.6/site-packages/Django-1.9.13-py3.6.egg/django/contrib/admin

vi sites.py

class AdminSite(object):

    """

    An AdminSite object encapsulates an instance of the Django admin application, ready

    to be hooked in to your URLconf. Models are registered with the AdminSite using the

    register() method, and the get_urls() method can then be used to access Django view

    functions that present a full admin interface for the collection of registered

    models.

    """


    # Text to put at the end of each page's <title>.

    site_title = ugettext_lazy('配置管理系统')

    #site_title = ugettext_lazy('Django site admin')


    # Text to put in each page's <h1>.

    site_header = ugettext_lazy('配置管理系统')

    #site_header = ugettext_lazy('Django administration')


    # Text to put at the top of the admin index page.

    index_title = ugettext_lazy('配置管理系统')

    #index_title = ugettext_lazy('Site administration')


    # URL for the "View site" link at the top of each admin page.


#####################################################

[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()

##################################################################

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#开机启动















本文转自shangshanyang51CTO博客,原文链接:http://blog.51cto.com/qqran/1965836 ,如需转载请自行联系原作者



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
记录一次vue-cli2下的webpack3.x升级webpack4.x的历程
由于公司项目是19年创建的,因此还是基于vue/cli2搭建的,webpack版本也是3.6.0。(当时有vue/cli3和webpack4不知道为啥不用(`・ω・´))每次打包的时候都要等好久,一直有人在解决这个问题。但是好像一直没结果,这次公司举行首席前端的竞选。把项目升级和提升打包速度作为了考核,所以来搞一下。听说升级到webpack4之后打包速度贼快,不知道是不是真的。下面就记录一下vue/cli2下的webpack3升级webpack4中遇到的一些问题及解决方案。
67 0
【错误记录】Flutter 设备连接显示 Loading... ( 断网 | 删除 flutter/bin/cache/lockfile 文件 )
【错误记录】Flutter 设备连接显示 Loading... ( 断网 | 删除 flutter/bin/cache/lockfile 文件 )
36 0
mysql数据库表记录数
经常用到,在这里记录一下,使用的命令 use information_schema; select table_name,table_rows from tables where TABLE_SCHEMA = '数据库名' order by table_rows desc; 查询出来的是每张表的行数
1442 0
MySQL利用存储过程清除所有表中的所有记录
MySQL利用存储过程清除所有表中的所有记录 在使用MySQL过程中,难免会出现一些测试数据,然而这些测试数据在真正部署运行的时候,通常要被清楚掉,并且自增长的字段要回复初始值。如何高效快速的实现以上功能,可以使用存储过程实现这一个功能。
1771 0
7.17 Linux脚本程序包及安装方法详解(以webmin为例)
脚本程序并不多见,所以在软件包分类中并没有把它列为一类。它更加类似于 Windows 下的程序安装,有一个可执行的安装程序,只要运行安装程序,然后进行简单的功能定制选择(比如指定安装目录等),就可以安装成功,只不过是在字符界面完成的。
22 0
从SQLSERVER/MYSQL数据库中随机取一条或者N条记录
原文:从SQLSERVER/MYSQL数据库中随机取一条或者N条记录 从SQLSERVER/MYSQL数据库中随机取一条或者N条记录 很多人都知道使用rand()函数但是怎麽使用可能不是每个人都知道 建立测试表 USE [sss] GO CREATE TABLE RANDTEST(...
1380 0
+关注
10140
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载