Python sqlite3.Binary() Examples

def create_ua_object(self, http_object, browser, ua):
        c = self.connection.cursor()
        obj = UAObject(browser, ua)
        obj.copy_data(http_object)
        c.execute("SELECT MAX(id) FROM ua")
        rowid = c.fetchone()[0]
        if rowid is None:
            rowid = 0
        obj.id = rowid + 1
        pobj = sqlite3.Binary(pickle.dumps(obj, protocol=2))
        c.execute(("INSERT INTO ua (parent_id, object, complete, key)"
                   " VALUES (?,?,?,?)"),
                  (http_object.id, pobj, False, browser))
        self.connection.commit()
        c.close()
        return obj 

def _format_event(self, event):
        """
        Convert an event object triggered by the subscription into ordered lists for the SQL insert string

        Args:
            event: The event returned by the subscription

        Returns: List of event fields (SQL column names), List of '?' placeholders, Tuple of variant binaries

        """
        placeholders = []
        ev_variant_binaries = []

        ev_variant_dict = event.get_event_props_as_fields_dict()
        names = list(ev_variant_dict.keys())
        names.sort()  # sort alphabetically since dict is not sorted

        # split dict into two synchronized lists which will be converted to SQL strings
        # note that the variants are converted to binary objects for storing in SQL BLOB format
        for name in names:
            variant = ev_variant_dict[name]
            placeholders.append('?')
            ev_variant_binaries.append(sqlite3.Binary(variant.to_binary()))

        return self._list_to_sql_str(names), self._list_to_sql_str(placeholders, False), tuple(ev_variant_binaries) 

def store_full_hash(self, threat_list, hash_value, cache_duration, malware_threat_type):
        """Store full hash found for the given hash prefix"""
        log.info('Storing full hash %s to list %s with cache duration %s',
                 to_hex(hash_value), str(threat_list), cache_duration)
        qi = '''INSERT OR IGNORE INTO full_hash
                    (value, threat_type, platform_type, threat_entry_type, malware_threat_type, downloaded_at)
                VALUES
                    (?, ?, ?, ?, ?, current_timestamp)
        '''
        qu = "UPDATE full_hash SET expires_at=datetime(current_timestamp, '+{} SECONDS') \
            WHERE value=? AND threat_type=? AND platform_type=? AND threat_entry_type=?"

        i_parameters = [sqlite3.Binary(hash_value), threat_list.threat_type,
                        threat_list.platform_type, threat_list.threat_entry_type, malware_threat_type]
        u_parameters = [sqlite3.Binary(hash_value), threat_list.threat_type,
                        threat_list.platform_type, threat_list.threat_entry_type]

        with self.get_cursor() as dbc:
            dbc.execute(qi, i_parameters)
            dbc.execute(qu.format(int(cache_duration)), u_parameters) 

def insert_contract(name: str, abi, bytecode: str, gas_estimates, method_identifiers, cwd):
    '''insert_contract into the localdb, also converts the type
    '''
    assert name
    assert abi
    assert bytecode
    assert gas_estimates
    assert method_identifiers
    gas = pickle.dumps(gas_estimates)
    methods = pickle.dumps(method_identifiers)
    result = cursor.execute(insert_contract_sql, (name,
                                                str(abi),
                                                bytecode,
                                                sqlite3.Binary(gas),
                                                sqlite3.Binary(methods)))
    connection.commit()
    return result 

def adapt_obj(obj):
    """Binarize objects to be stored in an SQLite database.

    Parameters
    ----------
    obj : object
        Any picklable object.

    Returns
    -------
    blob : memoryview
        A buffer (Python 2) or memoryview (Python 3) of the pickled object
        that can be stored as a BLOB in an SQLite database.

    """
    blob = sqlite3.Binary(cPickle.dumps(obj))
    if len(blob) > config.max_blob_size:
        warnings.warn('large objects stored in SQLite' +
                      LARGE_BLOB_WARNING.format(type(obj), len(blob)))
        # Prevent the warning with variable message from repeating
        warnings.filterwarnings('ignore', 'large objects .*')
    return blob 

def save(self, session):
        """Saves a requests.Session object for the next heartbeat process.
        """

        if not HAS_SQL:  # pragma: nocover
            return
        try:
            conn, c = self.connect()
            c.execute('DELETE FROM {0}'.format(self.table_name))
            values = {
                'value': sqlite3.Binary(pickle.dumps(session, protocol=2)),
            }
            c.execute('INSERT INTO {0} VALUES (:value)'.format(self.table_name), values)
            conn.commit()
            conn.close()
        except:  # pragma: nocover
            log.traceback(logging.DEBUG) 

def cache_call_refresh(self, method, *options):
		"""
		Call a remote method and update the local cache with the result
		if it already existed.

		:param str method: The name of the remote procedure to execute.
		:return: The return value from the remote function.
		"""
		options_hash = self.encode(options)
		if len(options_hash) > 20:
			options_hash = hashlib.new('sha1', options).digest()
		options_hash = sqlite3.Binary(options_hash)

		with self.cache_lock:
			cursor = self.cache_db.cursor()
			cursor.execute('DELETE FROM cache WHERE method = ? AND options_hash = ?', (method, options_hash))
		return_value = self.call(method, *options)
		store_return_value = sqlite3.Binary(self.encode(return_value))
		with self.cache_lock:
			cursor = self.cache_db.cursor()
			cursor.execute('INSERT INTO cache (method, options_hash, return_value) VALUES (?, ?, ?)', (method, options_hash, store_return_value))
			self.cache_db.commit()
		return return_value 

def _add_signature(
        self, sampleID, calibrationID, instrument, environment, measurement,
            xUnit, yUnit, minWavelength, maxWavelength, xData, yData):
        import sqlite3
        import array
        sql = '''INSERT INTO Spectra (SampleID, SensorCalibrationID, Instrument,
                 Environment, Measurement, XUnit, YUnit, MinWavelength, MaxWavelength,
                 NumValues, XData, YData) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'''
        xBlob = sqlite3.Binary(tobytes(array.array(arraytypecode, xData)))
        yBlob = sqlite3.Binary(tobytes(array.array(arraytypecode, yData)))
        numValues = len(xData)
        self.cursor.execute(
            sql, (
                sampleID, calibrationID, instrument, environment, measurement,
                xUnit, yUnit, minWavelength, maxWavelength, numValues, xBlob,
                yBlob))
        rowId = self.cursor.lastrowid
        self.db.commit()
        return rowId 

def _add_record(self, sha1, subj, tset, data):
        if not self.is_valid():
            print "  Invalid TrustStore.sqlite3"
            return
        conn = sqlite3.connect(self._path)
        c = conn.cursor()
        c.execute('SELECT COUNT(*) FROM tsettings WHERE subj=?', [sqlite3.Binary(subj)])
        row = c.fetchone()
        if row[0] == 0:
            c.execute('INSERT INTO tsettings (sha1, subj, tset, data) VALUES (?, ?, ?, ?)', [sqlite3.Binary(sha1), sqlite3.Binary(subj), sqlite3.Binary(tset), sqlite3.Binary(data)])
            print '  Certificate added'
        else:
            c.execute('UPDATE tsettings SET sha1=?, tset=?, data=? WHERE subj=?', [sqlite3.Binary(sha1), sqlite3.Binary(tset), sqlite3.Binary(data), sqlite3.Binary(subj)])
            print '  Existing certificate replaced'
        conn.commit()
        conn.close() 

def database_thumbnail_update(self, fullpath, database, modified_date, orientation, temporary=False, force=False):
        """Check if a thumbnail is already in database, check if out of date, update if needed.
        Arguments:
            fullpath: String, the database-relative path to the photo.
            database: String, database directory the photo is in.
            modified_date: Integer, the modified date of the original photo.
            orientation: Integer, EXIF orientation code.
            temporary: Boolean, if True, uses the temporary thumbnails database.
            force: Boolean, if True, will always update thumbnail, regardless of modified date.
        Returns: Boolean, True if thumbnail updated, False if not.
        """

        #check if thumbnail is already in database, check if out of date, update if needed
        matches = self.database_thumbnail_get(fullpath, temporary=temporary)
        if matches:
            if modified_date <= matches[1] and not force:
                return False
        thumbnail = self.generate_thumbnail(local_path(fullpath), local_path(database))
        thumbnail = sqlite3.Binary(thumbnail)
        self.database_thumbnail_write(fullpath=fullpath, modified_date=modified_date, thumbnail=thumbnail, orientation=orientation, temporary=temporary)
        return True 

def create_http_object(self, remote_system, cli_parsed):
        c = self.connection.cursor()
        obj = HTTPTableObject()
        obj.remote_system = remote_system
        obj.set_paths(
            cli_parsed.d, None)
        obj.max_difference = cli_parsed.difference
        c.execute("SELECT MAX(id) FROM http")
        rowid = c.fetchone()[0]
        if rowid is None:
            rowid = 0
        obj.id = rowid + 1
        pobj = sqlite3.Binary(pickle.dumps(obj, protocol=2))
        c.execute(("INSERT INTO http (object, complete)"
                   "VALUES (?,?)"),
                  (pobj, False))
        self.connection.commit()
        c.close()
        return obj 

def loopix_mixes():
    sec_params = SphinxParams(header_len=1024)

    dbManager.create_mixnodes_table('Mixnodes')
    mixes = []
    pubs_mixes = []
    for i in range(3):
        mix = LoopixMixNode(sec_params, 'Mix%d'%(i+1), 9999-i, '1.2.3.%d'%i, i)
        mix.transport = proto_helpers.FakeDatagramTransport()
        mix.config_params = mix.config_params._replace(DATABASE_NAME = 'test.db')
        mixes.append(mix)
        dbManager.insert_row_into_table('Mixnodes',
                [None, mix.name, mix.port, mix.host,
                sqlite3.Binary(petlib.pack.encode(mix.pubk)), mix.group])
    pubs_mixes = [Mix(m.name, m.port, m.host, m.pubk, m.group) for m in mixes]
    return mixes, pubs_mixes 

def loopix_providers():
    sec_params = SphinxParams(header_len=1024)

    dbManager.create_providers_table('Providers')
    providers = []
    pubs_providers = []
    for i in range(3):
        p = LoopixProvider(sec_params, 'Provider%d'%(i+1), 9995-i, '1.2.%d.4'%i)
        p.transport = proto_helpers.FakeDatagramTransport()
        p.config_params = p.config_params._replace(DATABASE_NAME = 'test.db')
        providers.append(p)
        dbManager.insert_row_into_table('Providers',
            [None, p.name, p.port, p.host,
            sqlite3.Binary(petlib.pack.encode(p.pubk))])
    pubs_providers = [Provider(p.name, p.port, p.host, p.pubk) for p in providers]
    return providers, pubs_providers 

def loopix_clients(pubs_providers, pubs_mixes):

    sec_params = SphinxParams(header_len=1024)

    dbManager.create_users_table('Users')
    clients = []
    pubs_clients = []
    for i in range(3):
        provider = pubs_providers[i]
        c = LoopixClient(sec_params, 'Client%d'%(i+1), 9993 - i, '1.%d.3.4'%i, provider.name)
        c.register_mixes(pubs_mixes)
        c.transport = proto_helpers.FakeDatagramTransport()
        c.config_params = c.config_params._replace(DATABASE_NAME = 'test.db')
        c.provider = dbManager.select_provider_by_name(provider.name)
        clients.append(c)
        dbManager.insert_row_into_table('Users',
            [None, c.name, c.port, c.host,
            sqlite3.Binary(petlib.pack.encode(c.pubk)),
            c.provider.name])
    pubs_clients = [User(c.name, c.port, c.host, c.pubk, c.provider) for c in clients]
    return clients, pubs_clients 

def set(self, audit, key, data, protocol="http", timestamp=None, lifespan=None):
        protocol = self._sanitize_protocol(protocol)
        data = self.encode(data)
        data = sqlite3.Binary(data)
        if lifespan is None:
            lifespan = 0
        if timestamp is None:
            self.__cursor.execute("""
                INSERT INTO cache (audit, key, protocol, data, lifespan)
                VALUES            (  ?,    ?,     ?,       ?,     ?    );
            """,                  (audit, key, protocol, data, lifespan))
        else:
            self.__cursor.execute("""
                INSERT INTO cache (audit, key, protocol, data, timestamp, lifespan)
                VALUES            (  ?,    ?,     ?,       ?,      ?,        ?    );
            """,                  (audit, key, protocol, data, timestamp, lifespan))


    #-------------------------------------------------------------------------- 

def test_combine_worker_dbs():
    session_folder = make_session_folder()
    # make a random number of tempdbs with dummy data
    img = new("RGB", (256, 256), "red")
    data = img_to_buf(img, 'jpeg').read()
    z = randint(2, 5)
    for x in xrange(z):
        TempDB(session_folder).insert_image_blob(x, 0, 0, Binary(data))
    # confirm that combine_worker_dbs assimilates all tempdb's into gpkg
    chdir(session_folder) # necessary to put gpkg in session_folder
    gpkg = Geopackage("test.gpkg", 4326)
    combine_worker_dbs(gpkg)
    result = gpkg.execute("select count(*) from tiles;")
    assert (result.fetchone())[0] == z


# todo: test main 

def SavePoisonersToDb(result):

	for k in [ 'Poisoner', 'SentToIp', 'ForName', 'AnalyzeMode' ]:
		if not k in result:
			result[k] = ''

	cursor = sqlite3.connect(settings.Config.DatabaseFile)
	cursor.text_factory = sqlite3.Binary  # We add a text factory to support different charsets
	res = cursor.execute("SELECT COUNT(*) AS count FROM Poisoned WHERE Poisoner=? AND SentToIp=? AND ForName=? AND AnalyzeMode=?", (result['Poisoner'], result['SentToIp'], result['ForName'], result['AnalyzeMode']))
	(count,) = res.fetchone()
        
	if not count:
		cursor.execute("INSERT INTO Poisoned VALUES(datetime('now'), ?, ?, ?, ?)", (result['Poisoner'], result['SentToIp'], result['ForName'], result['AnalyzeMode']))
		cursor.commit()

	cursor.close() 

def copy_style(self, symbol_key: str, copy_count: int, out_file: str):
        copyfile(self.style, out_file)
        conn = self._create_connection(out_file)
        query_sql = f"SELECT * FROM ITEMS WHERE KEY = '{symbol_key}'"
        cur = conn.cursor()
        cur.execute(query_sql)
        exiting_symbol = cur.fetchone()
        if not exiting_symbol:
            return
        sym_cim = json.loads(exiting_symbol[5].strip('\x00'), parse_float=str)
        insert_rows = []
        for i in range(copy_count):
            new_sym = copy.deepcopy(sym_cim)
            new_sym['symbolLayers'][0]['primitiveName'] = f"{new_sym['symbolLayers'][0]['primitiveName']}_{i}"
            insert_rows.append(dict(
                CLASS=exiting_symbol[1],
                CATEGORY=exiting_symbol[2],
                NAME=exiting_symbol[3],
                TAGS=exiting_symbol[4],
                CONTENT=sqlite3.Binary(json.dumps(new_sym).encode()),
                KEY=f"{exiting_symbol[6]}_{i}"))
        insert_rows = [tuple(row.values()) for row in insert_rows]
        cur.executemany(self.INSERT_SQL, insert_rows)
        cur.close()
        conn.commit()
        conn.close() 

def save_options(self, cli_parsed):
        opts = sqlite3.Binary(pickle.dumps(cli_parsed, protocol=2))
        c = self.connection.cursor()
        c.execute("INSERT INTO opts (object) VALUES (?)",
                  (opts,))
        self.connection.commit()
        c.close() 

def update_vnc_rdp_object(self, obj):
        c = self.connection.cursor()
        o = sqlite3.Binary(pickle.dumps(obj, protocol=2))
        c.execute(("UPDATE rdpvnc SET complete=?, object=? WHERE id=?"),
                  (True, o, obj.id))
        self.connection.commit()
        c.close() 

def worker_map(temp_db, tile_dict, extra_args, invert_y):
    """
    Function responsible for sending the correct oriented tile data to a
    temporary sqlite3 database.

    Inputs:
    temp_db -- a temporary sqlite3 database that will hold this worker's tiles
    tile_dict -- a dictionary with TMS coordinates and file path for a tile
    tile_info -- a list of ZoomMetadata objects pre-generated for this tile set
    imagery -- the type of image format to send to the sqlite3 database
    invert_y -- a function that will flip the Y axis of the tile if present
    """
    tile_info = extra_args['tile_info']
    imagery = extra_args['imagery']
    jpeg_quality = extra_args['jpeg_quality']
    zoom = tile_dict['z']
    level = next((item for item in tile_info if item.zoom == zoom), None)
    x_row = tile_dict['x'] - level.min_tile_row
    if invert_y is not None:
        y_offset = invert_y(tile_dict['z'], level.max_tile_col)
        y_column = invert_y(tile_dict['z'], tile_dict['y'])
        y_column -= y_offset
    else:
        y_column = tile_dict['y'] - level.min_tile_col
    if IOPEN is not None:
        img = IOPEN(tile_dict['path'], 'r')
        data = ioBuffer()
        if imagery == 'mixed':
            if img_has_transparency(img):
                data = img_to_buf(img, 'png', jpeg_quality).read()
            else:
                data = img_to_buf(img, 'jpeg', jpeg_quality).read()
        else:
            data = img_to_buf(img, imagery, jpeg_quality).read()
        temp_db.insert_image_blob(zoom, x_row, y_column, sbinary(data))
    else:
        file_handle = open(tile_dict['path'], 'rb')
        data = buffer(file_handle.read())
        temp_db.insert_image_blob(zoom, x_row, y_column, data)
        file_handle.close() 

def update_http_object(self, http_object):
        c = self.connection.cursor()
        o = sqlite3.Binary(pickle.dumps(http_object, protocol=2))
        c.execute(("UPDATE http SET object=?,complete=? WHERE id=?"),
                  (o, True, http_object.id))
        self.connection.commit()
        c.close() 

def _set(self, item_id, item):
        def _encode(obj):
            return sqlite3.Binary(pickle.dumps(obj, protocol=pickle.HIGHEST_PROTOCOL))

        self._open()
        now = datetime.datetime.now()
        if not now.microsecond:  # now is to the second
            now += datetime.timedelta(microseconds=1)  # add 1 microsecond, required for dbapi2
        query = 'REPLACE INTO %s (key,time,value) VALUES(?,?,?)' % self._table_name
        self._execute(True, query, values=[item_id, now, _encode(item)])
        self._optimize_item_count()
        pass 

def CheckBinary(self):
        with test_support.check_py3k_warnings():
            b = sqlite.Binary(chr(0) + "'") 

def encode(obj):
    """Serialize an object using pickle to a binary format accepted by SQLite."""
    return sqlite3.Binary(dumps(obj, protocol=PICKLE_PROTOCOL)) 

def pack_array(arr):
    # chew up a numpy array, masked array, or list for insertion into a sqlite column of type blob
    out = io.BytesIO()

    if type(arr) is np.ndarray or type(arr) is np.ma.core.MaskedArray:
        arr.dump(out)
    elif type(arr) is list:
        pickle.dump(arr, out)
    out.seek(0)
    return sqlite3.Binary(out.read()) 

def encode(self, data):

        # Encode the data.
        data = super(AuditSQLiteDB, self).encode(data)

        # Tell SQLite the encoded data is a BLOB and not a TEXT.
        return sqlite3.Binary(data)


    #-------------------------------------------------------------------------- 

def save_message(self, cursor, message):
        cursor.execute("INSERT INTO message (data) VALUES (?)",
                       (sqlite3.Binary(bpickle.dumps(message)),)) 

def add_task(self, cursor, queue, data):
        data = bpickle.dumps(data)
        cursor.execute(
            "INSERT INTO task (queue, timestamp, data) VALUES (?,?,?)",
            (queue, time.time(), sqlite3.Binary(data)))
        return PackageTask(self._db, cursor.lastrowid) 

def add_hash_id_request(self, cursor, hashes):
        hashes = list(hashes)
        cursor.execute("INSERT INTO hash_id_request (hashes, timestamp)"
                       " VALUES (?,?)",
                       (sqlite3.Binary(bpickle.dumps(hashes)), time.time()))
        return HashIDRequest(self._db, cursor.lastrowid)