This code works, but is very slow. And I will like to use sqlalchemy module because the rest of the script uses that instead of mysql. Is there any advantage of using sqlalchemy or should I continue with this ...
for emp_id in mylist:
try:
connection = mysql.connector.connect(host='x.x.x.x', port='3306', database='xxx', user='root', password='xxx')
cursor = connection.cursor(prepared=True)
sql_fetch_blob_query = """SELECT col1, col2, Photo from tbl where ProfileID = %s"""
cursor.execute(sql_fetch_blob_query, (emp_id, ))
record = cursor.fetchall()
for row in record:
image = row[2]
file_name = 'myimages4'+'/'+str(row[0])+ '_' + str(row[1]) + '/' + 'simage' + str(emp_id) + '.jpg'
write_file(image, file_name)
except mysql.connector.Error as error :
connection.rollback()
print("Failed to read BLOB data from MySQL table {}".format(error))
finally:
if(connection.is_connected()):
cursor.close()
connection.close()
Do you really need to set up new mysql connection and obtain cursor on each iteration? If no, opening it once at the beginning will really speed up your code.
connection = mysql.connector.connect(host='x.x.x.x', port='3306', database='xxx', user='root', password='xxx', charset="utf8")
cursor = connection.cursor(prepared=True)
for emp_id in mylist:
try:
sql_fetch_blob_query = """SELECT col1, col2, Photo from tbl where ProfileID = %s"""
cursor.execute(sql_fetch_blob_query, (emp_id, ))
record = cursor.fetchall()
for row in record:
image = row[2]
file_name = 'myimages4'+'/'+str(row[0])+ '_' + str(row[1]) + '/' + 'simage' + str(emp_id) + '.jpg'
write_file(image, file_name)
except mysql.connector.Error as error :
connection.rollback()
print("Failed to read BLOB data from MySQL table {}".format(error))
finally:
# ouch ...
if(connection.is_connected()):
cursor.close()
connection.close()
UPD:
Actually you don't even need to make N queries to database, because all data can be obtained in one query with WHERE ProfileID IN (.., ..)
SQL statement. Take a look this small code, which solves a pretty much identical task:
transaction_ids = [c['transaction_id'] for c in checkouts]
format_strings = ','.join(['%s'] * len(transaction_ids))
dm_cursor.execute("SELECT ac_transaction_id, status FROM transactions_mapping WHERE ac_transaction_id IN (%s)" % format_strings, tuple(transaction_ids))
payments = dm_cursor.fetchall()
Please use it to solve your problem.