MySQL SSL Connection, Need help

[LordVaderXIII]

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!"

[LordVaderXIII]

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!"

[buran]

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

[LordVaderXIII]

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

Thank you very much for the help. I will give that a tr.