import os
import mysql.connector

# get all files inside a specific folder
dir_path = r'C:/Users/Administrator/PycharmProjects/pythonProject9/InputFiles/'
for path in os.scandir(dir_path):
    if path.is_file():
        print(path.name)
        with open(dir_path+path.name, 'rb') as file:
            binaryData = file.read()
            try:
                connection = mysql.connector.connect(host='localhost',
                                                     database='blob',
                                                     user='root',
                                                     password='root')

                cursor = connection.cursor()
                sql_insert_blob_query = """ INSERT INTO pybindata
                                  (name, data) VALUES (%s,%s)"""



                # Convert data into tuple format
                insert_blob_tuple = (path.name, binaryData)
                result = cursor.execute(sql_insert_blob_query, insert_blob_tuple)
                connection.commit()
                print("Image and file inserted successfully as a BLOB into python_employee table", result)

            except mysql.connector.Error as error:
                print("Failed inserting BLOB data into MySQL table {}".format(error))

            finally:
                if connection.is_connected():
                    cursor.close()
                    connection.close()
                    print("MySQL connection is closed")

import mysql.connector

dir_path = r'C:/Users/Administrator/PycharmProjects/pythonProject9/DownLoadFiles/'
print("Reading BLOB data from python_employee table")

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='blob',
                                         user='root',
                                         password='root')

    cursor = connection.cursor()
    sql_fetch_blob_query = """SELECT * from pybindata"""

    cursor.execute(sql_fetch_blob_query)
    record = cursor.fetchall()
    for row in record:
        print("Id = ", row[0], )
        print("Name = ", row[2])
        print("Storing employee image and bio-data on disk \n")
        with open(dir_path + row[2], 'wb') as file:
            file.write(row[1])

except mysql.connector.Error as error:
    print("Failed to read BLOB data from MySQL table {}".format(error))

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")