Storing BLOB in MySql DB

Dev Shed Forums Sponsor:

January 28th, 2004, 02:30 PM

geskerrett

Junior Member

Join Date: Nov 2002

Location: Nomadic

Posts: 14

Time spent in forums: < 1 sec
Reputation Power: 0

Storing BLOB in MySql DB

I was wondering if someone had an example of storing data in a BLOB field in mysql.

I want to archive emails from my email server.
After extracting the header, to, from, msgid I would like to store the body of the email in a blob field.
I am running in to a problem with the unprintable charaters interfering with the SQL statement.

If you have a working example I would appreciate if you could post here.

I think what I am looking for is something equivalent to PHP's stripquotes addquotes functions ... but maybe not ?

January 28th, 2004, 05:32 PM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,537

Time spent in forums: 1 Week 2 Days 18 h 17 m 47 sec
Reputation Power: 68

I havn't got an example for this one but i think you want a raw string which should escape the special chars i.e.

Code:

>>> 'blob \ \\blob\blob'
'blob \\ \\blob\x08lob'
>>> r'blob \ \\blob\blob'
'blob \\ \\\\blob\\blob'

Note: The diferance being raw strings have r at the start

Think this is what you want, since theres no function called stripquotes or addquotes in PHP (or at least not on the website) i couldn't really look them up.

Mark.

__________________
programming language development: www.netytan.com – Hula

January 31st, 2004, 07:35 PM

Scorpions4ever

Banned ;)

Dev Shed God 9th Plane (9000 - 9499 posts)

Join Date: Nov 2001

Location: Woodland Hills, Los Angeles County, California, USA

Posts: 9,385

Time spent in forums: 1 Month 4 Weeks 1 Day 21 h 30 m 25 sec
Reputation Power: 4080

Depending on your version of MySQLdb, you can do this on newer versions:

Code:

sql = "INSERT INTO sometable (col1, col2) VALUES (%s, %s)"
cursor.execute(sql, (val1, val2))

On older versions of MySQLdb, you have to use escape_string() to properly escape the string.

Code:

sql = "INSERT INTO sometable (col1, col2) VALUES (%s, %s)"
cursor.execute(sql, (MySQLdb.escape_string(val1),  MySQLdb.escape_string(val2))

__________________
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne

February 1st, 2004, 01:22 PM

netytan

Hello World :)

Join Date: Mar 2003

Location: Hull, UK

Posts: 2,537

Time spent in forums: 1 Week 2 Days 18 h 17 m 47 sec
Reputation Power: 68

So using raw strings you could have be done something like this... but since MySQLdb now escapes by default? This seems pretty pointless!

Code:

sql = "INSERT INTO table (col1, col2) VALUES (%r, %r)"
cursor.execute(sql, (val1, val2))

Oh, you have unbalenced parenthesise in that last example scrop

Mark.

Storing BLOB in MySql DB

Developer Shed Advertisers and Affiliates