如何在Postgres数据库上保存图像文件？ [英] How to save a image file on a Postgres database?

问题描述

为了学习目的，我使用Python + Flask创建了一个网站。我想从数据库恢复一个图像，并在屏幕上显示它。但是一次只能一步。

For learning purposes, I'm creating a site using Python+Flask. I want to recover an image from database and show it on screen. But one step at a time.

我不知道如何在我的数据库中保存一个图像。我的搜索只显示我必须在我的数据库中使用 bytea 类型。然后我得到我的形象，并以某种方式（??）转换为字节数组（bytea ==数组的bites？）和某种方式（??）使用这个数组在一个插入命令。

I have no idea how to save an image in my database in the first place. My searches only revealed that I have to use a bytea type in my database. Then I get my image and somehow (??) convert it to an array of bytes (bytea == array of bites?) and somehow (??) use this array in a insert command.

我能够发现（也许）如何在Java中做到这一点（此处）和C＃（这里），但我真的想使用Python，至少现在。

I was able to discover (maybe) how to do it in Java (here) and C# (here), but I would really like to use Python, at least for now.

有人可以帮助我吗？

这个网站有很多这样的问题。但大多数（容易超过85％）他们被回答为你不应该保存图像在你的数据库，他们属于fs，并无法回答这个问题。其余的不完全解决我的问题。

There are tons of questions of this kind in this site. But most (easily over 85%) of them are replied as "You shouldn't save images in your database, they belong in fs" and fail to answer the question. The rest don't quite solve my problem. So please don't mark this as duplicate if the duplicate has this kind of answer.

推荐答案

我通常不会写完整的示例程序，但是你没有要求它，它是一个很简单的，所以这里你去：

I don't normally write complete example programs for people, but you didn't demand it and it's a pretty simple one, so here you go:

#!/usr/bin/env python3

import os
import sys
import psycopg2
import argparse

db_conn_str = "dbname=regress user=craig"

create_table_stm = """
CREATE TABLE files (
    id serial primary key,
    orig_filename text not null,
    file_data bytea not null
)
"""

def main(argv):
    parser = argparse.ArgumentParser()
    parser_action = parser.add_mutually_exclusive_group(required=True)
    parser_action.add_argument("--store", action='store_const', const=True, help="Load an image from the named file and save it in the DB")
    parser_action.add_argument("--fetch", type=int, help="Fetch an image from the DB and store it in the named file, overwriting it if it exists. Takes the database file identifier as an argument.", metavar='42')
    parser.add_argument("filename", help="Name of file to write to / fetch from")

    args = parser.parse_args(argv[1:])

    conn = psycopg2.connect(db_conn_str)
    curs = conn.cursor()

    # Ensure DB structure is present
    curs.execute("SELECT 1 FROM information_schema.tables WHERE table_schema = %s AND table_name = %s", ('public','files'))
    result = curs.fetchall()
    if len(result) == 0:
        curs.execute(create_table_stm)

    # and run the command
    if args.store:
        # Reads the whole file into memory. If you want to avoid that,
        # use large object storage instead of bytea; see the psycopg2
        # and postgresql documentation.
        f = open(args.filename,'rb')

        # The following code works as-is in Python 3.
        #
        # In Python 2, you can't just pass a 'str' directly, as psycopg2
        # will think it's an encoded text string, not raw bytes. You must
        # either use psycopg2.Binary to wrap it, or load the data into a
        # "bytearray" object.
        #
        # so either:
        #
        #   filedata = psycopg2.Binary( f.read() )
        #
        # or
        #
        #   filedata = buffer( f.read() )
        #
        filedata = f.read()
        curs.execute("INSERT INTO files(id, orig_filename, file_data) VALUES (DEFAULT,%s,%s) RETURNING id", (args.filename, filedata))
        returned_id = curs.fetchone()[0]
        f.close()
        conn.commit()
        print("Stored {0} into DB record {1}".format(args.filename, returned_id))

    elif args.fetch is not None:
        # Fetches the file from the DB into memory then writes it out.
        # Same as for store, to avoid that use a large object.
        f = open(args.filename,'wb')
        curs.execute("SELECT file_data, orig_filename FROM files WHERE id = %s", (int(args.fetch),))
        (file_data, orig_filename) = curs.fetchone()

            # In Python 3 this code works as-is.
            # In Python 2, you must get the str from the returned buffer object.
        f.write(file_data)
        f.close()
        print("Fetched {0} into file {1}; original filename was {2}".format(args.fetch, args.filename, orig_filename))

    conn.close()

if __name__ == '__main__':
    main(sys.argv)

用Python 3.3编写。使用Python 2.7需要读取文件并转换为缓冲区对象或使用大对象函数。转换为Python 2.6及以上版本需要安装argparse，可能还有其他更改。

Written with Python 3.3. Using Python 2.7 requires that you read the file and convert to a buffer object or use the large object functions. Converting to Python 2.6 and older requires installation of argparse, probably other changes.

如果您想要将数据库连接字符串更改为适合您的系统

You'll want to change the database connection string to something suitable for your system if you're going to test-run it.

如果您使用大图片，请考虑使用> lo_import <> / code>用于存储， lo_export 用于直接写入文件，大对象读取函数用于一次读取图像的小块。

If you're working with big images consider using psycopg2's large object support instead of bytea - in particular, lo_import for store, lo_export for writing directly to a file, and the large object read functions for reading small chunks of the image at a time.

这篇关于如何在Postgres数据库上保存图像文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！