Flask-sqlalchemy

From WikiName
(Redirected from Flask-alchemy)
Jump to: navigation, search

This is my boilerplate to code with python/flask/sqlalchemy
Most of the tutorial in the net is quite different so I decide to make this for myself.
This is a psudo code. You cannot just copy and run this code. You need to adapt it.

Workflow[edit]

  1. import all necessary modules
  2. create class for table
  3. Work with db object
    1. Query (view)
      1. Create table object with query criteria
      2. Display it on python console or web
    2. Add rows
      1. Create table object with new data
      2. Add to session
      3. Commit session

Heading[edit]

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy import Column, Integer

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://xxx:yyy@localhost/db_name'
db = SQLAlchemy(app)

Sample Class[edit]

class Customer(db.Model):
    __tablename__ = "tbl_customers"
    id = Column(Integer, primary_key=True)
    cus_name = Column(db.String(200))

    def __init__(self,cus_name):
        self.cus_name = cus_name

    def __repr__(self):
        return '<Customer %r>' % self.cus_name

DB Operatons[edit]

Insert[edit]

This is to add new row

from home import db
from home import Customer
new_cus = Customer('aaaaa')
db.session.add(new_cus)
db.session.commit()
cus = Customer.query.all()
cus

Delete[edit]

def delcus(cus_id):
    d = Customer.query.filter_by(id=cus_id).first()
    db.session.delete(d)
    db.session.commit()
    return redirect('/customers')

Update[edit]

def updatecus(cus_id):
    d = Customer.query.filter_by(id=cus_id)
    d.is_hide = True  #Just put update a new value
    db.session.commit()
    return redirect('/customers')

Join example[edit]

prod = db.session.query(Product.id,Product.prod_name, Product.prod_price1, Unit.unit_name)\
        .filter(Product.prod_unit == Unit.id)\
        .filter(Product.is_hide.isnot(True))\
        .order_by(Product.prod_name.asc())\
        .all()

Display on view[edit]

on route[edit]

To query all rows from Customer (table )

@app.route('/customers/cusview')
def cusview():
    cus = Customer.query.all()
    return render_template('cusview.html',cus=cus)

on view[edit]

Use Jinja2 template engine to loop all rows and display.

{% for customer in cus %}
  {{ customer.cus_name }} <br />
{% endfor %}