So, your saying that I need to use mysqldb's string escape method? Because other than that it looks awfully similar to what I was doing... I was also hoping for a DB agnostic solution...
On Tue, Jun 12, 2012 at 10:48 AM, John Fabiani <[email protected]> wrote: > On 06/12/2012 07:01 AM, Nathan Lowrie wrote: >> On Jun 9, 2012, at 1:45 PM, Paul McNett wrote: >> >>> On 6/9/12 9:49 AM, Ed Leafe wrote: >>>> On Jun 9, 2012, at 11:26 AM, John Fabiani wrote: >>>> >>>>>> I am having issue with the binary field type. >>>>> What type of issue? >>>> And what database? >>> I'm storing images to an sqlite database, I'll dig out the code. I found >>> that I >>> needed different code to store to MySQL. This shouldn't be, since Dabo's >>> supposed to >>> wrap the differences but that's all I had time to find out. >> I can do the following just fine: >> >> f = open('my/path/to/file.pdf', 'rb') >> data = f.read() # contains binary data >> >> outfile = open('file/to/write/to.pdf', 'wb') >> outfile.write(data) >> >> >> It will rewrite the data just fine. However if I store it in a binary field >> in a bizobj and try to write from the binary field, I get a unicode error. >> >> f = open('my/path/to/file.pdf', 'rb') >> data = f.read() # contains binary data >> >> bizobj.setFieldVal('file', data) >> bizobj.save() >> >> data = bizobj.getFieldVal('file') >> outfile = open('file/to/write/to.pdf', 'wb') >> outfile.write(data) >> >> This results in a >> >> UnicodeEncodeError: 'ascii' codec can't encode character: ordinal not in >> range(128) >> >> Not sure how to proceed... What data type does the binary database field >> type translate to? >> >> Also, if anyone wants to know why, I have a request to link receipts (either >> email attachments, scanned pictures, or pdfs) to an expense report so that >> they can easily be found. Storing a file location is not an option in this >> case. >> >> Regards, >> >> Nate >> > > > I found this: > > > Writing images > > Some people prefer to put their images into the database, some prefer to > keep them on the file system for their applications. Technical > difficulties arise when we work with millions of images. Images are > binary data. MySQL database has a special data type to store binary data > called *BLOB* (Binary Large Object). > > mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data > MEDIUMBLOB); > Query OK, 0 rows affected (0.06 sec) > > For this example, we create a new table called Images. > > #!/usr/bin/python > # -*- coding: utf-8 -*- > > import MySQLdb as mdb > import sys > > try: > fin = open("chrome.png") > img = fin.read() > fin.close() > > except IOError, e: > > print "Error %d: %s" % (e.args[0],e.args[1]) > sys.exit(1) > > > try: > conn = mdb.connect(host='localhost',user='testuser', > passwd='test623', db='testdb') > cursor = conn.cursor() > cursor.execute("INSERT INTO Images SET Data='%s'" % \ > mdb.escape_string(img)) > > conn.commit() > > cursor.close() > conn.close() > > except mdb.Error, e: > > print "Error %d: %s" % (e.args[0],e.args[1]) > sys.exit(1) > > In the above script, we read a png image and insert it into the Images > table. > > fin = open("chrome.png") > img = fin.read() > > We open and read an image. The *read()* function returns the data as > string. > > cursor.execute("INSERT INTO Images SET Data='%s'" % \ > mdb.escape_string(img)) > > This string data is inserted into the table. Before doing so, it is > processed by the *escape_string()* method. It escapes a string for use > as a query parameter. This is common practice to avoid malicious sql > injection attacks. > > > Reading images > > In the previous example, we have inserted an image into the database > table. Now we are going to read the image back from the table. > > #!/usr/bin/python > # -*- coding: utf-8 -*- > > import MySQLdb as mdb > import sys > > try: > conn = mdb.connect(host='localhost',user='testuser', > passwd='test623', db='testdb') > > cursor = conn.cursor() > > cursor.execute("SELECT Data FROM Images LIMIT 1") > > fout = open('image.png','wb') > fout.write(cursor.fetchone()[0]) > fout.close() > > cursor.close() > conn.close() > > except IOError, e: > > print "Error %d: %s" % (e.args[0],e.args[1]) > sys.exit(1) > > We read one image from the Images table. > > cursor.execute("SELECT Data FROM Images LIMIT 1") > > We select one record from the table. > > fout = open('image.png','wb') > > We open a writable binary file. > > fout.write(cursor.fetchone()[0]) > > We fetch the data from the previous SQL statement and write it to the file. > > Now we should have an image called image.png in our current directory. > We can check if it is the same image, that we have inserted into the table. > > > Johnf > > > > --- StripMime Report -- processed MIME parts --- > multipart/alternative > text/plain (text body -- kept) > text/html > --- > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev Searchable Archives: http://leafe.com/archives/search/dabo-dev This message: http://leafe.com/archives/byMID/CAGcHxzbjmYLaU6TqF1brz0bdc=PSJSkv_pey5p+8Rh=awjn...@mail.gmail.com
