MySQL基本操作（六）：建表，插入多条记录，更新表中特定数据

代码如下：

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

import MySQLdb

#create HostCluster database first
def CreateHostTable():
    conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
    cursor = conn.cursor()
    cursor.execute("DROP TABLE IF EXISTS compute_nodes")
    sql = """create table compute_nodes(cpu_total INT,
                                      cpu_used INT,
                                      mem_total INT,
                                      mem_used INT,
                                      name VARCHAR(20) NOT NULL)"""
    cursor.execute(sql)
    conn.close()

def InsertHostData():
    conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
    cursor = conn.cursor()
    sql = """insert into compute_nodes(cpu_total,cpu_used,mem_total,mem_used,name) values
             (24, 0, 4096, 0, 'redhat1'),(24, 0, 4096, 0, 'redhat2'),(24, 0, 4096, 0, 'redhat3'),
             (24, 0, 4096, 0, 'windows1'),(24, 0, 4096, 0, 'windows2'),(24, 0, 4096, 0, 'windows3')"""
    cursor.execute(sql)
    conn.commit()
    conn.close()

class UpdateHostdb(object):
    def __init__(self, taskhost):
        self.taskhost = taskhost

    def SetAvailable(self):
        conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
        cursor = conn.cursor()
        for host in self.taskhost:
            sql="update compute_nodes set cpu_used = 0 where name = '%s'" % host
            cursor.execute(sql)
            conn.commit()
        conn.close()
    def SetNOTavailable(self):
        conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
        cursor = conn.cursor()
        for host in self.taskhost:
            sql="update compute_nodes set cpu_used = 24 where name = '%s'" % host
            cursor.execute(sql)
            conn.commit()
        conn.close()


if __name__ == '__main__':

    #CreateHostTable()
    #InsertHostData()
    taskhost = ['redhat1', 'redhat2']
    updatedb = UpdateHostdb(taskhost)
    updatedb.SetNOTavailable()
    #updatedb.SetAvailable()

更新后的代码如下：

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

import MySQLdb

#create HostCluster database first
def CreateHostTable():
    conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
    cursor = conn.cursor()
    cursor.execute("DROP TABLE IF EXISTS compute_nodes")
    sql = """create table compute_nodes(cpu_total INT,
                                      cpu_used INT,
                                      mem_total INT,
                                      mem_used INT,
                                      name VARCHAR(20) NOT NULL)"""
    cursor.execute(sql)
    conn.close()

def CreateResultTable():
    conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
    cursor = conn.cursor()
    cursor.execute("DROP TABLE IF EXISTS schedule_result")
    sql = """create table schedule_result(task_id INT,
                                          name VARCHAR(20) NOT NULL)"""
    cursor.execute(sql)
    conn.close()


def InsertHostData():
    conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
    cursor = conn.cursor()
    sql = """insert into compute_nodes(cpu_total,cpu_used,mem_total,mem_used,name) values
             (24, 0, 4096, 0, 'redhat1'),(24, 0, 4096, 0, 'redhat2'),(24, 0, 4096, 0, 'redhat3'),
             (24, 0, 4096, 0, 'windows1'),(24, 0, 4096, 0, 'windows2'),(24, 0, 4096, 0, 'windows3')"""
    cursor.execute(sql)
    conn.commit()
    conn.close()

class UpdateHostdb(object):
    def __init__(self, task_id, res):
        self.task_id = task_id
        self.res = res
    #1.由调度结果得到被调度任务的一串物理机名字
    def GetHostName(self):
        ip_to_hostname = {'192.168.10.111':'redhat1','192.168.10.55':'redhat2','192.168.10.113':'redhat3',
                          '192.168.10.114':'windows1','192.168.10.115':'windows2','192.168.10.116':'windows3'}
        taskhost = []
        for i in range(len(self.res)):
            ip = res[i]['host']
            name = ip_to_hostname[ip]
            taskhost.append(name)
        #print taskhost
        return taskhost
    #2、将调度结果写入数据库，并将资源标记为不可用  
    def CreateResult(self):
        taskhost = self.GetHostName()
        print 'taskhost: ', taskhost
        conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
        cursor = conn.cursor()
        for host in taskhost:
            sql="insert into schedule_result values (%d, '%s')" % (task_id, host)
            cursor.execute(sql)
            conn.commit()
        conn.close()
        print "create task host into db finished!"

    def SetNOTavailable(self):
        taskhost = self.GetHostName()
        conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
        cursor = conn.cursor()
        for host in taskhost:
            sql="update compute_nodes set cpu_used = 24 where name = '%s'" % host
            cursor.execute(sql)
            conn.commit()
        conn.close()
        print "set host NOT available finished !"
#3、任务结束后凭task_id释放相应物理资源
class UpdateHostdb_Client():

    def __init__(self, task_id):
        self.task_id = task_id

    def GetHostFromDB(self):
        conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
        cursor = conn.cursor()
        cursor.execute("select name from schedule_result where task_id = %d" % task_id)
        taskhost = [] 
        for item in cursor.fetchall():
            taskhost.append(item[0])
        print 'get taskhost by task_id: ', taskhost
        return taskhost
    def SetAvailable(self):
        taskhost = self.GetHostFromDB()
        conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "111111", db = "HostCluster" )
        cursor = conn.cursor()
        for host in taskhost:
            sql="update compute_nodes set cpu_used = 0 where name = '%s'" % host
            cursor.execute(sql)
            conn.commit()
        conn.close()
        print "set host available finished !"




if __name__ == '__main__':

    #CreateHostTable()
    #CreateResultTable()
    #InsertHostData()
    #taskhost = ['redhat1', 'redhat2']
    #updatedb = UpdateHostdb(taskhost)
    #updatedb.SetNOTavailable()
    #updatedb.SetAvailable()

    res = [{'mem': 2048, 'host': '192.168.10.114', 'cpu': 2}, {'mem': 4096, 'host': '192.168.10.115', 'cpu': 4}]
    task_id = 123
    updatedb = UpdateHostdb(task_id, res)
    #updatedb.CreateResult()
    #updatedb.SetNOTavailable()

    updatedb_client = UpdateHostdb_Client(task_id)
    updatedb_client.GetHostFromDB()
    updatedb_client.SetAvailable()