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 %[email protected]%s", self.database,self.user, self.host)
            return 0
        except mdb.Error as e:
            self.logger.error("Cannot connect to DB %s at %[email protected]%s Error %d: %s", self.database, self.user, self.host, e.args[0], e.args[1])
            return -1 

def insert_pagelength(self, source_article_id, screen_positions_1920_1080, zip_file_path):

               data={}
               data['source_article_id'] = source_article_id
               if screen_positions_1920_1080 is not None:
                   data['page_length_1920_1080'] = screen_positions_1920_1080
               else:
                   data['page_length_1920_1080'] = None
               #print data
               sql = "INSERT INTO redirects_candidates_page_length (id, page_length_1920_1080) VALUES" \
                      "(%(source_article_id)s, %(page_length_1920_1080)s);"
               try:
                   self.cursor.execute(sql, data)
               except (MySQLdb.Error, MySQLdb.Warning), e:
                   print ('FAIL: Data caused warning or error "%s" for source_article_id: "%s"', data,  source_article_id)
                   print 'FAIL: EXCEPTION:', e
                   print zip_file_path 

def retrieve_number_of_common_articles(self, id1, id2):
        """computes the number of articles that link to both referenced articles

            @param id1 the id of the first article to be linked to
            @param id2 the id of the second article to be linked to

            @return the number of articles that link to both referenced articles
        """
        # retrieve from database and store in cache
        try:
            if id1 not in self._link_cache:
                self._cursor.execute('SELECT source_article_id FROM links WHERE target_article_id=%s;', (id1,))
                self._link_cache[id1] = self._cursor.fetchall()
            if id2 not in self._link_cache:
                self._cursor.execute('SELECT source_article_id FROM links WHERE target_article_id=%s;', (id2,))
                self._link_cache[id2] = self._cursor.fetchall()
        except MySQLdb.Error, e:
            logging.error('error resolving links for source article id %d or %d: %s (%d)'
                          % (id1, id2, e.args[1], e.args[0]))

        # find common articles 

def retrieve_all_articles(self):
        """retrieves all articles. useful for crawling or making media wiki api requests
        @return a list of dictionaries holding the following keys:
           'id': the id of the retrieved article
           'rev_id': the revision id  of the retrieved article
           'title': the title of the retrieved article
                """
        articles = []
        try:
            #self._cursor.execute('SELECT * FROM articles WHERE RAND()<=0.0006 limit 1000;')
            #self._cursor.execute('SELECT * FROM articles limit 1000;')
            self._cursor.execute('SELECT * FROM articles;')
            result = self._cursor.fetchall()
            for row in result:
                article = {}
                article['id'] = row[0]
                article['rev_id'] = row[1]
                article['title'] = row[2]
                articles.append(article)
        except MySQLdb.Error, e:
            logging.error('error retrieving 1000 random articles  %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_articles_questionmark(self):
            """retrieves all articles. useful for crawling or making media wiki api requests
            @return a list of dictionaries holding the following keys:
               'id': the id of the retrieved article
               'rev_id': the revision id  of the retrieved article
               'title': the title of the retrieved article
                    """
            articles = []
            try:
                #self._cursor.execute('SELECT * FROM articles WHERE RAND()<=0.0006 limit 1000;')
                #self._cursor.execute('SELECT * FROM articles limit 1000;')
                self._cursor.execute('SELECT * FROM articles WHERE title LIKE %s;', ("?%",))
                result = self._cursor.fetchall()
                for row in result:
                    article = {}
                    article['id'] = row[0]
                    article['rev_id'] = row[1]
                    article['title'] = row[2]
                    articles.append(article)
            except MySQLdb.Error, e:
                logging.error('error retrieving 1000 random articles  %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_transitions(self):
                """retrieves all transitions from the wikipeida clickstream_derived that are an internal links. These are the network edges
                @return a list of dictionaries holding the following keys:
                   'from': the source article id
                   'to': the target article id
                        """
                links = []
                try:
                    self._cursor.execute('SELECT * FROM clickstream_derived WHERE link_type_derived LIKE %s AND NOT link_type_derived=%s;', ("internal%", "internal-nonexistent",))
                    result = self._cursor.fetchall()
                    for row in result:
                        link = {}
                        link['from'] = row[0]
                        link['to'] = row[1]
                        links.append(link)
                except MySQLdb.Error, e:
                    logging.error('error retrieving unique links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_internal_transitions(self):
        """retrieves all internal links transitions from the wikipeida clickstream_derived that are an internal links. These are the network edges
        @return a list of dictionaries holding the following keys:
           'from': the source article id
           'to': the target article id
                """
        links = []
        try:
            self._cursor.execute('SELECT * FROM clickstream_derived WHERE link_type_derived=%s;', ("internal-link",))
            result = self._cursor.fetchall()
            for row in result:
                link = {}
                link['from'] = row[0]
                link['to'] = row[1]
                links.append(link)
        except MySQLdb.Error, e:
            logging.error('error retrieving unique links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_internal_transitions_counts(self):
        """retrieves all internal links transitions from the wikipeida clickstream_derived that are an internal links. These are the network edges
        @return a list of dictionaries holding the following keys:
           'from': the source article id
           'to': the target article id
                """
        links = []
        try:
            self._cursor.execute('SELECT * FROM clickstream_derived WHERE link_type_derived=%s;', ("internal-link",))
            result = self._cursor.fetchall()
            for row in result:
                link = {}
                link['from'] = row[0]
                link['to'] = row[1]
                link['counts']=row[2]
                links.append(link)
        except MySQLdb.Error, e:
            logging.error('error retrieving unique links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_coords(self):
            """retrieves all xy coord for all links in wikipeida.
            @return a list of coords holding the following keys:
               'source_article_id': the wikipedia article id
               'x': x position on screen
               'y': y position on screen
                    """
            coords = []
            try:
                self._cursor.execute('SELECT source_article_id, target_x_coord_1920_1080, target_y_coord_1920_1080 FROM links where target_x_coord_1920_1080 is not Null and target_y_coord_1920_1080 is not Null and target_x_coord_1920_1080!=0 and target_y_coord_1920_1080!=0 and source_article_id!=target_article_id;')
                result = self._cursor.fetchall()
                for row in result:
                    link = {}
                    link['source_article_id']= row[0]
                    link['x'] = row[1]
                    link['y'] = row[2]
                    coords.append(link)
            except MySQLdb.Error, e:
                logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_multpile_occ(self):
        """retrieves all xy coord for all links in wikipeida.
        @return a list of coords holding the following keys:
           'source_article_id': the wikipedia article id
           'x': x position on screen
           'y': y position on screen
                """
        coords = []
        try:
            self._cursor.execute('SELECT source_article_id, target_article_id, target_x_coord_1920_1080, target_y_coord_1920_1080 FROM links where target_x_coord_1920_1080 is not Null and target_y_coord_1920_1080 is not Null and target_x_coord_1920_1080!=0 and target_y_coord_1920_1080!=0 and source_article_id!=target_article_id;')
            result = self._cursor.fetchall()
            for row in result:
                link = {}
                link['key']= row[0], row[1]
                link['x'] = row[2]
                link['y'] = row[3]
                coords.append(link)
        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def insert_pagelength(self, source_article_id, screen_positions_1920_1080, zip_file_path):

               data={}
               data['source_article_id'] = source_article_id
               if screen_positions_1920_1080 is not None:
                   data['page_length_1920_1080'] = screen_positions_1920_1080
               else:
                   data['page_length_1920_1080'] = None
               #print data
               sql = "INSERT INTO page_length (id, page_length_1920_1080) VALUES" \
                      "(%(source_article_id)s, %(page_length_1920_1080)s);"
               try:
                   self.cursor.execute(sql, data)
               except (MySQLdb.Error, MySQLdb.Warning), e:
                   print ('FAIL: Data caused warning or error "%s" for source_article_id: "%s"', data,  source_article_id)
                   print 'FAIL: EXCEPTION:', e
                   print zip_file_path 

def rbo():
    print 'loading'
    db = MySQLDatabase(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME)
    conn = db._create_connection()
    cursor = conn.cursor()
    sm = []
    try:
        cursor.execute('select curr_id, sum(counts) as counts_sum, curr_title from clickstream_derived where link_type_derived=%s group by curr_id order by counts_sum desc limit 10000;', ("entry-sm",))
        result = cursor.fetchall()
        for row in result:
            record = {}
            record['curr_id']= row[0]
            record['counts_sum'] = row[1]
            record['curr_title'] = row[2]
            sm.append(row[0])
    except MySQLdb.Error, e:
        print e 

def get_slave_status(self):
        """Run the query!"""
        try:
            sql = 'SHOW SLAVE "{0}" STATUS'.format(self.connection_name)
            conn = None
            conn = MySQLdb.Connection(
                self.hostname,
                self.username,
                self.password)

            curs = conn.cursor(MySQLdb.cursors.DictCursor)
            curs.execute(sql)
            conn.commit()

            self._slave_status = curs.fetchall()[0]
            if self.verbose: # pragma: no cover
                print(self._slave_status)
        except MySQLdb.Error as exc:
            msg = "{0}: {1}".format(exc.args[0], exc.args[1])
            self.unknown_state(msg)
        finally:
            if conn:
                conn.close() 

def reconnect(self):
        retries = 5
        while True:
            try:
                self.db = MySQLdb.connect(host='localhost', user=self.dbuser, passwd=self.dbpassword, db=self.dbname)
                self.cur = self.db.cursor()
                return
            except MySQLdb.Error, e:
                try:
                    print "Failed to connect. MySQL Error [%d]: %s" % (e.args[0], e.args[1])
                    retries = retries - 1
                    if (retries > 0):
                        print "Waiting to retry (%d)" % retries
                        time.sleep(30)
                    else:
                        raise e
                except IndexError:
                    print "MySQL Error: %s" % str(e)
                    raise e 

def executeAndFetchAll(self, command, *args):
        retries = 3
        while retries > 0:
            try:
                self.cur.execute(command, args)
                rows = self.cur.fetchall()
                return rows
            except MySQLdb.Error, e:
                try:
                    print "executeAndFetchAll(): MySQL Error [%d] on line %d: %s" % (e.args[0], sys.exc_info()[-1].tb_lineno, e.args[1])
                except IndexError:
                    print "executeAndFetchAll(): MySQL Error: %s" % str(e)
                if (e.args[0] == 2006):
                    self.reconnect()
                retries = retries - 1
                if (retries > 0):
                    print "Retrying..."
                else:
                    return 

def insert_user(self,uid,name,pic):
        try:
            userid=0
            bSginal=self.curosr.execute("SELECT * FROM yzy_users WHERE uid='%s'"%(uid))
            if bSginal==1:
                results=self.curosr.fetchone()
                userid=results[0]
            else:
                sql = """INSERT INTO yzy_users(uid,uname,avatar)
                                    VALUES('%s','%s','%s')"""%(uid,name,pic)
                vsql = sql.encode('utf8')
                if self.curosr.execute(vsql)==1:
                    userid=self.curosr.lastrowid

        except MySQLdb.Error,e:
            print "Error:%d:%s" % (e.args[0], e.args[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 correlations(network_name):
    db = MySQLDatabase(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME)
    conn = db._create_connection()
    cursor = conn.cursor()
    # wikipedia  graph  structural statistics

    results = None
    try:
        results = cursor.execute('select c.curr_id,  sum(c.counts) as counts from clickstream_derived c where c.link_type_derived= %s  group by c.curr_id;', ("internal-link",))
        results = cursor.fetchall()


    except MySQLdb.Error, e:
        print ('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def insert_link(self, source_article_id, target_article_name, data, position,  zip_file_path):

        target_article_id = self.db_build_view._resolve_title(target_article_name.split('-----##$$$##-----')[0].replace('_', ' '))
        data['target_article_name'] = target_article_name.split('-----##$$$##-----')[0].replace('_', ' ')
        data['source_article_id'] = source_article_id
        data['target_article_id'] = target_article_id
        if position is not None:
            data['target_x_coord_1920_1080'] = position[0]
            data['target_y_coord_1920_1080'] = position[1]
            if data['target_y_coord_1920_1080'] < 0:
                data['target_y_coord_1920_1080'] = 0
        else:
            data['target_x_coord_1920_1080'] = None
            data['target_y_coord_1920_1080'] = None
        #print data
        sql = "INSERT INTO redirects_candidates (source_article_id, target_article_id, target_article_name," \
               "target_position_in_text, target_position_in_text_only, target_position_in_section, " \
               "target_position_in_section_in_text_only, section_name," \
               " section_number, target_position_in_table, table_number, table_css_class, table_css_style," \
               " target_x_coord_1920_1080, target_y_coord_1920_1080) VALUES" \
               "(%(source_article_id)s, %(target_article_id)s, %(target_article_name)s, %(target_position_in_text)s," \
               "%(target_position_in_text_only)s, %(target_position_in_section)s,  %(target_position_in_section_in_text_only)s, " \
               "%(section_name)s, %(section_number)s, %(target_position_in_table)s, %(table_number)s, " \
               "%(table_css_class)s, %(table_css_style)s," \
               "%(target_x_coord_1920_1080)s, %(target_y_coord_1920_1080)s);"
        try:
            self.cursor.execute(sql, data)
            #logging.info('DB Insert Success for  target article id: "%s" ' % target_article_id)
        except (MySQLdb.Error, MySQLdb.Warning), e:
            #print sql
            print ('FAIL: Data caused warning or error "%s" for target_article_id: "%s" in for source_article_id: "%s"', data, target_article_id, source_article_id)
            print 'FAIL: EXCEPTION:', e
            print zip_file_path
            #print('DB Insert Error  target article id: "%s" ' % target_article_id) 

def insert_article(self, id, rev_id, title):
        """saves an article in the database

           @param id the id of the article
           @param title the title of the article 
        """
        try:
            self._cursor.execute('INSERT INTO articles(id, rev_id, title) VALUES(%s, %s, %s);',
                (id, rev_id, title))
        except MySQLdb.Error, e:
            logging.error('error saving article "%s" to database: %s (%d)' 
                % (title.encode('ascii', 'ignore'), e.args[1], e.args[0])) 

def insert_redirect(self, source_name, target_name):
        """saves a redirect in the database

        @param source_name the name of the source article
        @param target_name the name of the target article
        """
        try:
            self._cursor.execute('INSERT INTO redirects(source_article_name, target_article_name) VALUES(%s, %s);',
                (source_name, target_name))
        except MySQLdb.Error, e:
            logging.error('error saving redirect "%s" --> "%s" to database: %s (%d)' 
                % (source_name.encode('ascii', 'ignore'), target_name.encode('ascii', 'ignore'), e.args[1], e.args[0])) 

def resolve_redirect(self, name):
        """resolves a redirect and returns the real article name

           @param name the name of the redirect
           
           @return the real name of the article or None if it cannot be resolved
        """
        try:
            self._cursor.execute('SELECT target_article_name FROM redirects WHERE source_article_name=%s;', (name,))
            row = self._cursor.fetchone()
            if row != None:
                return row[0]
        except MySQLdb.Error, e:
            logging.error('error resolving redirect for name "%s": %s (%d)'
                          % (name.encode('ascii', 'ignore'), e.args[1], e.args[0])) 

def resolve_title(self, title):
        """resolves an article and returns it

           @param title the title of the article

           @return a dictionary with fields 'id' and 'title' or None if could not be resolved
        """
        if title in self._article_cache:
            return self._article_cache[title]

        try:
            t = title[0].upper() + title[1:]
            self._cursor.execute('SELECT id, title FROM articles WHERE title=%s;', (t,))
            row = self._cursor.fetchone()
            if row == None:
                self._cursor.execute(
                    'SELECT id, title FROM articles WHERE title=(SELECT target_article_name FROM redirects WHERE source_article_name=%s);',
                    (title,))
                row = self._cursor.fetchone()

            if row == None:
                print t
                self._article_cache[title] = None
            else:
                self._article_cache[title] = {'id': row[0], 'title': row[1]}
                if (row[1] != title):
                    self._article_cache[row[1]] = {'id': row[0], 'title': row[1]}
        except MySQLdb.Error, e:
            logging.error('error resolving article "%s": %s (%d)'
                          % (title.encode('ascii', 'ignore'), e.args[1], e.args[0])) 

def retrieve_all_page_lengths(self):
                """retrieves all page lengths.
                @return a dict of lengths holding the following keys:
                   'id': the lenght of the page with the id
                """
                pages = {}
                try:
                    self._cursor.execute('SELECT *  FROM page_length;')
                    result = self._cursor.fetchall()
                    for row in result:
                        pages[row[0]]=row[1]
                except MySQLdb.Error, e:
                    logging.error('error retrieving pagelength: %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_coords_clicks_first_occ(self):
        """retrieves all xy coord for all links in wikipeida.
        @return a list of coords holding the following keys:
           'source_article_id': the wikipedia article id
           'x': x position on screen
           'y': y position on screen
                """
        links = {}
        try:
            self._cursor.execute('select l.source_article_id, l.target_article_id, l.target_x_coord_1920_1080, l.target_y_coord_1920_1080, c.counts, p.page_length_1920_1080 from links l, clickstream_derived c, page_length p where l.source_article_id=c.prev_id and l.target_article_id=c.curr_id and c.link_type_derived like %s and l.source_article_id = p.id and l.target_x_coord_1920_1080 is not Null and l.target_y_coord_1920_1080 is not Null and l.target_x_coord_1920_1080!=0 and l.target_y_coord_1920_1080!=0 and l.source_article_id!=l.target_article_id;', ("internal%",))
            result = self._cursor.fetchall()
            for row in result:
                link = {}
                link['x'] = row[2]
                link['y'] = row[3]
                link['counts'] = row[4]
                link['page_length'] = row[5]
                try:
                    prev = links[row[0], row[1]]
                    if prev['y'] > link['y']:
                        links[row[0], row[1]] = link
                    if prev['y']==link['y']:
                        if prev['x']>link['y']:
                            links[row[0], row[1]] = link
                except:
                    links[row[0], row[1]] = link
        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_coords_page_rank(self):

        results = []
        try:
            self._cursor.execute('select l.target_x_coord_1920_1080, l.target_y_coord_1920_1080, l.target_article_page_rank, p.page_length_1920_1080 from link_features l, page_length p where  l.source_article_id = p.id and l.target_x_coord_1920_1080 is not Null and l.target_y_coord_1920_1080 is not Null  and l.target_x_coord_1920_1080!=0 and l.target_y_coord_1920_1080!=0;')
            result = self._cursor.fetchall()

        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_coords_indegree(self):

        result = []
        try:
            self._cursor.execute('select l.target_x_coord_1920_1080, l.target_y_coord_1920_1080, l.target_article_in_degree, p.page_length_1920_1080 from link_features l,  page_length p where l.source_article_id = p.id and l.target_x_coord_1920_1080 is not Null and l.target_y_coord_1920_1080 is not Null  and l.target_x_coord_1920_1080!=0 and l.target_y_coord_1920_1080!=0;')
            result = self._cursor.fetchall()
        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_coords_outdegree(self):
        result = []
        try:
            self._cursor.execute('select l.target_x_coord_1920_1080, l.target_y_coord_1920_1080, l.target_article_out_degree, p.page_length_1920_1080 from link_features l, page_length p where  l.source_article_id = p.id and l.target_x_coord_1920_1080 is not Null and l.target_y_coord_1920_1080 is not Null  and l.target_x_coord_1920_1080!=0 and l.target_y_coord_1920_1080!=0;')
            result = self._cursor.fetchall()

        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_coords_clustering(self):
        result = []
        try:
            self._cursor.execute('select l.target_x_coord_1920_1080, l.target_y_coord_1920_1080, l.target_article_local_clust, p.page_length_1920_1080 from link_features l, page_length p where  l.source_article_id = p.id and l.target_x_coord_1920_1080 is not Null and l.target_y_coord_1920_1080 is not Null  and l.target_x_coord_1920_1080!=0 and l.target_y_coord_1920_1080!=0;')
            result = self._cursor.fetchall()
        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_coords_kcore(self):

        result = []
        try:
            self._cursor.execute('select l.target_x_coord_1920_1080, l.target_y_coord_1920_1080, l.target_article_kcore, p.page_length_1920_1080 from link_features l, page_length p where l.source_article_id = p.id and l.target_x_coord_1920_1080 is not Null and l.target_y_coord_1920_1080 is not Null  and l.target_x_coord_1920_1080!=0 and l.target_y_coord_1920_1080!=0;')
            result = self._cursor.fetchall()
        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_all_links_coords_eigenvector(self):
        result = []
        try:
            self._cursor.execute('select l.target_x_coord_1920_1080, l.target_y_coord_1920_1080, l.target_article_eigen_centr, p.page_length_1920_1080 from link_features l, page_length p where  l.source_article_id = p.id and l.target_x_coord_1920_1080 is not Null and l.target_y_coord_1920_1080 is not Null  and l.target_x_coord_1920_1080!=0 and l.target_y_coord_1920_1080!=0;')
            result = self._cursor.fetchall()
        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def retrieve_internalcounts_degree(self):
        in_degree = []
        out_degree = []
        degree = []
        page_rank = []
        kcore = []
        local_clust = []
        eigenvector_centr =  []
        hub =  []
        authority =  []
        counts = []
        try:
            self._cursor.execute('select a.in_degree, a.out_degree, a.degree, a.page_rank, a.kcore,a.local_clustering, a.eigenvector_centr, a.hits_hub,  a.hits_authority, sum(c.counts) as counts from clickstream_derived c, article_features a where c.link_type_derived= %s  and a.id=c.curr_id  group by c.curr_id;', ("internal-link",))
            results = self._cursor.fetchall()
            for row in results:
                in_degree.append(float(row[0]))
                out_degree.append(float(row[1]))
                degree.append(float(row[2]))
                page_rank.append(float(row[3]))
                kcore.append(float(row[4]))
                local_clust.append(float(row[5]))
                eigenvector_centr.append(float(row[6]))
                hub.append(float(row[7]))
                authority.append(float(row[8]))
                counts.append(float(row[9]))

        except MySQLdb.Error, e:
            logging.error('error retrieving xy coord for all links links %s (%d)' % (e.args[1], e.args[0])) 

def update_article_features():

    connection = db._create_connection()
    cursor = connection.cursor()

    network = load_graph("output/wikipedianetwork.xml.gz")
    print 'graph loaded'
    articles = db_work_view.retrieve_all_articles()
    print 'articles loaded'

    # setup logging
    LOGGING_FORMAT = '%(levelname)s:\t%(asctime)-15s %(message)s'
    LOGGING_PATH = 'tmp/articlefeatures-dbinsert.log'
    logging.basicConfig(filename=LOGGING_PATH, level=logging.DEBUG, format=LOGGING_FORMAT, filemode='w')

    for article in articles:
        try:
            article_features = {}
            vertex = network.vertex(article['id'])
            article_features['id'] = article['id']
            article_features['hits_authority'] = network.vertex_properties["authority"][vertex]
            article_features['hits_hub'] = network.vertex_properties["hub"][vertex]
            #article_features['katz'] = network.vertex_properties["katz"][vertex]

            sql  = "UPDATE article_features " \
                   "SET hits_authority = %(hits_authority)s, hits_hub = %(hits_hub)s " \
                   "WHERE id = %(id)s;"

            cursor.execute(sql, article_features)

        except MySQLdb.Error as e:
            #logging.error('DB Insert Error  article id: "%s" ' % article['id'])
            print e
        except ValueError as v:
            logging.error('ValueError for article id: "%s"' % article['id'])
            print v
        connection.commit()
    connection.close() 

def update_article_features_karz():

    connection = db._create_connection()
    cursor = connection.cursor()

    network = load_graph("output/wikipedianetwork.xml.gz")
    print 'graph loaded'
    articles = db_work_view.retrieve_all_articles()
    print 'articles loaded'

    # setup logging
    LOGGING_FORMAT = '%(levelname)s:\t%(asctime)-15s %(message)s'
    LOGGING_PATH = 'tmp/articlefeatures-dbinsert.log'
    logging.basicConfig(filename=LOGGING_PATH, level=logging.DEBUG, format=LOGGING_FORMAT, filemode='w')

    for article in articles:
        try:
            article_features = {}
            vertex = network.vertex(article['id'])
            article_features['id'] = article['id']
            article_features['katz'] = network.vertex_properties["katz"][vertex]

            sql  = "UPDATE article_features " \
                   "SET  katz=%(katz)s " \
                   "WHERE id = %(id)s;"

            cursor.execute(sql, article_features)

        except MySQLdb.Error as e:
            logging.error('DB Insert Error  article id: "%s" ' % article['id'])
            #print e
        except ValueError:
            logging.error('ValueError for article id: "%s"' % article['id'])
        connection.commit()
    connection.close() 

def pickle_aggregated_counts_distribution():

    db = MySQLDatabase(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME)
    db_worker_view = db.get_work_view()
    cursor = db_worker_view._cursor
    results = {}
    try:
        cursor.execute('select sum(counts) from clickstream_derived_internal_links group by prev_id;')
        result = cursor.fetchall()
        results['source_article']=result
    except MySQLdb.Error, e:
        print e 

def insert_table_class(self, source_article_id, table_class, cursor):
        sql = "INSERT INTO table_css_class (source_article_id, css_class) VALUES (%s, %s);"
        try:
            cursor.execute(sql, (source_article_id,table_class))
            #logging.info('DB Insert Success for  target article id: "%s" ' % target_article_id)
        except MySQLdb.Error, e:
            logging.error('DB Insert Error source article id: "%s" ' % source_article_id) 

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

def test_get_slave_status_exc(self, mock_mysqldb_connection):
        mock_mysqldb_connection.side_effect = MySQLdb.Error('test code', 'test exc')
        self.assertRaises(SystemExit, self.slave_status_check.get_slave_status)
        self.slave_status_check.unknown_state.assert_called_once_with('test code: test exc') 

def GetLastConfigFromMySQL(target):
    # По умолчанию конфиг пустой
    last_conf = ''
    try:
	# Пробуем подключиться к базе данных MySQL. Используем таймаут в 2 секунды
        db_conn = MySQLdb.connect(host = mysql_addr_w, user = mysql_user_w, passwd = mysql_pass_w, db = mysql_base_w, connect_timeout = 2)
	db_conn.autocommit(True)
    except MySQLdb.Error as err:
	# Если возникла ошибка при подключении, сообщаем об этом в лог
        logger.info("ERROR: MySQL Error (%s): %s", mysql_addr_w, err.args[1])
    else:
	# Если ошибок не было, создаем 'курсор'
        mysql_cr = db_conn.cursor()
	try:
	    mysql_cr.execute("SELECT config FROM {0}.{1} JOIN {0}.{2} ON {1}.hash={2}.hash WHERE {1}.target=INET_ATON('{3}') AND {1}.direction='up' ORDER BY {1}.timestamp DESC LIMIT 1;".format(mysql_base_w, mysql_ttbl_w, mysql_ctbl_w, target))
	except MySQLdb.Error as err:
	    # При ошибке сообщаем в лог
	    logger.info("ERROR: MySQL Query Error: %s", err.args[1])
	else:
	    data = mysql_cr.fetchall()
	    if len(data):
		last_conf = data[0][0]
	finally:
	    db_conn.close()
    return last_conf

# Функция для определения типа передачи, режима передачи, имени файла, номера блока, IP-адреса цели, типа данных и самих данных 

def executeAndCommit(self, command, *args):
        retries = 3
        while retries > 0:
            try:
                status = self.cur.execute(command, args)
                self.db.commit()
                return status
            except MySQLdb.Error, e:
                try:
                    print "executeAndCommit(): MySQL Error [%d]: %s" % (e.args[0], e.args[1])
                except IndexError:
                    print "executeAndCommit(): MySQL Error: %s" % str(e)
                if (e.args[0] == 2006):
                    self.reconnect()
                else:
                    try:
                        print "Rolling back..."
                        self.db.rollback()
                    except MySQLdb.Error:
                        print "Rollback failed."
                    
                retries = retries - 1
                if (retries > 0):
                    print "Retrying..."
                else:
                    return None 

def process_item(self,item,spider):
        try:
            userid = self.insert_user(item['uid'],item['name'],item['avatar'])
            sql="""INSERT INTO
                                yzy_resources(tid,cid,uid,title,size,url,pwd,description,available,sharetime)
                                VALUES('%d','%d','%d','%s','%s','%s','%s','%s','%d','%s')
                                """%(item['tid'],item['cid'],userid,item['title'],item['size'],item['url'],item['pwd'],item['description'],item['available'],item['sharetime'])
            vsql=sql.encode('utf8')
            self.curosr.execute(vsql)

        except MySQLdb.Error,e:
            print "Error:%d:%s" % (e.args[0],e.args[1]) 

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

def insert_to_mysql(conversations):
  for conversation in conversations:
    try:
      mysql_cursor.execute("""
        INSERT IGNORE INTO unique_conversation (conversation_id, sentence_id, time_added, feed_time, body)
        VALUES (%s, %s, %s, %s, %s)
      """, conversation)

      mysql_connection.commit()

    except mysql.Error as e:
      if mysql_connection:
        mysql_connection.rollback()

      print "Error %d: %s" % (e.args[0], e.args[1]) 

def insert_to_mysql_many_at_once(conversations):
  """
  MySQLdb tutorial here: http://zetcode.com/db/mysqlpython/
  """
  # pdb.set_trace()

  total = len(conversations)
  per_page = 1000
  pages = (total / per_page) + 1
  page = 1

  while page <= pages:
    offset = ((page - 1) * per_page)
    upper = page * per_page
    _conversations = conversations[offset:upper] # conversations[0:100], conversations[100:200], ...
    _conversations_size = len(_conversations)
    page += 1

    if (_conversations_size > 0):
      try:
        print "inserting {numbers} sentences".format(numbers=_conversations_size)

        mysql_cursor.executemany("""
          INSERT IGNORE INTO unique_conversation (conversation_id, sentence_id, time_added, feed_time, body)
          VALUES (%s, %s, %s, %s, %s)
        """, _conversations)

        mysql_connection.commit()

      except mysql.Error as e:
        if mysql_connection:
          mysql_connection.rollback()

        print "Error %d: %s" % (e.args[0], e.args[1]) 

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

def sync_glpi():

    import MySQLdb
    import re
    from socket import gaierror, gethostbyname_ex
    from wato import ping, prepare_git_commit
    from watolib import ActivateChangesManager, Folder, Host, check_mk_automation, default_site

    query = """
        SELECT c.id host_id,
        LOWER(c.name) hostname,
        LOWER(s.name) state,
        ct.name type,
        os.name osname
        FROM glpi_computers c
        LEFT JOIN glpi_states s
        ON c.states_id = s.id
        LEFT JOIN glpi_computertypes ct
        ON c.computertypes_id = ct.id
        LEFT JOIN glpi_operatingsystems os
        ON c.operatingsystems_id = os.id
        WHERE c.is_deleted = 0
        AND c.is_template = 0
        AND LOWER(s.name) IN (%s)
        ORDER BY hostname
    """ % ', '.join(map(lambda x: '%s', glpi_config['states']))

    try:
        db_con = MySQLdb.connect(
            user = glpi_config['db_user'],
            passwd = glpi_config['db_passwd'],
            host = glpi_config['host'],
            db = glpi_config['db_name'],
            use_unicode=True,
            charset='utf8')
        cur = db_con.cursor()
        cur.execute(query, glpi_config['states'])
        db_result = cur.fetchall()
    except MySQLdb.Error, e:
        return str(e) 

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

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

def __init__(self,url,port,username,password,dbname,charset="utf8"):
        self.url=url
        self.port=port
        self.username=username
        self.password=password
        self.dbname=dbname
        self.charset=charset
        try:
            self.conn=MySQLdb.connect(self.url,self.username,self.password,self.dbname,self.port)
            self.conn.set_character_set(self.charset)
            self.cur=self.conn.cursor()
        except MySQLdb.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 

def query(self,sql):
        try:
            n=self.cur.execute(sql)
            return n
        except MySQLdb.Error as e:
            print("Mysql Error:%s\nSQL:%s" %(e,sql)) 

def __init__(self,url,username,password,dbname,charset="utf8"):
		self.url=url
		self.username=username
		self.password=password
		self.dbname=dbname
		self.charset=charset
		try:  
			self.conn=MySQLdb.connect(self.url,self.username,self.password,self.dbname)  
			self.conn.set_character_set(self.charset)  
            		self.cur=self.conn.cursor()  
        	except MySQLdb.Error as e:  
            		print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 

def query(self,sql):
        	try:
           		n=self.cur.execute(sql)
           		return n
        	except MySQLdb.Error as e:
           		print("Mysql Error:%s\nSQL:%s" %(e,sql)) 

def __init__(self,url,username,password,dbname,charset="utf8"):
		self.url=url
		self.username=username
		self.password=password
		self.dbname=dbname
		self.charset=charset
		try:  
			self.conn=MySQLdb.connect(self.url,self.username,self.password,self.dbname)  
			self.conn.set_character_set(self.charset)  
            		self.cur=self.conn.cursor()  
        	except MySQLdb.Error as e:  
            		print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 

def __init__(self,url,username,password,dbname,charset="utf8"):
		self.url=url
		self.username=username
		self.password=password
		self.dbname=dbname
		self.charset=charset
		try:  
			self.conn=MySQLdb.connect(self.url,self.username,self.password,self.dbname)  
			self.conn.set_character_set(self.charset)  
            		self.cur=self.conn.cursor()  
        	except MySQLdb.Error as e:  
            		print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 

def query(self,sql):
        	try:
           		n=self.cur.execute(sql)
           		return n
        	except MySQLdb.Error as e:
           		print("Mysql Error:%s\nSQL:%s" %(e,sql)) 

def __init__(self,url,username,password,dbname,charset="utf8"):
                self.url=url
                self.username=username
                self.password=password
                self.dbname=dbname
                self.charset=charset
                try:
                        self.conn=MySQLdb.connect(self.url,self.username,self.password,self.dbname)
                        self.conn.set_character_set(self.charset)
                        self.cur=self.conn.cursor()
                except MySQLdb.Error as e:
                        print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 

def query(self,sql):
                try:
                        n=self.cur.execute(sql)
                        return n
                except MySQLdb.Error as e:
                        print("Mysql Error:%s\nSQL:%s" %(e,sql)) 

def __init__(self,url,username,password,dbname,charset="utf8"):
		self.url=url
		self.username=username
		self.password=password
		self.dbname=dbname
		self.charset=charset
		try:  
			self.conn=MySQLdb.connect(self.url,self.username,self.password,self.dbname)  
			self.conn.set_character_set(self.charset)  
            		self.cur=self.conn.cursor()  
        	except MySQLdb.Error as e:  
            		print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 

def __init__(self,url,username,password,dbname,charset="utf8"):
		self.url=url
		self.username=username
		self.password=password
		self.dbname=dbname
		self.charset=charset
		try:  
			self.conn=MySQLdb.connect(self.url,self.username,self.password,self.dbname)  
			self.conn.set_character_set(self.charset)  
            		self.cur=self.conn.cursor()  
        	except MySQLdb.Error as e:  
            		print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 

def query(self,sql):
        	try:
           		n=self.cur.execute(sql)
           		return n
        	except MySQLdb.Error as e:
           		print("Mysql Error:%s\nSQL:%s" %(e,sql)) 

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 

def check_uuid(self, uuid):
        '''check in the database if this uuid is already present'''
        try:
            cmd = "SELECT * FROM uuids where uuid='" + str(uuid) + "'"
            with self.con:
                self.cur = self.con.cursor(mdb.cursors.DictCursor)
                self.logger.debug(cmd)
                self.cur.execute(cmd)
                rows = self.cur.fetchall()
                return self.cur.rowcount, rows
        except (mdb.Error, AttributeError) as e:
            return self.format_error(e, "check_uuid", cmd) 

def edit_host(self, host_id, host_dict):
        #get next port index
        for retry_ in range(0,2):
            cmd=""
            try:
                with self.con:
                    self.cur = self.con.cursor()

                    #update table host
                    numa_list = host_dict.pop('numas', () )                    
                    if host_dict:
                        self._update_rows_internal("hosts", host_dict, {"uuid": host_id})
                        
                    where = {"host_id": host_id} 
                    for numa_dict in numa_list:
                        where["numa_socket"] = str(numa_dict.pop('numa_socket'))
                        interface_list = numa_dict.pop('interfaces', () )
                        if numa_dict:
                            self._update_rows_internal("numas", numa_dict, where)
                        for interface in interface_list:
                            source_name = str(interface.pop("source_name") )
                            if interface:
                            #get interface id from resources_port
                                cmd= "SELECT rp.id as id FROM resources_port as rp join numas as n on n.id=rp.numa_id join hosts as h on h.uuid=n.host_id " +\
                                    "WHERE host_id='%s' and rp.source_name='%s'" %(host_id, source_name)
                                self.logger.debug(cmd)
                                self.cur.execute(cmd)
                                row = self.cur.fetchone()
                                if self.cur.rowcount<=0:
                                    return -HTTP_Bad_Request, "Interface source_name='%s' from numa_socket='%s' not found" % (source_name, str(where["numa_socket"]))
                                interface_id = row[0]
                                self._update_rows_internal("resources_port", interface, {"root_id": interface_id})
                return self.get_host(host_id)
            except (mdb.Error, AttributeError) as e:
                r,c = self.format_error(e, "edit_host", cmd)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def delete_row(self, table, uuid):
        for retry_ in range(0,2):
            cmd=""
            try:
                with self.con:
                    #delete host
                    self.cur = self.con.cursor()
                    cmd = "DELETE FROM %s WHERE uuid = '%s'" % (table, uuid)
                    self.logger.debug(cmd)
                    self.cur.execute(cmd)
                    deleted = self.cur.rowcount
                    if deleted == 1:
                        #delete uuid
                        if table == 'tenants': tenant_str=uuid
                        else: tenant_str='Null'
                        self.cur = self.con.cursor()
                        cmd = "DELETE FROM uuids WHERE uuid = '%s'" % uuid
                        self.logger.debug(cmd)
                        self.cur.execute(cmd)
                        #inserting new log
                        cmd = "INSERT INTO logs (related,level,uuid,tenant_id,description) VALUES ('%s','debug','%s','%s','delete %s')" % (table, uuid, tenant_str, table[:-1])
                        self.logger.debug(cmd)
                        self.cur.execute(cmd)                    
                return deleted, table[:-1] + " '%s' %s" %(uuid, "deleted" if deleted==1 else "not found")
            except (mdb.Error, AttributeError) as e:
                r,c = self.format_error(e, "delete_row", cmd, "delete", 'instances' if table=='hosts' or table=='tenants' else 'dependencies')
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def get_ports(self, WHERE):
        ''' Obtain ports using the WHERE filtering.
        Attributes:
            'where_': dict of key:values, translated to key=value AND ... (Optional)
        Return: a list with dictionarys at each row
        '''
        for retry_ in range(0,2):
            cmd=""
            try:
                with self.con:

                    self.cur = self.con.cursor(mdb.cursors.DictCursor)
                    select_ = "SELECT uuid,'ACTIVE' as status,admin_state_up,name,net_id,\
                        tenant_id,type,mac,vlan,switch_port,instance_id,Mbps FROM ports "

                    if WHERE is None or len(WHERE) == 0:  where_ = ""
                    else:
                        where_ = "WHERE " + " AND ".join(map( lambda x: str(x) + (" is Null" if WHERE[x] is None else "='"+str(WHERE[x])+"'"),  WHERE.keys()) ) 
                    limit_ = "LIMIT 100"
                    cmd =  " ".join( (select_, where_, limit_) )
    #                print "SELECT multiple de instance_ifaces, iface_uuid, external_ports" #print cmd
                    self.logger.debug(cmd)
                    self.cur.execute(cmd)
                    ports = self.cur.fetchall()
                    if self.cur.rowcount>0:  af.DeleteNone(ports)
                    return self.cur.rowcount, ports
    #                return self.get_table(FROM=from_, SELECT=select_,WHERE=where_,LIMIT=100)
            except (mdb.Error, AttributeError) as e:
                r,c = self.format_error(e, "get_ports", cmd)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def insert_article_features():

    connection = db._create_connection()
    cursor = connection.cursor()

    network = load_graph("output/wikipedianetwork.xml.gz")
    print 'graph loaded'
    articles = db_work_view.retrieve_all_articles()
    print 'articles loaded'

    # setup logging
    LOGGING_FORMAT = '%(levelname)s:\t%(asctime)-15s %(message)s'
    LOGGING_PATH = 'tmp/articlefeatures-dbinsert.log'
    logging.basicConfig(filename=LOGGING_PATH, level=logging.DEBUG, format=LOGGING_FORMAT, filemode='w')

    for article in articles:
        try:
            article_features = {}
            vertex = network.vertex(article['id'])
            article_features['id'] = article['id']
            article_features['in_degree'] = vertex.in_degree()
            article_features['out_degree'] = vertex.out_degree()
            article_features['degree'] = vertex.in_degree() + vertex.out_degree()
            article_features['page_rank'] = network.vertex_properties["page_rank"][vertex]
            article_features['eigenvector_centr'] = network.vertex_properties["eigenvector_centr"][vertex]
            article_features['local_clust'] = network.vertex_properties["local_clust"][vertex]
            article_features['kcore'] = network.vertex_properties["kcore"][vertex]
            article_features['hits_authority'] = network.vertex_properties["authority"][vertex]
            article_features['hits_hub'] = network.vertex_properties["hub"][vertex]

            sql = "INSERT INTO article_features (id, in_degree," \
                             "out_degree, degree, page_rank, " \
                             "local_clustering, eigenvector_centr," \
                             " kcore, hits_authority, hits_hub) VALUES" \
                             "(%(id)s, %(in_degree)s, %(out_degree)s," \
                             "%(degree)s, %(page_rank)s,  %(eigenvector_centr)s, " \
                             "%(local_clust)s, %(kcore)s, %(hits_authority)s, %(hits_hub)s);"


            cursor.execute(sql, article_features)
            #logging.info('DB Insert Success for article id: "%s" ' % article['id'])
        except MySQLdb.Error as e:
            logging.error('DB Insert Error  article id: "%s" ' % article['id'])
        except ValueError:
            logging.error('ValueError for article id: "%s"' % article['id'])
        connection.commit()
    connection.close() 

def update_link_features_sem_similarity():

    connection = db._create_connection()
    cursor = connection.cursor()

    # setup logging
    LOGGING_FORMAT = '%(levelname)s:\t%(asctime)-15s %(message)s'
    LOGGING_PATH = 'tmp/link_features_semsim-dbinsert.log'
    logging.basicConfig(filename=LOGGING_PATH, level=logging.DEBUG, format=LOGGING_FORMAT, filemode='w')
    for dirname, dirnames, filenames in os.walk("/home/psinger/WikiLinks/data/sem_sim"):
        for file_name in filenames:
            if file_name.endswith(".p"):
                print file_name
                sem_sim = cPickle.load( open( "/home/psinger/WikiLinks/data/sem_sim/"+file_name, "rb" ) )
                for link, sim in sem_sim.iteritems():
                    try:
                        link_features = {}
                        link_features['source_article_id'] = link[0]
                        link_features['target_article_id'] = link[1]
                        link_features['sim'] = sim

                        sql  = "UPDATE link_features " \
                               "SET  sem_similarity=%(sim)s " \
                               "WHERE source_article_id = %(source_article_id)s AND target_article_id = %(target_article_id)s;"

                        cursor.execute(sql, link_features)

                    except MySQLdb.Error as e:
                        logging.error(e)
                    connection.commit()
                    try:
                        link_features = {}
                        link_features['source_article_id'] = link[1]
                        link_features['target_article_id'] = link[0]
                        link_features['sim'] = sim

                        sql  = "UPDATE link_features " \
                               "SET  sem_similarity=%(sim)s " \
                               "WHERE source_article_id = %(source_article_id)s AND target_article_id = %(target_article_id)s;"

                        cursor.execute(sql, link_features)

                    except MySQLdb.Error as e:
                        logging.error(e)
                    connection.commit()
                connection.close() 

def create_db():
  try:
    print YELLOW,'Wizard creation Database for COOK DEVICE'
    print '','verify you have installed mysql and configured credential for access'
    print '','and verify if service of mysql is running (systemctl status mariadb.service \ mysql.service)'
    print '','or you start the mysql (systemctl start mariadb.service \ mysql.service)\n'
    print RESET

    user = raw_input('user: ')
    password  = getpass.getpass('password: ')
    CONN = MySQLdb.connect(user=user, passwd=password)
    CUR = CONN.cursor()

    # create database
    database = raw_input('create new database: ')
    CUR.execute("CREATE DATABASE %s" % database)
    CUR.fetchone()
    print GREEN,'result: ok, %s' % database
    print RESET

    # use database created
    CUR.execute("USE %s" % database)

    # create table
    table = raw_input('insert new table name: ')
    CUR.execute("CREATE TABLE %s (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, DeviceName VARCHAR(32), Ip VARCHAR(16), Session VARCHAR(8))" % table)
    CUR.fetchone()
    print GREEN,'result: ok, %s' % table
    print RESET

    # insert device in table
    path_file = raw_input('import file (.csv): ')
    query = "LOAD DATA LOCAL INFILE"
    query2 = " '%s'" % path_file
    query3 = " INTO TABLE %s FIELDS TERMINATED BY ',' (DeviceName, Ip, Session);\n" % table
    CUR.execute(query + query2 + query3)
    CONN.commit()
    CUR.fetchall()
    print GREEN,'ok, uploaded, %s' % path_file
    print RESET

    CUR.close()
    CONN.close()

  except MySQLdb.Error, e:
    try:
      print YELLOW, "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
      print 'exit', RESET
    except IndexError:
      print YELLOW, "MySQL Error: %s" % str(e), RESET
      print 'exit', RESET

      CUR.close()
      CONN.close()


# Function for insert new item into tables 

def list_table_db():
  try:
    print 'List all records of table'

    user = raw_input('user: ')
    password  = getpass.getpass('password: ')
    CONN = MySQLdb.connect(user=user, passwd=password)
    CUR = CONN.cursor()

    print 'Databases found: \n'

    db = ("SHOW DATABASES")
    CUR.execute(db)
    response_db_list = CUR.fetchall()

    for row in response_db_list:
      print BRIGHT,'-',row[0]
    print RESET # reset color text

    print ''

    database  = raw_input('database: ')
    CUR.execute("USE %s" % database)

    print 'table found in %s : \n' % database
    tables = ("SHOW TABLES")
    CUR.execute(tables)
    response_tables_list = CUR.fetchall()

    for row in response_tables_list:
      print BRIGHT,'-',row[0]
    print RESET

    print ''

    table = raw_input('table: ')

    print 'records in %s: ' % table
    data = ("SELECT * FROM %s" % table)
    CUR.execute(data)
    response_tables_all_data = CUR.fetchall()

    for row in response_tables_all_data:
      print GREEN,'-',row[0],'-', row[1],'-', row[2],'-', row[3]
    print RESET

    CUR.close()
    CONN.close()

  except MySQLdb.Error, e:
    try:
      print YELLOW, "MySQL Error [%d]: %s" % (e.args[0], e.args[1]), RESET
    except IndexError:
      print YELLOW, "MySQL Error: %s" % str(e), RESET

      CUR.close()
      CONN.close()


# delete row in database table 

def delete_full_database():
  try:
    print "Delete the full Database from MySQL"

    user = raw_input('user: ')
    password  = getpass.getpass('password: ')
    CONN = MySQLdb.connect(user=user, passwd=password)
    CUR = CONN.cursor()

    print 'Databases found: \n'

    db = ("SHOW DATABASES")
    CUR.execute(db)
    response_db_list = CUR.fetchall()

    for row in response_db_list:
      print BRIGHT,'-',row[0],RESET
    print ''

    database = raw_input("Database: ")

    for line in response_db_list:
      data = re.search(database, str(line))
      if data:
        data.group(0)
        CUR.execute("DROP DATABASE %s;" % database)
        CUR.fetchall()
        CONN.commit()

    print RED,'deleted', RESET
    print ''

    db = ("SHOW DATABASES")
    CUR.execute(db)
    response_db_list = CUR.fetchall()

    for row in response_db_list:
      print BRIGHT,'-',row[0],RESET
    print ''

    CUR.close()
    CONN.close()

  except MySQLdb.Error, e:
    try:
      print YELLOW,"MySQL Error [%d]: %s" % (e.args[0], e.args[1]), RESET
    except IndexError:
      print YELLOW,"MySQL Error: %s" % str(e), RESET

      CUR.close()
      CONN.close()


# Main function 

def GetDataFromDB():
    # Значения данных по умолчанию
    devices_data = [ ('0.0.0.0', 0, ''   ) ]
    ports_data   = [ ('0.0.0.0', 0, 0, '') ]
    # Пробуем подключиться к базе данных PostgreSQL либо MySQL. Используем таймаут в 2 секунды
    try:
	if use_postgresql == True:
	    db_conn = psycopg2.connect( host = postgresql_addr, user = postgresql_user, password = postgresql_pass, dbname = postgresql_base, connect_timeout = 2 )
	else:
	    db_conn = MySQLdb.connect( host = mysql_addr, user = mysql_user, passwd = mysql_pass, db = mysql_base, connect_timeout = 2 )
    # Если возникла ошибка при подключении, сообщаем об этом в лог и возвращаем 'пустые' данные
    except psycopg2.Error as p_err:
	logger.info("ERROR: PostgreSQL Error (%s): %s", postgresql_addr, p_err.args)
	return devices_data, ports_data
    except MySQLdb.Error as m_err:
	logger.info("ERROR: MySQL Error (%s): %s", mysql_addr, m_err.args[1])
	return devices_data, ports_data
    # Если ошибок не было, сообщаем в лог об успешном подключении и создаем 'курсор'
    else:
	if use_postgresql == True:
	    logger.info("INFO: Connection to PostgreSQL Server '%s' established", postgresql_addr)
	else:
	    logger.info("INFO: Connection to MySQL Server '%s' established", mysql_addr)
	db_cr = db_conn.cursor()
	# Пробуем выполнить запрос к базе и получить все данные из 'курсора' (списоки устройств и портов)
	try:
	    db_cr.execute(devices_query)
	    devices_data = db_cr.fetchall()
	    db_cr.execute(ports_query)
	    ports_data = db_cr.fetchall()
	# Если возникла ошибка при выполнении запроса, сообщаем об этом в лог и возвращаем 'пустые' данные
	except psycopg2.Error as p_err:
	    logger.info("ERROR: PostgreSQL Query failed: %s", p_err.args)
	    return devices_data, ports_data
	except MySQLdb.Error as m_err:
	    logger.info("ERROR: MySQL Query failed: %s", m_err.args[1])
	    return devices_data, ports_data
	# Если ошибок не возникло, сообщаем в лог об успешном подключении
        else:
	    if use_postgresql == True:
		logger.info("INFO: PostgreSQL Query OK. %s rows found for devices and %s for ports", len(devices_data), len(ports_data))
	    else:
		logger.info("INFO: MySQL Query OK. %s rows found for devices and %s for ports", len(devices_data), len(ports_data))
	# Закрываем подключение
	finally:
	    db_conn.close()
    # Возвращаем списки портов и устройств
    return devices_data, ports_data

# Функция размещения полученной конфигурации в базе MySQL 

def PutConfigToMySQL(direction, cfg, target, name, switch, ip, m5d, cfg_type):
    # Сортируем и склеиваем блоки данных
    cfg = ''.join([cfg[i] for i in sorted(cfg.keys())])
    # Формируем данные для размещения их в базе
    mysql_query = "INSERT INTO {0}.{1} ({1}.timestamp, {1}.ip, {1}.target, {1}.direction, {1}.switch, {1}.name, {1}.hash) VALUES ".format(mysql_base_w, mysql_ttbl_w)
    mysql_query+= "('{0}', INET_ATON('{1}'), INET_ATON('{2}'), '{3}', '{4}', '{5}', '{6}');".format(GetDTTM()[0], ip, target, direction, switch, name, m5d)
    # Пробуем подключиться к базе данных MySQL. Используем таймаут в 2 секунды
    try:
	db_conn = MySQLdb.connect(host = mysql_addr_w, user = mysql_user_w, passwd = mysql_pass_w, db = mysql_base_w, connect_timeout = 2)
	db_conn.autocommit(True)
    # Если возникла ошибка при подключении, сообщаем об этом в лог
    except MySQLdb.Error as err:
        logger.info("ERROR: MySQL Error (%s): %s", mysql_addr_w, err.args[1])
    # Если ошибок не было, создаем 'курсор'
    else:
        mysql_cr = db_conn.cursor()
	# Пробуем записать в базу информацию об операции
	try:
	    mysql_cr.execute(mysql_query)
	# При ошибке выполнения запроса сообщаем в лог о проблеме
	except MySQLdb.Error as err:
	    logger.info("ERROR: MySQL Query Error: %s", err.args[1])
	else:
	    # Помещать в базу данных следует только саму конфигурацию, потому что бэкапы там уже есть, а ПО не должно быть вообще
	    if cfg_type == 'config':
		# Пробуем выполнить запрос к базе и подсчитать кол-во уникальных записей для hash
		try:
		    mysql_cr.execute("SELECT COUNT(*) FROM {0}.{1} WHERE {1}.hash='{2}'".format(mysql_base_w, mysql_ctbl_w, m5d))
		# При ошибке выполнения запроса сообщаем в лог о проблеме
		except MySQLdb.Error as err:
		    logger.info("ERROR: MySQL Query Error: %s", err.args[1])
		else:
		    hash_count = mysql_cr.fetchall()[0][0]
		    # Определяем, является ли данный hash уникальным
		    unique_hash = (hash_count == 0)
		    # Если hash уникален (еще не встречался), записываем конфиг в базу
		    if unique_hash:
			try:
			    mysql_cr.execute("INSERT INTO {0}.{1} ({1}.hash, {1}.config) VALUES ('{2}', '{3}');".format(mysql_base_w, mysql_ctbl_w, m5d, cfg))
			# При ошибке выполнения запроса сообщаем в лог о проблеме
			except MySQLdb.Error as err:
			    logger.info("ERROR: MySQL Query Error: %s", err.args[1])
			# Либо сообщаем об успешной операции записи
			else:
			    logger.info("INFO: Succesfully sent %s bytes to MySQL for '%s' ('%s'). Request from %s", len(cfg), target, switch, ip)
	finally:
	    db_conn.close()

# Функция получения последней конфигурации из базы MySQL 

def on_message(mosq, obj, msg):
    print(msg.topic+" "+str(msg.qos)+" "+str(msg.payload))
    vars_to_sql = []
    keys_to_sql = []
    list = []
    
    list = json.loads(msg.payload)
    
    for key,value in list.iteritems():
      print ("")
      print key, value
      if key == 'tst':
        print "time found"
        print value
        value = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(float(value)))
        print value
        
      value_type = type(value)
      if value_type is not dict:
        print "value_type is not dict"
        if value_type is unicode:
          print "value_type is unicode"
          vars_to_sql.append(value.encode('ascii', 'ignore'))
          keys_to_sql.append(key.encode('ascii', 'ignore'))
        else:
          print "value_type is not unicode"
          vars_to_sql.append(value)
          keys_to_sql.append(key)
    #add the msg.topic to the list as well
    print "topic", msg.topic
    addtopic = 'topic'
    vars_to_sql.append(msg.topic.encode('ascii', 'ignore'))
    keys_to_sql.append(addtopic.encode('ascii', 'ignore'))
    
    keys_to_sql = ', '.join(keys_to_sql)

    try:
       # Execute the SQL command 
       # change locations to the table you are using
       queryText = "INSERT INTO locations(%s) VALUES %r"
       queryArgs = (keys_to_sql, tuple(vars_to_sql))
       cursor.execute(queryText % queryArgs)
       print('Successfully Added record to mysql')
       db.commit()
    except MySQLdb.Error, e:
        try:
            print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
        except IndexError:
            print "MySQL Error: %s" % str(e)
        # Rollback in case there is any error
        db.rollback()
        print('ERROR adding record to MYSQL') 

def new_row(self, table, INSERT, add_uuid=False, log=False):
        ''' Add one row into a table.
        Atribure 
            INSERT: dictionary with the key: value to insert
            table: table where to insert
            add_uuid: if True, it will crated 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
        '''
        for retry_ in range(0,2):
            cmd=""
            try:
                if add_uuid:
                    #create uuid if not provided
                    if 'uuid' not in INSERT:
                        uuid = INSERT['uuid'] = str(myUuid.uuid1()) # create_uuid
                    else: 
                        uuid = str(INSERT['uuid'])
                else:
                    uuid=None
                with self.con:
                    self.cur = self.con.cursor()
                    if add_uuid:
                        #inserting new uuid
                        cmd = "INSERT INTO uuids (uuid, used_at) VALUES ('%s','%s')" % (uuid, table)
                        self.logger.debug(cmd)
                        self.cur.execute(cmd)
                    #insertion
                    cmd= "INSERT INTO " + table +" (" + \
                        ",".join(map(str, INSERT.keys() ))   + ") VALUES(" + \
                        ",".join(map(lambda x: 'Null' if x is None else "'"+str(x)+"'", INSERT.values() )) + ")"
                    self.logger.debug(cmd)
                    self.cur.execute(cmd)
                    nb_rows = self.cur.rowcount
                    #inserting new log
                    if nb_rows > 0 and log:                
                        if add_uuid: del INSERT['uuid']
                        #obtain tenant_id for logs
                        if 'tenant_id' in INSERT: 
                            tenant_id = INSERT['tenant_id']
                            del INSERT['tenant_id']
                        elif table == 'tenants':    
                            tenant_id = uuid
                        else:                       
                            tenant_id = None
                        if uuid is None: uuid_k = uuid_v = ""
                        else: uuid_k=",uuid"; uuid_v=",'" + str(uuid) + "'"
                        if tenant_id is None: tenant_k = tenant_v = ""
                        else: tenant_k=",tenant_id"; tenant_v=",'" + str(tenant_id) + "'"
                        cmd = "INSERT INTO logs (related,level%s%s,description) VALUES ('%s','debug'%s%s,\"new %s %s\")" \
                            % (uuid_k, tenant_k, table, uuid_v, tenant_v, table[:-1], str(INSERT))
                        self.logger.debug(cmd)
                        self.cur.execute(cmd)                    
                    return nb_rows, uuid

            except (mdb.Error, AttributeError) as e:
                r,c = self.format_error(e, "new_row", cmd)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def new_flavor(self, flavor_dict, tenant_id ):
        '''Add new flavor into the database. Create uuid if not provided
        Atributes
            flavor_dict: flavor dictionary with the key: value to insert. Must be valid flavors columns
            tenant_id: if not 'any', it matches this flavor/tenant inserting at tenants_flavors table
        Return: (result, data) where result can be
            negative: error at inserting. data contain text
            1, inserted, data contain inserted uuid flavor
        '''
        for retry_ in range(0,2):
            cmd=""
            try:
                with self.con:
                    self.cur = self.con.cursor()

                    #create uuid if not provided
                    if 'uuid' not in flavor_dict:
                        uuid = flavor_dict['uuid'] = str(myUuid.uuid1()) # create_uuid
                    else: #check uuid is valid
                        uuid = str(flavor_dict['uuid'])
                    #    result, data = self.check_uuid(uuid)
                    #    if (result == 1):
                    #        return -1, "UUID '%s' already in use" % uuid

                    #inserting new uuid
                    cmd = "INSERT INTO uuids (uuid, used_at) VALUES ('%s','flavors')" % uuid
                    self.logger.debug(cmd)
                    self.cur.execute(cmd)

                    #insert in table flavor
                    keys    = ",".join(flavor_dict.keys())
                    values  = ",".join( map(lambda x:  "Null" if x is None else "'"+str(x)+"'", flavor_dict.values() ) )
                    cmd = "INSERT INTO flavors (" + keys + ") VALUES (" + values + ")"
                    self.logger.debug(cmd)
                    self.cur.execute(cmd)
                    #if result != 1: return -1, "Database Error while inserting at flavors table"

                    #insert tenants_flavors
                    if tenant_id != 'any':
                        cmd = "INSERT INTO tenants_flavors (tenant_id,flavor_id) VALUES ('%s','%s')" % (tenant_id, uuid)
                        self.logger.debug(cmd)
                        self.cur.execute(cmd)

                    #inserting new log
                    del flavor_dict['uuid']
                    if 'extended' in flavor_dict: del flavor_dict['extended'] #remove two many information
                    cmd = "INSERT INTO logs (related,level,uuid, tenant_id, description) VALUES ('flavors','debug','%s','%s',\"new flavor: %s\")" \
                        % (uuid, tenant_id, str(flavor_dict))
                    self.logger.debug(cmd)
                    self.cur.execute(cmd)                    

                    #inseted ok
                return 1, uuid
            except (mdb.Error, AttributeError) as e:
                r,c = self.format_error(e, "new_flavor", cmd, "update", tenant_id)
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c 

def delete_row_by_dict(self, **sql_dict):
        ''' Deletes rows from a table.
        Attribute sql_dir: dictionary with the following key: value
            'FROM': string of table name (Mandatory)
            'WHERE': dict of key:values, translated to key=value AND ... (Optional)
            'WHERE_NOT': dict of key:values, translated to key<>value AND ... (Optional)
            'WHERE_NOTNULL': (list or tuple of items that must not be null in a where ... (Optional)
            'LIMIT': limit of number of rows (Optional)
        Return: the (number of items deleted, descriptive test) if ok; (negative, descriptive text) if error
        '''
        #print sql_dict
        from_  = "FROM " + str(sql_dict['FROM'])
        #print 'from_', from_
        if 'WHERE' in sql_dict and len(sql_dict['WHERE']) > 0:
            w=sql_dict['WHERE']
            where_ = "WHERE " + " AND ".join(map( lambda x: str(x) + (" is Null" if w[x] is None else "='"+str(w[x])+"'"),  w.keys()) ) 
        else: where_ = ""
        if 'WHERE_NOT' in sql_dict and len(sql_dict['WHERE_NOT']) > 0: 
            w=sql_dict['WHERE_NOT']
            where_2 = " AND ".join(map( lambda x: str(x) + (" is not Null" if w[x] is None else "<>'"+str(w[x])+"'"),  w.keys()) )
            if len(where_)==0:   where_ = "WHERE " + where_2
            else:                where_ = where_ + " AND " + where_2
        if 'WHERE_NOTNULL' in sql_dict and len(sql_dict['WHERE_NOTNULL']) > 0: 
            w=sql_dict['WHERE_NOTNULL']
            where_2 = " AND ".join(map( lambda x: str(x) + " is not Null",  w) )
            if len(where_)==0:   where_ = "WHERE " + where_2
            else:                where_ = where_ + " AND " + where_2
        #print 'where_', where_
        limit_ = "LIMIT " + str(sql_dict['LIMIT']) if 'LIMIT' in sql_dict else ""
        #print 'limit_', limit_
        cmd =  " ".join( ("DELETE", from_, where_, limit_) )
        self.logger.debug(cmd)
        for retry_ in range(0,2):
            try:
                with self.con:
                    #delete host
                    self.cur = self.con.cursor()
                    self.cur.execute(cmd)
                    deleted = self.cur.rowcount
                return deleted, "%d deleted from %s" % (deleted, sql_dict['FROM'][:-1] )
            except (mdb.Error, AttributeError) as e:
                r,c =  self.format_error(e, "delete_row_by_dict", cmd, "delete", 'dependencies')
                if r!=-HTTP_Request_Timeout or retry_==1: return r,c