Exemple #1
0
def doDailyClubs(infile, conn, cdate, firsttime=False):
    """ infile is a file-like object """
    global changecount
    from datetime import datetime, timedelta

    curs = conn.cursor()
    reader = csv.reader(infile)

    hline = next(reader)
    headers = cleanheaders(hline)

    try:
        clubcol = headers.index("clubnumber")
    except ValueError:
        if not hline[0].startswith('{"Message"'):
            print("'clubnumber' not in '%s'" % hline)
        return

    try:
        prospectiveclubcol = headers.index("prospectiveclub")
    except ValueError:
        prospectiveclubcol = False

    # Find out what fields we have in the database itself
    dbfields = []
    curs.execute("describe clubs")
    for l in curs.fetchall():
        dbfields.append(l[0])

    inform("clubs for", cdate, suppress=1)
    dbheaders = [p for p in headers]

    # Convert between Toastmasters' names for address and location and ours; they've changed it a few times.  *sigh*
    if "address1" in dbheaders:
        addrcol1 = dbheaders.index("address1")
    else:
        addrcol1 = dbheaders.index("location")
    if "address2" in dbheaders:
        addrcol2 = dbheaders.index("address2")
    else:
        addrcol2 = dbheaders.index("address")

    dbheaders[addrcol1] = "place"
    dbheaders[addrcol2] = "address"
    expectedheaderscount = len(dbheaders)
    dbheaders.append("firstdate")
    dbheaders.append("lastdate")  # For now...

    areacol = dbheaders.index("area")
    divisioncol = dbheaders.index("division")
    statecol = dbheaders.index("state")

    # Now, suppress anything in the file that's not in the database:
    suppress = []
    oldheaders = dbheaders
    dbheaders = []
    for i in range(len(oldheaders)):
        if oldheaders[i] in dbfields:
            dbheaders.append(oldheaders[i])
        else:
            suppress.append(i)
    suppress.reverse()  # We remove these columns from the input

    Club.setfieldnames(dbheaders)

    # We need to get clubs for the most recent update so we know whether to update an entry
    #   or start a new one.
    yesterday = datetime.strftime(
        datetime.strptime(cdate, "%Y-%m-%d") - timedelta(1), "%Y-%m-%d")
    clubhist = Club.getClubsOn(curs, date=yesterday)

    for row in reader:
        if len(row) < expectedheaderscount:
            break  # we're finished
        if prospectiveclubcol is not None and row[prospectiveclubcol]:
            continue  # Ignore prospective clubs

        for i in suppress:
            del row[i]

        if len(row) > expectedheaderscount:
            # Special case...Millbrae somehow snuck two club websites in!
            row[16] = row[16] + "," + row[17]
            del row[17]

        # print row[addrcol1]
        # print row[addrcol2]
        # Now, clean up the address:
        # Address line 1 is "place" information and can be multiple lines.
        # Address line 2 is the real address and should be treated as one line, with spaces normalized.
        place = "\n".join([x.strip() for x in row[addrcol1].split("  ")])
        row[addrcol1] = place
        address = normalize(row[addrcol2])
        row[addrcol2] = address

        # Toastmasters is currently reversing the "Area" and "Division" items.  "Area" should be a
        #    number; if not, swap the two.
        try:
            thearea = row[areacol]
            thedivision = row[divisioncol]
            areanum = int(row[areacol])
        except ValueError:
            row[areacol] = thedivision
            row[divisioncol] = thearea

        # Collapse state names into their abbreviations
        if row[statecol] in statelist:
            row[statecol] = statelist[row[statecol]]

        # Get the right number of items into the row by setting today as the
        #   tentative first and last date
        row.append(cdate)
        row.append(cdate)

        # And create the object
        club = Club(row)

        # Now, clean up things coming from Toastmasters

        if club.clubstatus.startswith("Open") or club.clubstatus.startswith(
                "None"):
            club.clubstatus = "Open"
        else:
            club.clubstatus = "Restricted"

        # Clean up the club and district numbers and the area
        club.clubnumber = cleanitem(club.clubnumber)
        club.district = cleanitem(club.district)
        club.area = cleanitem(club.area)

        # If a club is partially unassigned, mark it as completely unassigned.
        if ((club.area == "0A") or (club.area == "0D")
                or (club.division == "0D") or (club.division == "0A")):
            club.area = "0A"
            club.division = "0D"

        # Clean up the charter date
        if not club.charterdate.strip():
            continue  # This is a prospective club that Toastmasters didn't mark properly; ignore it.
        club.charterdate = cleandate(club.charterdate)

        # Clean up advanced status
        club.advanced = "1" if (club.advanced != "") else "0"

        # Clean up online status
        club.allowsonlineattendance = ("1" if
                                       (club.allowsonlineattendance != "") else
                                       "0")

        # Add missing schemes to any URLs
        club.fixURLSchemes()

        # Now, take care of missing latitude/longitude
        if ("latitude") in dbheaders:
            try:
                club.latitude = float(club.latitude)
            except ValueError:
                club.latitude = 0.0
        else:
            club.latitude = 0.0

        if ("longitude") in dbheaders:
            try:
                club.longitude = float(club.longitude)
            except ValueError:
                club.longitude = 0.0
        else:
            club.longitude = 0.0

        # Sometimes, Toastmasters gets the latitude and longitude backwards
        # If that turns out to create an impossible location (which it will in California),
        #    let's swap them.
        if abs(club.latitude) > 90.0:
            (club.latitude, club.longitude) = (club.longitude, club.latitude)

        # And put it into the database if need be
        if club.clubnumber in clubhist:
            changes = different(club, clubhist[club.clubnumber],
                                dbheaders[:-2])
        else:
            changes = []

        if club.clubnumber not in clubhist and not firsttime:
            # This is a new (or reinstated) club; note it in the changes database.
            curs.execute(
                'INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, "New Club", "", "")',
                (club.clubnumber, cdate),
            )

        if club.clubnumber not in clubhist or changes:
            club.firstdate = club.lastdate
            # Encode newlines in the place as double-semicolons for the database
            club.place = club.place.replace("\n", ";;")
            values = [club.__dict__[x] for x in dbheaders]

            # And then put the place back into normal form
            club.place = club.place.replace(";;", "\n")

            thestr = ("INSERT IGNORE INTO clubs (" + ",".join(dbheaders) +
                      ") VALUES (" + ",".join(["%s"
                                               for each in values]) + ");")

            try:
                changecount += curs.execute(thestr, values)
            except Exception as e:
                print(e)
            # Capture changes
            for (item, old, new) in changes:
                if item == "place":
                    # Clean up the place (old and new) for the database
                    old = old.replace("\n", ";;")
                    new = new.replace("\n", ";;")
                try:
                    curs.execute(
                        "INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, %s, %s, %s)",
                        (club.clubnumber, cdate, item, old, new),
                    )
                except Exception as e:
                    print(e)
            clubhist[club.clubnumber] = club
            if different(club, clubhist[club.clubnumber], dbheaders[:-2]):
                print("it's different after being set.")
                sys.exit(3)
        else:
            # update the lastdate
            changecount += curs.execute(
                "UPDATE clubs SET lastdate = %s WHERE clubnumber = %s AND lastdate = %s;",
                (cdate, club.clubnumber, clubhist[club.clubnumber].lastdate),
            )

    # If all the files were processed, today's work is done.
    curs.execute(
        'INSERT IGNORE INTO loaded (tablename, loadedfor) VALUES ("clubs", %s)',
        (cdate, ),
    )
Exemple #2
0
def doDailyClubs(infile, conn, cdate, firsttime=False):
    """ infile is a file-like object """
    global changecount
    from datetime import datetime, timedelta
    
    curs = conn.cursor()
    reader = csv.reader(infile)

    hline = reader.next()
    headers = cleanheaders(hline)

    try:
        clubcol = headers.index('clubnumber')    
    except ValueError:
        if not hline[0].startswith('{"Message"'):
            print "'clubnumber' not in '%s'" % hline
        return
        
    try:
        prospectiveclubcol = headers.index('prospectiveclub')
    except ValueError:
        prospectiveclubcol = False
        
    # Find out what fields we have in the database itself
    dbfields = []
    curs.execute("describe clubs")
    for l in curs.fetchall():
        dbfields.append(l[0])
        
    inform("clubs for", cdate, suppress=1)
    dbheaders = [p for p in headers]
    
    # Convert between Toastmasters' names for address and location and ours; they've changed it a few times.  *sigh*
    if 'address1' in dbheaders:
        addrcol1 = dbheaders.index('address1')
    else:
        addrcol1 = dbheaders.index('location')
    if 'address2' in dbheaders:
        addrcol2 = dbheaders.index('address2')
    else:
        addrcol2 = dbheaders.index('address')
        
    dbheaders[addrcol1] = 'place'
    dbheaders[addrcol2] = 'address'
    expectedheaderscount = len(dbheaders)
    dbheaders.append('firstdate')
    dbheaders.append('lastdate')     # For now...
    
    areacol = dbheaders.index('area')
    divisioncol = dbheaders.index('division')
    statecol = dbheaders.index('state')
    
    # Now, suppress anything in the file that's not in the database:
    suppress = []
    oldheaders = dbheaders
    dbheaders = []
    for i in xrange(len(oldheaders)):
        if oldheaders[i] in dbfields:
            dbheaders.append(oldheaders[i])
        else:
            suppress.append(i)
    suppress.reverse()   # We remove these columns from the input
    
        
    Club.setfieldnames(dbheaders)

    # We need to get clubs for the most recent update so we know whether to update an entry 
    #   or start a new one.
    yesterday = datetime.strftime(datetime.strptime(cdate, '%Y-%m-%d') - timedelta(1),'%Y-%m-%d')
    clubhist = Club.getClubsOn(curs, date=yesterday)
   
    for row in reader:
        if len(row) < expectedheaderscount:
            break     # we're finished
        if prospectiveclubcol is not None and row[prospectiveclubcol]:
            continue   # Ignore prospective clubs

        for i in suppress:
            del row[i]

        # Convert to unicode.  Toastmasters usually uses UTF-8 but occasionally uses Windows CP1252 on the wire.
        try:
            row = [unicode(t.strip(), "utf8") for t in row]
        except UnicodeDecodeError:
            row = [unicode(t.strip(), "CP1252") for t in row]
         
        if len(row) > expectedheaderscount:
            # Special case...Millbrae somehow snuck two club websites in!
            row[16] = row[16] + ',' + row[17]
            del row[17]
            
        #print row[addrcol1]
        #print row[addrcol2]
        # Now, clean up the address:
        # Address line 1 is "place" information and can be multiple lines.
        # Address line 2 is the real address and should be treated as one line, with spaces normalized.
        place = '\n'.join([x.strip() for x in row[addrcol1].split('  ')]) 
        row[addrcol1] = place
        address = normalize(row[addrcol2])
        row[addrcol2] = address
        
        # Toastmasters is currently reversing the "Area" and "Division" items.  "Area" should be a
        #    number; if not, swap the two.
        try:
            thearea = row[areacol]
            thedivision = row[divisioncol]
            areanum = int(row[areacol])
        except ValueError:
            row[areacol] = thedivision
            row[divisioncol] = thearea
            
        # Collapse state names into their abbreviations
        if row[statecol] in statelist:
            row[statecol] = statelist[row[statecol]]
            
        # Get the right number of items into the row by setting today as the 
        #   tentative first and last date
        row.append(cdate)
        row.append(cdate)
        
        # And create the object
        club = Club(row)



        
        # Now, clean up things coming from Toastmasters

        if club.clubstatus.startswith('Open') or club.clubstatus.startswith('None'):
            club.clubstatus = 'Open'
        else:
            club.clubstatus = 'Restricted'
        
        # Clean up the club and district numbers and the area
        club.clubnumber = cleanitem(club.clubnumber)
        club.district = cleanitem(club.district)
        club.area = cleanitem(club.area)

        
        # If a club is partially unassigned, mark it as completely unassigned.
        if (club.area == '0A') or (club.area == '0D') or (club.division == '0D') or (club.division == '0A'):
            club.area = '0A'
            club.division = '0D'

    
        # Clean up the charter date
        club.charterdate = cleandate(club.charterdate)

    
        # Clean up advanced status
        club.advanced = '1' if (club.advanced != '') else '0'
        
        # Clean up online status
        club.allowsonlineattendance = '1' if (club.allowsonlineattendance != '') else '0'
    
        # Now, take care of missing latitude/longitude
        if ('latitude') in dbheaders:
            try:
                club.latitude = float(club.latitude)
            except ValueError:
                club.latitude = 0.0
        else:
            club.latitude = 0.0
            
        if ('longitude') in dbheaders:
           try:
               club.longitude = float(club.longitude)
           except ValueError:
               club.longitude = 0.0
        else:
           club.longitude = 0.0
           
        # Sometimes, Toastmasters gets the latitude and longitude backwards
        # If that turns out to create an impossible location (which it will in California),
        #    let's swap them.
        if abs(club.latitude) > 90.0:
            (club.latitude, club.longitude) = (club.longitude, club.latitude)

        # And put it into the database if need be
        if club.clubnumber in clubhist:
            changes = different(club, clubhist[club.clubnumber], dbheaders[:-2])
        else:
            changes = []

        
        if club.clubnumber not in clubhist and not firsttime:
            # This is a new (or reinstated) club; note it in the changes database.
            curs.execute('INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, "New Club", "", "")', (club.clubnumber, cdate))
                
        if club.clubnumber not in clubhist or changes:
            club.firstdate = club.lastdate
            # Encode newlines in the place as double-semicolons for the database
            club.place = club.place.replace('\n',';;')
            values = [club.__dict__[x] for x in dbheaders]
            
            # And then put the place back into normal form
            club.place = club.place.replace(';;','\n')
            
        
            thestr = 'INSERT IGNORE INTO clubs (' + ','.join(dbheaders) + ') VALUES (' + ','.join(['%s' for each in values]) + ');'

            try:
                changecount += curs.execute(thestr, values)
            except Exception, e:
                print e
            # Capture changes
            for (item, old, new) in changes:
                if (item == 'place'):
                    # Clean up the place (old and new) for the database
                    old = old.replace('\n', ';;')
                    new = new.replace('\n', ';;')
                try:
                    curs.execute('INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, %s, %s, %s)', (club.clubnumber, cdate, item, old, new))
                except Exception, e:
                     print e
            clubhist[club.clubnumber] = club
            if different(club, clubhist[club.clubnumber], dbheaders[:-2]):
                print 'it\'s different after being set.'
                sys.exit(3) 
Exemple #3
0
def doDailyClubs(infile, conn, cdate, firsttime=False):
    """ infile is a file-like object """
    global changecount
    from datetime import datetime, timedelta

    curs = conn.cursor()
    reader = csv.reader(infile)

    hline = next(reader)
    headers = cleanheaders(hline)

    try:
        clubcol = headers.index("clubnumber")
    except ValueError:
        if not hline[0].startswith('{"Message"'):
            print("'clubnumber' not in '%s'" % hline)
        return

    try:
        prospectiveclubcol = headers.index("prospectiveclub")
    except ValueError:
        prospectiveclubcol = False

    # Find out what fields we have in the database itself
    dbfields = []
    curs.execute("describe clubs")
    for l in curs.fetchall():
        dbfields.append(l[0])

    inform("clubs for", cdate, suppress=1)
    dbheaders = [p for p in headers]

    # Convert between Toastmasters' names for address and location and ours; they've changed it a few times.  *sigh*
    if "address1" in dbheaders:
        addrcol1 = dbheaders.index("address1")
    else:
        addrcol1 = dbheaders.index("location")
    if "address2" in dbheaders:
        addrcol2 = dbheaders.index("address2")
    else:
        addrcol2 = dbheaders.index("address")

    dbheaders[addrcol1] = "place"
    dbheaders[addrcol2] = "address"
    expectedheaderscount = len(dbheaders)
    dbheaders.append("firstdate")
    dbheaders.append("lastdate")  # For now...

    areacol = dbheaders.index("area")
    divisioncol = dbheaders.index("division")
    statecol = dbheaders.index("state")

    # Now, suppress anything in the file that's not in the database:
    suppress = []
    oldheaders = dbheaders
    dbheaders = []
    for i in range(len(oldheaders)):
        if oldheaders[i] in dbfields:
            dbheaders.append(oldheaders[i])
        else:
            suppress.append(i)
    suppress.reverse()  # We remove these columns from the input

    Club.setfieldnames(dbheaders)

    # We need to get clubs for the most recent update so we know whether to update an entry
    #   or start a new one.
    yesterday = datetime.strftime(
        datetime.strptime(cdate, "%Y-%m-%d") - timedelta(1), "%Y-%m-%d"
    )
    clubhist = Club.getClubsOn(curs, date=yesterday)

    for row in reader:
        if len(row) < expectedheaderscount:
            break  # we're finished
        if prospectiveclubcol is not None and row[prospectiveclubcol]:
            continue  # Ignore prospective clubs

        for i in suppress:
            del row[i]


        if len(row) > expectedheaderscount:
            # Special case...Millbrae somehow snuck two club websites in!
            row[16] = row[16] + "," + row[17]
            del row[17]

        # print row[addrcol1]
        # print row[addrcol2]
        # Now, clean up the address:
        # Address line 1 is "place" information and can be multiple lines.
        # Address line 2 is the real address and should be treated as one line, with spaces normalized.
        place = "\n".join([x.strip() for x in row[addrcol1].split("  ")])
        row[addrcol1] = place
        address = normalize(row[addrcol2])
        row[addrcol2] = address

        # Toastmasters is currently reversing the "Area" and "Division" items.  "Area" should be a
        #    number; if not, swap the two.
        try:
            thearea = row[areacol]
            thedivision = row[divisioncol]
            areanum = int(row[areacol])
        except ValueError:
            row[areacol] = thedivision
            row[divisioncol] = thearea

        # Collapse state names into their abbreviations
        if row[statecol] in statelist:
            row[statecol] = statelist[row[statecol]]

        # Get the right number of items into the row by setting today as the
        #   tentative first and last date
        row.append(cdate)
        row.append(cdate)

        # And create the object
        club = Club(row)

        # Now, clean up things coming from Toastmasters

        if club.clubstatus.startswith("Open") or club.clubstatus.startswith("None"):
            club.clubstatus = "Open"
        else:
            club.clubstatus = "Restricted"

        # Clean up the club and district numbers and the area
        club.clubnumber = cleanitem(club.clubnumber)
        club.district = cleanitem(club.district)
        club.area = cleanitem(club.area)

        # If a club is partially unassigned, mark it as completely unassigned.
        if (
            (club.area == "0A")
            or (club.area == "0D")
            or (club.division == "0D")
            or (club.division == "0A")
        ):
            club.area = "0A"
            club.division = "0D"

        # Clean up the charter date
        club.charterdate = cleandate(club.charterdate)

        # Clean up advanced status
        club.advanced = "1" if (club.advanced != "") else "0"

        # Clean up online status
        club.allowsonlineattendance = (
            "1" if (club.allowsonlineattendance != "") else "0"
        )

        # Add missing schemes to any URLs
        club.fixURLSchemes()

        # Now, take care of missing latitude/longitude
        if ("latitude") in dbheaders:
            try:
                club.latitude = float(club.latitude)
            except ValueError:
                club.latitude = 0.0
        else:
            club.latitude = 0.0

        if ("longitude") in dbheaders:
            try:
                club.longitude = float(club.longitude)
            except ValueError:
                club.longitude = 0.0
        else:
            club.longitude = 0.0

        # Sometimes, Toastmasters gets the latitude and longitude backwards
        # If that turns out to create an impossible location (which it will in California),
        #    let's swap them.
        if abs(club.latitude) > 90.0:
            (club.latitude, club.longitude) = (club.longitude, club.latitude)

        # And put it into the database if need be
        if club.clubnumber in clubhist:
            changes = different(club, clubhist[club.clubnumber], dbheaders[:-2])
        else:
            changes = []

        if club.clubnumber not in clubhist and not firsttime:
            # This is a new (or reinstated) club; note it in the changes database.
            curs.execute(
                'INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, "New Club", "", "")',
                (club.clubnumber, cdate),
            )

        if club.clubnumber not in clubhist or changes:
            club.firstdate = club.lastdate
            # Encode newlines in the place as double-semicolons for the database
            club.place = club.place.replace("\n", ";;")
            values = [club.__dict__[x] for x in dbheaders]

            # And then put the place back into normal form
            club.place = club.place.replace(";;", "\n")

            thestr = (
                "INSERT IGNORE INTO clubs ("
                + ",".join(dbheaders)
                + ") VALUES ("
                + ",".join(["%s" for each in values])
                + ");"
            )

            try:
                changecount += curs.execute(thestr, values)
            except Exception as e:
                print(e)
            # Capture changes
            for (item, old, new) in changes:
                if item == "place":
                    # Clean up the place (old and new) for the database
                    old = old.replace("\n", ";;")
                    new = new.replace("\n", ";;")
                try:
                    curs.execute(
                        "INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, %s, %s, %s)",
                        (club.clubnumber, cdate, item, old, new),
                    )
                except Exception as e:
                    print(e)
            clubhist[club.clubnumber] = club
            if different(club, clubhist[club.clubnumber], dbheaders[:-2]):
                print("it's different after being set.")
                sys.exit(3)
        else:
            # update the lastdate
            changecount += curs.execute(
                "UPDATE clubs SET lastdate = %s WHERE clubnumber = %s AND lastdate = %s;",
                (cdate, club.clubnumber, clubhist[club.clubnumber].lastdate),
            )

    # If all the files were processed, today's work is done.
    curs.execute(
        'INSERT IGNORE INTO loaded (tablename, loadedfor) VALUES ("clubs", %s)',
        (cdate,),
    )
Exemple #4
0
def overrideClubPositions(clubs, overridefile, apikey, log=False, ignorefields=[], donotlog=[], createnewclubs=False):
    """ Updates 'clubs' with information from the override spreadsheet
        Note:  the same apikey is used for Google Maps and the Google spreadsheet """
    from geocode import myclub
    gmaps = googlemaps.Client(key=apikey)
    myclub.setgmaps(gmaps)
    
    # Get the data from the spreadsheet
    # We may be passed a whole URL or just the key
    if '/' in overridefile:
        # Have a whole URL; get the key
        overridefile = re.search(r'/spreadsheets/d/([a-zA-Z0-9-_]+)', overridefile).groups()[0]

    
    # Connect to the spreadsheet and get the values
    service = discovery.build('sheets', 'v4', developerKey=apikey)
    request = service.spreadsheets().values().get(spreadsheetId=overridefile, range='a1:zz999')
    values = request.execute()['values']
    
    keys = values[0]
    # Make sure all clubs have all the keys in our override, plus "touchedby"
    requiredkeys = list(keys)
    requiredkeys.append('touchedby')
    # We may be creating new keys; the default for each is None.
    for c in clubs:
        for k in requiredkeys:
            club = clubs[c]
            if k not in club.__dict__:
                club.__dict__[k] = None
                
    
    # Now, work through the overrides
    for line in values[1:]:
        # Convert the values to a dictionary, based on the keys.
        row = {}
        for i, key in enumerate(keys):
            try:
                # Normalize whitespace, including non-breaking spaces
                row[key] = normalizespaces(line[i])
            except IndexError:
                row[key] = ''
        
        # Now, process the data.
        clubnumber = row['clubnumber']
        if clubnumber not in clubs and createnewclubs:            
            club = Club([normalizespaces(f) for f in line], fieldnames=keys)  
            clubs[clubnumber] = club
                
            if log:
                print(("%8s/%s *** New Club ***" % (club.clubnumber, club.clubname)))

        if clubnumber in clubs:
            club = clubs[clubnumber]
            
            # Indicate we touched this club
            club.touchedby = overridefile
            
            # Override anything specified
            for key in keys:
                if key not in ignorefields and row[key]:
                    if log and key not in donotlog and club.__dict__[key] != row[key]:
                        print(("%8s/%s: Updating '%s' from '%s' to '%s'" % (club.clubnumber, club.clubname, key, club.__dict__[key], row[key])))
                    club.__dict__[key] = row[key]
                    
                    
            # Now, compute latitude and longitude if need be
           
            try:
                # Use explicit coordinates if we find them
                club.latitude = float(club.latitude)
                club.longitude = float(club.longitude)
                # Both are specified, so we use them
            except:  # latitude and longitude not both specified
                if row['address']:
                    parts = ['address', 'city', 'state', 'zip', 'country']
                    address = ', '.join([row[p] for p in parts])
                    # No coordinates provided but an address is here; use it.
                    gres = gmaps.geocode(address) # Includes city, state, zip, country if needed
                    # Make a dummy club entry for geocoding
                    reloinfo = myclub(clubnumber, club.clubname, club.place, club.address, club.city, club.state, club.zip, club.country, 0.0, 0.0)
                    # TODO: get rid of the need for a dummy club entry - have geocode return coordinates.
                    reloinfo.process(gres)
                    club.latitude = reloinfo.latitude
                    club.longitude = reloinfo.longitude
def overrideClubPositions(clubs, overridefile, apikey, log=False, ignorefields=[], donotlog=[], createnewclubs=False):
    """ Updates 'clubs' with information from the override spreadsheet
        Note:  the same apikey is used for Google Maps and the Google spreadsheet """
    from geocode import myclub
    gmaps = googlemaps.Client(key=apikey)
    myclub.setgmaps(gmaps)
    
    # Get the data from the spreadsheet
    # We may be passed a whole URL or just the key
    if '/' in overridefile:
        # Have a whole URL; get the key
        overridefile = re.search(r'/spreadsheets/d/([a-zA-Z0-9-_]+)', overridefile).groups()[0]

    
    # Connect to the spreadsheet and get the values
    service = discovery.build('sheets', 'v4', developerKey=apikey)
    request = service.spreadsheets().values().get(spreadsheetId=overridefile, range='a1:zz999')
    values = request.execute()['values']
    
    keys = values[0]
    # Make sure all clubs have all the keys in our override, plus "touchedby"
    requiredkeys = list(keys)
    requiredkeys.append('touchedby')
    # We may be creating new keys; the default for each is None.
    for c in clubs:
        for k in requiredkeys:
            club = clubs[c]
            if k not in club.__dict__:
                club.__dict__[k] = None
                
    
    # Now, work through the overrides
    linenum = 0
    for line in values[1:]:
        linenum += 1
        # Convert the values to a dictionary, based on the keys.
        row = {}
        for i, key in enumerate(keys):
            try:
                # Normalize whitespace, including non-breaking spaces
                row[key] = normalizespaces(line[i])
            except IndexError:
                row[key] = ''
        
        # Now, process the data.
        clubnumber = row['clubnumber'].strip()
        if not clubnumber:
            clubnumber = '%s' % (0 - linenum)
            row['clubnumber'] = clubnumber

        if clubnumber not in clubs and createnewclubs:            
            club = Club([normalizespaces(f) for f in line], fieldnames=keys, fillall=True)  
            club.clubnumber = clubnumber
            clubs[clubnumber] = club
                
            if log:
                print(("%8s/%s *** New Club ***" % (club.clubnumber, club.clubname)))

        if clubnumber in clubs:
            club = clubs[clubnumber]
            
            # Indicate we touched this club
            club.touchedby = overridefile
            
            # Override anything specified; add anything new
            for key in keys:
                if key not in ignorefields and (row[key] or key not in club.__dict__):
                    if log and key not in donotlog and key in club.__dict__ and club.__dict__[key] != row[key]:
                        print(("%8s/%s: Updating '%s' from '%s' to '%s'" % (club.clubnumber, club.clubname, key, club.__dict__[key], row[key])))
                    club.__dict__[key] = row[key]
                    
                    
            # Now, compute latitude and longitude if need be
           
            try:
                # Use explicit coordinates if we find them
                club.latitude = float(club.latitude)
                club.longitude = float(club.longitude)
                # Both are specified, so we use them
            except:  # latitude and longitude not both specified
                if row['address']:
                    parts = ['address', 'city', 'state', 'zip', 'country']
                    address = ', '.join([row[p] for p in parts])
                    # No coordinates provided but an address is here; use it.
                    gres = gmaps.geocode(address) # Includes city, state, zip, country if needed
                    # Make a dummy club entry for geocoding
                    reloinfo = myclub(clubnumber, club.clubname, club.place, club.address, club.city, club.state, club.zip, club.country, 0.0, 0.0)
                    # TODO: get rid of the need for a dummy club entry - have geocode return coordinates.
                    reloinfo.process(gres)
                    club.latitude = reloinfo.latitude
                    club.longitude = reloinfo.longitude