1

I am trying to store an html as a blob in a sqlite3 DB. However, I get the following error "could not convert BLOB to Buffer". I could store the html as a TEXT but I am running into unicode errors.

So my current approach is like this. 

def update(self, table_name, column, value, searchColumn, searchValue, BLOB=False):
    ''' Update a single column with a value
        column: column to update
        value: Value to be updated
        searchColumn: Find record with this column
        searchValue: Value of search column
    '''
    if (BLOB == False):
        sql_query = "update %s set %s = ? where %s = '%s';" % (table_name, column, value, searchColumn, searchValue)
        self.conn.execute(sql_query)
    else:
        sql_query = "update %s set %s = ? where %s = '%s';" % (table_name, column, searchColumn, searchValue)
        print sql_query
        self.conn.execute(sql_query, (sqlite3.Binary(value),))

    self.conn.commit()

And the code to put the HTML is 

        self.urlDB.update("URL", "content", content, "address", this_url, BLOB=True)

content - Unicode version of HTML. From this I get the above error. Could someone tell me what is currently wrong with this code ? Or if I can save it as a TEXT how would I go about using the above interface to do it to save as text. The HTML is currently read like this. 

def fetch(self):
    request, handle = self._open()
    self._addHeaders(request)
    if handle:
        try:
            data=handle.open(request)
            mime_type=data.info().gettype()
            url=data.geturl();
            if mime_type != "text/html":
                raise OpaqueDataException("Not interested in files of type %s" % mime_type,
                                          mime_type, url)
            self.content = unicode(data.read(), "utf-8",
                                   errors="replace")

I have seen the other responses for this problem but they didn't seem to help in this case. Thanks

ssarangi
  • 602
  • 1
  • 10
  • 28
  • Never build strings to execute a query. Use `?` in the missing fields and pass extra arguments to `conn.execute` – JBernardo Sep 18 '11 at 17:08
  • Thanks Bernado. I definitely prefer your way of doing it but I was not sure how to pass the arguments to a function and then use that with conn.execute. Could you give me a small example of how you would pass arguments to the "update" function and set it up to pass to conn.execute ? – ssarangi Sep 18 '11 at 18:08
  • something like (using your var): `self.conn.execute('select * from ?', 'the_table_name')` – JBernardo Sep 18 '11 at 22:05
  • It should be `self.conn.execute(statement, tuple_or_list_of_values)`. e.g. `self.conn.execute("INSERT INTO %s VALUES (?, ?, ?, ?, ?);" % "Tracks", [track.id, track.title, track.artist, track.year, track.album])`, or whatever. – Yamaho Nov 04 '15 at 20:01
  • Note that using `?` is not a mere convenience. It's a very important security measure, without which your application would be vulnerable to SQL injection attacks. The only parts that cannot be filled with `?`s are identifiers, such as table and field names. For that reason, if you ever are going to receive identifiers from an external source and use them to build the statement, you should take care to validate them first. At the very least verify that they are identifiers in the first place, such as by using `re.match("[a-zA-Z][a-zA-Z0-9_]*", identifier)` (super lazy method). – Yamaho Nov 04 '15 at 20:11

0 Answers0