Wsrtjtyk

I tried to setup a very simple app. I wanted to create this app as fullstack app as training for future projects. So i wrote a backend in python which provides data from a DB (SQLLite) via an API (Flask/Connexion). The API is documented via Swagger. The DB should have a table where each row got 2 values:
1. name
2. images
I quickly faced a problem: I actually don't know how to handle images in APIs. Therefore I created the backup with a placeholder. Till now images is just another string which is mostly empty. Everything works fine. But now I want to be able to get Images via API and save them in the DB. I have absolutly no Idea how to do this. Hope one of you can help me.

Here is my Code so far:

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

To save an image in SQLITE (not that it's recommended, better to save the image as a file and to save the path in the DB) you save it as an array of bytes (storage type of BLOB, not that the column has to be defined as a BLOB).

In SQL you specify an array of bytes as a hex string. So you read you image and build a hex string

• 
  

  Noting

  
  
  
  
  

  • 
    

    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.

    
    

  
  
  
  

Also

• Noting
  
  
  
  

  • 
    

    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.

    
    

  
  
  
  

The resultant SQL would be along the lines of :-

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

As a demo using your table (slightly modified to include the "correct" column type BLOB, which makes little difference) :-

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;

The result would be :-

• Note Navicat was used to run text the above. Blobs are inherently difficult to display hence display. However, what is shown is that the above obviously stores and retrieves the data.

As previously stated it's much simpler to just store the path to the image file and when it boils down to it there is likely very little need for the image as data. You're unlikely to be querying the data that the image is comprised of, whilst using naming standards could allow useful searches/queries of a stored name/path.

However, in contradiction of the above, SQLite can, in some circumstances (images with an average size around 100k or less (maybe more)) allow faster access than the file system 35% Faster Than The Filesystem.