Re: How to display stored image as bytea or as large objects? - Mailing list pgsql-general

Hi Ho John Lee,

I found one can in DBeaver upload an image into bytea data type column.

So my issue is gone; by using DBeaver I can upload images and also 
display those images stored in a postgresql database.

Thanks!

2021. 07. 15. 6:47 keltezéssel, Ho John Lee írta:
> I use python with psycopg2, for a table 'myimage_table' with columns 
> 'label' and 'b' of type varchar and bytea something like this:
> 
> <set up database connection, and get a cursor>
> query = 'insert into myimage_table (label, b) values (%s, %s)'
> imagelabel = 'some_label'
> imagebuf = <byte data of your image>
> b = psycopg2.Binary(imagebuf)
> cur.execute(query, (imagelabel, b))
> 
> The SQL query doesn't have raw binary data in it, the text representing 
> the raw data looks something like this: \\xffd8ffe000104a...
> Psycopg2 constructs the SQL query, including encoding the raw bytes into 
> text to send to Postgres (which converts back to raw bytes to store.)
> Once you actually have raw image data in your table, you can use dbeaver 
> or other tools and get a rendered image corresponding to the value stored.
> 
> --hjl
> 
> On Wed, Jul 14, 2021 at 9:12 PM Csanyi Pal <csanyipal@gmail.com 
> <mailto:csanyipal@gmail.com>> wrote:
> 
>     2021. 07. 14. 22:17 keltezéssel, Ho John Lee írta:
>      > If you have the binary data for the image in the bytea field,
>     DBeaver
>      > will display the image when you select that field in table view
>     of the data.
>      > The field will display something like "ÿØÿà  JFIF   [nnnnn]" in
>     the row
>      > (first part of data as unicode, and data length in bytes), you
>     won't see
>      > the image unless you put focus on it.
> 
>     I will try this, but do not know which SQL code will store an image
>     in a
>     bytea data type column?
> 
>     Could you give such SQL code please?
> 
>     At the moment I have stored image in an oid data type column.
> 
> 
>      > .--hjl
> 
>      > On Wed, Jul 14, 2021 at 12:57 PM Csanyi Pal <csanyipal@gmail.com
>     <mailto:csanyipal@gmail.com>
>      > <mailto:csanyipal@gmail.com <mailto:csanyipal@gmail.com>>> wrote:
>      >
>      >     Hi,
>      >
>      >     I am a novice in postgresql language.
>      >
>      >     By reading the documentation about storing binary data in
>     postgresql
>      >     database, I realize that that one can store images as binary
>     data by
>      >     using bytea or BLOB data types.
>      >
>      >     I am running postgresql on Ubuntu 20.04.2 LTS and using
>     pgAdmin4 in
>      >     Desktop mode.
>      >
>      >     I have stored an image in one of my table with this command:
>      >
>      >     <code>
>      >     insert into leckek
>      >     (az, a_tanitas_nyelve, mely_negyedevben, mely_osztalyban,
>      >     mely_tagozaton, mely_orakon, a_tanitas_temaja,
>      >        a_lecke_neve, a_lecke_gyakorlati_reszenek_neve,
>      >     a_lecke_gyakorlati_reszenek_dokumentuma,
>      >        a_lecke_elmeleti_reszenek_neve,
>      >     a_lecke_elmeleti_reszenek_dokumentuma,
>      >     a_lecke_elmeleti_reszenek_tesztje)
>      >     values (default, 'Hu', 1, 5, 'b', '1-2', 'Élet és mukakörnyezet',
>      >                      'A technika és technológia fogalma, szerepe és
>      >     jelentősége',
>      >                      'másolás',
>      >                      default,
>      >                      'A lényeg',
>      >
>      >   
>
 lo_import('/home/pali/Irataim/Iskola/IskolaEv_2020_2021/MuszakiOraim/Digitalis_Tananyagok_Orakra/Hu/5/5_osztaly_Tananyag_01_02_orara.png'),
>      >                      default)
>      >     </code>
>      >
>      >     so I have now in this table the first row:
>      >     1       "Hu"    1       5       "b"     "1-2  " "Élet és
>      >     mukakörnyezet" "A technika és
>      >     technológia fogalma, szerepe és jelentősége"    "másolás"
>      >         "A lényeg"      "16800"
>      >
>      >     Here the 16800 is the OID of the binary data, the image.
>      >
>      >     I would like to know how can one display this stored image
>     somehow by
>      >     using some IDE for manage databases?
>      >
>      >     In applications like pgAdmin4, Libreoffice Base, pgModeler,
>     DBeaver one
>      >     can't display this stored image.
>      >
>      >     In SQLiteBrowser (this is an IDE for SQLite databases) one
>     can display
>      >     with easy the stored image.
>      >
>      >     So is there an easy way to display a stored image in postgresql?
>      >
>      >     --
>      >     Best,
>      >     from Paul