upload web files to mysql in python

Question

I am new to python and looking for a way to execute a "complicated" task.

I need to store small images (less than 1mb each) into the Image column (blob) of my DB. My script currently retrieve URLs required to read the files. I've found no way so far to retrieve the images and upload them to the DB without writing them the the HDD (to spare the HDD).

I am currently using URLlib2 and MySQL Connector, and I'd like to remain if possible with them as they work on both Windows and Debian.

codes = [xx, aa, ab] # This line is simulated as it is gerated by a long script run previously...

print "-> Downloading Flags..."
for code in codes:
    if not code == 'xx':
        filename = "%s-lgflag.gif" % code
        url = "%s%s" % (flagurl, filename)
        index = code.index(code)

        opener = urllib2.build_opener()
        opener.addheaders = [('User-agent', 'Mozilla/5.0')]
        infile = opener.open(url)
        pic = infile.read()

        update_time = int(time.time())

        wquery = ("UPDATE `cin`.`flags` SET `flag`='%s', `update_time`='%d' WHERE `name`='%s' AND `lockid`='%s'") % (pic_bin, update_time, names[index], lockid)
        cursor.execute(wquery)
        cnx.commit()
        sys.exit() ### Exit to make sure that I don't pass the whole list of 500+ flags
print "-> Flags downloaded"

This code return error:

Traceback (most recent call last):
  File "C:\Users\***\flagstest.py", line 61, in <module>
    wquery = ("UPDATE `cin`.`flags` SET `flag`='%s', `update_time`='%d' WHERE `name`='%s' AND `lockid`='%s'") % (pic, update_time, names[index], lockid)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc1 in position 41: ordinal not in range(128)

Hope anybody can help me find a way to work arround it...

PS: I know storing images in DB is not the best solution, but as the DB is sync across devices and some may require offline data access, these images need to be stored there.

Community · Accepted Answer · 2017-05-23 11:50:01Z

0

in your query:

 wquery = ("UPDATE `cin`.`flags` SET `flag`='%s', `update_time`='%d' WHERE `name`='%s' AND `lockid`='%s'") % (pic_bin, update_time, names[index], lockid)

what you do, basically, is to create a string from a binary using the string format system. There, python tries to transform each argument to a string (i.e. str(pic_bin)), and then replace the matching %s in your string with that. You should instead be using the cursor's system:

wquery = "UPDATE `cin`.`flags` SET `flag`='%s', `update_time`='%d' WHERE `name`='%s' AND `lockid`='%s'"
cursor.execute(wquery, (pic_bin, update_time, names[index], lockid))

Have a look at other questions like yours

HTH

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Jun 23, 2013 at 16:25

zmo

24.9k4 gold badges58 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Xop777 Over a year ago

Thanks I understood that the variable was not used in the correct way but was not able to find over google... But I still get this error: Traceback (most recent call last): File "C:\Users...\Xflags.py", line 61, in <module> cursor.execute(wquery, (pic_bin, update_time, names[index], lockid,)) File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 381, in execute "Wrong number of arguments during string formatting") ProgrammingError: Wrong number of arguments during string formatting

Collectives™ on Stack Overflow

upload web files to mysql in python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related