def get_data_from_db(db_url): db = DataBase(db_url) if db.connect(): if not db.table_exists("inf_list"): db.execute("CREATE TABLE inf_list(id VARCHAR(255) PRIMARY KEY, date TIMESTAMP, data LONGBLOB)") db.close() return {} else: inf_list = {} res = db.select("select * from inf_list order by id desc") if len(res) > 0: for elem in res: #inf_id = elem[0] #date = elem[1] try: inf = pickle.loads(elem[2]) if not inf.deleted: inf_list[inf.id] = inf except: InfrastructureManager.logger.exception("ERROR reading infrastructure %d from database, ignoring it!." % inf.id) else: InfrastructureManager.logger.error("ERROR getting inf_list from database!.") db.close() return inf_list else: InfrastructureManager.logger.error("ERROR connecting with the database!.") return {}
def _reinit(): """Restart the class attributes to initial values.""" InfrastructureList.infrastructure_list = {} InfrastructureList._lock = threading.Lock() db = DataBase(Config.DATA_DB) if db.connect(): db.execute("delete from inf_list") db.close()
def delete_data_from_db(db_url, date): db = DataBase(db_url) if db.connect(): db.execute("DELETE FROM inf_list WHERE deleted = 1 and date < '%s';" % date) db.close() else: sys.stderr.write("ERROR connecting with the database!.") sys.exit(-1)
def test_sqlite_db(self): filename = "/tmp/inf.dat" if os.path.exists(filename): os.unlink(filename) db_url = "sqlite://" + filename db = DataBase(db_url) self.assertTrue(db.connect()) if not db.table_exists("test"): db.execute("CREATE TABLE test(id int PRIMARY KEY, date TIMESTAMP, data LONGBLOB)") self.assertTrue(db.table_exists("test")) db.execute("insert into test (id, data, date) values (%s, %s, now())", (1, "Data")) res = db.select("select data from test where id = %s", (1,)) self.assertEqual(res, [("Data",)]) db.close()
def init_table(): """ Creates de database """ db = DataBase(Config.DATA_DB) if db.connect(): if not db.table_exists("inf_list"): db.execute( "CREATE TABLE inf_list(id VARCHAR(255) PRIMARY KEY, deleted INTEGER," " date TIMESTAMP, data LONGBLOB)") db.close() return True else: InfrastructureList.logger.error( "ERROR connecting with the database!.") return False
def _save_data_to_db(db_url, inf_list, inf_id=None): db = DataBase(db_url) if db.connect(): infs_to_save = inf_list if inf_id: infs_to_save = {inf_id: inf_list[inf_id]} for inf in infs_to_save.values(): data = inf.serialize() if db.db_type == DataBase.MONGO: res = db.replace("inf_list", {"id": inf.id}, { "id": inf.id, "deleted": int(inf.deleted), "data": data, "date": time.time() }) else: res = db.execute( "replace into inf_list (id, deleted, data, date) values (%s, %s, %s, now())", (inf.id, int(inf.deleted), data)) db.close() return res else: InfrastructureList.logger.error( "ERROR connecting with the database!.") return None
def init_table(): """ Creates de database """ db = DataBase(Config.DATA_DB) if db.connect(): if not db.table_exists("inf_list"): InfrastructureList.logger.debug("Creating the IM database!.") if db.db_type == DataBase.MYSQL: db.execute("CREATE TABLE inf_list(rowid INTEGER NOT NULL AUTO_INCREMENT UNIQUE," " id VARCHAR(255) PRIMARY KEY, deleted INTEGER, date TIMESTAMP, data LONGBLOB)") elif db.db_type == DataBase.SQLITE: db.execute("CREATE TABLE inf_list(id VARCHAR(255) PRIMARY KEY, deleted INTEGER," " date TIMESTAMP, data LONGBLOB)") db.close() return True else: InfrastructureList.logger.error("ERROR connecting with the database!.") return False
def test_mysql_db(self, mdb_conn): connection = MagicMock() mdb_conn.return_value = connection db_url = "mysql://*****:*****@server/db_name" db = DataBase(db_url) self.assertTrue(db.connect()) if not db.table_exists("test"): db.execute("CREATE TABLE test(id int PRIMARY KEY, date TIMESTAMP, data LONGBLOB)") db.execute("insert into test (id, data, date) values (%s, %s, now())", (1, "Data")) cursor = MagicMock() cursor.fetchall.return_value = [("Data",)] connection.cursor.return_value = cursor res = db.select("select data from test where id = %s", (1,)) self.assertEqual(res, [("Data",)]) db.close()
def rename_old_data(): db = DataBase(Config.DATA_DB) if db.connect(): if db.table_exists("inf_list"): now = str(int(time.time() * 100)) if db.db_type == DataBase.SQLITE: db.execute('ALTER TABLE inf_list RENAME TO inf_list_%s;' % now) db.close() elif db.db_type == DataBase.MYSQL: db.execute('RENAME TABLE inf_list TO inf_list_%s;' % now) db.close() else: db.close() sys.stderr.write("ERROR connecting with the database!.") sys.exit(-1) else: db.close() else: sys.stderr.write("ERROR connecting with the database!.") sys.exit(-1)
def save_data_to_db(db_url, inf_list, inf_id = None): db = DataBase(db_url) if db.connect(): infs_to_save = inf_list if inf_id: infs_to_save = {inf_id: inf_list[inf_id]} for inf in infs_to_save.values(): res = db.execute("replace into inf_list set id = %s, data = %s, date = now()", (inf.id, pickle.dumps(inf))) db.close() return res else: InfrastructureManager.logger.error("ERROR connecting with the database!.") return None
def _save_data_to_db(db_url, inf_list, inf_id=None): db = DataBase(db_url) if db.connect(): infs_to_save = inf_list if inf_id: infs_to_save = {inf_id: inf_list[inf_id]} for inf in infs_to_save.values(): res = db.execute("replace into inf_list (id, deleted, data, date) values (%s, %s, %s, now())", (inf.id, int(inf.deleted), inf.serialize())) db.close() return res else: InfrastructureList.logger.error("ERROR connecting with the database!.") return None
def insertRecipe(name, version, module, recipe, desc, requirements, galaxy_module=None, isapp=0): """ Static method to insert a recipe in the DB """ if not DataBase.db_available: return False else: try: db = DataBase(Config.RECIPES_DB_FILE) db.connect() res = db.execute( '''insert into recipes values ("%s", "%s", "%s", "%s", %d, %d, "%s", "%s")''' % (name, version, module, recipe, isapp, galaxy_module, desc, requirements)) return res except Exception: return False
sys.path.append("..") sys.path.append(".") from IM.config import Config from IM.db import DataBase if __name__ == "__main__": if not Config.DATA_DB: sys.stderr.write("No DATA_DB defined in the im.cfg file!!") sys.exit(-1) db = DataBase(Config.DATA_DB) if db.connect(): if db.table_exists("inf_list"): if db.db_type == DataBase.MYSQL: sys.stdout.write("Updating DB: %s.\n" % Config.DATA_DB) db.execute( "ALTER TABLE `inf_list` ADD COLUMN `rowid` INT AUTO_INCREMENT UNIQUE FIRST;" ) else: sys.stdout.write("SQLite DB does not need to be updated.") db.close() else: sys.stdout.write( "There are no inf_list table. Do not need to update.") else: sys.stderr.write("Error connecting with DB: %s\n" % Config.DATA_DB) sys.exit(-1) sys.exit(0)