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
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
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.
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:
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}")
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)
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
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
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
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}")