Python Data Insert From Beautiful Soup Table to MYSQL
Question: I am trying to insert data into a MYSQL table from a html table using
beautiful soup written in python. Currently I was able to do this by creating
an interim csv file but it does not seems to work in Linux and seems
inefficient. Below is my code that works with the CSV, which is the portion I
will like to elimnate.

    
    
    import urllib2, base64, csv
    from bs4 import BeautifulSoup
    request = urllib2.Request("http://website.com")
    base64string = base64.encodestring('%s:%s' % ('username','password')).replace('\n','')
    request.add_header("Authorization", "Basic %s" % base64string)   
    result = urllib2.urlopen(request)
    soup = BeautifulSoup(result.read())
    
    table=soup.findAll('table')[6]
    
    f = open('output.csv', 'w')
    
    for row in table.findAll('tr'):
        cells = row.findAll('td')
        #For each "tr", assign each "td" to a variable.
        if len(cells) == 19:
        column1 = cells[0].find(text=True)
        column2 = cells[1].find(text=True)
        column3 = cells[2].find(text=True)
        column4 = cells[3].find(text=True)
        column5 = cells[4].find(text=True)
        column6 = cells[5].find(text=True)
        column7 = cells[6].find(text=True)
        column8 = cells[7].find(text=True)
        column9 = cells[8].find(text=True)
        column10 = cells[9].find(text=True)
        column11 = cells[10].find(text=True)
        column12 = cells[11].find(text=True)
        column13 = cells[12].find(text=True)
        column14 = cells[13].find(text=True)
        column15 = cells[14].find(text=True)
        column16 = cells[15].find(text=True)
        column17 = cells[16].find(text=True)
        column18 = cells[17].find(text=True)
        column19 = cells[18].find(text=True)
        TOTAL_AD_CALLS = column6.replace(',','')
        TOTAL_US_AD_CALLS = column7.replace(',','')
        TOTAL_NON_US_AD_CALLS = column8.replace(',','')
        TOTAL_NON_CLEAN_US_AD_CALLS = column10.replace(',','')
        TOTAL_CLEAN_US_AD_CALLS = column12.replace(',','')
        UNUSABLE_AD_CALLS = column13.replace(',','')
        NO_ADS_RETURNED = column14.replace(',','')
        PSAS_RETURNED = column15.replace(',','')
        ADS_RETURNED = column16.replace(',','')
        TOTAL_RETURNED = column17.replace(',','')
        TOTAL_IMPRESSIONS  = column18.replace(',','')
        Engagements = column19.replace(',','')
        NOT_US_RATE = column9.replace('%','')
        NOT_CLEAN_RATE = column11.replace('%','')
    
    
    
    #district can be a list of lists, so we want to iterate through the top level lists first...
    
                write_to_file = column1 + "," + column2 + "," + column3 + "," + column4 + "," + column5 + "," + TOTAL_AD_CALLS + "," + TOTAL_US_AD_CALLS + "," + TOTAL_NON_US_AD_CALLS + "," + NOT_US_RATE + "," + TOTAL_NON_CLEAN_US_AD_CALLS + "," + NOT_CLEAN_RATE + "," + TOTAL_CLEAN_US_AD_CALLS + "," + UNUSABLE_AD_CALLS + "," + NO_ADS_RETURNED + "," + PSAS_RETURNED + "," + ADS_RETURNED + "," + TOTAL_RETURNED + "," +  TOTAL_IMPRESSIONS  + "," +  Engagements + "\n"
                print write_to_file
                f.write(write_to_file)
    f.close()
    
    
    
    import MySQLdb
    import os
    import string
    
    # Open database connection
    db = MySQLdb.connect(host="ipadress", # your host, usually localhost
                     user="admin", # your username
                      passwd="other", # your password
                      db="dailies") # name of the data base
    
    cursor=db.cursor()
    
    #Query under testing
    sql = """LOAD DATA LOCAL INFILE 'output.csv' \
          INTO TABLE PYTHON_TEST \
          FIELDS TERMINATED BY ',' \
          OPTIONALLY ENCLOSED BY '"'  \
          LINES TERMINATED BY '\r\n' \
          IGNORE 0 LINES;;"""
    
      #LINES TERMINATED BY '\r\n' \
    
    try:
        # Execute the SQL command
        cursor.execute(sql)
        # Commit your changes in the database
        db.commit()
    except:
       # Rollback in case there is any error
       db.rollback()
    
    # disconnect from server
    db.close()
    


Answer: I was able to figure it out. If anyone else as a similar problem hope this
helps.

    
    
    import urllib2, base64, csv
    from bs4 import BeautifulSoup
    import MySQLdb
    import os
    import string
    import datetime
    
    request = urllib2.Request("website.com")
    base64string = base64.encodestring('%s:%s' % ('username','password')).replace('\n', '')
    request.add_header("Authorization", "Basic %s" % base64string)   
    result = urllib2.urlopen(request)
    soup = BeautifulSoup(result.read())
    
    #print(soup.prettify())
    #table=soup.find('table', {"class":"resultsTable,ruler,sortable"})[0]
    
    
    # Open database connection
    db = MySQLdb.connect(host="ipaddress", # your host, usually localhost
                         user="username", # your username
                          passwd="password", # your password
                          db="databsae") # name of the data base
    
    cursor=db.cursor()
    
    table=soup.findAll('table')[6]
    
    #print table
    
    for row in table.findAll('tr'):
        cells = row.findAll('td')
        #For each "tr", assign each "td" to a variable.
        if len(cells) == 19:
            column1 = cells[0].find(text=True)
            column2 = cells[1].find(text=True)
            column3 = cells[2].find(text=True)
            column4 = cells[3].find(text=True)
            column5 = cells[4].find(text=True)
            column6 = cells[5].find(text=True)
            column7 = cells[6].find(text=True)
            column8 = cells[7].find(text=True)
            column9 = cells[8].find(text=True)
            column10 = cells[9].find(text=True)
            column11 = cells[10].find(text=True)
            column12 = cells[11].find(text=True)
            column13 = cells[12].find(text=True)
            column14 = cells[13].find(text=True)
            column15 = cells[14].find(text=True)
            column16 = cells[15].find(text=True)
            column17 = cells[16].find(text=True)
            column18 = cells[17].find(text=True)
            column19 = cells[18].find(text=True)
            TOTAL_AD_CALLS = column6.replace(',','')
            TOTAL_US_AD_CALLS = column7.replace(',','')
            TOTAL_NON_US_AD_CALLS = column8.replace(',','')
            TOTAL_NON_CLEAN_US_AD_CALLS = column10.replace(',','')
            TOTAL_CLEAN_US_AD_CALLS = column12.replace(',','')
            UNUSABLE_AD_CALLS = column13.replace(',','')
            NO_ADS_RETURNED = column14.replace(',','')
            PSAS_RETURNED = column15.replace(',','')
            ADS_RETURNED = column16.replace(',','')
            TOTAL_RETURNED = column17.replace(',','')
            TOTAL_IMPRESSIONS  = column18.replace(',','')
            Engagements = column19.replace(',','')
            NOT_US_RATE = column9.replace('%','')
            NOT_CLEAN_RATE = column11.replace('%','')
            Created = datetime.datetime.now()
    
    print Engagements
    
    
    cursor.execute ("INSERT INTO PYTHON_TEST (REPORT_TYPE, THE_DATE, PARENT_ID, SITE_ID,SITE_NAME, TOTAL_AD_CALLS, TOTAL_US_AD_CALLS, TOTAL_NON_US_AD_CALLS, NOT_US_RATE, TOTAL_NON_CLEAN_US_AD_CALLS, NOT_CLEAN_RATE, TOTAL_CLEAN_US_AD_CALLS, UNUSABLE_AD_CALLS, NO_ADS_RETURNED, PSAS_RETURNED, ADS_RETURNED, TOTAL_RETURNED, TOTAL_IMPRESSIONS, ENGAGEMENTS, CREATED) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);", (column1, column2, column3, column4, column5, TOTAL_AD_CALLS, TOTAL_US_AD_CALLS, TOTAL_NON_US_AD_CALLS, NOT_US_RATE ,TOTAL_NON_CLEAN_US_AD_CALLS, NOT_CLEAN_RATE, TOTAL_CLEAN_US_AD_CALLS, UNUSABLE_AD_CALLS, NO_ADS_RETURNED, PSAS_RETURNED, ADS_RETURNED, TOTAL_RETURNED, TOTAL_IMPRESSIONS, Engagements, Created))
    
    #cursor.execute ("INSERT INTO PYTHON_TEST (ENGAGEMENTS,434 ) VALUES (%s);", (Engagements))
    db.commit()
    '''
    cursor.execute ("INSERT INTO PYTHON_TEST  VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);", (column1, column2, column3, column4, column5, TOTAL_AD_CALLS, TOTAL_US_AD_CALLS, TOTAL_NON_US_AD_CALLS, NOT_US_RATE ,TOTAL_NON_CLEAN_US_AD_CALLS, NOT_CLEAN_RATE, TOTAL_CLEAN_US_AD_CALLS, UNUSABLE_AD_CALLS, NO_ADS_RETURNED, PSAS_RETURNED, ADS_RETURNED, TOTAL_RETURNED, TOTAL_IMPRESSIONS, Engagements, Created, Created))'''
    
    
    # disconnect from server
    db.close()
    

