Fetching data from BigQuery and inserting into MySQL

[truptanan...@homedepot.com]

My Python program connects to big query and fetching data which I want to insert into a mysql table. 

Its successfully fetching the results from bigquery. Its also successfully connecting to mysql DB. but its not inserting the data I see its complaining for the row[1] . 

Whats the right way to insert the values from bigquery response into mysql table columns.

I was following the sample code @ https://cloud.google.com/bigquery/create-simple-app-api#completecode  but my requirement is not to pring but to insert the data into mysql table/

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]');")

Traceback (most recent call last): File "./myfile.py", line 32, in <module> cursor.execute ("INSERT INTO database.datable VALUES ('row[0]','row[1]','row[2]','row[3]','row[4]');") File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue_mysql_exceptions.OperationalError: (1366, "Incorrect integer value: 'row[0]' for column 'CountAll' at row 1")

Also, I tried to use 

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

Traceback (most recent call last): File "./myfile.py", line 32, in <module> cursor.execute ("INSERT INTO database.table VALUES (%s,%s,%s,%s,%s);") File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s,%s,%s,%s,%s)' at line 1")

or 

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

Traceback (most recent call last): File "./myfile.py", line 32, in <module> cursor.execute ("INSERT INTO database.table VALUES (row[0],row[1],row[2],row[3],row[4]);") File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[0],row[1],row[2],row[3],row[4])' at line 1")

But in all it fails while inserting values in mysql table

[Nicholas (Google Cloud Support)]

Good day and thanks sharing posting your questions here!

Assuming your cursor is a MySQLCursor, I might suggest reviewing the documentation for the execute method.  Based on the errors you've encountered, the issue lies with cursor.execute() method.  If you don't mind, I'll go through each of your attempts to point out what is likely causing the errors you're seeing.

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

The resulting SQL statement here will insert a new record with 5 string literals as values: 'row[0]', 'row[1]', 'row[2]', 'row[3]', 'row[4]'

These literals will not be replaced with the values found at row[0], row[1], etc.

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

The syntax here is correct.  The main issue is that the execute method attempts to replace occurrences of %s with values provided to its second argument.  In this case cursor.execute is only invoked with 1 argument.  You've not provided any values.

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

This statement throws a syntax error because row[0], row[1], etc. are not known variables to the SQL instance.  The row list only exists in python at this point.

I believe what you might be looking for is something more like this:

# prepare the statement
insert_statement = 'INSERT INTO database.tables VALUES (%s,%s, %s, %s, %s);'

# loop through each BQ row

for row in query_response['rows']:

    # prepare the set of values
    # strongly advise sanitizing the values before inserting
    # type checks, value checks, SQL injection checks, etc.
    values_to_insert = (

        row[0],
        row[1],
        row[2],
        row[3],
        row[4])

    # insert data
    cursor.execute(insert_statement, values_to_insert)

Hope this helps!

[truptanan...@homedepot.com]

Hi Nicholas,

Thank you very much for the detail note. I tried to use your suggested method but thats again throwing below error

[root@myserver ~]# python myfile.py 

Traceback (most recent call last):

  File "myfile.py", line 27, in <module>

    row[0],

KeyError: 0

[root@myserver ~]# 

When I used  print('\t'.join(field['v'] for field in row['f']))

it does print the output : ( These are the values fetched from my BigQuery response. The BigQuery code is taken from the google site https://cloud.google.com/bigquery/create-simple-app-api#completecode )

15658 53.35023630093262 221.0 237.0 436.0

My requirement is insert these values to various columns in the MySQL database table. I have tested the connection and able to describe the table within my python program.

[Nicholas (Google Cloud Support)]

At this point, I would strongly suggest posting this as a question on Stack Overflow as that's a far better forum for this type of code debugging.  Be sure to provide a complete code sample stack trace so that the Stack Exchange community is best equipped to help.  Then post a link to your Stack Overflow question here so that others encountering this post can follow through.

This forum is more appropriate for general discussions, announcements and sharing of beset practices.

On Tuesday, June 14, 2016 at 3:31:02 PM UTC-4, truptanan...@homedepot.com wrote:

[truptanan...@homedepot.com]

Hi Nicholas,

Here is the Stack Overflow link for this 

http://stackoverflow.com/questions/37817042/how-to-insert-values-into-mysql-table-from-another-bigquery-response

Thanks

[Rob]

You can also use something like ParaSQL Hyper Connect Engine to link the servers directly... so you can write a statement like:

insert into MySQLTable

select a,b,c from MBigQueryTable where ....

This is far more efficient as the data doesn't have to go to the client app and then back to the database (this would be more like a database to database copy).

On Tuesday, June 14, 2016 at 3:31:02 PM UTC-4, truptanan...@homedepot.com wrote: