python - 使用 Python Flask/Connexion 和 Swagger 处理 API 中的图像

Question

我尝试设置一个非常简单的应用程序。我想将此应用程序创建为全栈应用程序，作为 future 项目的培训。所以我用 python 编写了一个后端，它通过 API (Flask/Connexion) 提供来自数据库 (SQLLite) 的数据。该 API 通过 Swagger 进行记录。数据库应该有一个表，其中每行有 2 个值:1. 姓名2.图像我很快就遇到了一个问题:我实际上不知道如何在 API 中处理图像。因此，我使用占位符创建了备份。到目前为止，图像只是另一个大部分为空的字符串。一切正常。但现在我希望能够通过 API 获取图像并将它们保存在数据库中。我完全不知道该怎么做。希望你们中的一位可以帮助我。

这是迄今为止我的代码:

SqlliteHandler.py

import sqlite3

conn = sqlite3.connect('sprint_name.db')
c = conn.cursor()


def connect_db():
    global conn
    global c
    conn = sqlite3.connect('sprint_name.db')
    c = conn.cursor()
    c.execute("CREATE TABLE if not exists sprint_names ( name text, image text)")


def make_db_call(execute_statement, fetch_smth=""):
    global c
    connect_db()
    print(execute_statement)
    c.execute(execute_statement)
    response = ""
    if fetch_smth is "one":
        response = transform_tuple_to_dict(c.fetchone())
    if fetch_smth is "all":
        response_as_tuples = c.fetchall()
        response = []
        for sug in response_as_tuples:
            response.append(transform_tuple_to_dict(sug))

    conn.commit()
    conn.close()
    return response


def transform_tuple_to_dict(my_tuple):
    return {"name": my_tuple[0], "image": my_tuple[1]}


def add_name(suggestion):
    name = suggestion.get("name")
    image = "" if suggestion.get("image") is None else suggestion.get("image")
    execute_statement = "SELECT * FROM sprint_names WHERE name='" + name + "'"
    print(execute_statement)
    alreadyexists = False if make_db_call(execute_statement, "one") is None else True
    print(alreadyexists)
    if not alreadyexists:
        execute_statement = "INSERT INTO sprint_names VALUES ('" + name + "', '" + image + "')"
        make_db_call(execute_statement)


def delete_name(suggestion_name):
    execute_statement = "DELETE FROM sprint_names WHERE name='" + suggestion_name + "'"
    print(execute_statement)
    make_db_call(execute_statement)


def delete_all():
    make_db_call("DELETE FROM sprint_names")


def get_all_names():
    return make_db_call("SELECT * FROM sprint_names", "all")


def get_name(suggestion_name):
    print(suggestion_name)
    execute_statement = "SELECT * FROM sprint_names WHERE name='" + suggestion_name + "'"
    print(execute_statement)
    return make_db_call(execute_statement, "one")


def update_image(suggestion_name, suggestion):
    new_name = suggestion.get("name" )
    new_image = "" if suggestion.get("image") is None else suggestion.get("image")
    execute_statement = "UPDATE sprint_names SET name='" + new_name + "', image='" + new_image + "' WHERE name='"\
                        + suggestion_name + "'"
    make_db_call(execute_statement)

RunBackEnd.py

from flask import render_template
import connexion

# Create the application instance
app = connexion.App(__name__, specification_dir='./')
# Read the swagger.yml file to configure the endpoints
app.add_api('swagger.yml')

# Create a URL route in our application for "/"
@app.route('/')
def home():
    """
    This function just responds to the browser ULR
    localhost:5000/
    :return:        the rendered template 'home.html'
    """
    return render_template('home.html')

# If we're running in stand alone mode, run the application
if __name__ == '__main__':
    app.run(port=5000)

Swagger.yml

    swagger: "2.0"
info:
  description: This is the swagger file that goes with our server code
  version: "1.0.0"
  title: Swagger REST Article
consumes:
  - "application/json"
produces:
  - "application/json"

basePath: "/api"

# Paths supported by the server application
paths:
  /suggestions:
    get:
      operationId: SqlliteHandler.get_all_names
      tags:
        - suggestions
      summary: The names data structure supported by the server application
      description: Read the list of names
      responses:
        200:
          description: Successful read names list operation
          schema:
            type: array
            items:
              properties:
                name:
                  type: string
                image:
                  type: string
    post:
      operationId: SqlliteHandler.add_name
      tags:
        - suggestions
      summary: Create a name and add it to the names list
      description: Create a new name in the names list
      parameters:
        - name: suggestion
          in: body
          description: Suggestion you want to add to the sprint
          required: True
          schema:
            type: object
            properties:
              name:
                type: string
                description: Name you want to submit
              image:
                type: string
                description: path to the picture of that name
      responses:
        201:
          description: Successfully created name in list

  /suggestions/{suggestion_name}:
    get:
      operationId: SqlliteHandler.get_name
      tags:
        - suggestions
      summary: Read one name from the names list
      description: Read one name from the names list
      parameters:
        - name: suggestion_name
          in: path
          description: name of the sprint name to get from the list
          type: string
          required: True
      responses:
        200:
          description: Successfully read name from names list operation
          schema:
            type: object
            properties:
              name:
                type: string
              image:
                type: string

    put:
      operationId: SqlliteHandler.update_image
      tags:
        - suggestions
      summary: Update an image in the suggestion list via the name of the suggestions
      description: Update an image in the suggestion list
      parameters:
        - name: suggestion_name
          in: path
          description: Suggestion you want to edit
          type: string
          required: True
        - name: suggestion
          in: body
          schema:
            type: object
            properties:
              name:
                type: string
              image:
                type: string
      responses:
        200:
          description: Successfully updated suggestion in suggestion list

    delete:
      operationId: SqlliteHandler.delete_name
      tags:
        - suggestions
      summary: Delete a suggestion via its name from the suggestion list
      description: Delete a suggestion
      parameters:
        - name: suggestion_name
          in: path
          type: string
          required: True
      responses:
        200:
          description: Successfully deleted a suggestion from the list

Answer 1

要在 SQLITE 中保存图像(不建议，最好将图像另存为文件并将路径保存在数据库中)，请将其另存为字节数组(BLOB 存储类型，并不是列必须定义为 BLOB)。

在 SQL 中，您将字节数组指定为十六进制字符串。所以你读取图像并构建一个十六进制字符串

• 注意

  • Maximum length of a string or BLOB

    The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this:

    -DSQLITE_MAX_LENGTH=123456789 The current implementation will only support a string or BLOB length up to 231-1 or 2147483647. And some built-in functions such as hex() might fail well before that point. In security-sensitive applications it is best not to try to increase the maximum string and blob length. In fact, you might do well to lower the maximum string and blob length to something more in the range of a few million if that is possible.

    During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row.

    The maximum string or BLOB length can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size) interface.

还有

• 注意到

  • Maximum Length Of An SQL Statement

    The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000. You can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH and 1073741824.

    If an SQL statement is limited to be a million bytes in length, then obviously you will not be able to insert multi-million byte strings by embedding them as literals inside of INSERT statements. But you should not do that anyway. Use host parameters for your data. Prepare short SQL statements like this:

    INSERT INTO tab1 VALUES(?,?,?); Then use the sqlite3_bind_XXXX() functions to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It is also runs faster since the large string does not need to be parsed or copied as much.

    The maximum length of an SQL statement can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_SQL_LENGTH,size) interface.

生成的 SQL 将类似于:-

INSERT INTO mytable (myimage) VALUES (x'fffe004577aabbcc33f1f8');

作为使用表的演示(稍微修改以包含“正确”列类型 BLOB，这几乎没有什么区别):-

DROP TABLE If EXISTS sprint_names;
CREATE TABLE if not exists sprint_names ( name text, image text, altimage BLOB);
INSERT INTO sprint_names VALUES
    ('SPRINT001',x'fffe004577aabbcc33f1f8',x'fffe004577aabbcc33f1f8'), -- obviously image would be larger
    ('SPRINT002',x'99008877665544332211f4d6e9c2aaa8b7b4',x'99008877665544332211f4d6e9c2aaa8b7b4')
;
SELECT * FROM sprint_names;

结果将是:-

• 注意 Navicat 用于运行上面的文本。 Blob 本质上很难显示，因此无法显示。然而，上面显示的显然是存储和检索数据。

如前所述，仅存储图像文件的路径要简单得多，当它归结为它时，可能很少需要图像作为数据。您不太可能查询图像组成的数据，而使用命名标准可以允许对存储的名称/路径进行有用的搜索/查询。

然而，与上述相反，在某些情况下(平均大小约为 100k 或更小(可能更多)的图像)，SQLite 可以允许比文件系统 35% Faster Than The Filesystem 更快的访问速度。