psycopg2 insertion and reading binary data to PostgreSQL database (bytea datatype)

[romap at libero.it]

Hello.
This is the full work version.

Do yuo have:
- Pyton, PostgreSQL, Psycopg2
- PostgreSQL dababase named "MyDATABASE" with table named "phonebook"
- Table "phonebook" have this columns: "lastname" [TEXT datatype] and
"c2image" [BYTEA datatype]
- Do you have an jpeg file named "sun.jpg" on c:/

Open  db connection

>>> import psycopg2
>>> conn_string = "host='localhost' dbname='MyDATABASE' user='MyUSER' password='MyPASSWORD'"
>>> conn = psycopg2.connect(conn_string)

Write image on database (binary data on bytea column)

>>> mypic=open('c:/sun.jpg','rb').read()
>>> cursor = conn.cursor()
>>> cursor.execute("INSERT INTO phonebook(lastname,c2image) VALUES (%s,%s);", ('MyPICTURENAME', psycopg2.Binary(mypic)))
>>> conn.commit()

Read image from database and write to a file

>>> cursor = conn.cursor()
>>> cursor.execute("SELECT (c2image) FROM phonebook WHERE lastname='MyPICTURENAME';")
>>> mypic2 = cursor.fetchone()
>>> open('c:/copyofsun.jpg', 'wb').write(str(mypic2[0]))

Close db connection

>>> cursor.close()
>>> conn.close()