PostgreSQL has this datatype called bytea. It is their version of a blob.
In displaying images, png/jpg/gif, the current trend nowadays is to use Data URLs.
My question is how to convert a bytea value into a Base64 Data URL value?
Add a comment
|
3 Answers
You may also fetch the complete image data URL from your database, e.g.:
SELECT 'data:image/gif;base64,' || encode(image_data, 'base64') AS image_url
FROM ...
3 Comments
Abel Callejo
that thing worked well. Although I have to take note that instead of
encode(image_data, 'base64'), it should be encode("image_data", 'base64'). Implementers should be wary though that in this solution, the resources of the database server gets being used instead of the back-end scripts'
clemens
The column name in my post is just an example. The name and if you should protect it by double quotes depends on the real database.
Abel Callejo
Yes you are right @clemens, it's just that pgsql has this own convention that you should enclose in quotes the field names especially if they are on an upper case
$img_bin = hex2bin(substr($the_bytea, 2));
$url = 'data:image/gif;base64,' . base64_encode($img_bin);
Postgres bytea is a string starting with \x followed by hexadecimal characters. To get the image in binary format, need to strip off the leading \x and convert from hexadecimal characters to binary with hex2bin. Now that you have it in binary, you can base64_encode it.
2 Comments
Abel Callejo
bare naked it is.
clemens
You should use
pg_unescape_bytea instead of hex2bin.Posting this answer incase anyone stumbles here using Postgres and sqlalchemy. Thank you to the other answers for helping me get this far.
I was able to use the answer provided by clemens above and place the sql into a column_property and leveraging literal_column to insert a rawsql statement.
class Attachment(BaseModel):
__tablename__ = "attachments"
id = Column(UUID(), primary_key=True, default=uuid.uuid4, )
data = Column(BYTEA(), nullable=True)
text = Column(Text, nullable=True)
file_name = Column(String(255), nullable=True)
content_type = Column(String(255), nullable=True)
model = Column(String(255), nullable=False)
model_id = Column(UUID(), nullable=False, index=True)
meta_data = Column(JSON, nullable=True)
deleted = Column(BOOLEAN, default=False)
# return a data url when the attachment is an image, doesnt work in sqlite
image_url = column_property(
literal_column("""
CASE
WHEN content_type LIKE 'image%' THEN 'data:image/gif;base64,' || encode(data, 'base64')
ELSE null
END
""", type_=String).label("image_url")
)
