[google-cloud-sql-discuss] Re: Fetching data from BigQuery and inserting into MySQL

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!

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

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

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b29a0057-cacc-44c2-82d6-bf59b31215a6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.