python mysql update data

╰半夏微凉° 2022-06-04 23:49 84阅读 0赞

# 目的 #

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

# import 说明 #

parameter 属于自定义配置文件, 忽略说明,   格式 dict
    logging ,  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/python
    
    import parameter
    import logging
    import logging.config
    import mysql.connector
    import sys
    
    
    
    class 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.py ceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 9c1ee88e-e38a-4a93-982f-342b07ed42c0 to nova.block_device_mapping success ceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 9c1ee88e-e38a-4a93-982f-342b07ed42c0 to cinder.volumes success ceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 98550a27-dd07-404a-b89a-438c80ba2ed0 to nova.block_device_mapping success ceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 98550a27-dd07-404a-b89a-438c80ba2ed0 to cinder.volumes success ceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 8432085b-6e2a-440b-873f-d6d98ef191c2 to nova.block_device_mapping success ceph_migration: DEBUG [CHANGEDB] 496d79d9-f032-41a2-b0c1-d9ec7e1b99fd 8432085b-6e2a-440b-873f-d6d98ef191c2 to cinder.volumes success