Python MySQLdb.Error() Examples

def SaveMy(sql):
    conn = MySQLdb.connect(
            host = host,
            user = user,
            passwd = pw,
            db = db,
            port = port,
            charset='utf8')
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        conn.commit()

    except MySQLdb.Error,e:
        # Rollback in case there is any error
        #mysqlErro = "Mysql Error %d: %s" % (e.args[0], e.args[1])
        conn.rollback()

#get monitor data 

def get_record_total_num(userid) :
    global dbconn
    query_str = 'select count(id) from %s where %s = %d' %(table_name, column_name[0], userid)
    #print query_str
    if (dbconn == None ):
        dbconn = connect_db()
    if (dbconn) :
        try:
            cur = dbconn.cursor();
            count = cur.execute(query_str)
            #print 'There is %s rows record' %count
            result = cur.fetchone()
            #print result
            #results = cur.fetchall()
        except MySQLdb.Error,e:
            warnString = " Mysql Error sql = %d %s " % (e.args[0],e.args[1])
            log_init().warning(warnString)
            sys.exit(1) 

def get_db_version(self):
        ''' Obtain the database schema version.
        Return: (negative, text) if error or version 0.0 where schema_version table is missing
                (version_int, version_text) if ok
        '''
        cmd = "SELECT version_int,version,openmano_ver FROM schema_version"
        for retry_ in range(0,2):
            try:
                with self.con:
                    self.cur = self.con.cursor()
                    #print cmd
                    self.cur.execute(cmd)
                    rows = self.cur.fetchall()
                    highest_version_int=0
                    highest_version=""
                    #print rows
                    for row in rows: #look for the latest version
                        if row[0]>highest_version_int:
                            highest_version_int, highest_version = row[0:2]
                    return highest_version_int, highest_version
            except (mdb.Error, AttributeError), e:
                #print cmd
                print "get_db_version DB Exception %d: %s" % (e.args[0], e.args[1])
                r,c = self.format_error(e)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def initial_setup(db, cursor):
    # Reads into database table with ID's of super PACs to be excluded from this analysis.
    csv_data = csv.reader(file('superPACs.csv', 'rU'))
    sql = []
    cursor.execute("DROP TABLE IF EXISTS super_PACs_list;")
    cursor.execute("""CREATE TABLE super_PACs_list (
                num CHAR(9) NOT NULL,
                fecid CHAR(11) NOT NULL,
                name VARCHAR(255) NOT NULL,
                filer VARCHAR(255) NOT NULL);""")
    sql.append("LOCK TABLES super_PACs_list WRITE, fec_committees AS T READ;")
    cursor.execute("LOAD DATA LOCAL INFILE 'superPACs.csv' into TABLE super_PACs_list fields terminated by ',' OPTIONALLY ENCLOSED BY '\"' lines terminated by '\r';")
    sql.append("UNLOCK TABLES;")
    sql.append("ALTER TABLE super_PACs_list ADD INDEX fecid (fecid);")
    commit_changes(db, cursor, sql)
    print "Table super_PACs_list"

    # Adds indexes to fec_committee_contributions before saving a constrained subset as fec_contributions.
    try:
        cursor.execute("ALTER TABLE fec_committee_contributions ADD INDEX combo (transaction_type, entity_type, date, fec_committee_id, other_id);")
    except MySQLdb.Error, e:
        handle_error(db, e)

    # Constrains FEC's fec_committee_contributions table to our needs: select subset of attributes that will be useful in queries, constrain on transaction type '24K', entity type 'PAC', year 2003 or later, contributor and recipient not present in list of super PACs. 

def _drop_db(self, database):
        """
        Drop a single database.

        Args:
            database (str): Database name.
        """
        if not self.dry_run:
            try:
                self.cursor.execute(
                    'DROP DATABASE IF EXISTS `{}`'.format(database)
                )
            except MySQLError as exc:
                logger.exception(
                    'Unable to remove MySQL DB: %s. %s', database, exc
                ) 

def _get_db_users(self, hash_prefix):
        """
        List of users filtering on a hash prefix.

        Args:
            hash_prefix (str): Hash prefix used to filter users.

        Returs:
            [tuple]: List of tuples with usernames.
        """
        if not self.cursor:
            logger.error('ERROR: Not connected to the database')
            return []
        prefix = "{hash_prefix}\\_%%".format(hash_prefix=hash_prefix)
        try:
            self.cursor.execute("SELECT User from mysql.user where User like %(prefix)s", {"prefix": prefix})
        except MySQLError as exc:
            logger.exception('Unable to retrieve old databases: %s', exc)
            return []
        return self.cursor.fetchall() 

def delete_row_by_key(self, table, key, value):
        for retry_ in range(0,2):
            cmd=""
            try:
                with self.con:
                    #delete host
                    self.cur = self.con.cursor()
                    cmd = "DELETE FROM %s" % (table)
                    if key!=None:
                        if value!=None:
                            cmd += " WHERE %s = '%s'" % (key, value)
                        else:
                            cmd += " WHERE %s is null" % (key)
                    else: #delete all
                        pass
                    self.logger.debug(cmd)
                    self.cur.execute(cmd)
                    deleted = self.cur.rowcount
                    if deleted < 1:
                        return -1, 'Not found'
                        #delete uuid
                    return 0, deleted
            except (mdb.Error, AttributeError) as e:
                r,c = self.format_error(e, "delete_row_by_key", cmd, "delete", 'instances' if table=='hosts' or table=='tenants' else 'dependencies')
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def update_datacenter_nets(self, datacenter_id, new_net_list=[]):
        ''' Removes the old and adds the new net list at datacenter list for one datacenter.
        Attribute 
            datacenter_id: uuid of the datacenter to act upon
            table: table where to insert
            new_net_list: the new values to be inserted. If empty it only deletes the existing nets
        Return: (Inserted items, Deleted items) if OK, (-Error, text) if error
        '''
        for retry_ in range(0,2):
            created_time = time.time()
            try:
                with self.con:
                    self.cur = self.con.cursor()
                    cmd="DELETE FROM datacenter_nets WHERE datacenter_id='%s'" % datacenter_id
                    print cmd
                    self.cur.execute(cmd)
                    deleted = self.cur.rowcount
                    for new_net in new_net_list:
                        created_time += 0.00001
                        self._new_row_internal('datacenter_nets', new_net, tenant_id=None, add_uuid=True, created_time=created_time)
                    return len (new_net_list), deleted
            except (mdb.Error, AttributeError), e:
                print "nfvo_db.update_datacenter_nets DB Exception %d: %s" % (e.args[0], e.args[1])
                r,c = self.format_error(e)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def initial_setup(db, cursor):
    # Reads into database table with ID's of super PACs to be excluded from this analysis.
    sql = []
    sql.append("DROP TABLE IF EXISTS super_PACs_list;")
    sql.append(""" CREATE TABLE super_PACs_list (
                fecid CHAR(9) NOT NULL);""")
    sql.append("LOCK TABLES super_PACs_list WRITE, fec_committees AS T READ;")
    sql.append("INSERT INTO super_PACs_list (fecid) SELECT T.fecid FROM fec_committees T WHERE T.is_super_PAC = '1';")
    sql.append("UNLOCK TABLES;")
    sql.append("ALTER TABLE super_PACs_list ADD INDEX fecid (fecid);")
    commit_changes(db, cursor, sql)
    print "Table super_PACs_list"

    # Adds indexes to fec_committee_contributions before saving a constrained subset as fec_contributions.
    try:
        cursor.execute("ALTER TABLE fec_committee_contributions ADD INDEX combo (transaction_type, entity_type, date, fec_committee_id, other_id);")
    except MySQLdb.Error, e:
        handle_error(db, e)

    # Constrains FEC's fec_committee_contributions table to our needs: select subset of attributes that will be useful in queries, constrain on transaction type '24K', entity type 'PAC', year 2003 or later, contributor and recipient not present in list of super PACs. 

def _get_integration_databases(self):
        """
        List of integration databases.
        """
        if not self.cursor:
            logger.error('ERROR: Not connected to the database')
            return []

        query = (
            "SELECT Db from mysql.db where Db REGEXP 'integration_{domain_suffix}'".format(
                domain_suffix=self.domain_suffix
            )
        )
        try:
            self.cursor.execute(query)
        except MySQLError as exc:
            logger.exception('Unable to retrieve integrations databases: %s', exc)
            return []
        return self.cursor.fetchall() 

def _get_cursor(url):
    """
    Returns a cursor on the database
    """
    database_url_obj = urlparse(url)
    try:
        connection = mysql.connect(
            host=database_url_obj.hostname,
            user=database_url_obj.username or '',
            passwd=database_url_obj.password or '',
            port=database_url_obj.port or 3306,
        )
    except MySQLError as exc:
        logger.exception('Cannot get MySQL cursor: %s', exc)
        return None
    return connection.cursor()


# Classes #################################################################### 

def setUp(self):
        _skip_if_no_MySQLdb()
        import MySQLdb
        try:
            # Try Travis defaults.
            # No real user should allow root access with a blank password.
            self.db = MySQLdb.connect(host='localhost', user='root', passwd='',
                                    db='pandas_nosetest')
        except:
            pass
        else:
            return
        try:
            self.db = MySQLdb.connect(read_default_group='pandas')
        except MySQLdb.ProgrammingError as e:
            raise nose.SkipTest(
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf. ")
        except MySQLdb.Error as e:
            raise nose.SkipTest(
                "Cannot connect to database. "
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf. ") 

def __get_used_net_vlan(self):
        #get used from database if needed
        try:
            cmd = "SELECT vlan FROM nets WHERE vlan>='%s' and (type='ptp' or type='data') ORDER BY vlan LIMIT 25" % self.net_vlan_lastused
            with self.con:
                self.cur = self.con.cursor()
                self.logger.debug(cmd)
                self.cur.execute(cmd)
                vlan_tuple = self.cur.fetchall()
                #convert a tuple of tuples in a list of numbers
                self.net_vlan_usedlist = []
                for k in vlan_tuple:
                    self.net_vlan_usedlist.append(k[0])
            return 0
        except (mdb.Error, AttributeError) as e:
            return self.format_error(e, "get_free_net_vlan", cmd) 

def deprovision_mysql(self, ignore_errors=False):
        """
        Drop all MySQL databases and users.
        """
        self.logger.info('Deprovisioning MySQL started.')
        if self.mysql_server and self.mysql_provisioned:
            try:
                cursor = _get_mysql_cursor(self.mysql_server)

                # Drop default databases and users
                for database in self.mysql_databases:
                    database_name = database["name"]
                    _drop_database(cursor, database_name)
                    _drop_user(cursor, database["user"])
                # Drop users with global privileges
                for user in self.global_users:
                    _drop_user(cursor, user)
            except MySQLError:
                if not ignore_errors:
                    raise

            self.mysql_provisioned = False
            self.save()
        self.logger.info('Deprovisioning MySQL finished.') 

def new_row(self, table, INSERT, tenant_id=None, add_uuid=False, log=False, created_time=0):
        ''' Add one row into a table.
        Attribute 
            INSERT: dictionary with the key: value to insert
            table: table where to insert
            tenant_id: only useful for logs. If provided, logs will use this tenant_id
            add_uuid: if True, it will create an uuid key entry at INSERT if not provided
        It checks presence of uuid and add one automatically otherwise
        Return: (result, uuid) where result can be 0 if error, or 1 if ok
        '''
        if table in tables_with_created_field and created_time==0:
            created_time=time.time()
        for retry_ in range(0,2):
            try:
                with self.con:
                    self.cur = self.con.cursor()
                    return self._new_row_internal(table, INSERT, tenant_id, add_uuid, None, log, created_time)
                    
            except (mdb.Error, AttributeError), e:
                print "nfvo_db.new_row DB Exception %d: %s" % (e.args[0], e.args[1])
                r,c = self.format_error(e)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def get_db_version(self):
        ''' Obtain the database schema version.
        Return: (negative, text) if error or version 0.0 where schema_version table is missing
                (version_int, version_text) if ok
        '''
        cmd = "SELECT version_int,version,openvim_ver FROM schema_version"
        for retry_ in range(0,2):
            try:
                with self.con:
                    self.cur = self.con.cursor()
                    self.logger.debug(cmd)
                    self.cur.execute(cmd)
                    rows = self.cur.fetchall()
                    highest_version_int=0
                    highest_version=""
                    #print rows
                    for row in rows: #look for the latest version
                        if row[0]>highest_version_int:
                            highest_version_int, highest_version = row[0:2]
                    return highest_version_int, highest_version
            except (mdb.Error, AttributeError) as e:
                self.logger.error("get_db_version DB Exception %d: %s. Command %s",e.args[0], e.args[1], cmd)
                r,c = self.format_error(e)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def connect(self, host=None, user=None, passwd=None, database=None):
        '''Connect to the concrete data base. 
        The first time a valid host, user, passwd and database must be provided,
        Following calls can skip this parameters
        '''
        try:
            if host     is not None: self.host = host
            if user     is not None: self.user = user
            if passwd   is not None: self.passwd = passwd
            if database is not None: self.database = database

            self.con = mdb.connect(self.host, self.user, self.passwd, self.database)
            self.logger.debug("connected to DB %s at %s@%s", self.database,self.user, self.host)
            return 0
        except mdb.Error as e:
            self.logger.error("Cannot connect to DB %s at %s@%s Error %d: %s", self.database, self.user, self.host, e.args[0], e.args[1])
            return -1 

def update_rows(self, table, UPDATE, WHERE, log=False, modified_time=0):
        ''' Update one or several rows into a table.
        Atributes
            UPDATE: dictionary with the key: value to change
            table: table where to update
            WHERE: dictionary of elements to update
        Return: (result, descriptive text) where result indicates the number of updated files
        '''
        if table in tables_with_created_field and modified_time==0:
            modified_time=time.time()
        for retry_ in range(0,2):
            try:
                with self.con:
                    self.cur = self.con.cursor()
                    return self.__update_rows(table, UPDATE, WHERE, log)
                    
            except (mdb.Error, AttributeError), e:
                print "nfvo_db.update_rows DB Exception %d: %s" % (e.args[0], e.args[1])
                r,c = self.format_error(e)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def connect(self, db_config):
        """
        Connect to MySQL database
        Args:
            db_config (dict): A dictionary containing the configuration
        Returns:
            (Mysql Connector): The established MySQL connection
        """
        try:
            print 'Connecting to MySQL database:',
            self.conn = MySQLdb.connect(
                host=db_config['host'],
                port=db_config['port'],
                user=db_config['user'],
                passwd=db_config['passwd'],
                db=db_config['db'])
            print 'OK'
            self.conn.autocommit(True)
            self.cursor = self.conn.cursor()
            return self.conn
        except MySQLdb.Error as error:
            print error
            return 0 

def query_database(self, query):
        """
        Perform a database query
        Args:
            query (str): The SQL query
        Returns:
            list: Mysql Rows
        """
        try:
            self.cursor.execute(query)
            return self.cursor.fetchall()
        except MySQLdb.Error as err:
            # print("Failed executing query: {}".format(err))
            return 0
        except MySQLdb.Warning as wrn:
            return 0 

def insert_into_db(sql,conn=None):
    global dbconn
    if (conn == None) :
        if (dbconn == None ):
            conn = connect_db()
        else:
            conn = dbconn
            
    if (conn) :
        try:
            cursor = conn.cursor()
            n = cursor.execute(sql)
            conn.commit();
            print n
        except MySQLdb.Error,e:
    #WARNING  Mysql Error sql = 1062 Duplicate entry "***" for key 'unique_key'
            if (e.args[0] == 1062):
                return 0
            warnString = " Mysql Error sql = %d %s " % (e.args[0],e.args[1])
            log_init().warning(warnString)
            if(e.args[0] == 2006):
                return 2
            else:
                return 0 

def query(sql, args=None, many=None, key='default'):
    """The connection raw sql query,  when select table,  show table
        to fetch records, it is compatible the dbi execute method::

    args:
    sql string: the sql stamtement like 'select * from %s'
    args maybe list: Wen set None, will use dbi execute(sql), else
        dbi execute(sql, args), the args keep the original rules, it shuld be tuple or list of list
    many maybe int: whe set, the query method will return genarate an iterate
    key: a key for your dabtabase you wanna use
    """
    con = None
    c = None

    def _all():
        try:
            result = c.fetchmany(many)
            while result:
                for row in result:
                    yield row
                result = c.fetchmany(many)
        finally:
            c and c.close()
            con and __connections[key].putcon(con)

    try:
        con = __connections[key].getcon()
        c = con.get_cursor()
        LOGGER.debug("sql: " + sql + " args:" + str(args))
        c.execute(sql, args)
        if many and many > 0:
            return _all()
        else:
            return c.fetchall()

    except MySQLdb.Error, e:
        LOGGER.error("Error Qeury on %s", e.args[1])
        raise DBError(e.args[0], e.args[1]) 

def _drop_user(self, username):
        """
        Drop a single username.

        Args:
            username (str): Username.
        """
        if not self.dry_run:
            try:
                self.cursor.execute('DROP USER `{username}`'.format(username=username))
            except MySQLError as exc:
                logger.exception('Unable to drop user: %s. %s', username, exc) 

def createGuiDB(self):
        # connect to MySQL
        conn, cursor = self.connect()
        
        try:
            cursor.execute(
                "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(MySQL.GUIDB))
        except mysql.Error as err:
            print("Failed to create DB: {}".format(err))        

        # close cursor and connection
        self.close(cursor, conn) 

    #------------------------------------------------------ 

def _get_integration_users(self):
        """
        List of users used in integration tests.
        """
        if not self.cursor:
            logger.error('ERROR: Not connected to the database')
            return []

        try:
            self.cursor.execute(
                """SELECT User from mysql.user where
                    User LIKE '%\\_ecommerce' OR
                    User LIKE '%\\_dashboard' OR
                    User LIKE '%\\_xqueue' OR
                    User LIKE '%\\_edxapp' OR
                    User LIKE '%\\_notes' OR
                    User LIKE '%\\_notifier' OR
                    User LIKE '%\\_api' OR
                    User LIKE '%\\_discovery' OR
                    User LIKE '%\\_reports' OR
                    User LIKE '%\\_program' OR
                    User LIKE '%\\_migrate' OR
                    User LIKE '%\\_read_only' OR
                    User LIKE '%\\_admin'"""
            )
        except MySQLError as exc:
            logger.exception('Unable to retrieve integration users: %s', exc)
            return []
        return self.cursor.fetchall() 

def _drop_user(cursor, user):
    """
    Drop MySQL user if it exists
    """
    # Newer versions of MySQL support "DROP USER IF EXISTS"
    # but at this point we can't be sure that all target hosts run one of these,
    # so we need to use a different approach for now:
    user_exists = cursor.execute("SELECT 1 FROM mysql.user WHERE user = %s", (user,))
    if user_exists:
        logger.info('Dropping mysql user: %s.', user)
        try:
            cursor.execute('DROP USER %s', (user,))
        except MySQLError as exc:
            logger.exception('Cannot drop MySQL user: %s. %s', user, exc)
            raise 

def _drop_database(cursor, database):
    """
    Drop MySQL database
    """
    with warnings.catch_warnings():
        warnings.simplefilter("ignore")
        logger.info('Dropping mysql db: %s', database)
        try:
            cursor.execute('DROP DATABASE IF EXISTS `{db}`'.format(db=database))
        except MySQLError as exc:
            logger.exception('Cannot drop MySQL database: %s. %s', database, exc)
            raise 

def _get_mysql_cursor(mysql_server):
    """
    Get a database cursor.
    """
    try:
        connection = mysql.connect(
            host=mysql_server.hostname,
            user=mysql_server.username,
            passwd=mysql_server.password,
            port=mysql_server.port,
        )
    except MySQLError as exc:
        logger.exception('Cannot get MySQL cursor: %s. %s', mysql_server, exc)
        raise
    return connection.cursor() 

def dropGuiDB(self):
        # connect to MySQL
        conn, cursor = self.connect()
        try:
            cursor.execute(
                "DROP DATABASE {}".format(MySQL.GUIDB))
        except mysql.Error as err:
            print("Failed to drop DB: {}".format(err))        

        # close cursor and connection
        self.close(cursor, conn) 
             
    #------------------------------------------------------ 

def exec_select_one(self, sql, params):
        '''
        ps：执行查询类型的sql语句
        '''
        try:
            self.cursor.execute(sql, params)
            result_set = self.cursor.fetchone()
            return result_set
        except MySQLdb.Error as e:
            print("Mysql Error:%s\nSQL:%s" %(e, sql))
            return False