Tornado – SQLite

I received a couple of requests for Tornado’s interaction with SQLite interface. So, here’s the blog…

SQLite is a file based and transactional database engine. Because of it’s lightweight capabilities, SQLite’s adoption has increased in the recent past. You can know more about it here. Below is an example of Tornado’s interaction with SQLite DB.

Consider a SQLite DB having table ‘stud’ with schema as below:

stud    table    CREATE TABLE stud (id INTEGER PRIMARY KEY, marks NUMERIC, name TEXT)
id    field    INTEGER PRIMARY KEY
name    field    NUMERIC
marks    field    TEXT

In Tornado, we implement two pages:

Code snippets implementing this in Tornado can be viewed below:

<html>
<title>
SQLite Example
</title>
<body>
<FORM ACTION="/create" METHOD=POST>
Name: <input type="text" name="name">
Marks: <input type="text" name="marks">
<input type="submit" value="Add" name="Submit"/>
</FORM>
</body>
</html>

view raw
sqliteform.html
hosted with ❤ by GitHub

<html>
<title>
Success
</title>
<body>
Record added successfully!!
<br /><br />
<FORM action="/show" METHOD="GET">
<input type="submit" value="Show Records" name="Submit"/>
</FORM>
</body>
</html>

view raw
success.html
hosted with ❤ by GitHub

import tornado.ioloop
import tornado.web
import tornado.database
import sqlite3
def _execute(query):
dbPath = '/home/ubuntu/tornado-2.2/db'
connection = sqlite3.connect(dbPath)
cursorobj = connection.cursor()
try:
cursorobj.execute(query)
result = cursorobj.fetchall()
connection.commit()
except Exception:
raise
connection.close()
return result
class Main(tornado.web.RequestHandler):
def get(self):
self.write("Main")
class AddStudent(tornado.web.RequestHandler):
def get(self):
self.render('sqliteform.html')
def post(self):
marks = int(self.get_argument("marks"))
name = self.get_argument("name")
query = ''' insert into stud (name, marks) values ('%s', %d) ''' %(name, marks);
_execute(query)
self.render('success.html')
class ShowStudents(tornado.web.RequestHandler):
def get(self):
query = ''' select * from stud'''
rows = _execute(query)
self._processresponse(rows)
def _processresponse(self,rows):
self.write("<b>Records</b> <br /><br />")
for row in rows:
self.write(str(row[0]) + " " + str(row[1]) + " " + str(row[2]) +" <br />" )
application = tornado.web.Application([
(r"/", Main),
(r"/create" ,AddStudent),
(r"/show",ShowStudents),
],debug=True)
if __name__ == "__main__":
application.listen(8888)
tornado.ioloop.IOLoop.instance().start()

view raw
tornadosqlite.py
hosted with ❤ by GitHub

Step 1: When user logs in to http://localhost:8888/create, he adds name and marks for a student. [Student id is auto generated]

Step 2: When user clicks on ‘Add’, a record gets added successfully

Step 3: Clicking on ‘Show Records’ shows all the records user has added to ‘stud’ table.

3 thoughts on “Tornado – SQLite

  1. good job (and) …. I have a question … How I can delete any item in the database from my website …

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.