psycopg2 for insertion of binary data to PostgreSQL database

[Thomas Jollans]

On Sunday 22 August 2010, it occurred to Julia Jacobson to exclaim:
> Thanks a lot, this was the solution.
> It would be greate, if you could also show me a way to extract the
> inserted binary object from the table on the server to a file on a client.

Probably something along the lines of:

* execute an appropriate SELECT query
* get the record you're interested in
* open a file for writing
* f.write(data)
* f.close() and other clean-up code

> 
>  > Peter Otten wrote:
> >> Julia Jacobson wrote:
> >> 
> >> Hello everybody out there using python,
> >> 
> >> For the insertion of pictures into my PostgreSQL database [with table
> >> foo created by SQL command "CREATE TABLE foo (bmp BYTEA)], I've written
> >> the following script:
> >> 
> >> #!/usr/bin/python
> >> import psycopg2
> >> 
> >> try:
> >>       conn = psycopg2.connect("dbname='postgres' user='postgres'
> >> 
> >> host='localhost' password='data'");
> >> 
> >> except:
> >>       print "I am unable to connect to the database"
> >> 
> >> cur = conn.cursor()
> >> f = open("test.bmp", 'rb')
> >> myfile = f.read()
> >> 
> >> try:
> >>       cur.execute("INSERT INTO foo VALUES (%s)",(buffer(myfile),))
> >> 
> >> except:
> >>       print "Insert unsuccessful"
> >> 
> >> "python script.py" runs the script without any errors or messages.
> >> However, the SQL command "SELECT * FROM foo"  returns the output "foo (0
> >> rows)" with no entries in the table.
> >> I'm using Python 2.7 and PostgreSQL 8.3.
> >> Could anyone help me to find a way to pin down the problem?
> > 
> > Perhaps you need to conn.commit() your changes.