如何将图像文件保存在 Postgres 数据库上？

出于学习目的，我正在使用 Python+Flask 创建一个网站。我想从数据库中恢复图像并将其显示在屏幕上。但一步一步来。

我首先不知道如何将图像保存在我的数据库中。我的搜索只显示我必须使用bytea输入我的数据库。然后我得到我的图像并以某种方式（？）将其转换为字节数组（bytea == 咬合数组？）并以某种方式（？？）在插入命令中使用该数组。

我能够（也许）发现如何用 Java 做到这一点（here http://jeebestpractices.blogspot.com.br/2011/03/save-images-into-database-postgres-with.html) 和 C# (here https://stackoverflow.com/questions/4852558/how-to-save-image-to-a-database），但我真的很想使用Python，至少现在是这样。

有人能帮我吗？

该网站上有大量此类问题。但大多数（轻松超过 85%）的回答是“你不应该将图像保存在数据库中，它们属于 fs”，并且未能回答问题。其余的并不能完全解决我的问题。因此，如果重复项有此类答案，请不要将其标记为重复项。

我通常不会为人们编写完整的示例程序，但您并不需要它，而且它是一个非常简单的程序，所以您可以这样做：

#!/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 要求您读取该文件并将其转换为buffer对象或使用大对象函数。转换到 Python 2.6 及更早版本需要安装 argparse，可能还需要其他更改。

如果您要测试运行它，您需要将数据库连接字符串更改为适合您的系统的字符串。

如果您正在处理大图像，请考虑使用psycopg2 的大对象支持 http://initd.org/psycopg/docs/usage.html#access-to-postgresql-large-objects代替bytea- 尤其，lo_import对于商店，lo_export用于直接写入文件，大对象读取功能用于一次读取图像的小块。