Re: [Pypgsql-users] Support for Large Objects in DBAPI Product

[Billy G. A. <bg...@mu...>]

Bell John wrote:
> Does pypgsql support import/export operations on
> Binary Large Objects in the DB-API variant?  If so,
> what is the import/export syntax.
> 
> John

John,

The short answer is yes.

The long answer is more complicated.  Are you trying to import/export from a remote client or on the same machine on which the database resides (it makes a difference.

If on the same machine as the database server, you can use the built-in registered lo_import and lo_export.  This example is from the PostgreSQL documentation:

	CREATE TABLE image (
	    name            text,
	    raster          oid
	);

	INSERT INTO image (name, raster)
	    VALUES ('beautiful image', lo_import('/etc/motd'));

	SELECT lo_export(image.raster, '/tmp/motd') from image
	    WHERE name = 'beautiful image';

Just use the SQL insert and select statements in the execute() method.

If you need to import from a remote system, you can't us the lo_import or lo_export functions.  You can do the following (this requires the latest CVS of pyPgSQL to work):

	# Assume the the table image as defined above exists.
	from pyPgSQL import PgSQL
		cx = PgSQL.connect(database="dbname", host="hostname")
	cur = cx.cursor()
	# Import a file into a new large object.
	lo = cx.binary(open('/etc/motd', 'r').read())
	cur.execute('INSERT INTO image (name, raster) VALUES (%s, %s)',
		    'beautiful image', lo)
	cx.commit()

	# Export a large object to a file
	cur.execute('SELECT raster FROM image WHERE name = %s', "beautiful image")
	rs = cur.fetchone()
	lo = rs.raster
	lo.open('r')
	open('/tmp/motd', 'w').write(lo.read())

The lo_import and lo_export C-API functiona are available as methods of the libpq.PgConnection object.  If you are importing/exporting files that reside on the machine hosting the database server, you can use the follwoing code:

	from pyPgSQL import PgSQL
	cx = PgSQL.connect(database="dbname", host="hostname")
	cur = cx.cursor()
	# Import a file into a new large object.
	lo = cx.conn.lo_import('/etc/motd')
	cur.execute('INSERT INTO image (name, raster) VALUES (%s, %s)',
		    'beautiful image', lo)
	cx.commit()

	# Export a large object to a file
	cur.execute('SELECT raster FROM image WHERE name = %s', "beautiful image")
	rs = cur.fetchone()
	cx.conn.lo_export(rs.raster.oid, '/tmp/motd')

Again, I want to emphasize that the lo_export and lo_import will only export/import files that reside on the same machine as the database server.  Also, you will need to use the latest code from the CVS repository, which contains bug fixes related to large objects.

I hope this helps.
-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |