Insert blob data to MySQL
Billy Ng
Would any body tell me how to insert a blob object to the mysql blob field
Thanks!
Billy Ng
Steve Holden
> Hi folks,
>
> Would any body tell me how to insert a blob object to the mysql blob field
>
Here "cc" is a cursor on a MySQLdb connection ...
>>> cc.execute("""CREATE TABLE blobtbl (
... i integer primary key auto_increment,
... b blob)""")
0L
>>> s = "string data but it could be anything"
>>> cc.execute("""INSERT INTO blobtbl (b) VALUES(%s)""", (s,))
1L
>>> cc.execute("""SELECT * FROM blobtbl""")
1L
>>> cc.fetchall()
((1L, 'string data but it could be anything'),)
>>>
Is this what you wanted?
must-remember-to-delete-that-table-ly y'rs - steve
--
Steve Holden: http://www.holdenweb.com/ ; Python Web Programming:
http://pydish.holdenweb.com/pwp/
Alex Martelli
...
>>>> s = "string data but it could be anything"
>>>> cc.execute("""INSERT INTO blobtbl (b) VALUES(%s)""", (s,))
> 1L
If s is 'anything' (e.g., a cPickle.dumps with binary option set),
MySQLdb.escape_string(s) is likely to work better than plain s
as the value to put in the tuple that's execute's second arg.
BTW, with PostgreSQL, recent releases (since 7.1 I believe, i.e.,
since 'toast' was integrated in the mainstream codebase), you
can use basically the same approach, except you call the field
type BYTEA rather than BLOB and psycopg.Binary(s) is the
function you call to 'escape' the bytestring to place there.
In both cases there is an alternative that hinges on "loading a
data file" (server-side) via a nonstandard SQL function (LOAD_FILE
in MySQL, LO_IMPORT in PostgreSQL) but almost always there
are too many pitfalls with that (you do need to get the data to the
DB server machine, which hampers architectures based on client
and server DB processes on separate machines; you need special
privileges in the database; with LO_IMPORT, you need the data
field to be of special type OID; ...) so I wouldn't follow that route.
Alex
Steve Holden
news:EXKA8.2549$CN3....@news2.tin.it...
> Steve Holden wrote:
> ...
> >>>> s = "string data but it could be anything"
> >>>> cc.execute("""INSERT INTO blobtbl (b) VALUES(%s)""", (s,))
> > 1L
>
> If s is 'anything' (e.g., a cPickle.dumps with binary option set),
> MySQLdb.escape_string(s) is likely to work better than plain s
> as the value to put in the tuple that's execute's second arg.
>
> BTW, with PostgreSQL, recent releases (since 7.1 I believe, i.e.,
> since 'toast' was integrated in the mainstream codebase), you
> can use basically the same approach, except you call the field
> type BYTEA rather than BLOB and psycopg.Binary(s) is the
> function you call to 'escape' the bytestring to place there.
>
Not yet done more than looked at PostgreSQL, but I'll get there when the
Linux server comes back up in a fe days.
> In both cases there is an alternative that hinges on "loading a
> data file" (server-side) via a nonstandard SQL function (LOAD_FILE
> in MySQL, LO_IMPORT in PostgreSQL) but almost always there
> are too many pitfalls with that (you do need to get the data to the
> DB server machine, which hampers architectures based on client
> and server DB processes on separate machines; you need special
> privileges in the database; with LO_IMPORT, you need the data
> field to be of special type OID; ...) so I wouldn't follow that route.
>
Ah, the joy of a standards-based database environment. Wouldn't it be nice
if all the database producers decided to break the SQL standard in the same
way? Thanks for your useful addition to my original somewhat over-simplified
note.
you-go-get-coffee-now-i'm-off-to-bed-ly y'rs - steve
Billy Ng
I have one more question. If the DOM object in the blob field is created by
java, can python uses it?
Billy Ng
Gerhard Häring
> "Billy Ng" <kwo...@earthlink.net> wrote ...
>> Hi folks,
>>
>> Would any body tell me how to insert a blob object to the mysql blob field
>>
>
> Here "cc" is a cursor on a MySQLdb connection ...
>
>>>> cc.execute("""CREATE TABLE blobtbl (
> ... i integer primary key auto_increment,
> ... b blob)""")
> 0L
Aren't transactions cool? ;-)
PostgreSQL-rules-ly yours,
Gerhard
PS: I know you could get transactions by using a different table type
in the CREATE statement.
--
mail: gerhard <at> bigfoot <dot> de registered Linux user #64239
web: http://www.cs.fhm.edu/~ifw00065/ OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9 3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))
Gerhard Häring
> Steve Holden wrote:
> ...
>>>>> s = "string data but it could be anything"
>>>>> cc.execute("""INSERT INTO blobtbl (b) VALUES(%s)""", (s,))
>> 1L
>
> If s is 'anything' (e.g., a cPickle.dumps with binary option set),
> MySQLdb.escape_string(s) is likely to work better than plain s
> as the value to put in the tuple that's execute's second arg.
AFAIC you shouldn't need to do this. converters.Thing2Literal is the
default converter in MySQL, which calls string_literal in _mysql,
which in turn calls mysql_real_escape_string.
> BTW, with PostgreSQL, recent releases (since 7.1 I believe, i.e.,
> since 'toast' was integrated in the mainstream codebase), you
> can use basically the same approach, except you call the field
> type BYTEA rather than BLOB and psycopg.Binary(s) is the
> function you call to 'escape' the bytestring to place there.
This is also needed in pyPgSQL, but just because a BYTEA is different
from a VARCHAR/TEXT field in that it can contain null characters.
Normally, all conversions are done automatically.
pyPgSQL also has a feature that I like a lot: you can open BLOBs as
file-like objects and use read, readlines, etc. on them:
from pyPgSQL import PgSQL
# BLOB data
blob = "".join(map(lambda x: chr(x), range(256)))
# Open connection, get cursor
db = PgSQL.connect()
cursor = db.cursor()
# Create a PgLargeObject
lo = db.binary(blob)
# Create table and insert the large object
cursor.execute("create table blobtest(id serial, b oid)")
cursor.execute("insert into blobtest(b) values (%s)", lo)
cursor.execute("select * from blobtest")
result = cursor.fetchone()
# Open the large object as a file-like object
result.b.open("r")
returned_blob = result.b.read()
assert(blob == returned_blob)
This uses several nonstandard feature, one I like is that the
PgResultSet that's returned from fetchone can be access as a tuple, as
a dictionary or just by accessing the fields as attributes, which I
did above.
I've given up on being SQL compatible on multiple databases. And on
compatibility with multiple DB-API modules, I've almost given up,
mostly because I like some of the nonstandard features of pyPgSQL and
MySQLdb a lot.
Gerhard
Alex Martelli
...
>>>>>> cc.execute("""INSERT INTO blobtbl (b) VALUES(%s)""", (s,))
>>> 1L
>>
>> If s is 'anything' (e.g., a cPickle.dumps with binary option set),
>> MySQLdb.escape_string(s) is likely to work better than plain s
>> as the value to put in the tuple that's execute's second arg.
>
> AFAIC you shouldn't need to do this. converters.Thing2Literal is the
Interesting! I was convinced the escape_string was needed, but
testing right now (with the latest versions) confirms Gerhard's
suggestion -- I'm not sure where my misconception had come from
(or why it had apparently never 'bit' me -- sheer "luck" I guess!).
To give good info in exchange for good info:
> blob = "".join(map(lambda x: chr(x), range(256)))
Peculiarly redundant -- ''.join(map(chr, range(256))) being quite
equivalent. And a good example of why we'd be better off without
lambda:-).
Alex
Steve Holden
news:slrnad7acj...@lilith.my-fqdn.de...
> Steve Holden wrote in comp.lang.python:
> > "Billy Ng" <kwo...@earthlink.net> wrote ...
> >> Hi folks,
> >>
> >> Would any body tell me how to insert a blob object to the mysql blob
field
> >>
> >
> > Here "cc" is a cursor on a MySQLdb connection ...
> >
> >>>> cc.execute("""CREATE TABLE blobtbl (
> > ... i integer primary key auto_increment,
> > ... b blob)""")
> > 0L
> > [...]
> > must-remember-to-delete-that-table-ly y'rs - steve
>
> Aren't transactions cool? ;-)
>
> PostgreSQL-rules-ly yours,
>
> Gerhard
>
> PS: I know you could get transactions by using a different table type
> in the CREATE statement.
Indeed. My experience with (for example) SQL Server has taught me that DDL
statements are often committed whether or not they are part of an explicit
transaction. Of course, the fact that the table was still there without
commit() doesn't tell me anything with MySQL's default type though.
regards
Steve