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:
- http://localhost:8888/create – To add student record in DB
- http://localhost:8888/show – To show all the existing records
Code snippets implementing this in Tornado can be viewed below:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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() |
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.
Chetan, do you know say if the sqlite will block the tornado ioLoop?
Yes it’ll block it. For this you should go for async library like motor
good job (and) …. I have a question … How I can delete any item in the database from my website …