MySQL SSL Connection, Need help - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: MySQL SSL Connection, Need help (/thread-5670.html) |
MySQL SSL Connection, Need help - LordVaderXIII - Oct-15-2017 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. 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!" RE: MySQL SSL Connection, Need help - LordVaderXIII - Oct-16-2017 I have found out that xlrd is for older excel docs so Im now using openpyxl Im still getting errors though: AttributeError: 'Worksheet' object has no attribute 'nrows' #import xlrd import openpyxl import MySQLdb # Open the workbook and define the worksheet wb = openpyxl.load_workbook("MINFOS.DailyTTL.xlsx") ws = wb.get_sheet_by_name("PivotEx") # Establish a MySQL connection ssl = {"cert":"/home/xxxx/certs/client-cert.pem", "key":"/home/xxxx/certs/client-key.pem", "ca":"/home/xxxx/certs/server-ca.pem"} database = MySQLdb.connect(host="xxxx", user="root", passwd="xxxx", db="manila_data", 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, ws.nrows): StoreID = ws.cell(r, 0).value Date = ws.cell(r, 1).value StoreName = ws.cell(r, 2).value CreateDate = ws.cell(r, 3).value Category = ws.cell(r, 4).value Scripts = ws.cell(r, 5).value Gross = ws.cell(r, 6).value Gst = ws.cell(r, 7).value CoPayDiscount = ws.cell(r, 8).value Net = ws.cell(r, 9).value CustomerPayments = ws.cell(r, 10).value GovernmentPayments = ws.cell(r, 11).value PremiumFreeIncentive = ws.cell(r, 12).value IncentiveOnline = ws.cell(r, 13).value EPF = ws.cell(r, 14).value rdPartyPayments = ws.cell(r, 15).value COGS = ws.cell(r, 16).value Gpdollar = ws.cell(r, 17).value Gppercent = ws.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(ws.ncols) #rows = str(ws.nrows) #print "I just imported " %2B columns %2B " columns and " %2B rows %2B " rows to MySQL!" RE: MySQL SSL Connection, Need help - buran - Oct-16-2017 well. Worksheet object does not have nrows property. use help on Worksheet to see available properties and methods you can use iter_rows() method to iterate over the rows of a worksheet. You can specify start/end column/row, etc. There is also rows property as a shorthand, but with it you iterate over all rows
RE: MySQL SSL Connection, Need help - LordVaderXIII - Oct-16-2017 (Oct-16-2017, 07:07 AM)buran Wrote: well. Worksheet object does not have nrows property. Thank you very much for the help. I will give that a tr. |