Python MySQLdb.Error() Examples

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 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 __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 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 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 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 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 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 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 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 _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 _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_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 _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 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 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 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 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 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 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 connect(self) -> None:
        try:
            self.connection = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, use_unicode=True, charset='utf8', autocommit=True)
            self.cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
            self.execute('SET NAMES utf8mb4')
            try:
                self.execute('USE {db}'.format(db=self.name))
            except DatabaseException:
                print('Creating database {db}'.format(db=self.name))
                self.execute('CREATE DATABASE {db} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'.format(db=self.name))
                self.execute('USE {db}'.format(db=self.name))
        except MySQLdb.Error:
            raise DatabaseException('Failed to initialize database in `{location}`'.format(location=self.name)) 

def execute_anything(self, sql: str, args: Optional[List[ValidSqlArgumentDescription]] = None, fetch_rows: bool = True) -> Tuple[int, List[Dict[str, ValidSqlArgumentDescription]]]:
        if args is None:
            args = []
        try:
            return self.execute_with_reconnect(sql, args, fetch_rows)
        except MySQLdb.Warning as e:
            if e.args[0] == 1050 or e.args[0] == 1051:
                return (0, []) # we don't care if a CREATE IF NOT EXISTS raises an "already exists" warning or DROP TABLE IF NOT EXISTS raises an "unknown table" warning.
            if e.args[0] == 1062:
                return (0, []) # We don't care if an INSERT IGNORE INTO didn't do anything.
            raise DatabaseException('Failed to execute `{sql}` with `{args}` because of `{e}`'.format(sql=sql, args=args, e=e))
        except MySQLdb.Error as e:
            raise DatabaseException('Failed to execute `{sql}` with `{args}` because of `{e}`'.format(sql=sql, args=args, e=e)) 

def __open(self):
        try:
            cnx = MySQLdb.connect(self.__host, self.__user, self.__password, self.__database)
            self.__connection = cnx
            self.__session    = cnx.cursor()
        except MySQLdb.Error as e:
            print "Error %d: %s" % (e.args[0],e.args[1])
    ## End def __open 

def is_usable(self):
        try:
            self.connection.ping()
        except Database.Error:
            return False
        else:
            return True 

def sql_query(queries, return_list=False, exit_fail=True):
    """
    This function will pass queries to the MySQL/MariaDB instance

    :queries: list of queries to execute
    :type queries: list / tuple
    :return_list: if you need a return from the query, set it to True
    :type return_list: boolean
    :exit_fail: you can choose if the program needs to continue on fail or not
    :type exit_fail: boolean

    :returns:
    :rtype: return a list of result

    """
    db = MySQLdb.connect(host=hostname, port=port, user=username, passwd=password)
    cur = db.cursor()

    try:
        query = ' '.join(queries)
        cur.execute(query)
    except MySQLdb.Error, e:
        try:
            print_color('fail', "MySQL Error [%d]: %s" % (e.args[0], e.args[1]))
            if (exit_fail):
                restore_toi()
                sys.exit(1)
        except IndexError:
            print_color('fail', "MySQL Error: %s" % str(e))
            if (exit_fail):
                restore_toi()
                sys.exit(1) 

def check_mysql_connection():
    """
    Check simple MySQL/MariaDB connection
    """

    try:
        print_color('+', 'Trying to connect to MySQL/MariaDB instance')
        db = MySQLdb.connect(host=hostname, port=port, user=username, passwd=password)
    except MySQLdb.Error, e:
        try:
            print_color('fail', "ERROR [%d]: %s" % (e.args[0], e.args[1]))
            sys.exit(1)
        except IndexError:
            print_color('fail', "ERROR: %s" % str(e))
            sys.exit(1) 

def disconnect(self):
        '''disconnect from the data base'''
        try:
            self.con.close()
            del self.con
        except mdb.Error as e:
            self.logger.error("while disconnecting from DB: Error %d: %s",e.args[0], e.args[1])
            return -1
        except AttributeError as e: #self.con not defined
            if e[0][-5:] == "'con'": return -1, "Database internal error, no connection."
            else: raise 

def format_error(self, e, func, cmd, command=None, extra=None):
        '''Creates a text error base on the produced exception
            Params:
                e: mdb exception
                func: name of the function that makes the call, for logging purposes
                cmd: database command that produce the exception
                command: if the intention is update or delete
                extra: extra information to add to some commands
            Return
                HTTP error in negative, formatted error text
        ''' 
                
        self.logger.error("%s DB Exception %s. Command %s",func, str(e), cmd)
        if type(e[0]) is str:
            if e[0][-5:] == "'con'": return -HTTP_Internal_Server_Error, "DB Exception, no connection."
            else: raise
        if e.args[0]==2006 or e.args[0]==2013 : #MySQL server has gone away (((or)))    Exception 2013: Lost connection to MySQL server during query
            #reconnect
            self.connect()
            return -HTTP_Request_Timeout,"Database reconnection. Try Again"
        fk=e.args[1].find("foreign key constraint fails")
        if fk>=0:
            if command=="update": return -HTTP_Bad_Request, "tenant_id %s not found." % extra
            elif command=="delete":  return -HTTP_Bad_Request, "Resource is not free. There are %s that prevent its deletion." % extra
        de = e.args[1].find("Duplicate entry")
        fk = e.args[1].find("for key")
        uk = e.args[1].find("Unknown column")
        wc = e.args[1].find("in 'where clause'")
        fl = e.args[1].find("in 'field list'")
        #print de, fk, uk, wc,fl
        if de>=0:
            if fk>=0: #error 1062
                return -HTTP_Conflict, "Value %s already in use for %s" % (e.args[1][de+15:fk], e.args[1][fk+7:])
        if uk>=0:
            if wc>=0:
                return -HTTP_Bad_Request, "Field %s can not be used for filtering" % e.args[1][uk+14:wc]
            if fl>=0:
                return -HTTP_Bad_Request, "Field %s does not exist" % e.args[1][uk+14:wc]
        return -HTTP_Internal_Server_Error, "Database internal Error %d: %s" % (e.args[0], e.args[1]) 

def update_rows(self, table, UPDATE, WHERE={}, log=False):
        ''' Update one or several rows into a table.
        Atributes
            UPDATE: dictionary with the key-new_value pairs to change
            table: table to be modified
            WHERE: dictionary to filter target rows, key-value
            log:   if true, a log entry is added at logs table
        Return: (result, None) where result indicates the number of updated files
        '''
        for retry_ in range(0,2):
            cmd=""
            try:
                #gettting uuid 
                uuid = WHERE.get('uuid')

                with self.con:
                    self.cur = self.con.cursor()
                    cmd= "UPDATE " + table +" SET " + \
                        ",".join(map(lambda x: str(x)+'='+ self.__data2db_format(UPDATE[x]),   UPDATE.keys() ));
                    if WHERE:
                        cmd += " WHERE " + " and ".join(map(lambda x: str(x)+ (' is Null' if WHERE[x] is None else"='"+str(WHERE[x])+"'" ),  WHERE.keys() ))
                    self.logger.debug(cmd)
                    self.cur.execute(cmd) 
                    nb_rows = self.cur.rowcount
                    if nb_rows > 0 and log:                
                        #inserting new log
                        if uuid is None: uuid_k = uuid_v = ""
                        else: uuid_k=",uuid"; uuid_v=",'" + str(uuid) + "'"
                        cmd = "INSERT INTO logs (related,level%s,description) VALUES ('%s','debug'%s,\"updating %d entry %s\")" \
                            % (uuid_k, table, uuid_v, nb_rows, (str(UPDATE)).replace('"','-')  )
                        self.logger.debug(cmd)
                        self.cur.execute(cmd)                    
                    return nb_rows, uuid
            except (mdb.Error, AttributeError) as e:
                r,c = self.format_error(e, "update_rows", cmd)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c