Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > psycopg2 insertion and reading binary data to PostgreSQL database(bytea datatype)

Thread Tools

psycopg2 insertion and reading binary data to PostgreSQL database(bytea datatype)
Posts: n/a
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()

Reply With Quote

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
psycopg2 for insertion of binary data to PostgreSQL database Julia Jacobson Python 7 08-23-2010 08:13 AM
psycopg2 / psycopg2.ProgrammingError: syntax error at or near"E'mytable'" mrdrew Python 5 04-05-2010 05:27 PM
psycopg2 / psycopg2.DataError: invalid input syntax for type ASh Python 10 03-31-2010 10:50 AM
Can't Write to PostGIS PostGreSQL database via psycopg2 David Michael Schruth, Python 2 10-23-2007 05:37 PM
PostgreSQL, psycopg2 and OID-less tables Dale Strickland-Clark Python 12 09-28-2006 09:43 AM