Store Gtk.Textbuffer in SQL database. Encoding troubles

Question

I'm working on a note taking app using python2/Gtk3/Glade.

The notes are stored in a MySQL Database and displayed in a TextView widget. I can load/store/display plain text fine. However I want the ability to add images to the note page, and store them in the Database.so the data has to be serialised and I'm having some trouble figuring out how to encode/decode the serialised data going in and out of the Database. I'm getting unicode start byte errors. If was working with files I could just open the file in binary mode, but I'm storing as a string in a Database. I've tried encoding/decoding as UTF-8 and ASCII using bytes() and string.encode()[see the sample code below] and a few other ways but none work.

I am using this function to add the image to the textview buffer:

def _AddImagetoNode(self,oWidget):
    filenm = None
    seliter = self.GetTreeSelection(self.treeview)
    filenm = self.FileOpenDiag("Select an Image To Insert.","Image","*.png,*.jpg,*.bmp")
    if filenm == None:
        return()

    #filenm =  "/home/drift/Pictures/a.png"
    buf = self.dataview.get_buffer()
    pixbuf = GdkPixbuf.Pixbuf.new_from_file(filenm)
    #pixbuf.scale_simple(dest_width, dest_height, gtk.gdk.INTERP_BILINEAR)
    buf.insert_pixbuf(buf.get_end_iter(), pixbuf) 
    self.dataview.set_buffer(buf)
    self.dataview.show()

This is the function that stores the textview buffer:

def SaveDataView(self):
    global DataViewNode
    global DataViewIsImage

    if len(self.GetProjectName()) == 0:
        return()
    buf = self.dataview.get_buffer()

    format = buf.register_serialize_tagset()
    data2 = buf.serialize(buf, format, buf.get_start_iter(), buf.get_end_iter())

    #convert bytes(data) to string
    data = data2.decode(encoding='UTF-8') #<< i think my problem is here
    print("save b4 decode >>>>>>:%s"%data2)

    sql = "UPDATE " + self.GetProjectName() + " SET tDataPath=%s  WHERE tNodeID=%s"
    val = (data, DataViewNode)

    self.cursor.execute(sql,val)
    self.mariadb_connection.commit()

This is the function that loads the Buffer:

def UpdateDataView(self, nodeid):
    global DataViewNode
    #global DataViewIsFile
    DataViewNode=nodeid


    if self.GetProjectName() != None and DataViewNode != None:
        self.dataview.set_sensitive(True)
    else:
        self.dataview.set_sensitive(False)
        self.dataview.show()
        return()

    buf = self.dataview.get_buffer()
    buf.set_text('')
    enc = self.DbGetNodeData(nodeid)



    #convert string(enc) to bytes
    data = enc.encode(encoding='UTF-8')#<<< i think my problem is here
    print("update after decode >>>>>>>>>: %s"%data)
    ########### load
    format = buf.register_deserialize_tagset()
    buf.deserialize(buf, format, buf.get_end_iter(),data) 


    #buf.set_text(enc)
    self.dataview.set_buffer(buf)
    self.dataview.show()

I'm using mysql.connector to connect to a mariadb. This is the sql connection string:

self.mariadb_connection = mariadb.connect(user='box', password='box', host='localhost', database='Boxer',charset='utf8')

This is the error im getting.

Traceback (most recent call last): File "Boxer.py", line 402, in _TreeSelectionChanged self.SaveDataView() File "Boxer.py", line 334, in SaveDataView data = data2.decode(encoding='UTF-8') UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb4 in position 174: invalid start byte Traceback (most recent call last): File "Boxer.py", line 398, in _DataViewLostFocus self.SaveDataView() File "Boxer.py", line 334, in SaveDataView data = data2.decode(encoding='UTF-8') UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb4 in position 174: invalid start byte

With this code I can add/edit plain text in the text view and successfully save/load it but as soon as I add the image, I'm get the encoding errors. Any help would be appreciated.

This worked for me: c.execute("UPDATE products SET description = %s WHERE id = 1", (psycopg2.Binary(data),)). But that is using PostgreSQL and Psycopg2. You did not specify what software you are using to connect to MySQL. — theGtknerd
Thanks for the response. I'm using mysql.connector to connect to mariadb (I use linux but that shouldn't matter) — Brad

theGtknerd theGtknerd · Accepted Answer · 2018-11-14T12:46:46

Here is a more complete example:

def example (self):

        #retrieve info from first textview
        buf = self.builder.get_object('textbuffer1')
        format = buf.register_serialize_tagset()
        data = buf.serialize(buf, format, buf.get_start_iter(), buf.get_end_iter())

        #run db update to prove it can be inserted into a database
        db = psycopg2.connect(database= 'silrep_restore3', host='192.168.0.101', 
                                        user='postgres', password = 'true', 
                                        port = '5432')
        c = db.cursor()
        c.execute("UPDATE products SET byt = %s WHERE id = 1", (psycopg2.Binary(data),))

        #append info to second treeview as a proof of concept
        c.execute("SELECT byt FROM products WHERE id = 1")
        data = c.fetchone()[0]

        buf = self.builder.get_object('textbuffer2')
        format = buf.register_deserialize_tagset()
        buf.deserialize(buf, format, buf.get_end_iter(), data)

Since you are using MySQL, I recommend reading this article about inserting and retrieving data like you are.

For my example I used a bytea column. In MySQL this is may be a BLOB or BINARY type.

P.S. Sorry for not having a complete MySQL example in my answer. I would have posted a comment, but comments are pathetic for proper formatting.

Store Gtk.Textbuffer in SQL database. Encoding troubles

2 Answers