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

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

'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(-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

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