postgresql 如何将图像文件保存在 Postgres 数据库中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16763904/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to save a image file on a Postgres database?
提问by Felipe Matos
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.
出于学习目的，我正在使用 Python+Flask 创建一个站点。我想从数据库中恢复图像并将其显示在屏幕上。但是一步一个脚印。
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 byteatype 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.
我首先不知道如何在我的数据库中保存图像。我的搜索只显示我必须bytea在我的数据库中使用一个类型。然后我得到我的图像并以某种方式（??）将其转换为字节数组（bytea == 数组？）并以某种方式（??）在插入命令中使用这个数组。
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.
我能够发现（也许）如何在 Java（这里）和 C#（这里）中做到这一点，但我真的很想使用 Python，至少现在是这样。
Can someone help me?
有人能帮我吗？
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.
本网站有大量此类问题。但大多数（轻松超过 85%）的回答是“你不应该将图像保存在你的数据库中，它们属于 fs”并且没有回答这个问题。其余的不能完全解决我的问题。因此，如果副本有这种答案，请不要将其标记为重复。
回答by Craig Ringer
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)
Written with Python 3.3. Using Python 2.7 requires that you read the file and convert to a bufferobject or use the large object functions. Converting to Python 2.6 and older requires installation of argparse, probably other changes.
用 Python 3.3 编写。使用 Python 2.7 要求您读取文件并转换为buffer对象或使用大对象函数。转换为 Python 2.6 及更早版本需要安装 argparse，可能还有其他更改。
You'll want to change the database connection string to something suitable for your system if you're going to test-run it.
如果您要测试运行它，您需要将数据库连接字符串更改为适合您系统的内容。
If you're working with big images consider using psycopg2's large object supportinstead of bytea- in particular, lo_importfor store, lo_exportfor writing directly to a file, and the large object read functions for reading small chunks of the image at a time.
如果您正在处理大图像，请考虑使用psycopg2 的大对象支持而不是bytea- 特别是lo_import对于存储，lo_export用于直接写入文件，以及用于一次读取小块图像的大对象读取功能。
回答by iraycd
回答by Mandeep Kaur
import psycopg2
import sys
def readImage():
try:
fin = open("woman.jpg", "rb")
img = fin.read()
return img
except IOError, e:
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
finally:
if fin:
fin.close()
try:
con = psycopg2.connect(database="testdb", user="abc")
cur = con.cursor()
data = readImage()
binary = psycopg2.Binary(data)
cur.execute("INSERT INTO images(id, data) VALUES (1, %s)", (binary,) )
con.commit()
except psycopg2.DatabaseError, e:
if con:
con.rollback()
print 'Error %s' % e
sys.exit(1)
finally:
if con:
con.close()
回答by Abirdcfly
that's my solution, it can work in my website:
这是我的解决方案，它可以在我的网站上运行：
@main.route('/upload', methods=['GET', 'POST'])
def upload_avatar():
if request.method == 'POST':
file = request.files['file']
if file and allowed_file(file.filename):
current_user.avatar_local = file.read()
db.session.add(current_user)
db.session.commit()
return redirect(url_for('main.user_page', username=current_user.username))
return render_template('upload_avatar.html', user=current_user)
using Flask,Flask-Alchemy to handle database.
使用 Flask、Flask-Alchemy 来处理数据库。
{% block edit_avatar %}
<form action="" method=post enctype=multipart/form-data>
<p><input type=file name=file>
<input type=submit value=Upload>
</form>
{% endblock %}
that's html file.you can embed it in you html.
那是 html 文件。你可以将它嵌入到你的 html 中。
