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

Discussion in 'Python' started by romap@libero.it, Mar 1, 2011.

  1. Guest

    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()
    , Mar 1, 2011
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Julia Jacobson
    Replies:
    7
    Views:
    1,589
    Peter Otten
    Aug 23, 2010
  2. Frank Millman
    Replies:
    0
    Views:
    120
    Frank Millman
    Jul 31, 2013
  3. Antoine Pitrou
    Replies:
    0
    Views:
    84
    Antoine Pitrou
    Jul 31, 2013
  4. Frank Millman
    Replies:
    2
    Views:
    86
    Frank Millman
    Aug 1, 2013
  5. Antoine Pitrou
    Replies:
    0
    Views:
    69
    Antoine Pitrou
    Jul 31, 2013
Loading...

Share This Page