Thread: How to display stored image as bytea or as large objects?
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
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.
On Wed, Jul 14, 2021 at 12:57 PM Csanyi Pal <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
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>> 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
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> 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>> 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
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