def CreateDB(UseExistingDatabase=False): with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None if UseExistingDatabase: print( "You have specified the UseExistingDatabase option, the the database itself will be kept, but all his content will be removed" ) if input( "This operation will create a new empty DB.\n If a database exists, it will DESTROY all existings data of the existing database.\nAre you SURE ? Confirm by Y !" ).lower() != "y": print("Import Aborted !!!") exit() print("Configuration is Database:", app.config['DB_DATABASE']) print("Login: "******"/", app.config['DB_PASSWORD']) print("Host: ", app.config['DB_HOST']) import psycopg2 if os.path.exists("vault"): print("Drop existings images") shutil.rmtree("vault") os.mkdir("vault") print("Connect Database") if UseExistingDatabase: conn = psycopg2.connect(user=app.config['DB_USER'], password=app.config['DB_PASSWORD'], host=app.config['DB_HOST'], database=app.config['DB_DATABASE']) else: # On se loggue en postgres pour dropper/creer les bases qui doit être déclaré trust dans hba_conf conn = psycopg2.connect(user='******', host=app.config['DB_HOST']) cur = conn.cursor() conn.set_session(autocommit=True) if UseExistingDatabase: print("Drop the existing public schema") sql = "DROP SCHEMA public cascade" else: print("Drop the existing database") sql = "DROP DATABASE IF EXISTS " + app.config['DB_DATABASE'] cur.execute(sql) if UseExistingDatabase: print("Create the public schema") sql = "create schema public AUTHORIZATION " + app.config['DB_USER'] else: print("Create the new database") sql = "create DATABASE " + app.config[ 'DB_DATABASE'] + " WITH ENCODING='LATIN1' OWNER=" + app.config[ 'DB_USER'] + " TEMPLATE=template0 LC_CTYPE='C' LC_COLLATE='C' CONNECTION LIMIT=-1 " cur.execute(sql) print("Create the Schema") dbcreate() print("Create Roles & Users") createadminuser() print("Creation Done")
def CreateDB(): with app.app_context(): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None if input("This operation will create a new empty DB.\n If a database exists, it will DESTROY all existings data of the existing database.\nAre you SURE ? Confirm by Y !").lower()!="y": print("Import Aborted !!!") exit() print("Configuration is Database:",app.config['DB_DATABASE']) print("Login: "******"/",app.config['DB_PASSWORD']) print("Host: ",app.config['DB_HOST']) import psycopg2 print("Connect Database") # On se loggue en postgres pour dropper/creer les bases qui doit être déclaré trust dans hba_conf conn=psycopg2.connect(user='******',host=app.config['DB_HOST']) cur=conn.cursor() conn.set_session(autocommit=True) print("Drop the existing database") sql="DROP DATABASE IF EXISTS "+app.config['DB_DATABASE'] cur.execute(sql) print("Create the new database") sql="create DATABASE "+app.config['DB_DATABASE']+" WITH ENCODING='LATIN1' OWNER="+app.config['DB_USER']+" TEMPLATE=template0 LC_CTYPE='C' LC_COLLATE='C' CONNECTION LIMIT=-1 " cur.execute(sql) print("Create the Schema") dbcreate() print("Create Roles & Users") createadminuser() print("Creation Done")
def dbcreate(): with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None db.create_all() from flask_migrate import stamp stamp(revision='head') database.ExecSQL("""create view objects as select oh.*,ofi.* from obj_head oh left join obj_field ofi on oh.objid=ofi.objfid""")
def ResetDBSequence(cur=None): with app.app_context(): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None print("Start Sequence Reset") if cur is None: cur=db.session cur.execute("SELECT setval('seq_taxonomy', (SELECT max(id) FROM taxonomy), true)") cur.execute("SELECT setval('seq_users', (SELECT max(id) FROM users), true)") cur.execute("SELECT setval('roles_id_seq', (SELECT max(id) FROM roles), true)") print("Sequence Reset Done")
def RecomputeStats(): """ Recompute stats related on Taxonomy and Projects """ import appli.cron with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None appli.cron.RefreshAllProjectsStat() appli.cron.RefreshTaxoStat()
def ExtractCategoriesFromRFModel(modeldir): from sklearn.externals import joblib import json from pathlib import Path with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None ModelFolder = Path("RF_models") / modeldir Meta = json.load((ModelFolder / "meta.json").open("r")) Classifier = joblib.load(ModelFolder / 'random_forest.jbl') # Meta['categories']=[int(x) for x in Classifier.classes_] Meta['categories'] = { r[0]: r[1] for r in database.GetTaxoNameFromIdList( [int(x) for x in Classifier.classes_]) } json.dump(Meta, (ModelFolder / "meta.json").open("w"), indent="\t")
def UpdateSunPos(ProjId): """ will update Sunpos field for object of the given project if projid = * all project are updated """ from appli import CalcAstralDayTime from astral import AstralError with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None param = [] sql = """select distinct objdate,objtime,round(cast(latitude as NUMERIC),4) latitude,round(cast(longitude as NUMERIC),4) longitude from obj_head where objdate is not null and objtime is not null and longitude is not null and latitude is not null """ if ProjId != '*': sql += " and projid=%s " param.append(ProjId) Obj = database.GetAll(sql, param) for o in Obj: # l=Location() # l.latitude=o['latitude'] # l.longitude = o['longitude'] # s=l.sun(date=o['objdate'],local=False) # dt=datetime.datetime(o['objdate'].year,o['objdate'].month,o['objdate'].day,o['objtime'].hour,o['objtime'].minute,o['objtime'].second,tzinfo=pytz.UTC) # if s['sunset'].time()>s['sunrise'].time() \ # and dt.time()>=s['sunset'].time(): Result='N' # elif dt>=s['dusk']: Result='U' # elif dt>=s['sunrise']: Result='D' app.logger.info("Process %s %s %s %s", o['objdate'], o['objtime'], o['latitude'], o['longitude']) try: Result = CalcAstralDayTime(o['objdate'], o['objtime'], o['latitude'], o['longitude']) sql = "update obj_head set sunpos=%s where objdate=%s and objtime=%s and round(cast(latitude as NUMERIC),4)=%s and round(cast(longitude as NUMERIC),4)=%s " param = [ Result, o['objdate'], o['objtime'], o['latitude'], o['longitude'] ] if ProjId != '*': sql += " and projid=%s " param.append(ProjId) database.ExecSQL(sql, param) app.logger.info(Result) except AstralError as e: app.logger.error("Astral error : %s", e)
def ResetDBSequence(cur=None): with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None print("Start Sequence Reset") if cur is None: cur = db.session cur.execute( "SELECT setval('seq_acquisitions', (SELECT max(acquisid) FROM acquisitions), true)" ) cur.execute( "SELECT setval('seq_images', (SELECT max(imgid) FROM images), true)" ) cur.execute( "SELECT setval('seq_objects', (SELECT max(objid) FROM obj_head), true)" ) cur.execute( "SELECT setval('seq_process', (SELECT max(processid) FROM process), true)" ) cur.execute( "SELECT setval('seq_projects', (SELECT max(projid) FROM projects), true)" ) cur.execute( "SELECT setval('seq_projectspriv', (SELECT max(id) FROM projectspriv), true)" ) cur.execute( "SELECT setval('seq_samples', (SELECT max(sampleid) FROM samples), true)" ) cur.execute( "SELECT setval('seq_taxonomy', (SELECT max(id) FROM taxonomy), true)" ) cur.execute( "SELECT setval('seq_temp_tasks', (SELECT max(id) FROM temp_tasks), true)" ) cur.execute( "SELECT setval('seq_users', (SELECT max(id) FROM users), true)") cur.execute( "SELECT setval('roles_id_seq', (SELECT max(id) FROM roles), true)") cur.execute( "SELECT setval('part_projects_pprojid_seq', (SELECT max(pprojid) FROM part_projects), true)" ) cur.execute( "SELECT setval('part_samples_psampleid_seq', (SELECT max(psampleid) FROM part_samples), true)" ) print("Sequence Reset Done")
def FullDBRestore(UseExistingDatabase=False): """ Will restore an exported DB as is and replace all existing data """ from appli.tasks.taskimportdb import RestoreDBFull if UseExistingDatabase: print( "You have specified the UseExistingDatabase option, the the database itself will be kept, but all his content will be removed" ) if input( "This operation will import an exported DB and DESTROY all existings data of the existing database.\nAre you SURE ? Confirm by Y !" ).lower() != "y": print("Import Aborted !!!") exit() with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None RestoreDBFull(UseExistingDatabase)
def RecomputePart(ProjectID, What, User, Email): if 'C' in What: if User is None or Email is None: print("-u and -e options are required for CTD import") quit(-1) with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None import appli.part.database as partdatabase import appli.part.prj as prj import appli.part.common_sample_import as common_import Prj = partdatabase.part_projects.query.filter_by( pprojid=ProjectID).first() Samples = database.GetAll( "select psampleid,profileid from part_samples where pprojid=(%s)", [ProjectID]) for S in Samples: print("Processing particle sample %s:%s" % (S['psampleid'], S['profileid'])) if 'D' in What: print("Det=", prj.ComputeHistoDet(S['psampleid'], Prj.instrumtype)) if 'R' in What: print("Red=", prj.ComputeHistoRed(S['psampleid'], Prj.instrumtype)) if 'M' in What: print("Match=", prj.ComputeZooMatch(S['psampleid'], Prj.projid)) if 'C' in What: print( "CTD=", "Imported" if common_import.ImportCTD( S['psampleid'], User, Email) else 'CTD No file') Samples = database.GetAll( "select psampleid,profileid,sampleid from part_samples where pprojid=(%s)", [ProjectID]) for S in Samples: if 'T' in What and S['sampleid']: print( "Zoo for particle sample %s:%s=" % (S['psampleid'], S['profileid']), prj.ComputeZooHisto(S['psampleid']))
def dbcreate(): with app.app_context(): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None db.create_all() import flask_migrate flask_migrate.stamp(revision='head')
def RecomputeDisplayName(): print ("RecomputeDisplayName") with app.app_context(): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None import appli.services appli.services.ComputeDisplayName([])
and coalesce(taxonomy.nbrobjcum,0)<>q.nbr""") print("RefreshTaxoStat updated %d level %d taxo" % (n, i)) if n == 0: break appli.part.prj.GlobalTaxoCompute() if __name__ == "__main__": # RefreshTaxoStat() from appli import app from flask import g import traceback, appli.tasks, logging app.logger.setLevel(logging.DEBUG) for h in app.logger.handlers: h.setLevel(logging.DEBUG) app.logger.info("Start Daily Task") try: with app.app_context( ): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent g.db = None RefreshAllProjectsStat() RefreshTaxoStat() app.logger.info(appli.tasks.taskmanager.AutoClean()) except Exception as e: s = str(e) tb_list = traceback.format_tb(e.__traceback__) for i in tb_list[::-1]: s += "\n" + i app.logger.error("Exception on Daily Task : %s" % s) app.logger.info("End Daily Task")