str.decode to PNG not working with MySql

[Tacllem]

Good day everyone.
I have inherited a Python 2.7 script that pulls records from a mysql database and creates png files to export my product's images. The line that worked for years is "fh.write(str.decode('base64'))" and the table "images" has a column named "imageFull" with a Collation of 'utf8_general_ci'

Last month the vendor updated the software and the table structure has changed. The column "imageFull" now has a Collation of 'utf8mb4_unicode_ci' and the write command fails. I tried updating to Python 3.13(64-bit)

I can't change the column structure as it is not my application. I can't say why they are storing images in this way. The vendor has no responsibility to helping with this export script.

So the question is "Do I need to use a decode other than 'base64'? Does it matter if i'm on python 2.7 or 3.13? Is there an alternate command to use for the write string? "


Here is a larger chunk of the code incase it helps.

try:
			#Convert image string to its png form
			print("Converting full size image...")
			str = row[1]#full size image
			fh = open(finalImageName, "wb")
			fh.write(str.decode('base64'))
			fh.close()
			totalSuccessLarge+=1
		except:
			print("This large image failed...skipping and continuing process...")
			totalFailLarge+=1
			time.sleep(5)
			pass
		
		try:
			print("Converting thumbnail size image...")
			str = row[2]#thumb image
			fh = open(finalImageName2, "wb")
            fh.write(str.decode('base64'))
			fh.close()
			totalSuccessThumb+=1
		except:
			print("This thumbnail image failed...skipping and continuing process...")
			totalFailThumb+=1
			time.sleep(5)
			pass

Larz60+ write Oct-02-2024, 09:48 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
BBcode Tags have been added for you. Please use BBCode tags on future posts.

[Tacllem]

Good morning.
The output of the script is as follows.

Part number for this image = zzz test part 1
Combined part num with .png = zzz test part 1.png
Final image name 2 (where small images are saved) = C:\Python27\thumbs\zzz test part 1.png
Converting full size image...
1
2
This large image failed...skipping and continuing process...
Converting thumbnail size image...
This thumbnail image failed...skipping and continuing process...
Moving large images into their folder...
PROCESS COMPLETE!
Large Image Success total = 0
Large Image Failed Total = 1
Thumb Image Success total = 0
Thumb Image Failed Total = 1

[snippsat]

Try use base64 module instead of str.decode('base64').
This should work for Python 3.13.

import base64

# Convert image string to bytes in base64, then decode
str_data = row[1]  # full-size image
image_data = base64.b64decode(str_data) 

with open(finalImageName, "wb") as fh:
    fh.write(image_data)

Thumbnail conversion:

str_data_thumb = row[2]  # thumbnail image
thumb_image_data = base64.b64decode(str_data_thumb)

with open(finalImageName2, "wb") as fh:
    fh.write(thumb_image_data)

You might need to encode it back to bytes before decoding from base64. For example:

str_data = row[1].encode('utf-8')  # Ensure string is encoded as bytes
image_data = base64.b64decode(str_data)

And try not to use str as variable name,Python use this name.

[Tacllem]

Hi.
Thank you for the suggestions. I beleve I implemented your changes. Unfortunately I still get the same error.

try:
			#Convert image string to its png form
			print("Converting full size image...")
			str_data = row[1].encode('utf-8')  # Ensure string is encoded as bytes
			image_data = base64.b64decode(str_data)
			#FB 19.7 was utf8_general_ci  while FB 24.3 is utf8mb4_unicode_ci
			with open(finalImageName, "wb") as fh: fh.write(image_data)
			fh.close()
			totalSuccessLarge+=1
		except:

[snippsat]

I don't do consulting time,i can try to help you here on forum.
Try this code.

import base64
import time
import mysql.connector
from mysql.connector import errorcode
import shutil
import os
import glob

source_dir = "C:/python27"
dst = 'C:/python27/largeSize'
totalSuccessLarge = 0
totalFailLarge = 0
totalSuccessThumb = 0
totalFailThumb = 0

print("WELCOME TO SUPER MEGA AWESOME IMAGE CONVERTER!")

# Load config variables
configVariables = []
filepath = 'imgConvert-Config.txt'
with open(filepath, 'r') as f:
    configVariables = f.readlines()

print("Loading config file...")
pw = os.environ.get('DB_PASSWORD')
if not pw:
    raise ValueError("Database password not found in environment variables.")
database = configVariables[0].strip()
user = configVariables[1].strip()
host = configVariables[2].strip()
pathFull = configVariables[3].strip()
pathSmall = configVariables[4].strip()

print("Connecting to DB...")
try:
    cnx = mysql.connector.connect(user=user, password=pw, host=host, port=3306, database=database)
    cursor = cnx.cursor()
    print("Executing queries...")
    sql_select_Query = "SELECT * FROM image"
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()

    print("Total number of rows in image is - ", cursor.rowcount)
    for row in records:
        # Extract data
        img_id, full_size_image, thumbnail_image, record_id = row
        print(f"Id = {img_id}")
        print(f"Full Size image = {full_size_image}")
        print(f"Thumbnail image = {thumbnail_image}")
        print(f"recordID = {record_id}\n")

        # Get part number
        sql_select_Query3 = "SELECT num FROM part WHERE id = %s"
        cursor.execute(sql_select_Query3, (record_id,))
        partNum = cursor.fetchone()[0]
        print(f"Part number for this image = {partNum}")

        # Prepare file paths
        finalImageName = os.path.join(dst, f"{partNum}.png")
        finalImageName2 = os.path.join(pathSmall, f"{partNum}.png")

        # Ensure directories exist
        os.makedirs(dst, exist_ok=True)
        os.makedirs(pathSmall, exist_ok=True)

        # Convert full-size image
        try:
            print("Converting full size image...")
            image_data = base64.b64decode(full_size_image)
            with open(finalImageName, "wb") as fh:
                fh.write(image_data)
            totalSuccessLarge += 1
        except Exception as e:
            print(f"This large image failed... {e}")
            totalFailLarge += 1
            continue
        # Convert thumbnail image
        try:
            print("Converting thumbnail size image...")
            thumb_image_data = base64.b64decode(thumbnail_image)
            with open(finalImageName2, "wb") as fh:
                fh.write(thumb_image_data)
            totalSuccessThumb += 1
        except Exception as e:
            print(f"This thumbnail image failed... {e}")
            totalFailThumb += 1
            continue

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'cnx' in locals():
        cnx.close()

print("PROCESS COMPLETE!")
print(f"Large Image Success total = {totalSuccessLarge}")
print(f"Large Image Failed Total = {totalFailLarge}")
print(f"Thumb Image Success total = {totalSuccessThumb}")
print(f"Thumb Image Failed Total = {totalFailThumb}")

[Tacllem]

Hi.
Thank you for helping. Your way of doing this is very interesting. Running this code I receive an error.

Executing queries...
Total number of rows in image is - 28345
Traceback (most recent call last):
File "C:\Python27\imageconverter-forum.py", line 46, in <module>
img_id, full_size_image, thumbnail_image, record_id = row
ValueError: too many values to unpack (expected 4)

[Tacllem]

Hi again. I have some progress.
If I remove the "= row" from the line "img_id, full_size_image, thumbnail_image, record_id = row" I get past this error.
Then at the top under the includes I add:

img_id = 0
full_size_image = ""
thumbnail_image = ""
record_id = 0

I get past the next error of "full_size_image" is not defined.

But then I hit the next landmine of:
Traceback (most recent call last):
File "C:\Python27\imageconverter-forum.py", line 60, in <module>
partNum = cursor.fetchone()[0]
TypeError: 'NoneType' object is not subscriptable

[snippsat]

Try before accessing the result, check if cursor.fetchone() returned a result:

cursor.execute(sql_select_Query3, (record_id,))
part_num_row = cursor.fetchone()
if part_num_row:
    partNum = part_num_row[0]
    print(f"Part number for this image = {partNum}")
else:
    print(f"No part number found for record ID {record_id}")
    totalFailLarge += 1
    totalFailThumb += 1
    continue  # Skip to the next iteration

[Tacllem]

Hello.
I added that change and get hundreds of lines that say:

No part number found for record ID = 0
Id = 0
Full size image =
thumbnail =
recordID = 0

[snippsat]

Your image table may has entries where recordID is 0.
Since there is no part with id = 0, the query to get the partNum fails.
The full_size_image and thumbnail_image fields are empty (''), which means there's no image data to process.
Try this.

import base64
import mysql.connector
from mysql.connector import errorcode
import os

source_dir = "C:/python27"
dst = 'C:/python27/largeSize'
totalSuccessLarge = 0
totalFailLarge = 0
totalSuccessThumb = 0
totalFailThumb = 0

print("WELCOME TO SUPER MEGA AWESOME IMAGE CONVERTER!")

# Load config variables
configVariables = []
filepath = 'imgConvert-Config.txt'
with open(filepath, 'r') as f:
    configVariables = f.readlines()

print("Loading config file...")
pw = "xxxx9999"
database = configVariables[0].strip()
user = configVariables[1].strip()
host = configVariables[2].strip()
pathFull = configVariables[3].strip()
pathSmall = configVariables[4].strip()

print("Connecting to DB...")
try:
    cnx = mysql.connector.connect(user=user, password=pw, host=host, port=3306, database=database)
    cursor = cnx.cursor()
    print("Executing queries...")

    # Modified SQL query to filter out invalid entries
    sql_select_Query = """
    SELECT id, full_size_image, thumbnail_image, record_id
    FROM image
    WHERE record_id > 0
      AND full_size_image IS NOT NULL AND full_size_image != ''
      AND thumbnail_image IS NOT NULL AND thumbnail_image != ''
    """
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()

    print("Total number of valid rows in image is - ", cursor.rowcount)
    for row in records:
        img_id, full_size_image, thumbnail_image, record_id = row
        print(f"Id = {img_id}")
        print(f"Full Size image = {full_size_image}")
        print(f"Thumbnail image = {thumbnail_image}")
        print(f"recordID = {record_id}\n")

        # Query for part number
        sql_select_Query3 = "SELECT num FROM part WHERE id = %s"
        cursor.execute(sql_select_Query3, (record_id,))
        part_num_row = cursor.fetchone()
        if part_num_row:
            partNum = part_num_row[0]
            print(f"Part number for this image = {partNum}")
        else:
            print(f"No part number found for record ID = {record_id}")
            totalFailLarge += 1
            totalFailThumb += 1
            continue  # Skip to the next image

        # Prepare file paths
        finalImageName = os.path.join(dst, f"{partNum}.png")
        finalImageName2 = os.path.join(pathSmall, f"{partNum}.png")

        # Ensure directories exist
        os.makedirs(dst, exist_ok=True)
        os.makedirs(pathSmall, exist_ok=True)

        # Convert full-size image
        try:
            print("Converting full size image...")
            image_data = base64.b64decode(full_size_image)
            with open(finalImageName, "wb") as fh:
                fh.write(image_data)
            totalSuccessLarge += 1
        except Exception as e:
            print(f"This large image failed... {e}")
            totalFailLarge += 1
            continue

        # Convert thumbnail image
        try:
            print("Converting thumbnail size image...")
            thumb_image_data = base64.b64decode(thumbnail_image)
            with open(finalImageName2, "wb") as fh:
                fh.write(thumb_image_data)
            totalSuccessThumb += 1
        except Exception as e:
            print(f"This thumbnail image failed... {e}")
            totalFailThumb += 1
            continue

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'cnx' in locals():
        cnx.close()

print("PROCESS COMPLETE!")
print(f"Large Image Success total = {totalSuccessLarge}")
print(f"Large Image Failed Total = {totalFailLarge}")
print(f"Thumb Image Success total = {totalSuccessThumb}")
print(f"Thumb Image Failed Total = {totalFailThumb}")