how to access sqlite3 database table values from javascript?

Question

My task is to report a graph by accessing sqlite3 database table values. So i created database in python and i used javascript to report a graph.In python, i fetched all values from database and stored in list. Now, my problem is i dono how to access python list values from javascript. please help me..

import sqlite3
list = []
conn = sqlite3.connect('persistentautomation.db')
cursor = conn.execute("SELECT date, gb from memoryused")
for row in cursor:
    print "date :", row[0]
    print "gb :", row[1]
    list.append([row[0],row[1]])
def memory():
    return list

req = memory();
print req  #This is the list which i created

memory();
ff = open('chart.html','w')
msg='''
<html>
<head>
<script type="text/javascript"
src=\'https://www.google.com/jsapi?autoload={
    "modules":[{
      "name":"visualization",
      "version":"1",
      "packages":["corechart"]
    }]
   }\'></script>

<script type="text/javascript">
google.setOnLoadCallback(drawChart);

function drawChart() {
    var data = google.visualization.arrayToDataTable([             .

     #i want to pass that list variable here...help please..

    ]);

    var options = {
      title: "PERSISTENT AUTOMATION MEMORY USAGE REPORT",
      curveType: "function",
      legend: { position: "bottom" }
    };

    var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));

    chart.draw(data, options);
}
</script>
</head>
<body>

<div id="curve_chart" style="width: 900px; height: 500px"></div>
</body>
</html>'''
ff.write(msg)
ff.close()

Mauro Baraldi · Accepted Answer · 2015-03-23 13:18:06Z

1

import sqlite3
from string import Template

conn = sqlite3.connect('persistentautomation.db')
cursor = conn.execute("SELECT date, gb from memoryused")

results = []

for row in cursor:
    results.append({'date': row[0], 'gb': row[1]})
    print "date :", row[0]
    print "gb :", row[1]

template = '''
<html>
<head>
<script type="text/javascript"
src=\'https://www.google.com/jsapi?autoload={
    "modules":[{
      "name":"visualization",
      "version":"1",
      "packages":["corechart"]
    }]
   }\'></script>

<script type="text/javascript">
google.setOnLoadCallback(drawChart);

function drawChart() {
    var data = google.visualization.arrayToDataTable([
      ["Memory", "Usage (GB)"],
     $res
    ]);

    var options = {
      title: "PERSISTENT AUTOMATION MEMORY USAGE REPORT",
      curveType: "function",
      legend: { position: "bottom" }
    };

    var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));

    chart.draw(data, options);
}
</script>
</head>
<body>

<div id="curve_chart" style="width: 900px; height: 500px"></div>
</body>
</html>'''

with open('chart.html', 'w') as html:
    data = ','.join(['["{date}", {gb}]'.format(**r) for r in results])
    html.write(Template(template).substitute(res=data))

edited Mar 23, 2015 at 13:18

answered Mar 20, 2015 at 16:43

Mauro Baraldi

6,6042 gold badges35 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

moni dallu Over a year ago

Hi Mauro thank u for ur relpy. everthing working good but in this html.write(template) if i use only template its working it writing the file if i use template with data html.write(template.format(res=data)). it doesnt pring anything into that file...

Mauro Baraldi Over a year ago

@monidallu fixed answer. You'll need to use the Template feature from string library. There is better approaches to handle with templates, but this works with your legacy code.

Mauro Baraldi · Accepted Answer · 2015-03-20 16:31:59Z

0

You should iterate over list and concatenate items to the result string.

items = ['apple', 'grape']

html_string = '''
<html> <body> <h1> My list </h1> <ul>{list}</ul> </body> </html>
'''

my_list = ''.join(['<li>{}</li>'.format(i) for i items])
html_string = html_string.format(list=my_list)

with open('test.html', 'w') as html:
    html.write(html_string)

edited Mar 20, 2015 at 16:31

Mauro Baraldi

6,6042 gold badges35 silver badges48 bronze badges

answered Mar 20, 2015 at 14:20

Alan Albuquerque

661 silver badge7 bronze badges

1 Comment

moni dallu Over a year ago

For the above code, i got list elements as a output in html. But i need to use that list elements inside a function drawChart() to report a graph..Help pls..

moni dallu · Accepted Answer · 2015-03-24 10:51:17Z

This is a very easy way to achieve the above task.

    import ast
    memory_used = "/Users/Shared/MemoryUsed.log"
    memory_used = []
    with open(memory_used) as f:
    for line in f:
        linesplit = line.split()
        date = linesplit[1]
        gbsplit = linesplit[2]
        gb = ast.literal_eval(gbsplit[0])
        memory_used.append([date,gb])

    print memory_used  #This is the list

    ff = open('chart_new_try.html','w')
    html1="""<html>
    <head>
    <script type="text/javascript"
      src="https://www.google.com/jsapi?autoload={
        'modules':[{
          'name':'visualization',
          'version':'1',
          'packages':['corechart']
        }]
      }"></script>


    <script type="text/javascript">
      google.setOnLoadCallback(drawChart);

    function drawChart() {
    var data = google.visualization.arrayToDataTable([
      ['Date', 'MemoryUsed'],"""

    html2 =  """ ]);

    var options = {
      title: 'GRAPH REPORT',
      curveType: 'function',
      legend: { position: 'bottom' }
    };

    var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));

    chart.draw(data, options);
    }
    </script>
    </head>
    <body>
      <div id="curve_chart" style="width: 900px; height: 500px"></div>
    </body>
    </html>"""

    msg = html1 + memory_used + html2
    ff.write(msg)
    ff.close()

Collectives™ on Stack Overflow

how to access sqlite3 database table values from javascript?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related