python mysql update data

最新推荐文章于 2024-05-29 10:40:35 发布

Terry_Tsang

最新推荐文章于 2024-05-29 10:40:35 发布

阅读量5.3k

点赞数

分类专栏： python 文章标签： python mysql

本文链接：https://blog.csdn.net/signmem/article/details/78512134

版权

python 专栏收录该内容

15 篇文章 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