python mysql update data

来源：互联网发布：自定义仅安装windows 编辑：程序博客网时间：2024/06/05 09:42

目的

通过 python 直接修改数据库, 更新 instances 中 volumes 挂载关系本文主要描述 python update db 功能

import 说明

parameter 属于自定义配置文件, 忽略说明,   格式 dictlogging ,  logging.config  用于自定义日志格式 忽略说明 mysql.connector 主要是 python 连接 mysql 的库要安装下面软件满足 mysql.connector 导入yum install -y mysql-connector-python.noarch

sql 连接代码简单说明

    def updateDB(self,sqlupdate,data):        """        :param sqlupdate:  直接定义需呀执行的sql , 支持 delete, update, insert         :param data:       直接定义对应 delete, update, insert 中的 db.table.cols 信息        :return:         """        dbconfig = {'host':self.dbhost, 'user':self.dbuser, 'port':self.dbport, 'database':'nova', 'charset':'utf8', 'password':self.dbpassword }        sqlquery = sqlupdate        try:            dbc = mysql.connector.connect(**dbconfig)        except mysql.connector.Error as e:            logger.error('[UPDATEDB] ' + str(e))  // 注意, 我用到了自定义日志方法, 正常直接 return e 或者 print e 就可以了            sys.exit(1)        cursor = dbc.cursor()      // 创建游标, 用于支持 sql 操作        try:            cursor.execute(sqlquery, data)            dbc.commit()        except mysql.connector.Error as e:            logger.error('[UPDATEDB] ' + str(e))   // 与上面同理, 自己注意            sys.exit(1)        finally:            cursor.close()            dbc.close()

参考 sql, 与 data 定义方法

            for volume in volumes:                descfile = self.pycephpool + '/volume-' + volume                devBlockMapInfo = '需要进行 update 的 hard code'                dbinfo = devBlockMapInfo.replace('\'','\"')  // 确保数据使用 "// 这里定义了两个需要更新的表                novaupdate = "update nova.block_device_mapping set connection_info=%s where volume_id=%s and instance_uuid=%s and deleted=0"// 说明一下, %s 代表字符, 上文中, 那么定义了 3 个变量                novadata = (dbinfo, volume, uuid)// 变量由 tuple 组成则可, 注意, 要满足 sql 中定义的顺序                cinderupdate = "update cinder.volumes set host=%s, volume_type_id=%s where id=%s and instance_uuid=%s"                self.updateDB(sqlupdate=novaupdate, data=novadata)                logger.debug('[CHANGEDB] ' + uuid + ' ' + volume + ' to nova.block_device_mapping success')                cinderdata = (self.pycephhost, self.pycephserviceid, volume, uuid)                self.updateDB(sqlupdate=cinderupdate, data=cinderdata)                logger.debug('[CHANGEDB] ' + uuid + ' ' + volume + ' to cinder.volumes success')

参考测试 python code

#!/usr/bin/pythonimport parameterimport loggingimport logging.configimport mysql.connectorimport sysclass vmChange():    def __init__(self,conf, uuid, volumes):        self.uuid = uuid        self.volumes = volumes        self.dbhost = conf['dbhost']        self.dbpassword = conf['dbpassword']        self.dbport = conf['dbport']        self.dbuser = conf['dbuser']        self.pycephconf = conf['pycephconf']        self.pycephuser = conf['pycephuser']        self.pycephpool = conf['pycephpool']        self.backupdir = conf['backupdir']        self.rsyncdir = conf['rsyncdir']        self.pycephmon = conf['pycephmon']        self.pycephid = conf['pycephid']        self.pycephhost = conf['pycephhost']        self.pycephserviceid = conf['pycephserviceid']    def updateDB(self,sqlupdate,data):        """        :param sqlupdate:  sql command        :param data:       update info        :return:        """        dbconfig = {'host':self.dbhost, 'user':self.dbuser, 'port':self.dbport, 'database':'nova', 'charset':'utf8', 'password':self.dbpassword }        sqlquery = sqlupdate        try:            dbc = mysql.connector.connect(**dbconfig)        except mysql.connector.Error as e:            logger.error('[UPDATEDB] ' + str(e))            sys.exit(1)        cursor = dbc.cursor()        try:            cursor.execute(sqlquery, data)            dbc.commit()        except mysql.connector.Error as e:            logger.error('[UPDATEDB] ' + str(e))            sys.exit(1)        finally:            cursor.close()            dbc.close()    def changeCephVolumeDB(self,uuid,volumes):        """         :uuid: openstack instance uuid.   type: str         :volumes: instance using volumes  type: list        """#        volumes = self.volumeGetID(uuid)        if  len(volumes) < 1:            logger.info('[ceph_CHANGEDB] ' + uuid  + ' no volumes change needed.')            sys.exit(0)        else:            for volume in volumes:                descfile = self.pycephpool + '/volume-' + volume                devBlockMapInfo = '{"driver_volume_type": "rbd", "serial": "' + volume + \                          '", "data": {"secret_type": "ceph", "device_path": null, "name": "'\                          + descfile + '", "secret_uuid": "' + self.pycephid + \                          '", "qos_specs": null, "hosts": ' +  str(self.pycephmon) + \                          ', "auth_enabled": true, "access_mode": "rw", "auth_username": "' + \                          self.pycephuser + '", "ports": ["6789", "6789", "6789"]}}'                dbinfo = devBlockMapInfo.replace('\'','\"')                novaupdate = "update nova.block_device_mapping set connection_info=%s where volume_id=%s and instance_uuid=%s and deleted=0"                novadata = (dbinfo, volume, uuid)                self.updateDB(sqlupdate=novaupdate, data=novadata)                logger.debug('[CHANGEDB] ' + uuid + ' ' + volume + ' to nova.block_device_mapping success')                cinderupdate = "update cinder.volumes set host=%s, volume_type_id=%s where id=%s and instance_uuid=%s"                cinderdata = (self.pycephhost, self.pycephserviceid, volume, uuid)                self.updateDB(sqlupdate=cinderupdate, data=cinderdata)                logger.debug('[CHANGEDB] ' + uuid + ' ' + volume + ' to cinder.volumes success')if __name__ == '__main__':    logging.config.fileConfig("./logger.conf")    logger = logging.getLogger("ceph_migration")    openstackconf = parameter.openstack    uuid = '496d79d9-f032-41a2-b0c1-d9ec7e1b99fd'    volumes = [ '9c1ee88e-e38a-4a93-982f-342b07ed42c0', '98550a27-dd07-404a-b89a-438c80ba2ed0', '8432085b-6e2a-440b-873f-d6d98ef191c2']    dbchange = vmChange(openstackconf, uuid, volumes)    dbchange.changeCephVolumeDB(uuid,volumes)

参考日志信息

当连接被防火墙拒绝时, 程序将会返回下面错误信息ceph_migration: ERROR    [UPDATEDB] 2003: Can't connect to MySQL server on '192.168.86.35:3306' (113 No route to host)当数据库权限错误, 则返回下面信息ceph_migration: ERROR    [UPDATEDB] 1143 (42000): SELECT command denied to user 'terry'@'10.100.84.43' for column 'volume_id' in table 'block_device_mapping'当成功 update 数据, 则返回下面日志[root@gx-yun-084043 test]# python dbf.pyceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 9c1ee88e-e38a-4a93-982f-342b07ed42c0 to nova.block_device_mapping successceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 9c1ee88e-e38a-4a93-982f-342b07ed42c0 to cinder.volumes successceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 98550a27-dd07-404a-b89a-438c80ba2ed0 to nova.block_device_mapping successceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 98550a27-dd07-404a-b89a-438c80ba2ed0 to cinder.volumes successceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 8432085b-6e2a-440b-873f-d6d98ef191c2 to nova.block_device_mapping successceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 8432085b-6e2a-440b-873f-d6d98ef191c2 to cinder.volumes success

阅读全文

0 0