Data-centric Web Apps

Data-centric Web Apps Databases, MySQL, Model-View-Controller, Django Introduction CS 370 SE Practicum, Cengiz Günay (Some slides courtesy of Eugene...
Author: Sarah Porter
5 downloads 1 Views 2MB Size
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