Essentially, this is the example python code taken from Python gviz_api library. The hard coded data section has been replaced with the contents of a sqlite database. Before starting SQLite must be installed, instructions and the download can be found on the SQLite web site.
Firstly, we need to recreate the hard coded data in a sqlite database, so open up a sqlite session with:
sqlite3 exampledb
Now create a “salaries” table and fill it with the data from the example:
create table salaries (name VARCHAR(10), salary REAL, full_time INTEGER);
insert into salaries values (“Mike”, 10000.0, 1);
insert into salaries values (“Jim”, 800.0, 0);
insert into salaries values (“Alice”, 12500.0, 1);
insert into salaries values (“Bob”, 7000.0, 1);
.quit
The converted example code (see here for the original) that reads from the sqlite database is as follows:
#!/usr/bin/python
import gviz_api
import sqlite3
page_template = """
<html>
<script src="https://www.google.com/jsapi" type="text/javascript"></script>
<script>
google.load('visualization', '1', {packages:['table']});
google.setOnLoadCallback(drawTable);
function drawTable() {
%(jscode)s
var jscode_table = new google.visualization.Table(document.getElementById('table_div_jscode'));
jscode_table.draw(jscode_data, {showRowNumber: true});
var json_table = new google.visualization.Table(document.getElementById('table_div_json'));
var json_data = new google.visualization.DataTable(%(json)s, 0.6);
json_table.draw(json_data, {showRowNumber: true});
}
</script>
<body>
<H1>Table created using ToJSCode</H1>
<div id="table_div_jscode"></div>
<H1>Table created using ToJSon</H1>
<div id="table_div_json"></div>
</body>
</html>
"""
def main():
# Don't forget to change this to match the data to be displayed
description = {"name": ("string", "Name"),
"salary": ("number", "Salary"),
"full_time": ("boolean", "Full Time Employee")}
#Open Sqlite database
conn = sqlite3.connect('exampledb')
# Loading the description into the gviz_api.DataTable
data_table = gviz_api.DataTable(description)
#Select the data using a cursor
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute('select * from salaries')
# Data is a list used to hold dictionaries of salary data
data =[]
salary_data = {}
for r in c.fetchall():
salary_data = {"name": r['name'], "salary": r['salary'], "full_time": r['full_time']}
data.append(salary_data)
#Close the cursor
c.close()
#Now load the data retrieved from the database into the data table.
data_table.LoadData(data)
# Creating a JavaScript code string
jscode = data_table.ToJSCode("jscode_data",
columns_order=("name", "salary", "full_time"),
order_by="salary")
# Creating a JSon string
json = data_table.ToJSon(columns_order=("name", "salary", "full_time"),
order_by="salary")
# Putting the JS code and JSon string into the template
print "Content-type: text/html"
print
print page_template % vars()
if __name__ == '__main__':
main()
