{"id":1229,"date":"2019-04-19T06:48:43","date_gmt":"2019-04-18T21:48:43","guid":{"rendered":"http:\/\/idealive.jp\/blog\/?p=1229"},"modified":"2019-04-19T06:48:43","modified_gmt":"2019-04-18T21:48:43","slug":"flask%e3%81%a7sqlite3%e3%82%92%e4%bd%bf%e7%94%a8%e3%81%99%e3%82%8b","status":"publish","type":"post","link":"https:\/\/idealive.jp\/blog\/2019\/04\/19\/flask%e3%81%a7sqlite3%e3%82%92%e4%bd%bf%e7%94%a8%e3%81%99%e3%82%8b\/","title":{"rendered":"Flask\u3067SQLite3\u3092\u4f7f\u7528\u3059\u308b"},"content":{"rendered":"

\u524d\u56de\u307e\u3067\u4f5c\u6210\u3057\u305f\u5546\u54c1\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30da\u30fc\u30b8\u306b\u3066\u3001\u5546\u54c1\u60c5\u5831\u3092\u30c7\u30a3\u30af\u30b7\u30e7\u30ca\u30ea\u306e\u914d\u5217\u304b\u3089\u8aad\u307f\u8fbc\u3093\u3067\u3044\u307e\u3057\u305f\u304c\u3001\u4eca\u56de\u306fSQLite3\u304b\u3089\u8aad\u307f\u8fbc\u3080\u3088\u3046\u306b\u4f5c\u6210\u3057\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n

instance\r\n\u2514\u2500\u2500 create_db.py     (1)\u30fb\u30fb\u30fb\u3000\u65b0\u898f\u4f5c\u6210\r\n\u2514\u2500\u2500 itemdb.sqlite3   (2)\u30fb\u30fb\u30fb\u3000\u65b0\u898f\u4f5c\u6210\r\nweb\r\n\u2514\u2500\u2500 itemdb.py        (3)\u30fb\u30fb\u30fb\u3000\u65b0\u898f\u4f5c\u6210\r\n\u2514\u2500\u2500 __init__.py      (4)\u30fb\u30fb\u30fb\u3000\u66f4\u65b0\r\n\u2514\u2500\u2500 items.py         (5)\u30fb\u30fb\u30fb\u3000\u66f4\u65b0\r\n\u2514\u2500\u2500 templates\r\n|  \u2514\u2500\u2500 base.html\r\n|  \u2514\u2500\u2500 index.html\r\n\u2514\u2500\u2500 static\r\n   \u2514\u2500\u2500 style.css\r\n<\/pre>\n
 <\/p>\n
\n
(1)create_db.py<\/p>\n
\u4ee5\u524d\u3001SQLite3\u306b\u3064\u3044\u3066\u89e6\u308c\u305f\u3068\u304d\u306b\u4f5c\u6210\u3057\u305f\u30bd\u30fc\u30b9\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002\u5546\u54c1\u60c5\u5831\u3092\uff15\u4ef6\u767b\u9332\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
import sqlite3\r\n\r\nconn = sqlite3.connect('itemdb.sqlite3')\r\n\r\nc = conn.cursor()\r\n\r\nc.execute('''CREATE TABLE items\r\n(id INTEGER PRIMARY KEY AUTOINCREMENT,\r\nitem_name TEXT,\r\nprice INTEGER)''')\r\n\r\nc.execute('''INSERT INTO items (item_name, price) VALUES('\u30ad\u30e3\u30d9\u30c4', 198)''')\r\n\r\nc.execute('''INSERT INTO items (item_name, price) VALUES('\u306b\u3093\u3058\u3093', 98)''')\r\n\r\nc.execute('''INSERT INTO items (item_name, price) VALUES('\u725b\u4e73', 168)''')\r\n\r\nc.execute('''INSERT INTO items (item_name, price) VALUES('\u3082\u3084\u3057', 38)''')\r\n\r\nc.execute('''INSERT INTO items (item_name, price) VALUES('\u306f\u304f\u3055\u3044', 128)''')\r\n\r\nconn.commit()\r\n\r\nc.execute(\"SELECT * FROM items\")\r\n\r\nresult = c.fetchall()\r\n\r\nprint(result)\r\n\r\nconn.close()<\/pre>\n
 <\/p>\n
\n
(2)itemdb.sqlite3<\/p>\n
\u4ee5\u4e0b\u306e\u3088\u3046\u306bAndroid pronpt\u4e0a\u3067\u300cpython create_db.py\u300d\u3092\u5b9f\u884c\u3059\u308b\u3068\u3001’itemdb.sqlite3’\u304c\u4f5c\u6210\u3055\u308c\u307e\u3059\u3002\uff08\u30d5\u30a1\u30a4\u30eb\u540d\u306f(1)\u306e\uff13\u884c\u76ee\u306b\u306a\u308a\u307e\u3059\u3002\uff09<\/p>\n
\"\"<\/p>\n
\"\"<\/p>\n
 <\/p>\n
\n
(3)itemdb.py<\/p>\n
DB\u64cd\u4f5c\u306e\u305f\u3081\u306e\u95a2\u6570\u3092\u5b9a\u7fa9\u3057\u307e\u3059\u3002<\/p>\n
import sqlite3\r\n\r\nfrom flask import current_app, g\r\n\r\ndef get_db():\r\n    if 'db' not in g:\r\n        g.db = sqlite3.connect(\r\n            current_app.config['DATABASE'],\r\n            detect_types=sqlite3.PARSE_DECLTYPES\r\n        )\r\n        g.db.row_factory = sqlite3.Row\r\n    \r\n    return g.db\r\n\r\ndef close_db(e=None):\r\n    db = g.pop('db', None)\r\n    if db is not None:\r\n        db.close()\r\n\r\ndef init_app(app):\r\n    app.teardown_appcontext(close_db)\r\n<\/pre>\n
\u30fb\uff13\u884c\u76ee\uff1a’current_app’\u306f\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u672c\u4f53\u306e\u60c5\u5831\u3084\u8a2d\u5b9a\u306e\u70ba\u306e\u30e1\u30bd\u30c3\u30c9\u3092\u5099\u3048\u305f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3067\u3059\u3002’g’\u306fDB\u306e\u63a5\u7d9a\u60c5\u5831\u3092\u4fdd\u6301\u3059\u308b\u30c7\u30a3\u30af\u30b7\u30e7\u30ca\u30ea\u3067\u3059\u3002<\/p>\n
\u30fb\uff15\u884c\u76ee\uff1aDB\u3088\u308a\u63a5\u7d9a\u60c5\u5831\u3092\u53d6\u5f97\u3057\u307e\u3059\u3002\u30ad\u30fc’db’\u306b\u63a5\u7d9a\u60c5\u5831\u3092\u4e0e\u3048\u307e\u3059\u3002<\/p>\n
\u30fb\uff11\uff15\u884c\u76ee\uff1aDB\u3092\u9589\u3058\u307e\u3059\u3002<\/p>\n
\u30fb\uff12\uff10\u884c\u76ee\uff1a\u53e4\u3044\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3092\u7834\u68c4\u3057\u3001\u521d\u671f\u5316\u3057\u307e\u3059\u3002<\/p>\n
 <\/p>\n
\n
(4)__init__.py<\/p>\n
(3)\u3092\u547c\u3073\u51fa\u3057\u3066DB\u3092\u64cd\u4f5c\u3057\u307e\u3059\u3002<\/p>\n
from flask import Flask\r\nimport os\r\n\r\ndef create_app():\r\n    app = Flask(__name__)\r\n\r\n    from . import items\r\n    app.register_blueprint(items.bp)\r\n\r\n    from . import itemdb\r\n    itemdb.init_app(app)\r\n    \r\n    app.config.from_mapping(\r\n        SECRET_KEY='temp',\r\n        DATABASE=os.path.join(app.instance_path, 'itemdb.sqlite3'),\r\n    )<\/pre>\n
\u30fb\uff12\u884c\u76ee\uff1aos\u3092\u30a4\u30f3\u30dd\u30fc\u30c8\u3002\uff08\u5f8c\u8a18\u306e\u76f8\u5bfe\u30d1\u30b9\u306e\u53d6\u5f97\u3067\u4f7f\u7528\uff09<\/p>\n
\u30fb\uff11\uff10\u884c\u76ee\uff1aitemdb\u30e2\u30b8\u30e5\u30fc\u30eb\u3092\u30a4\u30f3\u30dd\u30fc\u30c8\u3002<\/p>\n
\u30fb\uff11\uff13\u884c\u76ee\uff1a’SEACRET_KEY’\u306f\u6697\u53f7\u5316\u30ad\u30fc\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002’DATABASE’\u306b\u306fitems.sqlite3\u306e\u76f8\u5bfe\u30d1\u30b9\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002<\/p>\n
 <\/p>\n
\n
(5)items.py<\/p>\n
DB\u306e\u60c5\u5831\u3092\u53d6\u5f97\u3057\u3066\u3001\u5546\u54c1\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30da\u30fc\u30b8\u306b\u8868\u793a\u3057\u307e\u3059\u3002<\/p>\n
from flask import (\r\n    Blueprint, render_template\r\n)\r\n\r\nfrom web.itemdb import get_db\r\n\r\nbp = Blueprint('items', __name__)\r\n@bp.route('\/')\r\ndef index():\r\n\r\n    db=get_db()\r\n    alldata = db.execute('SELECT * FROM items').fetchall()\r\n\r\n    return render_template('index.html', items=alldata)\r\n<\/pre>\n
\u30fb\uff15\u884c\u76ee\uff1a(4)\u306eget_db\u95a2\u6570\u3092\u30a4\u30f3\u30dd\u30fc\u30c8\u3002<\/p>\n
\u30fb\uff11\uff11\u884c\u76ee\uff1aDB\u306b\u63a5\u7d9a\u3002<\/p>\n
\u30fb\uff11\uff12\u884c\u76ee\uff1aitems\u306e\u5168\u4ef6\u3092\u53d6\u5f97\u3057\u3066\u3001index\u30da\u30fc\u30b8\u306b\u8fd4\u3057\u307e\u3059\u3002<\/p>\n
 <\/p>\n
\n
\u5b9f\u884c\u7d50\u679c\u306f\u4ee5\u4e0b\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n
\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"
\u524d\u56de\u307e\u3067\u4f5c\u6210\u3057\u305f\u5546\u54c1\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30da\u30fc\u30b8\u306b\u3066\u3001\u5546\u54c1\u60c5\u5831\u3092\u30c7\u30a3\u30af\u30b7\u30e7\u30ca\u30ea\u306e\u914d\u5217\u304b\u3089\u8aad\u307f\u8fbc\u3093\u3067\u3044\u307e\u3057\u305f\u304c\u3001\u4eca\u56de\u306fSQLite3\u304b\u3089\u8aad\u307f\u8fbc\u3080\u3088\u3046\u306b\u4f5c\u6210\u3057\u3066\u3044\u304d\u307e\u3059\u3002 instance \u2514\u2500\u2500 create_db.py (1)\u30fb\u30fb…\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":9,"featured_media":1125,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,13],"tags":[],"class_list":["post-1229","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","category-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/posts\/1229"}],"collection":[{"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/comments?post=1229"}],"version-history":[{"count":11,"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/posts\/1229\/revisions"}],"predecessor-version":[{"id":1246,"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/posts\/1229\/revisions\/1246"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/media\/1125"}],"wp:attachment":[{"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/media?parent=1229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/categories?post=1229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/idealive.jp\/blog\/wp-json\/wp\/v2\/tags?post=1229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}