Oct-15-2017, 10:51 PM
Hey Everyone
I have a project that requires uploading excel files to a Google Cloud SQL Database (MySQL).
I have the below code that I have borrowed but I cant seem to get it to connect via SSL. Could someone help me get it working please? Disclaimer, I have never coded in Python before.
I have a project that requires uploading excel files to a Google Cloud SQL Database (MySQL).
I have the below code that I have borrowed but I cant seem to get it to connect via SSL. Could someone help me get it working please? Disclaimer, I have never coded in Python before.
import xlrd import MySQLdb # Open the workbook and define the worksheet book = xlrd.open_workbook("excel.xlsx") sheet = book.sheet_by_name("PivotEx") # Establish a MySQL connection ssl = {ssl_cert : "/home/user/certs/client-cert.pem", ssl_key : "/home/user/certs/client-key.pem", ssl_ca : "/home/user/certs/server-ca.pem"} database = MySQLdb.connect (host = "privateIP", user = "root", passwd = "rootpassword", db = "DBName", ssl = ssl ) # Get the cursor, which is used to traverse the database, line by line cursor = database.cursor() # Create the INSERT INTO sql query query = """INSERT INTO test (StoreID, Date, StoreName, CreateDate, Category, Scripts, Gross, Gst, CoPayDiscount, Net, CustomerPayments, GovernmentPayments, PremiumFreeIncentive, IncentiveOnline, EPF, rdPartyPayments, COGS, Gpdollar, Gppercent) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""" # Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers for r in range(1, sheet.nrows): StoreID = sheet.cell(r,0).value Date = sheet.cell(r,1).value StoreName = sheet.cell(r,2).value CreateDate = sheet.cell(r,3).value Category = sheet.cell(r,4).value Scripts = sheet.cell(r,5).value Gross = sheet.cell(r,6).value Gst = sheet.cell(r,7).value CoPayDiscount = sheet.cell(r,8).value Net = sheet.cell(r,9).value CustomerPayments = sheet.cell(r,10).value GovernmentPayments = sheet.cell(r,11).value PremiumFreeIncentive = sheet.cell(r,12).value IncentiveOnline = sheet.cell(r,13).value EPF = sheet.cell(r,14).value rdPartyPayments = sheet.cell(r,15).value COGS = sheet.cell(r,16).value Gpdollar = sheet.cell(r,17).value Gppercent = sheet.cell(r,18).value # Assign values from each row values = (StoreID, Date, StoreName, CreateDate, Category, Scripts, Gross, Gst, CoPayDiscount, Net, CustomerPayments, GovernmentPayments, PremiumFreeIncentive, IncentiveOnline, EPF, rdPartyPayments, COGS, Gpdollar, Gppercent) # Execute sql Query cursor.execute(query, values) # Close the cursor cursor.close() # Commit the transaction database.commit() # Close the database connection database.close() # Print results print "" print "All Done! Bye, for now." print "" columns = str(sheet.ncols) rows = str(sheet.nrows) #print "I just imported " %2B columns %2B " columns and " %2B rows %2B " rows to MySQL!"