Embedding database code into Python

Tutorial

In this tutorial we learn how to write Python programs with embedded SQL.

You will need:

1. SQLite installed. Pizza database pizza.db created from this script.
2. Python 3.X installed
3. [Optional]. Flask module installed (pip install flask).
4. [Optional]. curl installed (os-specific). For Windows: download zip file from this link, unzip and add bin folder to the PATH.

Code for this tutorial: code.zip.

Part I. Simple database application

• General code for queries and modifications: general_db.py.
• Code for pizza queries and modifications: pizza_db.py.
• Test code: test_db.py

Part II. RESTful Web Services with Flask

• REST interface: REST web services.pdf
• 'Hello, world' code: hello_flask.py.
  test: http://localhost:5000.
• Code for pizza server: pizza_service.py.
• Data format for web services: JSON.
• Tests:
  1. See all pizza names:
     curl http://localhost:5000/pizza/api/pizzas
  2. See all options of cheese pizza:
     curl http://localhost:5000/pizza/api/pizza/cheese
  3. Create a new pizza:
     curl -i -H "Content-Type: application/json" -X POST -d '{"pizzeria":"Dominos", "pizza":"eggplant", "price":11}' http://localhost:5000/pizza/api/pizza
     
     Windows: curl -i -H "Content-Type: application/json" -X POST -d " {"""pizzeria""":"""Dominos""", """pizza""":"""eggplant""", """price""":11} " http://localhost:5000/pizza/api/pizza
  4. Update pizza price:
     curl -i -H "Content-Type: application/json" -X PUT -d '{"pizzeria":"Dominos", "pizza":"eggplant", "price":15}' http://localhost:5000/pizza/api/pizza
     
     Windows: curl -i -H "Content-Type: application/json" -X PUT -d "{"""pizzeria""":"""Dominos""", """pizza""":"""eggplant""", """price""":15}" http://localhost:5000/pizza/api/pizza
  5. Delete pizza from pizzeria:
     curl -i -H "Content-Type: application/json" -X DELETE -d '{"pizzeria":"Dominos", "pizza":"cheese"}' http://localhost:5000/pizza/api/pizza
     
     Windows: curl -i -H "Content-Type: application/json" -X DELETE -d "{"""pizzeria""":"""Dominos""", """pizza""":"""cheese"""}" http://localhost:5000/pizza/api/pizza

Part III. Web application

• Client-side with knockout.js: static.zip. Unzip into folder 'static' in the flask server directory.
• Sample knockout code: link.

If you run: python pizza_service.py, you can see fully functioning app at: http://localhost:5000.