PostgreSQL 加载图片到数据库 [英] PostgreSQL load images to DB

问题描述

我已经知道如何在数据库中存储图像，只需在我的表中使用 bytea 类型

I've already know how to store images in DB, just use bytea type in my table

而且我已经可以通过我的项目 .net Core 中的代码将图像保存到数据库中，我只是通过 url 获取图像并在那里保存:

And I've already can save images to DB via code in my project .net Core, I've just getting image by url and saving like there:

using (HttpResponseMessage res = await client.GetAsync(photo_url))
  using (HttpContent content = res.Content) {
    byte[] imageByte = await content.ReadAsByteArrayAsync();
     using (NpgsqlConnection conn = new NpgsqlConnection("ConnectionString")) {
      conn.Open();
      using (NpgsqlTransaction tran = conn.BeginTransaction())
      using (NpgsqlCommand cmd = new NpgsqlCommand("Photo_Save", conn)) {           
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("photo", NpgsqlTypes.NpgsqlDbType.Bytea, imageByte);
        cmd.ExecuteScalar();           
        tran.Commit();
  }
}

效果很好

但我需要从我的电脑保存到表格图像

But I need save to table images from my pc

有没有什么方法可以不用宿主机或者其他项目的代码，直接用本地图片和Postges DB连接就可以把图片上传到数据库中?

Is there any way to upload images into the database without the code on the host or in any other project, just use local picture and connection to Postges DB?

推荐答案

如果你可以使用 psql，你可以使用 \lo_import 来导入图像和 lo_open 和 loread 函数将内容读取为 bytea.

If you can use psql, you could use \lo_import to import the image and the lo_open and loread functions to read the contents as a bytea.

假设我想将文件 chuck.jpg 导入到表 blobs 中，并且文件不超过 1000000 字节，可以这样做:

Assuming that I want to import file chuck.jpg into a table blobs, and the file does not exceed 1000000 bytes, that could be done like this:

test=> \lo_import chuck.jpg 
lo_import 152237

test=> INSERT INTO blobs VALUES (1, loread(lo_open(152237, 131072), 1000000));
INSERT 0 1

test=> \lo_unlink 152237
lo_unlink 152237

我使用 \lo_unlink 来移除临时大对象.

I used \lo_unlink to remove the temporary large object.

这篇关于PostgreSQL 加载图片到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！