CODEWITHSUNDEEP
mysqlgoogle-bigquerymysql-pythongoogle-cloud-sqlgoogle-api-python-client
coldbond
coldbond

Reputation: 49

how to insert values into mysql table from another bigquery response

My Python program connects to BigQuery and fetching data which I want to insert into a MySQL table. It's successfully fetching the results from BigQuery. It's also successfully connecting to MySQL DB but it's not inserting the data. I see its complaining for the row[1].

What's the right way to insert the values from BigQuery response into MySQL table columns?

query_data = {mybigquery}

query_response = query_request.query(projectId='myprojectid',body=query_data).execute()

for row in query_response['rows']:
  cursor.execute ("INSERT INTO database.table VALUES ('row[0]','row[1]','row[2]','row[3]','row[4]');")

Also, I tried to use

cursor.execute ("INSERT INTO database.table VALUES (%s,%s,%s,%s,%s);")

or 

cursor.execute ("INSERT INTO database.table VALUES (row[0],row[1],row[2],row[3],row[4]);")

But in all it fails while inserting values in mysql table

Upvotes: 0

Views: 1584

Answers (2)

Nicholas
Nicholas

Reputation: 1714

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.

Upvotes: 1

Herman
Herman

Reputation: 2072

What is the error message? It should be something like:

cursor.execute( "INSERT INTO database.table VALUES (%s, %s, %s, %s, %s)", row[0:5])

Upvotes: 0

Related Questions