Exemplo n.º 1
0
    def test_load_from_file_into_memory(self):
        source_db = sqlite3.connect(self.db_filename)
        self.open_connections.append(source_db)
        s_curr = source_db.cursor()
        s_curr.execute('CREATE TABLE baz(foo VARCHAR(20))')
        for value in ["abc", "x-x-x", "123"]:
            s_curr.execute('INSERT INTO baz VALUES (?)', (value, ))
        source_db.commit()
        s_curr.execute('SELECT * FROM baz ORDER BY foo DESC')
        source_data = s_curr.fetchall()
        s_curr.close()
        source_db.rollback()

        # save database to file
        db_dest = sqlite3.connect(':memory:')
        sqlitebck.copy(source_db, db_dest)
        # remove file database
        os.unlink(self.db_filename)
        # not try to fetch this data from file database
        self.open_connections.append(db_dest)
        d_curr = db_dest.cursor()
        d_curr.execute('SELECT * FROM baz ORDER BY foo DESC')
        dest_data = d_curr.fetchall()
        d_curr.close()
        self.assertEqual(source_data, dest_data)
Exemplo n.º 2
0
    def test_load_from_file_into_memory(self):
        source_db = sqlite3.connect(self.db_filename)
        self.open_connections.append(source_db)
        s_curr = source_db.cursor()
        s_curr.execute('CREATE TABLE baz(foo VARCHAR(20))')
        for value in ["abc", "x-x-x", "123"]:
            s_curr.execute('INSERT INTO baz VALUES (?)', (value, ))
        source_db.commit()
        s_curr.execute('SELECT * FROM baz ORDER BY foo DESC')
        source_data = s_curr.fetchall()
        s_curr.close()
        source_db.rollback()

        # save database to file
        db_dest = sqlite3.connect(':memory:')
        sqlitebck.copy(source_db, db_dest)
        # remove file database
        os.unlink(self.db_filename)
        # not try to fetch this data from file database
        self.open_connections.append(db_dest)
        d_curr = db_dest.cursor()
        d_curr.execute('SELECT * FROM baz ORDER BY foo DESC')
        dest_data = d_curr.fetchall()
        d_curr.close()
        self.assertEqual(source_data, dest_data)
Exemplo n.º 3
0
 def __exit__(self, exc_class, exc, traceback):
     self.conn.commit()
     if self.copy_on_exit:
         target = sqlite3.connect(self.copy_on_exit)
         sqlitebck.copy(self.conn, target)
         target.close()
     self.conn.close()
Exemplo n.º 4
0
 def __exit__(self, exc_class, exc, traceback):
     self.conn.commit()
     if self.copy_on_exit:
         target = sqlite3.connect(self.copy_on_exit)
         sqlitebck.copy(self.conn, target)
         target.close()
     self.conn.close()
Exemplo n.º 5
0
 def load_db(self,dbfile=os.path.join(DATA_DIR,'asx.db')):
     print("Loading db from from %s" % dbfile)
     bdb = sqlite3.connect(dbfile)
     sqlitebck.copy(bdb,self._conn)
     bdb.close()
     print("Loaded db from from %s" % dbfile)
     return True
Exemplo n.º 6
0
def main():

	import sqlite3, sqlitebck
	conn1 = sqlite3.connect('data.sqlite')
	conn2 = sqlite3.connect('data_backup/data_backup.sqlite')
	sqlitebck.copy(conn1, conn2)
	conn1.close()
	conn2.close()
Exemplo n.º 7
0
	def move_to_RAM(self):
		if not hasattr(self,'old_conn') and self.db_type == 'sqlite3':
			self.connection.commit()
			self.old_conn = self.connection
			self.old_cur = self.cursor
			self.connection = self.sql.connect(':memory:')#'file:' + self.uuid + '?mode=memory&cache=shared',uri=True)
			self.cursor = self.connection.cursor()
			sqlitebck.copy(self.old_conn,self.connection)
Exemplo n.º 8
0
def dump_db(filename):
    conn = create_connection(db_file)
    backup_conn = create_connection(filename)
    try:
        conn.backup(backup_conn)
    except AttributeError:
        # Python < 3.7
        import sqlitebck
        sqlitebck.copy(conn, backup_conn)
Exemplo n.º 9
0
 def move_to_RAM(self):
     if not hasattr(self, 'old_conn') and self.db_type == 'sqlite3':
         self.connection.commit()
         self.old_conn = self.connection
         self.old_cur = self.cursor
         self.connection = self.sql.connect(
             ':memory:'
         )  #'file:' + self.uuid + '?mode=memory&cache=shared',uri=True)
         self.cursor = self.connection.cursor()
         sqlitebck.copy(self.old_conn, self.connection)
Exemplo n.º 10
0
def register_backup():
    #/home/backup/... and /home/app/... is the path in docker file system ,not the path in the host
    backup_name = "/home/backup/register_" + datetime.now().strftime(
        '%Y%M%d_%H_%M_%S') + ".db"
    conn = sqlite3.connect("/home/app/dev.db")
    backcon = sqlite3.connect(backup_name)

    sqlitebck.copy(conn, backcon)

    conn.close()
    backcon.close()
    print "backup complete! backup: " + backup_name
Exemplo n.º 11
0
 def disconnectDB2(self):
     """
     Disconnect the database and copy on to the disk
     """
     #close cursor 
     self.cur.close()
     #Copy in memory databse to file
     if(self.fastmode):
         sqlitebck.copy(self.conn,self.conn2)
         self.conn2.close() 
     #Close connection
     self.conn.close()
Exemplo n.º 12
0
	def backup(self, db_file):
		#################################################################################
		# TODO:
		#	1. Check for trailing '/'
		#################################################################################

		logging.info('Backing up DB file [{0}] to [{1}]'.format(db_file, self._backup_dir))
		try:
			db_con_copy = sqlite3.connect(self._backup_dir + os.path.split(db_file)[1])
			sqlitebck.copy(self._db_con, db_con_copy)
			logging.info('... OK')
		except Exception as ex:
			logging.exception(ex)
Exemplo n.º 13
0
 def connectDB(self):
     """
     Connect to the database
     """
     if(self.db_file): self.conn2=sqlite3.connect(self.db_file,check_same_thread = False)
     else: self.conn2=sqlite3.connect(self.db_path,check_same_thread = False)
     #connect
     with self.conn2: self.conn2.execute("PRAGMA synchronous = OFF")
     if(self.fastmode):
         self.conn=sqlite3.connect(':memory:')
         sqlitebck.copy(self.conn2,self.conn)
     else: self.conn=self.conn2
     self.cur=self.conn.cursor()
Exemplo n.º 14
0
    def copy_features_tree(self, schema='memory_to_file'):
# TODO reduce file disk size
        if not self.link_to_detdup: return False
        # 1. copy database
        backup_conn    = sqlite3.connect(self.sqlite3db_path())
        current_conn   = self.features_tree._meta.database.get_conn()
        if schema == 'memory_to_file': _from = current_conn; _to = backup_conn
        if schema == 'file_to_memory': _from = backup_conn ; _to = current_conn
        if not (_from or _to): raise Exception("schema don't match!")
        sqlitebck.copy(_from, _to)
        backup_conn.close()

        print "loaded %s by %s" % (self.sqlite3db_path(), schema)
Exemplo n.º 15
0
 def backup(filename):
     db = database.connect()
     filePath = os.path.dirname(os.path.realpath(__file__))
     config = configparser.ConfigParser()
     config.read(os.path.join(filePath, 'totale_architektur.config'))
     path = os.path.join(filePath, config.get('database', 'backupDir'))
     if not os.path.exists(path):
         os.makedirs(path)
     path = os.path.join(path, filename)
     backup = sqlite3.connect(path)
     sqlitebck.copy(db, backup)
     db.close()
     backup.close()
Exemplo n.º 16
0
def backup(dbFile, dbBackupDir):
	#################################################################################
	# TODO: 
	#################################################################################
	#	1. Check for ending '/'
	#	2. Handle possible errors/exceptions from:
	#		a. sqlite3.connect
	#		b. sqlitebck.copy
	#################################################################################

	log.info('{0} Backing up DB file [{1}] to [{2}]'.format(current_thread().name, dbFile, dbBackupDir))
	dbCopyConnection = sqlite3.connect(dbBackupDir + os.path.split(dbFile)[1])
	sqlitebck.copy(dbConnection, dbCopyConnection)
	log.info('{0} ... OK'.format(current_thread().name))
Exemplo n.º 17
0
    def test_copy_multiple_pages(self):
        db = sqlite3.connect(':memory:')
        curr = db.cursor()
        curr.execute('CREATE TABLE foo(bar TEXT)')
        args = (
            'a' * (2 * SQLITE3_PAGE_SIZE),
            'b' * SQLITE3_PAGE_SIZE,
        )
        curr.execute('INSERT INTO foo VALUES (?), (?)', args)
        db.commit()
        curr.close()

        db2 = sqlite3.connect(':memory:')
        sqlitebck.copy(db, db2, pages=1, sleep=10)
Exemplo n.º 18
0
    def test_copy_multiple_pages(self):
        db = sqlite3.connect(':memory:')
        curr = db.cursor()
        curr.execute('CREATE TABLE foo(bar TEXT)')
        args = (
            'a' * (2 * SQLITE3_PAGE_SIZE),
            'b' * SQLITE3_PAGE_SIZE,
        )
        curr.execute('INSERT INTO foo VALUES (?), (?)', args)
        db.commit()
        curr.close()

        db2 = sqlite3.connect(':memory:')
        sqlitebck.copy(db, db2, pages=1, sleep=10)
Exemplo n.º 19
0
 def setup_databases(self, **kwargs):
     if sqlitebck is not None and 'quickstart' in connections:
         quickstart = connections['quickstart']
         connection = connections['default']
         quickstart.ensure_connection()
         connection.ensure_connection()
         sqlitebck.copy(quickstart.connection, connection.connection)
         serialize = connection.settings_dict.get(
             "TEST", {}).get("SERIALIZE", True)
         if serialize:
             serialized = connection.creation.serialize_db_to_string()
             connection._test_serialized_contents = serialized
         return 'quickstart'
     else:
         return super(QuickstartMixin, self).setup_databases(**kwargs)
Exemplo n.º 20
0
 def setup_databases(self, **kwargs):
     if sqlitebck is not None and 'quickstart' in connections:
         quickstart = connections['quickstart']
         connection = connections['default']
         quickstart.ensure_connection()
         connection.ensure_connection()
         sqlitebck.copy(quickstart.connection, connection.connection)
         serialize = connection.settings_dict.get("TEST", {}).get(
             "SERIALIZE", True)
         if serialize:
             serialized = connection.creation.serialize_db_to_string()
             connection._test_serialized_contents = serialized
         return 'quickstart'
     else:
         return super(QuickstartMixin, self).setup_databases(**kwargs)
Exemplo n.º 21
0
def _sqlite_copy(file_, restore=False):
    import sqlite3 as sqlite

    with Transaction().start(DB_NAME, 0) as transaction, \
            sqlite.connect(file_) as conn2:
        conn1 = transaction.connection
        if restore:
            conn2, conn1 = conn1, conn2
        if hasattr(conn1, 'backup'):
            conn1.backup(conn2)
        else:
            try:
                import sqlitebck
            except ImportError:
                return False
            sqlitebck.copy(conn1, conn2)
    return True
Exemplo n.º 22
0
def _sqlite_copy(file_, restore=False):
    try:
        import sqlitebck
    except ImportError:
        return False
    import sqlite3 as sqlite

    with Transaction().start(DB_NAME, 0, _nocache=True) as transaction, \
            sqlite.connect(file_) as conn2:
        conn1 = transaction.connection
        # sqlitebck does not work with pysqlite2
        if not isinstance(conn1, sqlite.Connection):
            return False
        if restore:
            conn2, conn1 = conn1, conn2
        sqlitebck.copy(conn1, conn2)
    return True
Exemplo n.º 23
0
    def copy_features_tree(self, schema='memory_to_file'):
        # TODO reduce file disk size
        if not self.link_to_detdup: return False
        # 1. copy database
        backup_conn = sqlite3.connect(self.sqlite3db_path())
        current_conn = self.features_tree._meta.database.get_conn()
        if schema == 'memory_to_file':
            _from = current_conn
            _to = backup_conn
        if schema == 'file_to_memory':
            _from = backup_conn
            _to = current_conn
        if not (_from or _to): raise Exception("schema don't match!")
        sqlitebck.copy(_from, _to)
        backup_conn.close()

        print "loaded %s by %s" % (self.sqlite3db_path(), schema)
Exemplo n.º 24
0
 def test_copy_from_memory_database(self):
     source_db = sqlite3.connect(':memory:')
     self.open_connections.append(source_db)
     s_curr = source_db.cursor()
     s_curr.execute('CREATE TABLE foo(bar VARCHAR(20))')
     for value in ["foo", "bar", "baz"]:
         s_curr.execute('INSERT INTO foo VALUES (?)', (value, ))
     source_db.commit()
     s_curr.execute('SELECT * FROM foo ORDER BY bar DESC')
     source_data = s_curr.fetchall()
     s_curr.close()
     # save database to file
     db_dest = sqlite3.connect(self.db_filename)
     sqlitebck.copy(source=source_db, dest=db_dest)
     # not try to fetch this data from file database
     self.open_connections.append(db_dest)
     d_curr = db_dest.cursor()
     d_curr.execute('SELECT * FROM foo ORDER BY bar DESC')
     dest_data = d_curr.fetchall()
     d_curr.close()
     self.assertEqual(source_data, dest_data)
Exemplo n.º 25
0
 def test_copy_from_memory_database(self):
     source_db = sqlite3.connect(':memory:')
     self.open_connections.append(source_db)
     s_curr = source_db.cursor()
     s_curr.execute('CREATE TABLE foo(bar VARCHAR(20))')
     for value in ["foo", "bar", "baz"]:
         s_curr.execute('INSERT INTO foo VALUES (?)', (value, ))
     source_db.commit()
     s_curr.execute('SELECT * FROM foo ORDER BY bar DESC')
     source_data = s_curr.fetchall()
     s_curr.close()
     # save database to file
     db_dest = sqlite3.connect(self.db_filename)
     sqlitebck.copy(source=source_db, dest=db_dest)
     # not try to fetch this data from file database
     self.open_connections.append(db_dest)
     d_curr = db_dest.cursor()
     d_curr.execute('SELECT * FROM foo ORDER BY bar DESC')
     dest_data = d_curr.fetchall()
     d_curr.close()
     self.assertEqual(source_data, dest_data)
Exemplo n.º 26
0
    def dump_to_file(self, location=None):
        """
        Saves to file the current DB to a given location. Useful for in-memory DBs

        NOTE: it doesn't update self.connection with the new location, so the old db will still be used for further
         query (intended behaviour since, if it's :memory:, is presumably faster)

        :param location: complete URL (path+filename) to save the db to
        :return:
        """
        import sqlitebck
        if location is None:
            filename = os.path.join(os.path.expanduser('~'), 'pvtracedb.sql')
        else:
            filename = location

        file_connection = sql.connect(filename)
        # disable journaling for faster operations
        cursor = file_connection.cursor()
        cursor.execute("PRAGMA synchronous = OFF")
        cursor.execute("PRAGMA journal_mode = OFF")

        sqlitebck.copy(self.connection, file_connection)
        self.logger.info("DB copy saved as "+str(filename))
Exemplo n.º 27
0
    def dump_to_file(self, location=None):
        """
        Saves to file the current DB to a given location. Useful for in-memory DBs

        NOTE: it doesn't update self.connection with the new location, so the old db will still be used for further
         query (intended behaviour since, if it's :memory:, is presumably faster)

        :param location: complete URL (path+filename) to save the db to
        :return:
        """
        import sqlitebck
        if location is None:
            filename = os.path.join(os.path.expanduser('~'), 'pvtracedb.sql')
        else:
            filename = location

        file_connection = sql.connect(filename)
        # disable journaling for faster operations
        cursor = file_connection.cursor()
        cursor.execute("PRAGMA synchronous = OFF")
        cursor.execute("PRAGMA journal_mode = OFF")

        sqlitebck.copy(self.connection, file_connection)
        self.logger.info("DB copy saved as " + str(filename))
Exemplo n.º 28
0
	def commit_from_RAM(self):
		if hasattr(self,'old_conn'):
			self.connection.commit()
			sqlitebck.copy(self.connection, self.old_conn)
Exemplo n.º 29
0
 def test_inerited_destination(self):
     self.create_test_db(self.native)
     sqlitebck.copy(self.native, self.inherited)
     curr = self.inherited.cursor()
     curr.execute('SELECT col FROM t')
     self.assertEqual(curr.fetchone(), ('test', ))
Exemplo n.º 30
0
def load_to_memory(conn):
    memorydb = sqlite3.connect(":memory:", timeout=60, check_same_thread=False)
    sqlitebck.copy(conn, memorydb)
    return memorydb
Exemplo n.º 31
0
def update_db(real_data, connector, c, first_time, BACKUP):
    #CREACION DE TABLAS PH, OD, TEMP. CADA ITEM ES UNA COLUMNA
    c.execute(
        'CREATE TABLE IF NOT EXISTS   PH(ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )
    c.execute(
        'CREATE TABLE IF NOT EXISTS   OD(ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )
    c.execute(
        'CREATE TABLE IF NOT EXISTS TEMP(ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )

    #se guardan las tablas agregados en la db si no existian
    connector.commit()

    #INSERCION DE LOS DATOS MEDIDOS
    #ph=: real_data[1];  OD=: real_data[2], Temp=: real_data[3]
    try:
        c.execute("INSERT INTO   PH VALUES (NULL,?,?)",
                  (datetime.datetime.now(), real_data[1]))
        c.execute("INSERT INTO   OD VALUES (NULL,?,?)",
                  (datetime.datetime.now(), real_data[2]))
        c.execute("INSERT INTO TEMP VALUES (NULL,?,?)",
                  (datetime.datetime.now(), real_data[3]))

    except:
        #print "no se pudo insertar dato en db"
        logging.info("no se pudo insertar dato en db")

    #se guardan los datos agregados en la db
    connector.commit()

    #Backup DB in RAM to DISK SD
    if BACKUP:

        filedb = '/home/pi/vprocess2/database/backup__' + first_time + '__.db'

        bck = sqlite3.connect(filedb)
        sqlitebck.copy(connector, bck)

        try:
            os.system(
                'sqlite3 -header -csv %s "select * from ph;"   > /home/pi/vprocess2/csv/%s'
                % (filedb, filedb[31:-3]) + 'full_ph.csv')
            os.system(
                'sqlite3 -header -csv %s "select * from od;"   > /home/pi/vprocess2/csv/%s'
                % (filedb, filedb[31:-3]) + 'full_od.csv')
            os.system(
                'sqlite3 -header -csv %s "select * from temp;" > /home/pi/vprocess2/csv/%s'
                % (filedb, filedb[31:-3]) + 'full_temp.csv')

            logging.info("\n Backup FULL REALIZADO \n")

        except:
            logging.info("\n Backup FULL NO REALIZADO, NO REALIZADO \n")

        try:
            #Se guarda el nombre de la db para ser utilizado en app.py
            f = open(DIR + "name_db.txt", "w")
            f.write(filedb + '\n')
            f.close()

        except:
            #print "no se pudo guardar el nombre de la DB para ser revisada en app.py"
            logging.info(
                "no se pudo guardar el nombre de la DB para ser revisada en app.py"
            )

        return True
Exemplo n.º 32
0
 def commit_from_RAM(self):
     if hasattr(self, 'old_conn'):
         self.connection.commit()
         sqlitebck.copy(self.connection, self.old_conn)
Exemplo n.º 33
0
 def dump_dp(self, path):
     con = sqlite3.connect(path)
     sqlitebck.copy(self.db, con)
     con.commit()
     con.close()
Exemplo n.º 34
0
def save_db():
    logging.debug("Saving memory db to disk... thread #{}".format(threading.get_ident()))
    disk_db = sqlite3.connect(TVTROPES_DB_PATH, timeout=60, check_same_thread=False)
    sqlitebck.copy(get_db_connection(), disk_db)
    disk_db.close()
    logging.debug("DB Saved!")
Exemplo n.º 35
0
 def dump_db(self,dbfile=os.path.join(DATA_DIR,'asx.db')):
     print("Dumping db to %s" % dbfile)
     bdb = sqlite3.connect(dbfile)
     sqlitebck.copy(self._conn,bdb)
     bdb.close()
     print("Dumped db to %s" % dbfile)
Exemplo n.º 36
0
 def test_inerited_destination(self):
     self.create_test_db(self.native)
     sqlitebck.copy(self.native, self.inherited)
     curr = self.inherited.cursor()
     curr.execute('SELECT col FROM t')
     self.assertEqual(curr.fetchone(), ('test', ))
def main():

    topicfilter = "w"
    #####Listen measures - estructura para zmq listen de ficha_producto ################
    port_sub1 = "5554"
    context_sub1 = zmq.Context()
    socket_sub1 = context_sub1.socket(zmq.SUB)
    socket_sub1.connect("tcp://localhost:%s" % port_sub1)
    socket_sub1.setsockopt(zmq.SUBSCRIBE, topicfilter)

    #####Listen measures - estructura para zmq listen real_data (measures from myserial.py)
    port_sub2 = "5557"
    context_sub2 = zmq.Context()
    socket_sub2 = context_sub2.socket(zmq.SUB)
    socket_sub2.connect("tcp://localhost:%s" % port_sub2)
    socket_sub2.setsockopt(zmq.SUBSCRIBE, topicfilter)
    ####################################################################################

    connector = sqlite3.connect(':memory:',
                                detect_types=sqlite3.PARSE_DECLTYPES
                                | sqlite3.PARSE_COLNAMES)
    c = connector.cursor()
    time.sleep(1)

    first_time = time.strftime("Fecha__%d-%m-%y__Hora__%H_%M_%S")
    BACKUP = True
    T_SEG = datetime.datetime.now()
    T_elapsed = datetime.datetime.now()

    while True:
        ##############   ZMQ connection for download data ficha_producto ###############
        try:
            ficha_producto = socket_sub1.recv(flags=zmq.NOBLOCK).split()[1]
            ficha_producto = ficha_producto.split(",")
            ficha_producto_save = ficha_producto
            #ficha_producto_save = ficha_producto
            #log para depuracion

            f = open(DIR + "/ficha_producto_basedatos.txt", "a+")
            f.write(str(ficha_producto) + "__up__" + "\n")
            f.close()

        except zmq.Again:
            pass

        ##############  ZMQ connection for download real_data (measures from myserial.py)
        try:
            temporal = socket_sub2.recv(flags=zmq.NOBLOCK).split()
            if temporal != "":
                real_data = temporal

                #f = open(DIR + "/real_data_basedatos.txt","a+")
                #f.write(str(real_data) + "__up__" + "\n" )
                #f.close()

        except zmq.Again:
            pass
        ########################## ZMQ connections #######################################

        # CREAR TABLAS SI NO EXISTEN!!!!
        c.execute(
            'CREATE TABLE IF NOT EXISTS PROCESO (ID INTEGER PRIMARY KEY autoincrement, FECHA TIMESTAMP NOT NULL, HORA TIMESTAMP NOT NULL, T_MOSTO REAL, T_SOMBRERO REAL, T_Promedio REAL, T_Setpoint REAL, Flujo_REMONTAJE REAL, Densidad REAL, Yan REAL, pH REAL, Brix REAL, Acidez REAL, Lote REAL, Dosis REAL, Bomba1 REAL, Bomba2 REAL, Electrovalvula_Aire REAL, CEPA REAL, FLUJO_AIRE REAL, co2 REAL)'
        )

        #CREACION DE TABLAS TEMP1(Sombrero), TEMP2(Mosto), TEMP_ (promedio). CADA ITEM ES UNA COLUMNA
        c.execute(
            'CREATE TABLE IF NOT EXISTS T_SOMBRERO (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
        )
        c.execute(
            'CREATE TABLE IF NOT EXISTS T_MOSTO    (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
        )
        c.execute(
            'CREATE TABLE IF NOT EXISTS T_PROMEDIO (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
        )

        logging.info("Se crearon las tablas!!!")

        #se guardan las tablas agregados en la db si no existian
        connector.commit()

        try:
            real_data_1 = (
                float(real_data[3]) + float(real_data[2])
            ) / 2  #temperatura promedio entre T_MOSTO y T_SOMBRERO
            real_data_2 = float(real_data[4])  #medicion de co2 [ppm]
            #comp1 = float(ficha_producto[12])#round(float(ficha_producto[14])*ficha_producto[12],2)                                                                                                                                                                            #Densidad_Opt       #TASA_Crec          #Etanol       #sustrato_i       #mezclador           #bomba1             #bomba2          #Temp_setpoint     #Temp_measure     #pH_setpoint     #pH_measure

            logging.info("DATOS FLOAT SE insertan datos en db")
            print "Datos Float obtenidos !!!!"

        except:
            print "Datos Float NO fueron obtenidos !!!!"
            logging.info("no se pudo insertar datos en db ??????????????")

        #INSERCION DE LOS DATOS MEDIDOS, TABLA FULL CON TODA LA DATA, NULL es para el ID
        try:
            c.execute(
                "INSERT INTO PROCESO  VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                (datetime.datetime.now().strftime("%Y-%m-%d"),
                 datetime.datetime.now().strftime("%H:%M:%S"),
                 round(float(real_data[3]), 2), round(float(
                     real_data[2]), 2), round(real_data_1, 2),
                 ficha_producto[9], ficha_producto[12], ficha_producto[0],
                 ficha_producto[1], ficha_producto[2], ficha_producto[3],
                 ficha_producto[4], ficha_producto[7], ficha_producto[8],
                 ficha_producto[10], ficha_producto[11], ficha_producto[13],
                 ficha_producto[6], ficha_producto[5], real_data_2))
            #Insercion solo de los datos de sensores
            print "TABLA GRANDE OK!!!! en db"

        except:
            print "no se pudo insertar datos TABLA GRANDE  en db"

        try:
            #Insercion solo de los datos de sensores
            c.execute("INSERT INTO T_MOSTO    VALUES (NULL,?,?)",
                      (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                       round(float(real_data[3]), 2)))
            c.execute("INSERT INTO T_SOMBRERO VALUES (NULL,?,?)",
                      (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                       round(float(real_data[2]), 2)))
            c.execute("INSERT INTO T_PROMEDIO VALUES (NULL,?,?)",
                      (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                       round(float(real_data[1]), 2)))

            print "Datos Insertados !!!"
            logging.info("se insertaron todos los datos en db")

            #time.sleep(0.5)

        except:
            #print "no se pudo insertar dato en db"
            print "Fallo al Insertar Dato !!!"
            logging.info("no se pudo insertar datos en db")

        #se guardan los datos agregados en la db
        connector.commit()
        '''
        if T_SEG - T_elapsed.second <= 0 :
            BACKUP = True
            T_elapsed = 0
        else:
            BACKUP = False
            T_elapsed = datetime.datetime.now()

        print BACKUP


        BACKUP = True
        if BACKUP :
            first_time = time.strftime("Fecha__%d-%m-%y__Hora__%H_%M_%S")
        else:
            first_time = "__NULO__"
        '''

        #Backup DB in RAM to DISK SD
        if BACKUP:

            filedb = '/home/pi/vprocess4c/database/backup__' + first_time + '__.db'

            bck = sqlite3.connect(filedb)
            sqlitebck.copy(connector, bck)

            try:
                os.system(
                    'sqlite3 -header -csv %s "select * from T_SOMBRERO;" > /home/pi/vprocess4c/csv/%s'
                    % (filedb, filedb[28:-3]) + 'T_SOMBRERO.csv')
                os.system(
                    'sqlite3 -header -csv %s "select * from T_MOSTO;"    > /home/pi/vprocess4c/csv/%s'
                    % (filedb, filedb[28:-3]) + 'T_MOSTO.csv')
                os.system(
                    'sqlite3 -header -csv %s "select * from T_PROMEDIO;" > /home/pi/vprocess4c/csv/%s'
                    % (filedb, filedb[28:-3]) + 'T_PROMEDIO.csv')

                os.system(
                    'sqlite3 -header -csv %s "select * from PROCESO;"    > /home/pi/vprocess4c/csv/%s'
                    % (filedb, filedb[28:-3]) + 'PROCESO.csv')

                logging.info("\n Backup CSV REALIZADO \n")

            except:
                logging.info("\n Backup FULL NO REALIZADO, NO REALIZADO \n")

            try:
                #Se guarda el nombre de la db para ser utilizado en app.py
                f = open(DIR + "/name_db.txt", "w")
                f.write(filedb + '\n')
                f.close()

            except:
                #print "no se pudo guardar el nombre de la DB para ser revisada en app.py"
                logging.info(
                    "no se pudo guardar el nombre de la DB para ser revisada en app.py"
                )
Exemplo n.º 38
0
def update_db(real_data, ficha_producto, connector, c, first_time, BACKUP):
    #CREACION DE TABLAS TEMP1(Sombrero), TEMP2(Mosto), TEMP_ (promedio). CADA ITEM ES UNA COLUMNA
    #TABLA FULL CON TODA LA DATA
    c.execute(
        'CREATE TABLE IF NOT EXISTS PROCESO (ID INTEGER PRIMARY KEY autoincrement, FECHA TIMESTAMP NOT NULL, HORA TIMESTAMP NOT NULL, Densidad_Opt REAL, TASA_Crec REAL, Etanol REAL, SUSTRATO_i REAL, MEZCLADOR REAL, BOMBA1 REAL, BOMBA2 REAL, T_SETPOINT REAL, TEMPERATURA REAL, pH_SETPOINT REAL, pH REAL )'
    )

    #CREACION DE TABLAS TEMP1(Sombrero), TEMP2(Mosto), TEMP_ (promedio). CADA ITEM ES UNA COLUMNA
    c.execute(
        'CREATE TABLE IF NOT EXISTS TEMPERATURA (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )
    c.execute(
        'CREATE TABLE IF NOT EXISTS PH (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )
    c.execute(
        'CREATE TABLE IF NOT EXISTS OD (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )

    logging.info("Se crearon las tablas!!!")

    #se guardan las tablas agregados en la db si no existian
    connector.commit()

    #INSERCION DE LOS DATOS MEDIDOS, TABLA FULL CON TODA LA DATA, NULL es para el ID
    #Densidad_Opt       #TASA_Crec          #Etanol       #sustrato_i       #mezclador           #bomba1             #bomba2          #Temp_setpoint     #Temp_measure     #pH_setpoint     #pH_measure
    try:
        c.execute(
            "INSERT INTO PROCESO  VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (datetime.datetime.now().strftime("%Y-%m-%d"),
             datetime.datetime.now().strftime("%H:%M:%S"), ficha_producto[0],
             ficha_producto[1], ficha_producto[2], ficha_producto[3],
             ficha_producto[4], ficha_producto[10], ficha_producto[11],
             ficha_producto[9], real_data[1], ficha_producto[8], real_data[3]))

        #Insercion solo de los datos de sensores
        c.execute("INSERT INTO PH          VALUES (NULL,?,?)",
                  (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                   round(float(real_data[3]), 2)))
        c.execute("INSERT INTO OD          VALUES (NULL,?,?)",
                  (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                   round(float(real_data[2]), 2)))
        c.execute("INSERT INTO TEMPERATURA VALUES (NULL,?,?)",
                  (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                   round(float(real_data[1]), 2)))

        logging.info("se insertaron todos los datos en db")

    except:
        #print "no se pudo insertar dato en db"
        logging.info("no se pudo insertar datos en db")

    #se guardan los datos agregados en la db
    connector.commit()

    #Backup DB in RAM to DISK SD
    if BACKUP:

        filedb = DIR + '/database/backup__' + first_time + '__.db'

        bck = sqlite3.connect(filedb)
        sqlitebck.copy(connector, bck)

        try:
            os.system(
                'sqlite3 -header -csv %s "select * from PROCESO;"    > /home/pi/vprocess6/csv/%s'
                % (filedb, filedb[28:-3]) + 'PROCESO.csv')
            logging.info("\n Backup CSV REALIZADO \n")

        except:
            logging.info("\n Backup FULL NO REALIZADO, NO REALIZADO \n")

        try:
            #Se guarda el nombre de la db para ser utilizado en app.py
            f = open(DIR + "/name_db.txt", "w")
            f.write(filedb + '\n')
            f.close()

        except:
            #print "no se pudo guardar el nombre de la DB para ser revisada en app.py"
            logging.info(
                "no se pudo guardar el nombre de la DB para ser revisada en app.py"
            )

        return True
Exemplo n.º 39
0
def update_db(real_data, ficha_producto, connector, c, first_time, BACKUP):
    #CREACION DE TABLAS TEMP1(Sombrero), TEMP2(Mosto), TEMP_ (promedio). CADA ITEM ES UNA COLUMNA
    # CREAR TABLAS SI NO EXISTEN!!!!
    c.execute('CREATE TABLE IF NOT EXISTS PROCESO (ID INTEGER PRIMARY KEY autoincrement, FECHA TIMESTAMP NOT NULL, HORA TIMESTAMP NOT NULL, T_MOSTO REAL, T_SOMBRERO REAL, T_Promedio REAL, T_Setpoint REAL, Flujo_REMONTAJE REAL, Densidad REAL, Yan REAL, pH REAL, Brix REAL, Acidez REAL, Concentracion_FDA REAL, Dosis_FDA REAL, Bomba1 REAL, Bomba2 REAL, Electrovalvula_Aire REAL, FLUJO_FDA REAL, FLUJO_AIRE REAL, CO2 REAL)')

    #CREACION DE TABLAS TEMP1(Sombrero), TEMP2(Mosto), TEMP_ (promedio). CADA ITEM ES UNA COLUMNA
    c.execute('CREATE TABLE IF NOT EXISTS T_SOMBRERO (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)')
    c.execute('CREATE TABLE IF NOT EXISTS T_MOSTO    (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)')
    c.execute('CREATE TABLE IF NOT EXISTS T_PROMEDIO (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)')


    logging.info("Se crearon las tablas!!!")

    #se guardan las tablas agregados en la db si no existian
    connector.commit()

    #INSERCION DE LOS DATOS MEDIDOS, TABLA FULL CON TODA LA DATA, NULL es para el ID
                                                                                                                                                                                             #Densidad_Opt       #TASA_Crec          #Etanol       #sustrato_i       #mezclador           #bomba1             #bomba2          #Temp_setpoint     #Temp_measure     #pH_setpoint     #pH_measure
    try:
        #sensor t_sombrero anti-ceros
        if float(real_data[2]) != 0:
            real_data_2_save = float(real_data[2])

        else:
            real_data[2] = real_data_2_save


        #sensor t_mosto anti-ceros
        if float(real_data[3]) != 0:
            real_data_3_save = float(real_data[3])

        else:
            real_data[3] = real_data_3_save


        #sensor c02 anti-ceros
        if float(real_data[4]) != 0:
            real_data_4_save = float(real_data[4])

        else:
            real_data[4] = real_data_4_save



        real_data_1 = (float(real_data[3]) + float(real_data[2]))/2  #temperatura promedio entre T_MOSTO y T_SOMBRERO
        real_data_2 =  float(real_data[4])                       #medicion de co2 [ppm]
        #comp1 = float(ficha_producto[12])#round(float(ficha_producto[14])*ficha_producto[12],2)                                                                                                                                                                            #Densidad_Opt       #TASA_Crec          #Etanol       #sustrato_i       #mezclador           #bomba1             #bomba2          #Temp_setpoint     #Temp_measure     #pH_setpoint     #pH_measure

        #logging.info("DATOS FLOAT SE insertan datos en db")
        #print "Datos Float obtenidos !!!!"


    except:
        pass
        #print "Datos Float NO fueron obtenidos !!!!"
        #logging.info("no se pudo insertar datos en db ??????????????")



    #INSERCION DE LOS DATOS MEDIDOS, TABLA FULL CON TODA LA DATA, NULL es para el ID
    try:
        c.execute("INSERT INTO PROCESO  VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (datetime.datetime.now().strftime("%Y-%m-%d"), datetime.datetime.now().strftime("%H:%M:%S"), round(float(real_data[3]),2), round(float(real_data[2]),2), round(real_data_1,2), ficha_producto[9], ficha_producto[12], ficha_producto[0], ficha_producto[1], ficha_producto[2], ficha_producto[3], ficha_producto[4], ficha_producto[7], ficha_producto[8], ficha_producto[10], ficha_producto[11], ficha_producto[13], ficha_producto[6], ficha_producto[5], real_data_2 ))
        #Insercion solo de los datos de sensores
        #print "TABLA GRANDE OK!!!! en db"

    except:
        #print "no se pudo insertar datos TABLA GRANDE  en db"
        pass


    try:
        #Insercion solo de los datos de sensores
        c.execute("INSERT INTO T_MOSTO    VALUES (NULL,?,?)", (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"), round(float(real_data[3]),2)))
        c.execute("INSERT INTO T_SOMBRERO VALUES (NULL,?,?)", (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"), round(float(real_data[2]),2)))
        c.execute("INSERT INTO T_PROMEDIO VALUES (NULL,?,?)", (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"), round(float(real_data[1]),2)))

        #print "Datos Insertados !!!"
        #logging.info("se insertaron todos los datos en db")

        #time.sleep(0.5)

    except:
        pass
        #print "no se pudo insertar dato en db"
        #print "Fallo al Insertar Dato !!!"
        #logging.info("no se pudo insertar datos en db")

    #se guardan los datos agregados en la db
    connector.commit()


    #Backup DB in RAM to DISK SD
    if BACKUP:

        filedb = DIR + '/database/backup__' + first_time + '__.db'

        bck = sqlite3.connect(filedb)
        sqlitebck.copy(connector, bck)

        try:
            #0s.system('sqlite3 -header -csv %s "select * from T_SOMBRERO;" > /home/pi/vprocess4c/csv/%s' % (filedb,filedb[28:-3])+'T_SOMBRERO.csv' )
            #os.system('sqlite3 -header -csv %s "select * from T_MOSTO;"    > /home/pi/vprocess4c/csv/%s' % (filedb,filedb[28:-3])+'T_MOSTO.csv'    )
            #os.system('sqlite3 -header -csv %s "select * from T_PROMEDIO;" > /home/pi/vprocess4c/csv/%s' % (filedb,filedb[28:-3])+'T_PROMEDIO.csv' )

            os.system('sqlite3 -header -csv %s "select * from PROCESO;"    > /home/pi/vprocess4c/csv/%s' % (filedb,filedb[28:-3])+'PROCESO.csv' )
            #logging.info("\n Backup CSV REALIZADO \n")

        except:
            pass
            #logging.info("\n Backup FULL NO REALIZADO, NO REALIZADO \n")


        try:
            #Se guarda el nombre de la db para ser utilizado en app.py
            f = open(DIR + "/name_db.txt","w")
            f.write(filedb + '\n')
            f.close()

        except:
            pass
            #print "no se pudo guardar el nombre de la DB para ser revisada en app.py"
            #logging.info("no se pudo guardar el nombre de la DB para ser revisada en app.py")

        return True
Exemplo n.º 40
0
def update_db(real_data, ficha_producto, connector, c, first_time, BACKUP):
    #CREACION DE TABLAS TEMP1(Sombrero), TEMP2(Mosto), TEMP_ (promedio). CADA ITEM ES UNA COLUMNA
    c.execute(
        'CREATE TABLE IF NOT EXISTS T_SOMBRERO (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )
    c.execute(
        'CREATE TABLE IF NOT EXISTS T_MOSTO    (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )
    c.execute(
        'CREATE TABLE IF NOT EXISTS T_PROMEDIO (ID INTEGER PRIMARY KEY autoincrement, FECHA_HORA TIMESTAMP NOT NULL, MAGNITUD REAL)'
    )

    #TABLA FULL CON TODA LA DATA
    c.execute(
        'CREATE TABLE IF NOT EXISTS PROCESO (ID INTEGER PRIMARY KEY autoincrement, FECHA TIMESTAMP NOT NULL, HORA TIMESTAMP NOT NULL, FUNDO TEXT NOT NULL, CEPA TEXT NOT NULL, T_MOSTO REAL, T_SOMBRERO REAL, T_Promedio REAL, T_Setpoint REAL, Flujo REAL, Densidad REAL, Yan REAL, pH REAL, Brix REAL, Acidez REAL, Lote REAL, Dosis REAL, Bomba1 REAL, Bomba2 REAL)'
    )

    logging.info("Se crearon las tablas!!!")

    #se guardan las tablas agregados en la db si no existian
    connector.commit()

    #INSERCION DE LOS DATOS MEDIDOS
    #T.SOMBRERO=: real_data[1];  T.MOSTO=: real_data[2], T.PROMEDIO=: real_data[3]
    try:
        #Insercion solo de los datos de sensores
        c.execute("INSERT INTO T_MOSTO    VALUES (NULL,?,?)",
                  (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                   real_data[3]))
        c.execute("INSERT INTO T_SOMBRERO VALUES (NULL,?,?)",
                  (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                   real_data[2]))
        c.execute("INSERT INTO T_PROMEDIO VALUES (NULL,?,?)",
                  (datetime.datetime.now().strftime("%Y-%m-%d,%H:%M:%S"),
                   real_data[1]))

        #TABLA FULL CON TODA LA DATA
        # NULL es para el ID
        c.execute(
            "INSERT INTO PROCESO  VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (datetime.datetime.now().strftime("%Y-%m-%d"),
             datetime.datetime.now().strftime("%H:%M:%S"), ficha_producto[5],
             ficha_producto[6], real_data[3], real_data[2], real_data[1],
             ficha_producto[9],
             (float(real_data[8]) * float(ficha_producto[12])),
             ficha_producto[0], ficha_producto[1], ficha_producto[2],
             ficha_producto[3], ficha_producto[4], ficha_producto[7],
             ficha_producto[8], ficha_producto[10], ficha_producto[11]))
        logging.info("se insertaron todos los datos en db")

    except:
        #print "no se pudo insertar dato en db"
        logging.info("no se pudo insertar datos en db")

    #se guardan los datos agregados en la db
    connector.commit()

    #Backup DB in RAM to DISK SD
    if BACKUP:

        filedb = '/home/pi/vprocess4/database/backup__' + first_time + '__.db'

        bck = sqlite3.connect(filedb)
        sqlitebck.copy(connector, bck)

        try:
            os.system(
                'sqlite3 -header -csv %s "select * from T_SOMBRERO;" > /home/pi/vprocess4/csv/%s'
                % (filedb, filedb[28:-3]) + 'T_SOMBRERO.csv')
            os.system(
                'sqlite3 -header -csv %s "select * from T_MOSTO;"    > /home/pi/vprocess4/csv/%s'
                % (filedb, filedb[28:-3]) + 'T_MOSTO.csv')
            os.system(
                'sqlite3 -header -csv %s "select * from T_PROMEDIO;" > /home/pi/vprocess4/csv/%s'
                % (filedb, filedb[28:-3]) + 'T_PROMEDIO.csv')

            os.system(
                'sqlite3 -header -csv %s "select * from PROCESO;"    > /home/pi/vprocess4/csv/%s'
                % (filedb, filedb[28:-3]) + 'PROCESO.csv')

            logging.info("\n Backup CSV REALIZADO \n")

        except:
            logging.info("\n Backup FULL NO REALIZADO, NO REALIZADO \n")

        try:
            #Se guarda el nombre de la db para ser utilizado en app.py
            f = open(DIR + "name_db.txt", "w")
            f.write(filedb + '\n')
            f.close()

        except:
            #print "no se pudo guardar el nombre de la DB para ser revisada en app.py"
            logging.info(
                "no se pudo guardar el nombre de la DB para ser revisada en app.py"
            )

        return True