#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Location
    Nomadic
    Posts
    14
    Rep 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 ?
  2. #2
  3. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    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

  4. #3
  5. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,646
    Rep Power
    4248
    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

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  6. #4
  7. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    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.
    programming language development: www.netytan.com Hula


IMN logo majestic logo threadwatch logo seochat tools logo