如何在django里上传csv文件并进行入库处理

运维平台导入数据这一功能实在是太重要了，我敢说在没有建自己的cmdb平台前，大多数公司管理服务器信息肯定是表格，用表格最麻烦的就是有点更新就得每个人发一份，这样大家信息才能统一，很不方便，终于有一天受不了了，搞了一个服务器信息管理平台，那面临的第一个问题不是说功能好或不不好，而是怎么才能把表里的数据导入到数据库中，所以你说重要不重要，当然如果你就喜欢自己手工录入（找虐的感觉），这个咱也不能说啥，各有所好嘛，那具体如何录的最快，这个不在我们今天的讨论范围，我只讨论如何自动导入。

提到导入，那一般有二个方法，一个是在前端上传完后存储在服务器上的某个目录里，然后读取文件进行分析处理。

另一种是上传文件后直接读取文件内容而不存储在服务器上，这二种方法都可以实现我们得目的，这篇主要是讨论的后面这种。

上传文件，首先我们建一个html文件，内容代码如下：

<form action="{% url "myapp:upload_csv" %}" method="POST" enctype="multipart/form-data" class="form-horizontal"> 
{% csrf_token %}
<div class="form-group">
    <label for="name" class="col-md-3 col-sm-3 col-xs-12 control-label">File: </label>
    <div class="col-md-8">
        <input type="file" name="csv_file" id="csv_file" required="True" class="form-control">
    </div>                    
</div>
<div class="form-group">                    
    <div class="col-md-3 col-sm-3 col-xs-12 col-md-offset-3" style="margin-bottom:10px;">
         <button class="btn btn-primary"> <span class="glyphicon glyphicon-upload" style="margin-right:5px;"></span>Upload </button>
    </div> 
</div>
</form>

{% csrf_token %}

</div>

<button class="btn btn-primary"> <span class="glyphicon glyphicon-upload" style="margin-right:5px;"></span>Upload </button>

</div>

</form>

这些都是基本的Html,只要主要enctype=”multipart/form-data”这个参数就可以，其它无特别说明。

展示如图：

加入路由，

url(r'^upload/csv/$', views.upload_csv, name='upload_csv'),

1	url(r'^upload/csv/$', views.upload_csv, name='upload_csv'),

那接下来就是处理上传的文件并入库了，这个代码在views.py文件里，代码如下：

def upload_csv(request):
	data = {}
	if "GET" == request.method:
		return render(request, "myapp/upload_csv.html", data)
    # if not GET, then proceed
	try:
		csv_file = request.FILES["csv_file"]
		if not csv_file.name.endswith('.csv'):
			messages.error(request,'File is not CSV type')
			return HttpResponseRedirect(reverse("myapp:upload_csv"))
        #if file is too large, return
		if csv_file.multiple_chunks():
			messages.error(request,"Uploaded file is too big (%.2f MB)." % (csv_file.size/(1000*1000),))
			return HttpResponseRedirect(reverse("myapp:upload_csv"))

		file_data = csv_file.read().decode("utf-8")		

		lines = file_data.split("\n")
		#loop over the lines and save them in db. If error , store as string and then display
		for line in lines:						
			fields = line.split(",")
			data_dict = {}
			data_dict["name"] = fields[0]
			data_dict["start_date_time"] = fields[1]
			data_dict["end_date_time"] = fields[2]
			data_dict["notes"] = fields[3]
			try:
				form = EventsForm(data_dict)
				if form.is_valid():
					form.save()					
				else:
					logging.getLogger("error_logger").error(form.errors.as_json())												
			except Exception as e:
				logging.getLogger("error_logger").error(repr(e))					
				pass

	except Exception as e:
		logging.getLogger("error_logger").error("Unable to upload file. "+repr(e))
		messages.error(request,"Unable to upload file. "+repr(e))

	return HttpResponseRedirect(reverse("myapp:upload_csv"))

def upload_csv(request):

data = {}

if "GET" == request.method:

return render(request, "myapp/upload_csv.html", data)

# if not GET, then proceed

try:

csv_file = request.FILES["csv_file"]

if not csv_file.name.endswith('.csv'):

messages.error(request,'File is not CSV type')

return HttpResponseRedirect(reverse("myapp:upload_csv"))

#if file is too large, return

if csv_file.multiple_chunks():

messages.error(request,"Uploaded file is too big (%.2f MB)." % (csv_file.size/(1000*1000),))

return HttpResponseRedirect(reverse("myapp:upload_csv"))

file_data = csv_file.read().decode("utf-8")

lines = file_data.split("\n")

#loop over the lines and save them in db. If error , store as string and then display

for line in lines:

fields = line.split(",")

data_dict = {}

data_dict["name"] = fields[0]

data_dict["start_date_time"] = fields[1]

data_dict["end_date_time"] = fields[2]

data_dict["notes"] = fields[3]

try:

form = EventsForm(data_dict)

if form.is_valid():

form.save()

else:

logging.getLogger("error_logger").error(form.errors.as_json())

except Exception as e:

logging.getLogger("error_logger").error(repr(e))

pass

except Exception as e:

logging.getLogger("error_logger").error("Unable to upload file. "+repr(e))

messages.error(request,"Unable to upload file. "+repr(e))

return HttpResponseRedirect(reverse("myapp:upload_csv"))

代码解释：

最开始判断如果是get请求直接渲染upload_csv.html文件，如果是post请求那么进行分析处理，首先是检查文件名是否是以csv结尾的，如果是就处理，不是就提示错误信息，再就是检查下上传文件的大小，其实这些检查也还好，如果是自己整理的表导入基本也不用做这些检查了，如果是有多人操作这些检查就有必要了，然后开始循环迭代文件行，内容是以逗号分隔，这里假设就是4列，如果你的表格列很多可以做修改，最后保存入库，如果有错误就记录错误信息到日志文件中。

至此我们需要的功能就完成了，虽然简单但非常实用。

Django ORM if you already know SQL

If you are migrating to Django from another MVC framework, chances are you already know SQL.

In this post, I will be illustrating how to use Django ORM by drawing analogies to equivalent SQL statements. Connecting a new topic to your existing knowledge will help you learn to use the ORM faster.

Let us consider a simple base model for a person with attributes name, age, and gender.

To implement the above entity, we would model it as a table in SQL.

CREATE TABLE Person (
    id int,
    name varchar(50),
    age int NOT NULL,
    gender varchar(10),
);

CREATE TABLE Person (

id int,

name varchar(50),

age int NOT NULL,

gender varchar(10),

);

The same table is modeled in Django as a class which inherits from the base Model class. The ORM creates the equivalent table under the hood.

class Person(models.Model):
    name = models.CharField(max_length=50, blank=True)
    age = models.IntegerField()
    gender = models.CharField(max_length=10, blank=True)

class Person(models.Model):

name = models.CharField(max_length=50, blank=True)

age = models.IntegerField()

gender = models.CharField(max_length=10, blank=True)

The most used data types are:

SQL	Django
`INT`	`IntegerField()`
`VARCHAR(n)`	`CharField(max_length=n)`
`TEXT`	`TextField()`
`FLOAT(n)`	`FloatField()`
`DATE`	`DateField()`
`TIME`	`TimeField()`
`DATETIME`	`DateTimeField()`

The various queries we can use are:

SELECT Statement

Fetch all rows
SQL:

SELECT *
FROM Person;

1 2	SELECT * FROM Person;

Django:

persons = Person.objects.all()
for person in persons:
    print(person.name)
    print(person.gender)
    print(person.age)

persons = Person.objects.all()

for person in persons:

print(person.name)

print(person.gender)

print(person.age)

Fetch specific columns
SQL:

SELECT name, age
FROM Person;

1 2	SELECT name, age FROM Person;

Django:

Person.objects.only('name', 'age')

1	Person.objects.only('name', 'age')

etch distinct rows
SQL:

SELECT DISTINCT name, age
FROM Person;

1 2	SELECT DISTINCT name, age FROM Person;

Django:

Person.objects.values('name', 'age').distinct()

1	Person.objects.values('name', 'age').distinct()

Fetch specific number of rows
SQL:

SELECT *
FROM Person
LIMIT 10;

SELECT *

FROM Person

LIMIT 10;

Django:

Person.objects.all()[:10]

1	Person.objects.all()[:10]

LIMIT AND OFFSET keywords
SQL:

SELECT *
FROM Person
OFFSET 5
LIMIT 5;

SELECT *

FROM Person

OFFSET 5

LIMIT 5;

Django:

Person.objects.all()[5:10]

1	Person.objects.all()[5:10]

WHERE Clause

Filter by single column
SQL:

SELECT *
FROM Person
WHERE id = 1;

SELECT *

FROM Person

WHERE id = 1;

Django:

Person.objects.filter(id=1)

1	Person.objects.filter(id=1)

Filter by comparison operators
SQL:

WHERE age > 18;
WHERE age >= 18;
WHERE age < 18;
WHERE age <= 18;
WHERE age != 18;

WHERE age > 18;

WHERE age >= 18;

WHERE age < 18;

WHERE age <= 18;

WHERE age != 18;

Django:

Person.objects.filter(age__gt=18)
Person.objects.filter(age__gte=18)
Person.objects.filter(age__lt=18)
Person.objects.filter(age__lte=18)
Person.objects.exclude(age=18)

Person.objects.filter(age__gt=18)

Person.objects.filter(age__gte=18)

Person.objects.filter(age__lt=18)

Person.objects.filter(age__lte=18)

Person.objects.exclude(age=18)

BETWEEN Clause
SQL:

SELECT *
FROM Person 
WHERE age BETWEEN 10 AND 20;

SELECT *

FROM Person

WHERE age BETWEEN 10 AND 20;

Django:

Person.objects.filter(age__range=(10, 20))

1	Person.objects.filter(age__range=(10, 20))

LIKE operator
SQL:

WHERE name like '%A%';
WHERE name like binary '%A%';
WHERE name like 'A%';
WHERE name like binary 'A%';
WHERE name like '%A';
WHERE name like binary '%A';

WHERE name like '%A%';

WHERE name like binary '%A%';

WHERE name like 'A%';

WHERE name like binary 'A%';

WHERE name like '%A';

WHERE name like binary '%A';

Django:

Person.objects.filter(name__icontains='A')
Person.objects.filter(name__contains='A')
Person.objects.filter(name__istartswith='A')
Person.objects.filter(name__startswith='A')
Person.objects.filter(name__iendswith='A')
Person.objects.filter(name__endswith='A')

Person.objects.filter(name__icontains='A')

Person.objects.filter(name__contains='A')

Person.objects.filter(name__istartswith='A')

Person.objects.filter(name__startswith='A')

Person.objects.filter(name__iendswith='A')

Person.objects.filter(name__endswith='A')

IN operator
SQL:

WHERE id in (1, 2);

1	WHERE id in (1, 2);

Django:

Person.objects.filter(id__in=[1, 2])

1	Person.objects.filter(id__in=[1, 2])

AND, OR and NOT Operators

SQL:

WHERE gender='male' AND age > 25;

1	WHERE gender='male' AND age > 25;

Django:

Person.objects.filter(gender='male', age__gt=25)

1	Person.objects.filter(gender='male', age__gt=25)

SQL:

WHERE gender='male' OR age > 25;

1	WHERE gender='male' OR age > 25;

Django:

from django.db.models import Q
Person.objects.filter(Q(gender='male') | Q(age__gt=25))

1 2	from django.db.models import Q Person.objects.filter(Q(gender='male') \| Q(age__gt=25))

SQL:

WHERE NOT gender='male';

1	WHERE NOT gender='male';

Django:

Person.objects.exclude(gender='male')

1	Person.objects.exclude(gender='male')

Person.objects.exclude(gender='male')

1	Person.objects.exclude(gender='male')

https://amitness.com/2018/10/django-orm-for-sql-users/

让我们来做个django小项目之二

上篇内容我们算是来了一个开场，创建了我们的项目，最后把数据库也建立完成了，这篇我们主要完成后台数据入库的部分，根据我们之前的思路，如果已经能实时获得每个站点性能信息了，如果能将每次获得信息插入到数据库中，这个就完成了我们的入库部分，这里说一下我们的数据库操作部分，要操作数据库，首先我们要跟数据库建立连接，然后进行常规的CRUD操作，操作完毕后再关闭数据库连接，这是一个基本流程，所以为了提高数据库的操作速度，我们可以把一些常用的操作封装成一个类，以下是我门数据库操作的类代码：

class database:
    def __init__(self, dbname=None, dbhost=None):
        self._logger = logger
        if dbname is None:
            self._dbname = DBNAME
        else:
            self._dbname = dbname
        if dbhost is None:
            self._dbhost = DBHOST
        else:
            self._dbhost = dbhost
            
        self._dbuser = DBUSER
        self._dbpassword = DBPWD
        self._dbport = int(DBPORT)
        self._conn = self.connectMySQL()
        
        if(self._conn):
            self._cursor = self._conn.cursor()


    def connectMySQL(self):
        conn = False
        try:
            conn = MySQLdb.connect(host=self._dbhost,user=self._dbuser,passwd=self._dbpassword,db=self._dbname,port=self._dbport)
        except Exception,data:
            self._logger.error("connect database failed, %s" % data)
            conn = False
        return conn


    def fetch_all(self, sql):
        res = ''
        if(self._conn):
            try:
                self._cursor.execute(sql)
                res = self._cursor.fetchall()
            except Exception, data:
                res = False
                self._logger.warn("query database exception, %s" % data)
        return res


    def update(self, sql):
        flag = False
        if(self._conn):
            try:
                self._cursor.execute(sql)
                self._conn.commit()
                flag = True
            except Exception, data:
                flag = False
                self._logger.warn("update database exception, %s" % data)

        return flag

    def close(self):
        if(self._conn):
            try:
                if(type(self._cursor)=='object'):
                    self._cursor.close()
                if(type(self._conn)=='object'):
                    self._conn.close()
            except Exception, data:
                self._logger.warn("close database exception, %s,%s,%s" % (data, type(self._cursor), type(self._conn)))

class database:

def __init__(self, dbname=None, dbhost=None):

self._logger = logger

if dbname is None:

self._dbname = DBNAME

else:

self._dbname = dbname

if dbhost is None:

self._dbhost = DBHOST

else:

self._dbhost = dbhost

self._dbuser = DBUSER

self._dbpassword = DBPWD

self._dbport = int(DBPORT)

self._conn = self.connectMySQL()

if(self._conn):

self._cursor = self._conn.cursor()

def connectMySQL(self):

conn = False

try:

conn = MySQLdb.connect(host=self._dbhost,user=self._dbuser,passwd=self._dbpassword,db=self._dbname,port=self._dbport)

except Exception,data:

self._logger.error("connect database failed, %s" % data)

conn = False

return conn

def fetch_all(self, sql):

res = ''

if(self._conn):

try:

self._cursor.execute(sql)

res = self._cursor.fetchall()

except Exception, data:

res = False

self._logger.warn("query database exception, %s" % data)

return res

def update(self, sql):

flag = False

if(self._conn):

try:

self._cursor.execute(sql)

self._conn.commit()

flag = True

except Exception, data:

flag = False

self._logger.warn("update database exception, %s" % data)

return flag

def close(self):

if(self._conn):

try:

if(type(self._cursor)=='object'):

self._cursor.close()

if(type(self._conn)=='object'):

self._conn.close()

except Exception, data:

self._logger.warn("close database exception, %s,%s,%s" % (data, type(self._cursor), type(self._conn)))

在这个类中我们先定义了__init__方法，指定了数据库连接，用户，端口，用户等信息，接下来是定义了三个方法，分别是建立数据库连接，获取数据内容、更新数据库及最后关闭数据库连接，正题逻辑就是这样，代码没特别的内容。

有了这个数据库类后，我们就可以用上我们之前的pycurl模块去获取每个站点的数据了，每次获取的数据插入到数据库中，因为可能非常多的站点，所以我们要考虑运行效率，所以我们这里使用并发去检查站点数据，采用ThreadPool线程池去实现，全部代码如下：

#!/usr/bin/env python
#coding=utf-8


import MySQLdb
import MySQLdb.cursors
import logging
import requests
import pycurl
import StringIO
import sys
import socket
from multiprocessing.dummy import Pool as ThreadPool 


LOGPATH = '/tmp/conndb.log' 
DBNAME = 'dbname'
DBHOST = 'localhost'
DBUSER = 'root'
DBPWD = 'password'
DBPORT = '3306'

logging.basicConfig(level=logging.WARNING, filename=LOGPATH,filemode='w')

logger = logging.getLogger()

class database:
    def __init__(self, dbname=None, dbhost=None):
        self._logger = logger
        if dbname is None:
            self._dbname = DBNAME
        else:
            self._dbname = dbname
        if dbhost is None:
            self._dbhost = DBHOST
        else:
            self._dbhost = dbhost
            
        self._dbuser = DBUSER
        self._dbpassword = DBPWD
        self._dbport = int(DBPORT)
        self._conn = self.connectMySQL()
        
        if(self._conn):
            self._cursor = self._conn.cursor()


    def connectMySQL(self):
        conn = False
        try:
            conn = MySQLdb.connect(host=self._dbhost,user=self._dbuser,passwd=self._dbpassword,db=self._dbname,port=self._dbport)
        except Exception,data:
            self._logger.error("connect database failed, %s" % data)
            conn = False
        return conn


    def fetch_all(self, sql):
        res = ''
        if(self._conn):
            try:
                self._cursor.execute(sql)
                res = self._cursor.fetchall()
            except Exception, data:
                res = False
                self._logger.warn("query database exception, %s" % data)
        return res


    def update(self, sql):
        flag = False
        if(self._conn):
            try:
                self._cursor.execute(sql)
                self._conn.commit()
                flag = True
            except Exception, data:
                flag = False
                self._logger.warn("update database exception, %s" % data)

        return flag

    def close(self):
        if(self._conn):
            try:
                if(type(self._cursor)=='object'):
                    self._cursor.close()
                if(type(self._conn)=='object'):
                    self._conn.close()
            except Exception, data:
                self._logger.warn("close database exception, %s,%s,%s" % (data, type(self._cursor), type(self._conn)))    

def check(urls):
    #if url not in errorurl:
    global HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD
    url = 'http://' + str(urls[0])
    c = pycurl.Curl()
    c.setopt(pycurl.CONNECTTIMEOUT,5)
    c.setopt(pycurl.TIMEOUT,5)
    c.setopt(pycurl.NOPROGRESS,1)
    c.setopt(pycurl.FORBID_REUSE,1)
    c.setopt(pycurl.MAXREDIRS,3)
    c.setopt(pycurl.DNS_CACHE_TIMEOUT,30)
    c.setopt(c.URL, url)
    try:
        b = StringIO.StringIO()
        c.setopt(c.WRITEFUNCTION, b.write)
        c.perform()
        HTTP_CODE = c.getinfo(pycurl.HTTP_CODE)
        DNS_TIME = c.getinfo(c.NAMELOOKUP_TIME) * 1000
        CONNECT_TIME = c.getinfo(c.CONNECT_TIME) * 1000
        PRETRANSFER_TIME = c.getinfo(c.PRETRANSFER_TIME) * 1000
        STARTTRANSFER_TIME = c.getinfo(c.STARTTRANSFER_TIME) * 1000
        TOTAL_TIME = c.getinfo(c.TOTAL_TIME) * 1000
        SPEED_DOWNLOAD = c.getinfo(c.SPEED_DOWNLOAD)
        b.close()
        c.close()
    except pycurl.error, error:
        pass
        #errno, errstr = error
        #print 'An error occurred: ', errstr
    #insert to db
    status = 'ok'
    sqldb = database()
    sql = "update site_info set http_code=%d, dns_time=%f,connect_time=%f,pretransfer_time=%f,starttransfter_time=%f,total_time=%f,spee
d_download=%f,status='%s' where url='%s'" %(HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLO
AD,status,urls[0]) 
    sqldb.update(sql)
    sqldb.close()
    #print HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD


if __name__ == '__main__':
    sqldb = database()
    sql2 = """select url from site_info"""
    res = sqldb.fetch_all(sql2)
    sqldb.close()
    pool = ThreadPool(3)
    pool.map(check,res)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

#!/usr/bin/env python

#coding=utf-8

import MySQLdb

import MySQLdb.cursors

import logging

import requests

import pycurl

import StringIO

import sys

import socket

from multiprocessing.dummy import Pool as ThreadPool

LOGPATH = '/tmp/conndb.log'

DBNAME = 'dbname'

DBHOST = 'localhost'

DBUSER = 'root'

DBPWD = 'password'

DBPORT = '3306'

logging.basicConfig(level=logging.WARNING, filename=LOGPATH,filemode='w')

logger = logging.getLogger()

class database:

def __init__(self, dbname=None, dbhost=None):

self._logger = logger

if dbname is None:

self._dbname = DBNAME

else:

self._dbname = dbname

if dbhost is None:

self._dbhost = DBHOST

else:

self._dbhost = dbhost

self._dbuser = DBUSER

self._dbpassword = DBPWD

self._dbport = int(DBPORT)

self._conn = self.connectMySQL()

if(self._conn):

self._cursor = self._conn.cursor()

def connectMySQL(self):

conn = False

try:

conn = MySQLdb.connect(host=self._dbhost,user=self._dbuser,passwd=self._dbpassword,db=self._dbname,port=self._dbport)

except Exception,data:

self._logger.error("connect database failed, %s" % data)

conn = False

return conn

def fetch_all(self, sql):

res = ''

if(self._conn):

try:

self._cursor.execute(sql)

res = self._cursor.fetchall()

except Exception, data:

res = False

self._logger.warn("query database exception, %s" % data)

return res

def update(self, sql):

flag = False

if(self._conn):

try:

self._cursor.execute(sql)

self._conn.commit()

flag = True

except Exception, data:

flag = False

self._logger.warn("update database exception, %s" % data)

return flag

def close(self):

if(self._conn):

try:

if(type(self._cursor)=='object'):

self._cursor.close()

if(type(self._conn)=='object'):

self._conn.close()

except Exception, data:

self._logger.warn("close database exception, %s,%s,%s" % (data, type(self._cursor), type(self._conn)))

def check(urls):

#if url not in errorurl:

global HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD

url = 'http://' + str(urls[0])

c = pycurl.Curl()

c.setopt(pycurl.CONNECTTIMEOUT,5)

c.setopt(pycurl.TIMEOUT,5)

c.setopt(pycurl.NOPROGRESS,1)

c.setopt(pycurl.FORBID_REUSE,1)

c.setopt(pycurl.MAXREDIRS,3)

c.setopt(pycurl.DNS_CACHE_TIMEOUT,30)

c.setopt(c.URL, url)

try:

b = StringIO.StringIO()

c.setopt(c.WRITEFUNCTION, b.write)

c.perform()

HTTP_CODE = c.getinfo(pycurl.HTTP_CODE)

DNS_TIME = c.getinfo(c.NAMELOOKUP_TIME) * 1000

CONNECT_TIME = c.getinfo(c.CONNECT_TIME) * 1000

PRETRANSFER_TIME = c.getinfo(c.PRETRANSFER_TIME) * 1000

STARTTRANSFER_TIME = c.getinfo(c.STARTTRANSFER_TIME) * 1000

TOTAL_TIME = c.getinfo(c.TOTAL_TIME) * 1000

SPEED_DOWNLOAD = c.getinfo(c.SPEED_DOWNLOAD)

b.close()

c.close()

except pycurl.error, error:

pass

#errno, errstr = error

#print 'An error occurred: ', errstr

#insert to db

status = 'ok'

sqldb = database()

sql = "update site_info set http_code=%d, dns_time=%f,connect_time=%f,pretransfer_time=%f,starttransfter_time=%f,total_time=%f,spee

d_download=%f,status='%s' where url='%s'" %(HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLO

AD,status,urls[0])

sqldb.update(sql)

sqldb.close()

#print HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD

if __name__ == '__main__':

sqldb = database()

sql2 = """select url from site_info"""

res = sqldb.fetch_all(sql2)

sqldb.close()

pool = ThreadPool(3)

pool.map(check,res)

以上就是我们全部的代码内容，pycurl部分我们不在多解释，不理解的小伙伴可以参考之前的文章，Dummy就是多进程模块的克隆文件，唯一不同的是，多进程模块使用的是进程，而dummy则使用线程，pool.map类似python的map函数，需要二个参数第一个参数是一个函数名，第二个参数是个迭代对象，在这里迭代对象我们是从数据库中直接获取的，至于数据库的数据从哪里来的呢？当然是用户输入的，那下篇我们实现下前端输入页面，让用户自己可以增加要监控的站点，这篇就到这里，喜欢的小伙伴请帮忙转发哟。

站点性能监控demo

上篇我们简单介绍了pycurl的基本使用，在组后我们留了几个问题需要去解决，因为最终我们要实现的是一个页面版的展示所有站点性能的demo, 那这篇就是正式开始我们这个小项目，废话不多说，首先我们创建我们的项目，在命令行模式下输入：

django-admin.py startproject web_monitor

1	django-admin.py startproject web_monitor

然后进入web_monitor目录中，创建一个app.

python manage.py startapp  sitemonitor

1	python manage.py startapp sitemonitor

创建完成后我们的目录结构是这样的：

├── manage.py
├── sitemonitor
│ ├── admin.py
│ ├── __init__.py
│ ├── migrations
│ │└── __init__.py
│ ├── models.py
│ ├── tests.py
│ └── views.py
└── web_monitor
    ├── __init__.py
    ├── __init__.pyc
    ├── settings.py
    ├── settings.pyc
    ├── urls.py
    └── wsgi.py

├── manage.py

├── sitemonitor

│ ├── admin.py

│ ├── __init__.py

│ ├── migrations

│ │└── __init__.py

│ ├── models.py

│ ├── tests.py

│ └── views.py

└── web_monitor

├── __init__.py

├── __init__.pyc

├── settings.py

├── settings.pyc

├── urls.py

└── wsgi.py

因为数据要需要入库的，所以第一步先设计我们的表结构，也就是我们的Model文件，进入到sitemonitor目录下，打开models.py文件，写入：

from django.db import models


# Create your models here.

class site_info(models.Model):
    url = models.CharField(null=False, blank=False, db_column="url", db_index=True, unique=True,verbose_name="url", max_length=50)
    port = models.CharField(null=False, blank=False, db_column="port", db_index=True, unique=False,verbose_name="port", max_length=50)
    status = models.CharField(null=True, blank=True, db_column="status", db_index=True, unique=False,verbose_name="status", max_length=
50)
    phone = models.CharField(null=True, blank=True, db_column="phone", db_index=True, unique=False,verbose_name="phone", max_length=50)
    http_code = models.SmallIntegerField(null=True,db_column="http_code",blank=True,db_index=True)
    dns_time = models.DecimalField(null=True,db_column="dns_time", blank=True,db_index=True,max_digits=6,decimal_places=3)
    connect_time = models.DecimalField(null=True,db_column="connect_time",blank=True, db_index=True,max_digits=6,decimal_places=3)
    pretransfer_time = models.DecimalField(null=True,db_column="pretransfer_time",blank=True, db_index=True,max_digits=6,decimal_places
=3)
    starttransfter_time = models.DecimalField(null=True,db_column="starttransfter_time",blank=True, db_index=True,max_digits=6,decimal_
places=3)
    total_time = models.DecimalField(null=True,db_column="total_time", blank=True,db_index=True,max_digits=6,decimal_places=3)
    speed_download = models.DecimalField(null=True,db_column="speed_download",blank=True, db_index=True,max_digits=10,decimal_places=2)

    class Meta:
        db_table = 'site_info'

    def __unicode__(self):
        return self.url

from django.db import models

# Create your models here.

class site_info(models.Model):

url = models.CharField(null=False, blank=False, db_column="url", db_index=True, unique=True,verbose_name="url", max_length=50)

port = models.CharField(null=False, blank=False, db_column="port", db_index=True, unique=False,verbose_name="port", max_length=50)

status = models.CharField(null=True, blank=True, db_column="status", db_index=True, unique=False,verbose_name="status", max_length=

50)

phone = models.CharField(null=True, blank=True, db_column="phone", db_index=True, unique=False,verbose_name="phone", max_length=50)

http_code = models.SmallIntegerField(null=True,db_column="http_code",blank=True,db_index=True)

dns_time = models.DecimalField(null=True,db_column="dns_time", blank=True,db_index=True,max_digits=6,decimal_places=3)

connect_time = models.DecimalField(null=True,db_column="connect_time",blank=True, db_index=True,max_digits=6,decimal_places=3)

pretransfer_time = models.DecimalField(null=True,db_column="pretransfer_time",blank=True, db_index=True,max_digits=6,decimal_places

=3)

starttransfter_time = models.DecimalField(null=True,db_column="starttransfter_time",blank=True, db_index=True,max_digits=6,decimal_

places=3)

total_time = models.DecimalField(null=True,db_column="total_time", blank=True,db_index=True,max_digits=6,decimal_places=3)

speed_download = models.DecimalField(null=True,db_column="speed_download",blank=True, db_index=True,max_digits=10,decimal_places=2)

class Meta:

db_table = 'site_info'

def __unicode__(self):

return self.url

这个文件我们定义了表结构和字段，按上篇我们获取的内容，我们存储的内容有http_code, dns_time,connect_time, pretransfer_time, starttransfter_time,total_time, speed_downlaod字段，url,port,phone这三个字段是为用户输入表单时准备的，这三个后续会用到的时候再说，这里只需要注意因为获取的数字很多都是小数，所以像dns_time我们使用了models.DecimalField字段类型，这个字段类型有2个参数：

max_digits：数字允许的最大位数

decimal_places：小数的最大位数

其它字段类型解释如下：

models.CharField：字符串字段，max_length是必须的参数。

models.SmallIntegerField : 数据库中的字段有：tinyint、smallint、int、bigint. 类似 IntegerField，不过只允许某个取值范围内的整数。(依赖数据库)

然后保存退出，打开site_monitor/settings.py文件，修改如下行：

# Application definition

INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'sitemonitor',    #add here
)

# Application definition

INSTALLED_APPS = (

'django.contrib.admin',

'django.contrib.auth',

'django.contrib.contenttypes',

'django.contrib.sessions',

'django.contrib.messages',

'django.contrib.staticfiles',

'sitemonitor', #add here

)

加入的行我已加注释，不多解释，接下来我们创建数据库表，运行：

python manger.py makemigrations

这个会在我们的app下建立 migrations目录，并记录下所有的关于modes.py的改动，比如0001_initial.py， 但是这个改动还没有作用到数据库文件，
后续如果我们改动了models.py这个文件就要执行这个命令

python manger.py makemigrations

这个会在我们的app下建立 migrations目录，并记录下所有的关于modes.py的改动，比如0001_initial.py，但是这个改动还没有作用到数据库文件，

后续如果我们改动了models.py这个文件就要执行这个命令

接着运行：

python manager.py migrate

将该改动作用到数据库文件，比如产生table，修改字段的类型。

python manager.py migrate

将该改动作用到数据库文件，比如产生table，修改字段的类型。

这时候我们的库表结果就建立完成了，那这样前期准备工作就全部完成了，下一篇我们开始写后台实现数据实时插入库。

python+pycurl检查网站性能指标

对于一个网站运维人员来说，一个网站的性能好坏是大家非常关心的内容，我们知道衡量一个网站的好好多方面，但对运维人员来说站点的性能指标是我们比较关注的内容，从用户角度来说，最直观的感受就是用户户输入网址后站点内容打开速度的快慢，那从一个用户发起请求到服务器，然后服务器响应提供给数据给客户端，了解http协议的小伙伴应该知道，这中间要经过多个步骤才能实现，所以说一个站点如果出现访问速度上的问题，要从整条链路上去查原因而不能只看某一方面，访问示意图如下：

在这个访问过程中我们会关注以下几个指标：

DNS解析所消耗时间
从建立连接到准备传输所消耗的时间
从建立连接到传输开始消耗的时间
传输结束所消耗的总时间
平均下载速度
返回的HTTP状态码

通过对以上几个指标的实时监测，当出现问题时就可以快速分析定位问题，那如何获得这些数据呢，下面就要用到我们的pycurl了，Pycurl包是一个libcurl的Python接口，它是由C语言编写的，速度非常的快，下面看看我们的具体实现：

import pycurl
import StringIO

def check(urls):
    #if url not in errorurl:
    global HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD
    url = 'http://' + str(urls)
    c = pycurl.Curl()
    c.setopt(pycurl.CONNECTTIMEOUT,5)
    c.setopt(pycurl.TIMEOUT,5)
    c.setopt(pycurl.NOPROGRESS,1)
    c.setopt(pycurl.FORBID_REUSE,1)
    c.setopt(pycurl.MAXREDIRS,3)
    c.setopt(pycurl.DNS_CACHE_TIMEOUT,30)
    c.setopt(c.URL, url)
    try:
        b = StringIO.StringIO()
        c.setopt(c.WRITEFUNCTION, b.write)
        c.perform()
        HTTP_CODE = c.getinfo(pycurl.HTTP_CODE)    #HTTP状态码
        DNS_TIME = c.getinfo(c.NAMELOOKUP_TIME) * 1000  #DNS消耗时间
        CONNECT_TIME = c.getinfo(c.CONNECT_TIME) * 1000  #建连消耗时间
        PRETRANSFER_TIME = c.getinfo(c.PRETRANSFER_TIME) * 1000 #建连到准备传输消耗
        STARTTRANSFER_TIME = c.getinfo(c.STARTTRANSFER_TIME) * 1000 #建连到传输消耗
        TOTAL_TIME = c.getinfo(c.TOTAL_TIME) * 1000  #传输结束消耗时间
        SPEED_DOWNLOAD = c.getinfo(c.SPEED_DOWNLOAD) #平均下载速度
        b.close()
        c.close()
    except pycurl.error, error:
        pass
        #errno, errstr = error
        #print 'An error occurred: ', errstr
    return HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD



with open('site.txt','a+') as f:
    f.write('HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD' + '\n')
    res = check('www.mindg.cn')
    f.write(str(res))

import pycurl

import StringIO

def check(urls):

#if url not in errorurl:

global HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD

url = 'http://' + str(urls)

c = pycurl.Curl()

c.setopt(pycurl.CONNECTTIMEOUT,5)

c.setopt(pycurl.TIMEOUT,5)

c.setopt(pycurl.NOPROGRESS,1)

c.setopt(pycurl.FORBID_REUSE,1)

c.setopt(pycurl.MAXREDIRS,3)

c.setopt(pycurl.DNS_CACHE_TIMEOUT,30)

c.setopt(c.URL, url)

try:

b = StringIO.StringIO()

c.setopt(c.WRITEFUNCTION, b.write)

c.perform()

HTTP_CODE = c.getinfo(pycurl.HTTP_CODE) #HTTP状态码

DNS_TIME = c.getinfo(c.NAMELOOKUP_TIME) * 1000 #DNS消耗时间

CONNECT_TIME = c.getinfo(c.CONNECT_TIME) * 1000 #建连消耗时间

PRETRANSFER_TIME = c.getinfo(c.PRETRANSFER_TIME) * 1000 #建连到准备传输消耗

STARTTRANSFER_TIME = c.getinfo(c.STARTTRANSFER_TIME) * 1000 #建连到传输消耗

TOTAL_TIME = c.getinfo(c.TOTAL_TIME) * 1000 #传输结束消耗时间

SPEED_DOWNLOAD = c.getinfo(c.SPEED_DOWNLOAD) #平均下载速度

b.close()

c.close()

except pycurl.error, error:

pass

#errno, errstr = error

#print 'An error occurred: ', errstr

return HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD

with open('site.txt','a+') as f:

f.write('HTTP_CODE,DNS_TIME,CONNECT_TIME,PRETRANSFER_TIME,STARTTRANSFER_TIME,TOTAL_TIME,SPEED_DOWNLOAD' + '\n')

res = check('www.mindg.cn')

f.write(str(res))

脚本里我只获取了一些比较核心的指标，当然pycurl功能不止这些，如果需要它还可以获取更细致的指标，例如：平均上传速度，HTTP头部大小等。有需要的小伙伴可以自己再研究下，脚本不做多解释了，关键行我加了注释，最终获取的数据写入到了文本里，可以定时执行这个脚本来实时获取最新数据，这个脚本对要求不高的场景使用没啥问题，但对我们自动化运维来说只迈出了第一步而已，因为最好的方式是通过一个web页面去实时展示各个站点的情况，便于只观的观看，那要实现一个基本的站点，有几个问题我们需要我们思考去解决：

获取的数据要保存下来，就是有一个入库操作
随着站点的增加，后台运行，为效率考虑必须要用并发执行
前台方面：入库的数据要在前台展示，就需要用ajax
前台展示的页面要能主动自动刷新
前台入口需要提供一个用户添加url地址的表单
当然为好看，我们也要使用boostrap等类似的前端框架。

做完这些才算是基本完成我们最终的目标，在后续的文章里我们会逐一完成上面任务，喜欢的小伙伴还请转发，谢谢。

一	二	三	四	五	六	日
« 十一
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31