Updating Google Fusion Tables from a CSV File Using Python

My Quality of Life Dashboard project is using Fusion Tables, and I needed to update the table from a CSV. I did it in Python, and it is not a work of art, but I figured someone might find it useful.

First, you’ll need to grab the Fusion Tables python scripts. They don’t have a download or instructions on their site, and if you don’t have subversion handy to check out the repo, here’s what you need to do.

  • Pick a folder where your python script is going to sit. Make a folder called "authorization" inside of it.
  • Head to the source code for the main trunk.
  • Make a file called ftclient.py in your main folder, and paste in the contents of that file from Google Code.
  • Go the the authorization folder in the source code browser and look at clientlogin.py. Make the same name file in your authorization folder and copy in the contents.
  • Make an empty _init_.py folder in the authorization folder. This will make it so you can import libraries from that folder in your script.

Edit: For easier download of SVN or GIT repos without installing said binaries on Windows, check out Download SVN.

You should be set up to do a little Fusion Tables work. There are more files you can grab for Oath, a file importer and a SQL statement builder, but for my purposes those weren’t necessary.

Here is my python script to update from a CSV. Note that to update a record in GFT requires 2 SQL calls - one to get the ROWID, and another to run the update command with the ROWID as the WHERE clause. Not terribly efficient, but it is what it is.

I make some assumptions about my CSV, which I documented in the code. This is just to get you started and you’ll need to tweak it to meet your needs. Note you’re updating a record at a time to the “cloud”. Grab a cup of coffee if you have a large table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

import csv
from decimal import *
import getpass
from authorization.clientlogin import ClientLogin
import ftclient

# check to see if something is an integer
def isInt(s):
    try: 
        int(s)
        return True
    except ValueError:
        return False

# check to see if something is a float
def isFloat(s):
    try: 
        float(s)
        return True
    except ValueError:
        return False


# open the CSV file
ifile  = open('npa_commuter_mode_2006_2010_primary.csv', "rb")
reader = csv.reader(ifile)

# GFT table ID
tableID = "tableid"
# your username
username = "username"
# prompt for your password - you can hardcode it but this is more secure
password = getpass.getpass("Enter your password: ")

# Get token and connect to GFT
token = ClientLogin().authorize(username, password)
ft_client = ftclient.ClientLoginFTClient(token)
  
  
# Loop through the CSV data and upload
# Assumptions for my data: if it's a float less than 0, it's a percentage
# Floats are being rounded to 1 significant digit
# Non-numbers are wrapped in a single quote for string-type in the updatate statement
# The first row is the column names and matches exactly the column names in Fustion tables
# The first column is the unique ID I'll use to select the record for updating in Fusion Tables
rownum = 0
setList = list()
nid = 0
for row in reader:
    # Save header row.
    if rownum == 0:
        header = row
    else:
        colnum = 0
        setList[:] = []        
        for col in row:
            thedata = col
            # This bit rounds numbers and turns numbers < 1 into percentages
            if isFloat(thedata):
                if isInt(thedata) is False:
                    if float(thedata) < 1:
                        thedata = float(thedata) * 100
                    thedata = round(float(thedata), 1)
            else:
                thedata = "'" + thedata + "'"
            # make sql where clause for row
            setList.append(header[colnum] + "=" + str(thedata))
            nid = row[0]
            colnum += 1
        # get rowid and update the record
        rowid = ft_client.query("select ROWID from " + tableID + " where ID = " + nid).split("\n")[1]        
        print rowid        
        print ft_client.query("update " + tableID + " set " + ",".join(map(str, setList)) + " where rowid = '" + rowid + "'")  
    rownum += 1

ifile.close()