#!/usr/bin/python
# -*- coding: utf-8 -*-
#name of this file: test-sqlite1.py
def SearchDatabase(message):
import sqlite3 as lite
import sys
cars = (
(1, 'Audi', 52642),
(2, 'Mercedes', 57127),
(3, 'Skoda', 9000),
(4, 'Volvo', 29000),
(5, 'Bentley', 350000),
(6, 'Hummer', 41400),
(7, 'Volkswagen', 21600)
)
con = lite.connect('test.db')
with con:
cur = con.cursor()
#cur.execute("DROP TABLE IF EXISTS Cars")
#cur.execute("CREATE TABLE Cars(Id INT, Name TEXT, Price INT)")
#cur.executemany("INSERT INTO Cars VALUES(?, ?, ?)", cars)
title=['SN','Model','Price']
print '<table border=1>'
print '<tr><td>%s</td><td>%s</td><td>%s</td></tr>' % (title[0], title[1], title[2])
sql="SELECT * FROM Cars where Price>"+message
cur.execute(sql)
while True:
row = cur.fetchone()
if row == None:
break
print '<tr><td>%d</td><td>%s</td><td>%s</td><tr>' % (row[0], row[1], row[2])
print '</table>'
#get all table names
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
rows = cur.fetchall()
for row in rows:
print 'Tables in the current database:',row[0]
def main():
import cgi
import cgitb
cgitb.enable()
print "Content-type:text/html\r\n\r\n"
print '<html>'
print '<head>'
print '<title>Hello World</title>'
print '</head>'
print '<body>'
print '<h2>Hello World! This is my first CGI program with sqlite3</h2>'
print "welcome to Wang Zhengyuan's home in Wisconsin."
print '<br>Jan 10th, 2014'
form = cgi.FieldStorage()
message = form.getvalue("message", "(no message)")
print """
<p>Previous message: %s</p>
<p>Please try my sqlite database:
<form method="post" action="test-sqlite1.py">
<p>Search database for cars above the price of: <input type="text" name="message"/></p>
<input type="submit" value="Submit">
</form>
""" % cgi.escape(message)
SearchDatabase(message)
print '</body>'
print '</html>'
if __name__ == "__main__":
main()
http://zetcode.com/db/sqlitepythontutorial/
ReplyDelete