How to Add Two BLObs to an MySQL Row via Python

Question

I can now insert a BLOb into a row using the following code snippets:-

TheData = open("/home/mjh/Documents/DemoData/sjp.bin", 'rb').read()

sql = "Insert into Results (idResults, idClient, TestDateTime, ResultBinary) 
    Values (10, 7, '2014-11-05 14:09:11', %s)"

cursor.execute(sql, (TheData,))

However I want to put multiple BLObs into the same row and expanded the code to:-

sql = "Insert into Results (idResults, idClient, TestDateTime, ResultBinary, SecondResult) 
    Values (10, 7, '2014-11-05 14:09:11', %s, %s)"

cursor.execute(sql, (TheData, SecondData,))

This generates the error:-

_mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')

This seems a logical change (to me) based on extending the insert to add other field types. Does this mean I have to do an insert (for the first BLOb) followed by an UPDATE (for the second BLOb)?

Answer 1

Have you tried using named parameters, viz:

sql = "Insert into Results (idResults, idClient, TestDateTime, ResultBinary, SecondResult) 
    Values (10, 7, '2014-11-05 14:09:11', %(one)s, %(two)s)"

cursor.execute(sql, { 'one': TheData, 'two': SecondData})

Reference

Answer 2

I have checked that it works for me. There is nothing wrong in passing multiple field values to the sql execute function. The mistake is you may pass the python List. You should convert it to string and pass to that execute function. Please view for the following link:

MySqlDb throws Operand should contain 1 column(s) on insert ignore statement

Answer 3

I found that changing the Execute line to:-

cursor.execute(sql, ([TheData, SecondData]))

worked. Will try the other approaches