Python + Flask + Persistance VI

Sqlalchemy

Install
In the environment

pip install flask-sqlalchemy



Create model
Create file in root path to define the class. Ex. model.py

from datetime import datetime
from ict import db

class Ticket(db.Model):
id = db.Column(db.Integer, primary_key=True)
signum = db.Column(db.String(10), nullable=True)
comments = db.Column(db.String(255), nullable=True)

Add methos to do the queries
       @staticmethod
def newest(num):
return Ticket.query.order_by(desc(Ticket.signum)).limit(num)

def __repr__(self):
return "<Ticket '{}' : '{}'> ".format(self.signum, self.comments)

Option in query
.limit
.first_or_404()
.get_or_404()


SQLITE
In the console at environment

• python
•  from ict import db     ict is the controller ict.py
• from model import Ticket     refers to the file model.py where the class is define
• db.create_all()   create DB

Controller

• Import

import os
from flask_sqlalchemy import SQLAlchemy
import model      the file with the object/class definition

• Define the DB

basedir = os.path.abspath( os.path.dirname(__file__) )

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'ict_dev.db')
db = SQLAlchemy(app)

• Use DB in 

ticket = model.Ticket(signum=signum, comments=comments)

db.session.add(ticket)

• In the model

from datetime import datetime

from sqlalchemy import desc

from ict import db

class Ticket(db.Model):

id = db.Column(db.Integer, primary_key=True)

signum = db.Column(db.String(10), nullable=True)

comments = db.Column(db.String(255), nullable=True)

engineer_id = db.Column(db.Integer, db.ForeignKey('engineer.id'), nullable=False)

@staticmethod

def newest(num):

return Ticket.query.order_by(desc(Ticket.signum)).limit(num)

def __repr__(self):

return "<Ticket '{}' : '{}'> ".format(self.signum, self.comments)

• Use the method from model to do the query

@app.route('/show_tickets')
def show_tickets():
return render_template('show_tickets.html', new_tickets=model.Ticket.newest(5))

Simple queries 

• Ticket.query.get(1)
• Ticket.query.all()
• Ticket.query.filter_by( attribute="value" ).first()