Last active
December 22, 2015 12:39
-
-
Save gjo/6473557 to your computer and use it in GitHub Desktop.
Code sample for PyCon APAC 2013 session
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
# -*- coding: utf-8 -*- | |
from flask import Flask | |
from werkzeug.exceptions import NotFound | |
from jinja2 import Template | |
import sqlite3 | |
app = Flask(__name__) | |
db = sqlite3.connect(':memory:') | |
cursor = db.cursor() | |
cursor.execute("""\ | |
CREATE TABLE page ( | |
slug TEXT PRIMARY KEY, | |
title TEXT, | |
description TEXT, | |
keywords TEXT | |
) | |
""") | |
COLUMNS = ('slug', 'title', 'description', 'keywords') | |
cursor.executemany( | |
'INSERT INTO page ({}) VALUES ({})'.format( | |
','.join(COLUMNS), | |
','.join(['?'] * len(COLUMNS)) | |
), [ | |
(u'hoge', u'ほげ', u'ほげほげ', u'hoge'), | |
(u'fuga', u'ふが', u'ふがふが', u'hoge fuga'), | |
(u'foo', u'ふー', u'foo foo', u'foo'), | |
(u'bar', u'ばー', u'bar bar', u'foo bar'), | |
(u'baz', u'ばず', u'baz baz', u'foo baz'), | |
(u'blah', u'ぶらー', u'blah, blah, blah', u'blah'), | |
] | |
) | |
@app.route('/<field>/<value>/') | |
@app.route('/') | |
def index(field=None, value=None): | |
sql = 'SELECT {} FROM page'.format(','.join(COLUMNS)) | |
params = [] | |
if None not in (field, value): | |
sql += ' WHERE {} LIKE ?'.format(field) | |
params.append(value) | |
cursor = db.cursor() | |
cursor.execute(sql, params) | |
pages = [dict(zip(COLUMNS, row)) for row in cursor] | |
context = dict(field=field, value=value, pages=pages) | |
template = Template("""\ | |
<!DocType html> | |
<meta charset=utf-8> | |
<title>{{ field }}={{ value }}</title> | |
<h1>{{ field }}={{ value }}</h1> | |
<table border=1> | |
{% for page in pages %} | |
<tr> | |
<td>{{ page.slug }} | |
<td>{{ page.title }} | |
<td>{{ page.description }} | |
<td>{{ page.keywords }} | |
{% else %} | |
<tr><td>No maches. | |
{% endfor %} | |
</table> | |
""") | |
return template.render(context) | |
if __name__ == '__main__': | |
app.run(debug=True) |
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
# -*- coding: utf-8 -*- | |
from flask import Flask | |
from werkzeug.exceptions import NotFound | |
from jinja2 import Template | |
import sqlite3 | |
app = Flask(__name__) | |
db = sqlite3.connect(':memory:') | |
cursor = db.cursor() | |
cursor.execute("""\ | |
CREATE TABLE page ( | |
slug TEXT PRIMARY KEY, | |
title TEXT, | |
description TEXT, | |
keywords TEXT | |
) | |
""") | |
COLUMNS = ('slug', 'title', 'description', 'keywords') | |
cursor.executemany( | |
'INSERT INTO page ({}) VALUES ({})'.format( | |
','.join(COLUMNS), | |
','.join(['?'] * len(COLUMNS)) | |
), [ | |
(u'hoge', u'ほげ', u'ほげほげ', u'hoge'), | |
(u'fuga', u'ふが', u'ふがふが', u'hoge fuga'), | |
(u'foo', u'ふー', u'foo foo', u'foo'), | |
(u'bar', u'ばー', u'bar bar', u'foo bar'), | |
(u'baz', u'ばず', u'baz baz', u'foo baz'), | |
(u'blah', u'ぶらー', u'blah, blah, blah', u'blah'), | |
] | |
) | |
@app.route('/<field>/<value>/') | |
@app.route('/') | |
def index(field=None, value=None): | |
sql = 'SELECT {} FROM page'.format(','.join(COLUMNS)) | |
params = [] | |
if None not in (field, value): | |
if field not in COLUMNS: | |
raise NotFound(field) | |
sql += ' WHERE {} LIKE ?'.format(field) | |
params.append('%{}%'.format( | |
value.replace(r'\', r'\\).replace(r'%', r'\%').replace(r'_', r'\_')) | |
) | |
cursor = db.cursor() | |
cursor.execute(sql, params) | |
pages = [dict(zip(COLUMNS, row)) for row in cursor] | |
context = dict(field=field, value=value, pages=pages) | |
template = Template("""\ | |
<!DocType html> | |
<meta charset=utf-8> | |
<title>{{ field }}={{ value }}</title> | |
<h1>{{ field }}={{ value }}</h1> | |
<table border=1> | |
{% for page in pages %} | |
<tr> | |
<td>{{ page.slug }} | |
<td>{{ page.title }} | |
<td>{{ page.description }} | |
<td>{{ page.keywords }} | |
{% else %} | |
<tr><td>No maches. | |
{% endfor %} | |
</table> | |
""", autoescape=True) | |
return template.render(context) | |
if __name__ == '__main__': | |
app.run() |
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
--- is_this_secure.py 2013-09-15 02:53:38.000000000 +0900 | |
+++ is_this_secure2.py 2013-09-15 02:54:28.000000000 +0900 | |
@@ -40,8 +40,12 @@ | |
sql = 'SELECT {} FROM page'.format(','.join(COLUMNS)) | |
params = [] | |
if None not in (field, value): | |
+ if field not in COLUMNS: | |
+ raise NotFound(field) | |
sql += ' WHERE {} LIKE ?'.format(field) | |
- params.append(value) | |
+ params.append('%{}%'.format( | |
+ value.replace(r'\', r'\\).replace(r'%', r'\%').replace(r'_', r'\_')) | |
+ ) | |
cursor = db.cursor() | |
cursor.execute(sql, params) | |
pages = [dict(zip(COLUMNS, row)) for row in cursor] | |
@@ -62,9 +66,9 @@ | |
<tr><td>No maches. | |
{% endfor %} | |
</table> | |
-""") | |
+""", autoescape=True) | |
return template.render(context) | |
if __name__ == '__main__': | |
- app.run(debug=True) | |
+ app.run() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment