Data-centric Web Apps Databases, MySQL, Model-View-Controller, Django Introduction
CS 370 SE Practicum, Cengiz Günay
(Some slides courtesy of Eugene Agichtein and the Internets)
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
1/8
Agenda
Warm-up project: Proposal due next Thursday 1/30
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
2/8
Agenda
Warm-up project: Proposal due next Thursday 1/30 Today: Data-centric Web Apps I
Files and Databases
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
2/8
Agenda
Warm-up project: Proposal due next Thursday 1/30 Today: Data-centric Web Apps I
Files and Databases
Web Frameworks I
Intro to Django
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
2/8
Remember Sessions?
Why did we need them again? How do we keep track?
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
3/8
Remember Sessions?
Why did we need them again? How do we keep track? I I
We need persistent data Let’s see some Python examples
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
3/8
Using persistent data • Store/update data: – In plain files (simplest)
• In a (g)dbm file (better performance) – string keys, string values
• In a "shelf" (stores objects) – avoids parsing/unparsing the values
• In a real database – 3rd party database extensions available
1/22/2013
CS 370, Spring 2013
16
Plain Files key = ...username, or session key, or whatever... try: f = open(key, "r") data = f.read() # read previous data f.close() except IOError: data = "" # no file yet: provide initial data data = update(data, form) # do whatever must be done f = open(key, "w") f.write(data) # write new data f.close() # (could delete the file instead if updated data is empty) 1/22/2013
CS 370, Spring 2013
17
(g)DBM files # better performance if there are many records
import gdbm key = “lastname” db = gdbm.open("DATABASE", "w") # open for reading+writing if db.has_key(key): data = db[key] # read previous data else: data = "" # provide initial data data = update(data, form) db[key] = data # write new data db.close()
1/22/2013
CS 370, Spring 2013
18
Driving your Website with MySQL
1/22/2013
CS 370, Spring 2013
19
Web 2.0 Runs On “LAMP” • Linux – File permissions
• Apache (web server) – Hello.html
• PHP or Python – Application logic+UI control
MySQL – Data (e.g., Facebook)
Applications: – Facebook, Flckr, YouTube… 1/22/2013
CS 370, Spring 2013
20
How Does Facebook Check Your password?
• Browser posts request to pwd.py with submitted user and password values user = "bob", pass = "1234". – Review: how to get these values from CGI?
• wd.py looks up user "bob" in MySQL $true_pass="1234" • if(pass == true_pass){ /*allow to proceed, else: try again*/} 1/22/2013
CS 370, Spring 2013
21
Databases • Organize data in tables • Just like Excel: , tables have rows and columns • Store presidential pets and their owners: http://www.presidentialpetmuseum.com/whitehousepets-1.htm Column = Field table: PresidentialPets
Row = Record
1/22/2013
President
Term
Pet
Barack H. Obama
2008
Bo
George W. Bush
2000
Miss Beasley
Bill Clinton
1992
Socks
CS 370, Spring 2013
22
Create a Table PresidentialPets President
Term
Pet
Barack H. Obama
2008
Bo
George W. Bush
2000
Miss Beasley
Bill Clinton
1992
Socks
• Databases use Structured Query Language (SQL) to store, access, and manipulate data – Supposed to be like English...
1/22/2013
CS 370, Spring 2013
23
Create a Table PresidentialPets: SQL CREATE (
TABLE PresidentialPets president varchar (128), term int, pet varchar (128)
); • Result: President
1/22/2013
Term
CS 370, Spring 2013
Pet
24
MySQL Made Easy:
.
• Access class version of phpMyAdmin here: http://dutch.mathcs.emory.edu/phpmyadmin/
1/22/2013
CS 370, Spring 2013
25
Storing Data: INSERT statement INSERT into PresidentialPets (president, term, pet) VALUES (“William H Taft”, 1908, “Pauline Wayne”);
1/22/2013
President
Term
Pet
Barack H. Obama
2008
Bo
George W. Bush
2000
Miss Beasley
Bill Clinton
1992
Socks
William H Taft
1908
Pauline Wayne
CS 370, Spring 2013
26
Accessing Data: SELECT statement SELECT ( president, term, pet) FROM PresidentialPets WHERE ( term >=2000 );
1/22/2013
President
Term
Pet
Barack H. Obama
2008
Bo
George W. Bush
2000
Miss Beasley
CS 370, Spring 2013
27
1/22/2013
CS 370, Spring 2013
28
Exercise (Lab): Leave a comment Goal: Store user’s vote for favorite donut in database to find out most popular flavors Try it: http://dutch.mathcs.emory.edu/~test/ Approach: 1 2 3 4
Process user input (easy) Connect to database (test) Store comment in database Retrieve and show all comments
Results will be in database (use phpmyadmin) I I
Login as test Look into database test
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
4/8
Step 0: Setup Table for your Data • Goal: store comments in table comments user
Comment
IP Address
Eugene
Blah!
1.2.3.4
James
foo
1.2.3.5
• Create table comments with 3 columns
1/22/2013
CS 370, Spring 2013
31
Step 2: Connect to Database #connect to mysql database import MySQLdb as mdb con = mdb.connect('localhost', 'cs370', 'pwd', 'cs370000')
1/22/2013
CS 370, Spring 2013
32
Step 3: Store Comment #use cursor cur = con.cursor() Sql = “INSERT INTO comments(comment, user, ip) VALUES(‘blah!’, ‘foo’, ‘1.2.3.4’)” cur.execute(Sql)
# Prepared statement (template) version: Sql = "INSERT INTO comments(comment, user, ip) VALUES(%s, %s, %s)“ cur.execute(Sql, (comment, user, ip))
1/22/2013
CS 370, Spring 2013
33
Need to sanitize user input!
Use: comment = cgi.escape(comment)
1/22/2013
CS 370, Spring 2013
34
Step 4: Display Data try: cur = con.cursor(mdb.cursors.DictCursor) cur.execute("SELECT * FROM comments") rows = cur.fetchall() print "" for row in rows: print "\n" print "%s%s%s" % (row["user"], row["ip"], row["comment"]) print "\n" print "" cur.close() except mdb.Error, e: print "Error %d: %s" % (e.args[0],e.args[1])
1/22/2013
CS 370, Spring 2013
35
Modify the code
If you want, you can SSH to dutch Login with same credentials as MySQL Go to the /home/test/public_html subdirectory Copy index.html (and other files) into a new name with your user id: I I
e.g., cgunay-index.html, . . . modify them and see the results!
Warning: Dutch is accessible from outside campus!
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
5/8
Reference URLs • SQL Tutorials: – http://www.w3schools.com/sql/ – http://sqlzoo.net/
• MySQL + Python programming: – http://zetcode.com/databases/mysqlpythontutorial/
1/22/2013
CS 370, Spring 2013
36
Architecture of Web App: Continued • The LAMP stack approach
1/24/2013
CS 370, Spring 2012
10
Today’s: Step Up a bit… • “When you go too far up, abstraction-wise, you run out of oxygen. Sometimes smart thinkers just don't know when to stop, and they create these absurd, allencompassing, high-level pictures of the universe that are all good and fine, but don't actually mean anything at all.” - Joel Spolsky 1/24/2013
Figure 1: Architecture Astronaut
CS 370, Spring 2012
11
Web Application Frameworks • Closely integrates Data, UI, and logic – Simplifies development, maintainance – Hides communication from app developer • Not required to manually manage database connectivity
1/24/2013
CS 370, Spring 2012
12
App without a framework
1/24/2013
CS 370, Spring 2012
13
Problems • What happens when multiple parts of your application need to connect to the database? – Solution: ?
• Should a developer really have to worry about printing the “Content-Type” line and remember to close the database connection? • What happens when this code is reused in multiple environments, each with a separate database and password? • What happens when a Web designer who has no experience coding Python wishes to redesign the page? One wrong character could crash the entire application. 1/24/2013
CS 370, Spring 2012
14
DRY Principle • DRY = Don’t Repeat Yourself – Every piece of knowledge must have a single, unambiguous, authoritative representation within a system. – Antonym: WET (We Enjoy Typing” ) BAD. – Antonym: Cut & Paste Programming
• Duplication (inadvertent or purposeful duplication) can lead to maintenance nightmares, poor factoring, and logical contradictions. 1/24/2013
CS 370, Spring 2012
15
Approach: Model-View-Controller Formalized in 1979
• Model: maintain state of application. Typically done with a database • View: output to client, usually as HTML code, rendered by a web browser. • Controller: interaction with user. handles user input, processes data and communicates with Model to save state. 1/24/2013
CS 370, Spring 2012
16
MVC: Example with HTML+CSS
Model
1/24/2013
View
CS 370, Spring 2012
Controller (= Browser)
17
Project 1 Proposal due on Thursday 1/30 Alone or paired one other student Think of simple web app that uses: I I I I
HTML/CSS Web forms Read-write to persistent data (MySQL database) Display results
Examples: blog, simple word guessing game, anything else Submit max 1-page proposal on Blackboard
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
6/8
Project 1 Proposal due on Thursday 1/30 Alone or paired one other student Think of simple web app that uses: I I I I
HTML/CSS Web forms Read-write to persistent data (MySQL database) Display results
Examples: blog, simple word guessing game, anything else Submit max 1-page proposal on Blackboard In-class presentation on 2/13 Best-of-class will get 2 pts extra credit
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
6/8
Next Time
Model-View-Controller approach in Django
CS 370, Günay (Emory)
Data-centric Web Apps
Spring 2014
8/8