Hi,

How do i store files into blob datatype into mysql via user input with python?

def read_file(filename):
    #Convert digital data to binary format
    with open(filename, 'wb') as f:
        photo = f.read()
    return photo

@app.route('/createProduct', methods=['GET', 'POST'])
def createProduct():
    createProductForm = CreateProductForm()
    msg = ''
    if request.method == 'POST' and 'productName' in request.form and 'productImage' in request.form and 'productModel' in request.form \
        and 'productInfo' in request.form and 'productCareandComposition' in request.form \
            and 'price' in request.form and 'category' in request.form:

        productName = request.form['productName']
        productImage = request.form['productImage']
        productModel = request.form['productModel']
        productInfo = request.form['productInfo']
        productCareandComposition = request.form['productCareandComposition']
        price = request.form['price']
        category = request.form['category']

        #Check if item exist
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM products WHERE productName = %s', (productName))
        PRODUCT = cursor.fetchone()

        if PRODUCT:
            msg = 'Item has been added successful !'
        else:
            cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
            #products_insert = 'INSERT INTO products (productName, productImage,productModel, productInfo, productCareandComposition, price, category) VALUES (NULL, %s, %s, %s, %s, %s, %d, %s)'

            pImage= read_file(productImage)
            pModel = read_file(productModel)

            print(pImage)
            print(pModel)

            #Convert data into tuple format
            #insert_blob_tuple = (productName, pImage,pModel, productInfo, productCareandComposition, price, category)
            cursor.execute('INSERT INTO products VALUES (NULL, %s, %s, %s, %s, %s, %d, %s)', (productName, pImage, pModel, productInfo, productCareandComposition, price, category))
            mysql.connection.commit()
            msg = 'You have successfully added the product!'
            return redirect(url_for('retrieveProducts'))

These are my codes. it doesnt seems to work. There is also no error message given during execution.

I don't know Python but I do have a lot of experience with MySQL. Does it work if you're not trying to insert BLOB content? Is the MySQL column set to the right data type?

commented: Yes it works without BLOB content. The columns are set to the right data type +0

Your INSERT statement is incorrect, it should read like - INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Then, why would you like to add large data (files, images etc) to a table field? The resources on your server will be huge if you have many users adding data.

Rather save the file/image to your server and only save the path to your data table. See the php documentation - file_put_contents

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.