String literals
Regarding the original question, the issue lies with quoting your variables. This causes the execute function to treat them as string literals rather than getting the values from them.
As suggested by @Herman, to properly execute the SQL statement with the values I think you intend, you would need something more like this:
query_data = {mybigquery}
statement = 'INSERT INTO database.table VALUE (%s, %s, %s);'
response = query_request.query(projectId='myprojectid', body=query_data).execute()
rows = response['rows']
for row in rows:
values = (row[0], row[1], row[2])
cursor.execute(statement, values)
BigQuery query JSON
Keep in mind however that the above will not work out of the box as row in the code above does not conform to the response received from the BigQuery Job: query API.
In this API, rows is an array of row objects. Each row object has a property f which is an array of fields. Lastly, each field has a property v which is the value of this field.
To get the value of second field in a row, you should use row['f'][1]['v']. Since you require a tuple or list for the params argument of the cursor.execute() method, you could get a list of field values using list comprehension as follows:
for row in rows:
values = [field['v'] for field in row['f]]
Sanitize values before inserting
The TypeError you get once correctly reading the field values may be raised because execute or str cannot convert a value to a string properly. One of the significant differences between BigQuery and MySQL is that a value in BigQuery can be a record with multiple values of its own. To ensure this gets inserted properly, you must sanitize those values yourself prior to inserting them. If the value is a list or dict, it cannot be stored in MySQL without being serialized in some way like with the str method.
Example
def sanitize(value):
if type(value) is list:
return str(value)
if type(value) is dict:
return str(value)
# this may be required for other types
return value
data = {mybigquery}
statement = 'INSERT INTO database.table VALUE (%s, %s, %s);'
response = request.query(projectId='projid', body=data).execute()
for row in response['rows']:
values = [sanitize(field['v']) for field in row['f']]
cursor.execute(statement, values)
This is very basic sanitation. You should really validate all field values and ensure that they will be properly converted to MySQL types and not simply insert an array of values.
