Python MySQLdb.Error() Examples

The following are code examples for showing how to use MySQLdb.Error(). They are from open source Python projects. You can vote up the examples you like or vote down the ones you don't like.

Example 1
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 7 votes vote down vote up
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 
Example 2
Project: wikilinks   Author: trovdimi   File: redirectscandidatespostioninserter.py    MIT License 6 votes vote down vote up
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 
Example 3
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 6 votes vote down vote up
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 
Example 4
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 6 votes vote down vote up
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])) 
Example 5
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 6 votes vote down vote up
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])) 
Example 6
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 6 votes vote down vote up
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])) 
Example 7
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 6 votes vote down vote up
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])) 
Example 8
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 6 votes vote down vote up
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])) 
Example 9
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 6 votes vote down vote up
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])) 
Example 10
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 6 votes vote down vote up
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])) 
Example 11
Project: wikilinks   Author: trovdimi   File: linkpostioninserter.py    MIT License 6 votes vote down vote up
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 
Example 12
Project: wikilinks   Author: trovdimi   File: rbo.py    MIT License 6 votes vote down vote up
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 
Example 13
Project: check_mariadb_slaves   Author: ordergroove   File: plugin.py    MIT License 6 votes vote down vote up
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() 
Example 14
Project: gthx   Author: gunnbr   File: DbAccess.py    GNU General Public License v2.0 6 votes vote down vote up
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 
Example 15
Project: gthx   Author: gunnbr   File: DbAccess.py    GNU General Public License v2.0 6 votes vote down vote up
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 
Example 16
Project: sbdspider   Author: onecer   File: pipelines.py    MIT License 6 votes vote down vote up
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]) 
Example 17
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 6 votes vote down vote up
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 
Example 18
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 6 votes vote down vote up
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) 
Example 19
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 6 votes vote down vote up
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 
Example 20
Project: wikilinks   Author: trovdimi   File: weighted_pagerank.py    MIT License 5 votes vote down vote up
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])) 
Example 21
Project: wikilinks   Author: trovdimi   File: redirectscandidatespostioninserter.py    MIT License 5 votes vote down vote up
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) 
Example 22
Project: wikilinks   Author: trovdimi   File: mysqlbuildview.py    MIT License 5 votes vote down vote up
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])) 
Example 23
Project: wikilinks   Author: trovdimi   File: mysqlbuildview.py    MIT License 5 votes vote down vote up
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])) 
Example 24
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 25
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 26
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 27
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 28
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 29
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 30
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 31
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 32
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 33
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 34
Project: wikilinks   Author: trovdimi   File: mysqlworkview.py    MIT License 5 votes vote down vote up
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])) 
Example 35
Project: wikilinks   Author: trovdimi   File: insertarticlefeatures.py    MIT License 5 votes vote down vote up
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() 
Example 36
Project: wikilinks   Author: trovdimi   File: insertarticlefeatures.py    MIT License 5 votes vote down vote up
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() 
Example 37
Project: wikilinks   Author: trovdimi   File: click_distributions.py    MIT License 5 votes vote down vote up
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 
Example 38
Project: wikilinks   Author: trovdimi   File: tableclassinserter.py    MIT License 5 votes vote down vote up
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) 
Example 39
Project: chattR   Author: patrickstocklin   File: base.py    GNU General Public License v2.0 5 votes vote down vote up
def is_usable(self):
        try:
            self.connection.ping()
        except Database.Error:
            return False
        else:
            return True 
Example 40
Project: check_mariadb_slaves   Author: ordergroove   File: test_plugin.py    MIT License 5 votes vote down vote up
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') 
Example 41
Project: dracon   Author: xcme   File: dracon.py    GNU General Public License v2.0 5 votes vote down vote up
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-адреса цели, типа данных и самих данных 
Example 42
Project: gthx   Author: gunnbr   File: DbAccess.py    GNU General Public License v2.0 5 votes vote down vote up
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 
Example 43
Project: sbdspider   Author: onecer   File: pipelines.py    MIT License 5 votes vote down vote up
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]) 
Example 44
Project: AneMo   Author: jspargo   File: base.py    GNU General Public License v2.0 5 votes vote down vote up
def is_usable(self):
        try:
            self.connection.ping()
        except Database.Error:
            return False
        else:
            return True 
Example 45
Project: dockerizeme   Author: dockerizeme   File: snippet.py    Apache License 2.0 5 votes vote down vote up
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]) 
Example 46
Project: dockerizeme   Author: dockerizeme   File: snippet.py    Apache License 2.0 5 votes vote down vote up
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]) 
Example 47
Project: GTDWeb   Author: lanbing510   File: base.py    GNU General Public License v2.0 5 votes vote down vote up
def is_usable(self):
        try:
            self.connection.ping()
        except Database.Error:
            return False
        else:
            return True 
Example 48
Project: glpi_sync   Author: skunkie   File: glpi.py    GNU General Public License v2.0 5 votes vote down vote up
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) 
Example 49
Project: liberator   Author: libscie   File: base.py    Creative Commons Zero v1.0 Universal 5 votes vote down vote up
def is_usable(self):
        try:
            self.connection.ping()
        except Database.Error:
            return False
        else:
            return True 
Example 50
Project: banruo   Author: yingshang   File: base.py    GNU Lesser General Public License v3.0 5 votes vote down vote up
def is_usable(self):
        try:
            self.connection.ping()
        except Database.Error:
            return False
        else:
            return True 
Example 51
Project: DBA_Mysql   Author: BoobooWei   File: mysqlcon.py    GNU General Public License v3.0 5 votes vote down vote up
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])) 
Example 52
Project: DBA_Mysql   Author: BoobooWei   File: mysqlcon.py    GNU General Public License v3.0 5 votes vote down vote up
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)) 
Example 53
Project: DBA_Mysql   Author: BoobooWei   File: booboo.py    GNU General Public License v3.0 5 votes vote down vote up
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])) 
Example 54
Project: DBA_Mysql   Author: BoobooWei   File: booboo.py    GNU General Public License v3.0 5 votes vote down vote up
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)) 
Example 55
Project: DBA_Mysql   Author: BoobooWei   File: binlog_rollbacktest.py    GNU General Public License v3.0 5 votes vote down vote up
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])) 
Example 56
Project: DBA_Mysql   Author: BoobooWei   File: binlog_analyze_all.py    GNU General Public License v3.0 5 votes vote down vote up
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])) 
Example 57
Project: DBA_Mysql   Author: BoobooWei   File: binlog_analyze_all.py    GNU General Public License v3.0 5 votes vote down vote up
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)) 
Example 58
Project: DBA_Mysql   Author: BoobooWei   File: foo.py    GNU General Public License v3.0 5 votes vote down vote up
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])) 
Example 59
Project: DBA_Mysql   Author: BoobooWei   File: foo.py    GNU General Public License v3.0 5 votes vote down vote up
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)) 
Example 60
Project: DBA_Mysql   Author: BoobooWei   File: binlog_analyze.py    GNU General Public License v3.0 5 votes vote down vote up
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])) 
Example 61
Project: DBA_Mysql   Author: BoobooWei   File: mysqlbala.py    GNU General Public License v3.0 5 votes vote down vote up
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])) 
Example 62
Project: DBA_Mysql   Author: BoobooWei   File: mysqlbala.py    GNU General Public License v3.0 5 votes vote down vote up
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)) 
Example 63
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 5 votes vote down vote up
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 
Example 64
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 5 votes vote down vote up
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]) 
Example 65
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 5 votes vote down vote up
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 
Example 66
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 5 votes vote down vote up
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) 
Example 67
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 5 votes vote down vote up
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 
Example 68
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 5 votes vote down vote up
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 
Example 69
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 5 votes vote down vote up
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 
Example 70
Project: wikilinks   Author: trovdimi   File: insertarticlefeatures.py    MIT License 4 votes vote down vote up
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() 
Example 71
Project: wikilinks   Author: trovdimi   File: insertarticlefeatures.py    MIT License 4 votes vote down vote up
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() 
Example 72
Project: cookdevice   Author: Simone-Della   File: manage_db_cook.py    GNU General Public License v3.0 4 votes vote down vote up
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 
Example 73
Project: cookdevice   Author: Simone-Della   File: manage_db_cook.py    GNU General Public License v3.0 4 votes vote down vote up
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 
Example 74
Project: cookdevice   Author: Simone-Della   File: manage_db_cook.py    GNU General Public License v3.0 4 votes vote down vote up
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 
Example 75
Project: dracon   Author: xcme   File: dracon.py    GNU General Public License v2.0 4 votes vote down vote up
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 
Example 76
Project: dracon   Author: xcme   File: dracon.py    GNU General Public License v2.0 4 votes vote down vote up
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 
Example 77
Project: dockerizeme   Author: dockerizeme   File: snippet.py    Apache License 2.0 4 votes vote down vote up
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') 
Example 78
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 4 votes vote down vote up
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 
Example 79
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 4 votes vote down vote up
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 
Example 80
Project: openmano   Author: nfvlabs   File: vim_db.py    Apache License 2.0 4 votes vote down vote up
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