Python: pyodbc with SQL Server

This post is in regards to connecting to SQL Server using pyodbc.

Install package

pip install pyodbc

If you are running in Databricks then the current driver will be “{ODBC Driver 17 for SQL Server}”.

If you are running in Synapse then the current driver will be “{ODBC Driver 18 for SQL Server}”.

Check pyodbc Version

import pyodbc
pyodbc.drivers()

Check Which Version of pyodbc in Databricks

%sh
cat /etc/odbcinst.ini

Install Databricks driver 17

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install msodbcsql17
apt-get -y install unixodbc-dev

Connect using SQL Auth

I do not recommend SQL Auth

import pyodbc

secret = "<GET SECRET SECURELY>"

connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};Server=tcp:<SERVER_NAME>;PORT=<PORT>;Database=<DATABASE>;Uid=<USER>;Pwd=<SECRET>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=<TIMEOUT>;')

Connect Using Domain Auth

import pyodbc

secret = "<GET SECRET SECURELY>"

connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};Server=tcp:<SERVER_NAME>;PORT=<PORT>;Database=<DATABASE>;Uid=<USER>;Pwd=<SECRET>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=<TIMEOUT>;Authentication=ActiveDirectoryPassword')

Connect using Azure SPN

pip install msal
import struct
import msal

global_token_cache = msal.TokenCache()
secret = "<GET SECRET SECURELY>"

global_spn_app = msal.ConfidentialClientApplication(
    <CLIENT_ID>, Authority='https://login.microsoftonline.com/<TENANT_ID>',
    client_credential=secret,
    token_cache=global_token_cache,
)

result = global_spn_app.acquire_token_for_client(scopes=['https://database.windows.net//.default'])
SQL_COPT_SS_ACCESS_TOKEN = 1256

token = bytes(result['access_token'], 'utf-8')
exptoken = b"";

for i in token:
    exptoken += bytes({i});
    exptoken += bytes(1);

token_struct = struct.pack("=i", len(exptoken)) + exptoken;

connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};Server=tcp:<SERVER_NAME>;PORT=<PORT>;Database=<DATABASE>;Uid=<USER>;Pwd=<SECRET>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=<TIMEOUT>;' attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct })

Once you have the connection you can setup the cursor.

cursor = connection.cursor()

Then execute a command

command = "<COMMAND>"
params = ()
cursor.execute(command, params)
connection.commit()

After you Are finish Close

cursor.close()
connection.close()

 

Python: Arguments

This post is in how do use argparse package.

First you must import the package.

import argparse

Next you setup the argument parser.

parser = argparse.ArgumentParser()

Then you create a list of arguments. See the link above for more options then the below set.

argument_list = [
    { "name": "<NAME>", "help": "<HELP_TEXT>", "type": "<TYPE>", "required": True}
]

Then we take your argument_list and create arguments and assign them to the parser.

for arg in argument_list:
    parser.add_argument("--{}".format(arg["name"], help=arg["help"], type=arg["type"], required=arg["required"])

Then we parse the args from “sys.argv”. Parsing args this way means that if anything is unknown to your program than your program won’t fail but instead it will set those variables to the unknown variable and continue your application.

args, unknown = parser.parse_known_args()

You could also parse the args from “sys.argv” this way. However that means that all the args passed to sys.argv must be known otherwise it will fail.

args = parser.parse_args()

Then as a final step we set the values with their key to the config.

config = vars(args)

 

 

 

Python: lxml

This post focus’ on the lxml package.

First you need to install the package

from lxml import etree

Create xml object by string

xml_str = "<root><subitem attr='test'>rec</subitem></root>"
root = etree.fromstring(xml_str)

Get text in node

text_str = root.xpath('//root/subitem/text()')[0]

Get Attribute

attr = root.xpath('//root/subitem')[0].attrib['attr']

 

Python: Create a Logger

This post is how-to create a logger.

First we need to import

import sys
import logging
from datetime import datetime
from pytz import timezone

Then we create a class for Formatter

class CustomFormatter(logging.Formatter):
    grey = "\x1b[38;20m"
    reset = "\x1b[0m"
    format = "%(asctime)s - %(name)s - %(levelname)s - %(message)s (%(filename)s:)"
    FORMATS = {
        logging.DEBUG: '\x1b[38;5;23m' + format + reset,
        logging.INFO: grey + format + reset,
        logging.WARNING: '\x1b[38;5;56m' + format + reset,
        logging.ERROR: '\x1b[38;5;197m' + format + reset,
        logging.CRITICAL: '\x1b[38;5;1m' + format +reset
    }

    def format(self, record):
        log_fmt = self.FORMATS.get(record.levelno)
        formatter = logging.Formatter(log_fmt)
        return formatter.format(record)

Then we create a function set our logger up.

def set_logger(logging_level, name, log_dir, timezone):
    LOGGING_LEVELS = ['WARNING','INFO','DEBUG','ERROR']
    if logging_level not in LOGGING_LEVELS:
        logging_level = 'INFO'

    level_lookup = {
        'WARNING': logging.WARNING,
        'INFO': logging.INFO,
        'DEBUG': logging.DEBUG,
        'ERROR': logging.ERROR,
    }
    logging.Formatter.converter = lambda *args: datetime.now(tz=timezone(timezone)).timetuple()
    logging.basicConfig(level=level_lookup[logging_level], format="[%(levelname)s] %(asctime)s - %(message)s:%(lineno)d")
    stream_handler = logging.StreamHandler(sys.stdout)
    stream_handler.setFormatter(CustomFormatter())
    logger = logging.getLogger(name)
    logger.addHandler(stream_handler)
    logger.setLevel(logging_level)

    Path(log_dir).mkdir(parents=True, exist_ok=True)

    now = datetime.now(tz=timezone(timezone))
    now = now.strftime("%H-%M-%S")

    log_file = '%slog_%s.log' % (log_dir, now)
    file_handler = logging.FileHandler(log_file, mode='a')
    file_handler.setFormatter(logging.Formatter("[%(levelname)s] %(asctime)s - %(message)s:%(lineno)d"))
    logger.addHandler(file_handler)

    return logger

References

https://alexandra-zaharia.github.io/posts/make-your-own-custom-color-formatter-with-python-logging/

Django: React Website

In this tutorial I will demonstrate how to create a Django + React website using Django 2.0. You must have Eclipse installed before you continue. If you have it already installed and configured you can continue on. We will require Postgres 9.4, nodejs before you continue. You can get Nodejs from here. You can get Postgres 9.4 from here.

Pip Django Install:
pip install django
pip install django-webpack-loader
Django Version:

If you are not sure what version you are running do the following

python -c "import django; print(django.get_version())"
Eclipse Create Project:

 

 

 

 

 

 

Eclipse Setup Project:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Eclipse Django DB Settings:

 

 

 

 

 

 

 

 

 

 

 

 

 

Eclipse Django Setup Successful:

Once you click “Finish” your project will look like the following.

 

 

 

Folder Structure:
  • Under djangoApp project.
  • folder: static
  • folder: djangoApp
    • folder: templates
      • file: index.html
      • folder: base
        • file: base.html
  • folder: assets
    • folder: bundles
    • folder: js
      • file: index.jsx
Node:

Inside the djangoApp application do the following

npm init
npm install --save-dev jquery react react-dom webpack webpack-bundle-tracker babel-loader babel-core babel-preset-es2015 babel-preset-react
npm install create-react-class --save
webpack.config.js:
var path = require('path')
var webpack = require('webpack')
var BundleTracker = require('webpack-bundle-tracker')

module.exports = {
    //the base directory (absolute path) for resolving the entry option
    context: __dirname,
    //the entry point we created earlier. Note that './' means 
    //your current directory.
    entry: {
		"index": [path.resolve(__dirname, "./assets/js/index.jsx")],
	},
	output: {
		path: path.resolve('./assets/bundles/'),
		filename: "[name]-[hash].js",
	},
    plugins: [
        //tells webpack where to store data about your bundles.
        new BundleTracker({filename: './webpack-stats.json'}), 
        //makes jQuery available in every module
        new webpack.ProvidePlugin({ 
            $: 'jquery',
            jQuery: 'jquery',
            'window.jQuery': 'jquery' 
        })
    ],
    module: {
        loaders: [
		{
			test: /\.jsx?$/,
			exclude: /(node_modules)/,
			loader: 'babel-loader',
			query: {
				presets: ['react','es2015']
			}
		}
        ]
    }
}
djangoApp\Settings.py:

Installed Apps

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'webpack_loader',
]

Add/Edit the following template directive

TEMPLATES = [
 {
    'BACKEND': 'django.template.backends.django.DjangoTemplates',
    'DIRS': [os.path.join(BASE_DIR, 'djangoApp', 'templates'),],
    'APP_DIRS': True,
    'OPTIONS': {
        'context_processors': [
            'django.template.context_processors.debug',
            'django.template.context_processors.request',
            'django.contrib.auth.context_processors.auth',
            'django.contrib.messages.context_processors.messages',
        ],
    },
},]

Add the following static directive

STATIC_URL = '/static/'

STATICFILES_DIRS = [
    os.path.join(BASE_DIR, 'assets'),
]

Modify DATABASES

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'YOUR_DB_NAME',
        'USER': 'YOUR_USER',
        'PASSWORD': 'YOUR_PASSWORD',
        'HOST': 'localhost',
        'PORT': 5432
    }
}

Webpack Loader

WEBPACK_LOADER = {
    'DEFAULT': {
        'BUNDLE_DIR_NAME': 'bundles/',
        'STATS_FILE': os.path.join(BASE_DIR, 'webpack-stats.json'),
    }
}
djangoApp\views.py:

We will create our index page view. Notice the third dict. Those are variables passed to the template to make our site dynamic

from django.shortcuts import render

def index(request):
    return render(request, 'index.html', {'title': 'Index Page', 'script_name': 'index'})
djangoApp\urls.py:

Add the following imports

from django.conf.urls import url
#This is the index view we created above
from djangoApp.views import index

urlpatterns = [
    url(r'^$', index, name='index'),
    path('admin/', admin.site.urls),
]
djangoApp\templates\base\base.html:

Let’s setup our base template and setup our blocks that the other templates will inherit from.

<html>
	<head>
		<title>{% block title %}{% endblock %}</title>
	</head>
	<body>
		{% block content %}
		{% endblock %}
	</body>
</html>
djangoApp\templates\index.html:

The important parts here are the extends otherwise your base.html template won’t be inherited. As well the {% with %} and title variable makes our template dynamic and allows us to incorporate react in our site.

{% extends "base/base.html"  %}
{% load render_bundle from webpack_loader %}
{% load staticfiles %}
{% block title %}
	{{title}}
{% endblock %}
{% block content %}
	<div id="container"></div>
	{% with script=script_name %}
		{% render_bundle script 'js' %}
	{% endwith %} 
{% endblock %}
assets\js\index.jsx:

This is our react class.

var React = require('react');
var ReactDOM = require('react-dom');
var createReactClass = require('create-react-class');

var App = createReactClass({
    render: function() {
        return (
            <h1>
            React App Page
            </h1>
        )
    }
});

ReactDOM.render(<App />, document.getElementById('container'));
Database Setup/Migration:

For this tutorial we used postgres. At this time please make sure you create your djangoApp db and user you specified in the settings.py file. Then run the following commands in order.

#Migrates the auth
python manage.py migrate auth
#migrates the rest
python manage.py migrate
#Create the user for accessing the django admin ui
#This will ask you for user names and passwords. Don't make it the same as in your settings.py file.
python manage.py createsuperuser
Start Server:
webpack -p
python manage.py runserver

Your site is now running at http://localhost:8000.

Your admin site is now running at http://localhost:8000/admin/.

 

References:

I used this video as a guideline to get the project started. However some didn’t work right and needed to adjust and made adjustments to require just one template, etc.

Python: Logging

If you want to do some basic logging to a file, etc. You can use the logging package that comes with python. Here are some of the basic ways to log.

You first have to import the package.

import logging

You can setup your own logging configuration but for this we will just use the basic setup and log to a file.

#If you are going to have multiple handlers you should setup your handler
logging.root.handlers = []

#The file to log to
log_file = /mnt/log/

#Setup the config with the level to log up to
logging.basicConfig(filename=log_file, level=logging.INFO)

Then you setup your logger

logger = logging.getLogger('my_awesome_log')

If you want your log to truncate after a certain size then you must add the handler for truncating the log and back. If you do not use the rotatingfilehandler then the log will increase till your drive runs out of space.

handler = RotatingFileHandler(log_file, maxBytes=1024, backupCount=1)
logger.addHandler(handler)

If you also want to log to console you will need to add an additional handler for the console setting the level to log.

console = logging.StreamHandler()
console.setLevel(logging.INFO)
logger.addHandler(console)

That’s it a basic example of how to use the logging package.

 

Python: Multiprocessing Pool

Sometimes we want to run a method using multiple processors to process our code due to a costly function. Below is an example of how you could do it. There is other api’s you could use like ‘map’ but here is just one example.

from multiprocessing import Pool
# Sets the pool to utilize 4 processes
pool = Pool(processes=4)
result = pool.apply_async(func=my_method, args=("some_info",))
# Performs the aync function
data = result.get()
pool.close()

Python: MRJob

If you use hadoop and you want to run a map reduce type job using Python you can use MRJob.

Installation:

pip install mrjob

Here is an example if you run just the mapper code and you load a json file. yield writes the data out.

from mrjob.job import MRJob, MRStep
import json

class MRTest(MRJob):
    def steps(self):
        return [
            MRStep(mapper=self.mapper_test)
        ]

    def mapper_test(self, _, line):
        result = {}
        doc = json.loads(line)

        yield key, result

if __name__ == '__main__':
    MRTest.run()

Python: Flask Resource

This tutorial helps setup flask restful api’s.

Install Python Packages:

Open cmd and navigate into your testApp folder and run the following commands.

pip install flask-RESTful && pip freeze > requirements.txt
__init__.py:

On the init of your application you will need to setup flask_restful. There are config options you could set for config.py. Look into it!

from flask_restful import Api
api = Api(app)

#Add api endpoints
#Get
api.add_resource(home.views.MyResource, '/home/getMyData/')

#Post
api.add_resource(home.views.MyResource, '/home/getMyData/', methods=['POST'])
Setup home views.py:

You need to import Resource in it’s most simplistic form. However if you want to deal with request parameters add in reqparse and inputs. Inputs give you access to boolean that way a boolean can be parsed into a python boolean easily.

from flask_restful import Resource, reqparse, inputs

You can now use get, post, etc. I will give you three examples below.

Get:

class MyResource(Resource):
	def get(self):
		return {}

Get /w Parameter:

class MyResource(Resource):
	def get(self, var):
		return {}

Get /w Parameter & Request Parameter:

class MyResource(Resource):
	def get(self, var):
        	parser = reqparse.RequestParser()
        	parser.add_argument('new_request_var', type=str, default='')

        	#If you want to have a boolean request parameter do the following.
        	parser.add_argument('new_request_var_bool', type=inputs.boolean, default=False)

        	args = parser.parse_args(strict=True)
        	new_request_var = args['new_request_var']
        	new_request_var_bool = args['new_request_var_bool']

		return {}

Post:

class MyResource(Resource):
	def post(self):
		return {}

Flask: React Website

This whole tutorial describes in depth how to create a React website with Python. You must have Eclipse installed before you continue. If you have it already installed and configured you can continue on. Note that you should probably have HTML Editor and TypeScript IDE installed for Eclipse.

FYI: I am using Windows at the moment for this tutorial but you can substitute Ubuntu in if you so chose.

We will require Postgres 9.4, nodejs before you continue. You can get Nodejs from here. You can get Postgres 9.4 from here.

In this tutorial we use Flask. If you are not familiar with it go here. Flask is a lightweight Python web framework based on Werkzeug and Jinja 2.

Folder Structure:
  • You will need to create a folder called “testApp”.
  • Inside testApp create the following:
    • folder: app
      • file: models.py
      • file: __init__.py
      • folder: auth
        • file: __init__.py
        • file: views.py
      • folder: home
        • file: __init__.py
        • file: views.py
        • folder: js
          • file: home.jsx
      • folder: templates
        • file: base.html
        • file: login.html
        • file: register.html
    • file: config.py
    • file: requirements.txt
    • file: webpack.config.js
    • file: run.py
    • folder: instance
      • file: config.py
    • folder: static
      • folder: common
        • folder: css
          • file: base.css
      • file: manifest.json
    • .babelrc
Install Python Packages:

Open cmd/terminal and navigate into your testApp folder and run the following commands.

pip install flask-sqlalchemy && pip freeze > requirements.txt
pip install flask-login && pip freeze > requirements.txt
pip install flask-migrate && pip freeze > requirements.txt
pip install psycopg2 && pip freeze > requirements.txt
pip install flask-Webpack && pip freeze > requirements.txt
pip install Flask-WTF && pip freeze > requirements.txt
pip install flask-bootstrap && pip freeze > requirements.txt

Couple things to note:

  • The “&& pip freeze > requirements.txt” saves the install in requirements.txt.
  • flask-migrate: database migrations package
  • flask-sqlalchemy: model engine for your database.
  • flask-login: provides user session management for Flask
  • psycopg2: Postgres driver
  • flask-Webpack: A Flask extension to manage assets with Webpack
  • flask-WTF: flask form validation
  • flask-bootstrap: An extension that includes Bootstrap in your project, without any boilerplate code
Routing:

For the purpose of this example we are using the basic Flask implementation. IE: @home.route(‘/’). However if you want to do a more advanced routing do Resource.

Database:

Open PgAdmin III and create yourself a database called “testApp”. Also create a user with password granting access to testApp database. Permission as you see fit. Don’t forget to write down your password :).

Setup Config.py:

Read here for SqlAlchemy configruation options. DEBUG is for flask debugging.

class Config(object):
    """
    This is the shared common configurations
    """

    # Put any configurations here that are common across all environments

class DevelopmentConfig(Config):
    """
    This is the development configurations
    """
    
    DEBUG = True
    SQLALCHEMY_ECHO = True #Display queries to console

class ProductionConfig(Config):
    """
    This is the production configurations
    """
    DEBUG = False
    SQLALCHEMY_ECHO = False #Do not Display queries to console

app_config = {
    'development': DevelopmentConfig,
    'production': ProductionConfig
}

Things to Note:

Notice how we don’t have any database connections in this config.py. That is because we really shouldn’t checkin to source control our database connection strings, etc.

Setup instance config.py:

We open the file config.py inside our “instance” folder and add this line only.

SQLALCHEMY_DATABASE_URI = "postgresql://##USER##:##PASSWORD##@##HOST##/testApp"
Setup __init__.py in “app” folder:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager
from flask_migrate import Migrate
from flask_bootstrap import Bootstrap
import os

#Importing from the config.py
from config import app_config

# db variable initialization
db = SQLAlchemy()

#The flask login manager
login_manager = LoginManager()

webpack = Webpack()

def create_app(config_name):
    #This will be either "development" or "production" mapped to what we write in the config.py application
    #static_folder is where the static folder will be located
    app = Flask(__name__, instance_relative_config=True, static_folder=os.path.join(os.getcwd(), "static"))
    print('Running in %s' % (config_name))
    app.config.from_object(app_config[config_name])
    app.config.from_pyfile('config.py')
    #You need a secret key to be able to utilise the database connection
    app.secret_key = 'Awesome App'
    Bootstrap(app)
    db.init_app(app)
    #This will make it so our chunked js files are able to be loaded on the template
    app.config.update({'WEBPACK_MANIFEST_PATH': '../manifest.json'})
    webpack.init_app(app)

    #if a user tries to access a page that they are not authorized to, it will redirect to the specified view and display the specified message.
    login_manager.init_app(app)
    login_manager.login_message = "You must be logged in to access this page."
    #auth.login is not the route but actually the class path.
    login_manager.login_view = "auth.login"
    
    #This let's us do our migrations
    migrate = Migrate(app, db)

    #Bring in our new tables
    from app import models

    #Our blueprints for our app

    #This is how you get authenticated
    from .auth import auth as auth_blueprint
    app.register_blueprint(auth_blueprint)
	
    #Bring in the home module
    from .home import home as home_blueprint
    app.register_blueprint(home_blueprint)

    return app
Environment Variable Setup:

Flask has two environment variables that you can set which point to the environment to use and the run.py file. These are FLASK_CONFIG and FLASK_APP. I set my environment to “development” and the app to use “run.py”. Notice that “development” maps to the key value pair in config.py.

Setup run.py:

Notice how we utilise the FLASK_CONFIG from the environment variables to setup our environment and grab the right config class.

import os #We need this to get the OS ENV VARIABLE 'FLASK_CONFIG'

#You are going to import the create_app from the __init__.py file
from app import create_app

#In our environment variables we create "FLASK_CONFIG" and set our value either development or production
config_name = os.getenv('FLASK_CONFIG')
app = create_app(config_name)

if __name__ == '__main__':
    app.run()

Now the fun really starts!!!

Setup models.py:

We setup our User model ensuring security of our password. Later on I will show you what happens with flask-migrate.

from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash

from app import db

class User(UserMixin, db.Model):
    """
    Create an Users table
    """

    # Ensures table will be named in plural and not in singular
    # as is the name of the model
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(60), index=True, unique=True)
    username = db.Column(db.String(60), index=True, unique=True)
    first_name = db.Column(db.String(60), index=True)
    last_name = db.Column(db.String(60), index=True)
    password_hash = db.Column(db.String(128))

    @property
    def password(self):
        """
        Prevent pasword from being accessed
        """
        raise AttributeError('password is not a readable attribute.')

    @password.setter
    def password(self, password):
        """
        Set password to a hashed password
        """
        self.password_hash = generate_password_hash(password)

    def verify_password(self, password):
        """
        Check if hashed password matches actual password
        """
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return ''.format(self.username)
Generate User Table:

Using flask-migrate we can now auto generate our User table into Postgres testApp database.

  1. Run “flask db init” to create our migration repository
    1. A “migrations” folder is created automatically.
  2. Run “flask db migrate”
    1. This generates the migration script
  3. Run “flask db upgrade”
    1. This creates the missing migrations into the database. AKA the users table.
    2. You will notice another table also got created “alembic_version”. This is how it stores the version it is at.
  4. Confirm that the db was migrated successfully.
Create Our Templates:

We use the base.html file for our react pages using the “app” id on the div. The login.html is for our login form and register.html is to register a new user. You can use the base.css file in the static/common/css folder to style it how you want. I recommend using flex.

base.html:

<!DOCTYPE html>
{% import "bootstrap/utils.html" as utils %}
{% import "bootstrap/wtf.html" as wtf %}
{% extends "bootstrap/base.html" %}
<html>
	<head>
		{% block head %}
		{{ super() }}
		<title>{{title}}</title>
		<link rel="stylesheet" type="text/css" href={{ url_for("static", filename="common/css/base.css") }} />
		{% endblock %}
	</head>
	{% block content %}
	<div class="container-app">
		<div class="container-header">Team Link</div>
	    <div class="container-body" id="app"></div>
	    <script type="text/javascript" src={{ asset_url_for(script_name) }}></script>
	</div>
	{% endblock %}
</html>

login.html:

<!DOCTYPE html>
{% import "bootstrap/utils.html" as utils %}
{% import "bootstrap/wtf.html" as wtf %}
{% extends "bootstrap/base.html" %}
<html>
	<head>
		{% block head %}
		{{ super() }}
		<title>{{title}}</title>
		<link rel="stylesheet" type="text/css" href={{ url_for("static", filename="common/css/base.css") }} />
		{% endblock %}
	</head>
	{% block content %}
	<div class="container-app">
		<div class="container-header">My Awesome App</div>
	    <div class="container-body" id="app">
			<div class="panel panel-default">
				<div class="panel-heading">
					<h3 class="panel-title">Login</h3>
				</div>
				<div class="panel-body">
					{{ wtf.quick_form(form) }}
				</div>	    
				{{ utils.flashed_messages() }}
				Click here to <a href="/register">register</a>
			</div>
		</div>
	</div>
	{% endblock %}
</html>

register.html

<!DOCTYPE html>
{% import "bootstrap/utils.html" as utils %}
{% import "bootstrap/wtf.html" as wtf %}
{% extends "bootstrap/base.html" %}
<html>
	<head>
		{% block head %}
		{{ super() }}
		<title>{{title}}</title>
		<link rel="stylesheet" type="text/css" href={{ url_for("static", filename="common/css/base.css") }} />
		{% endblock %}
	</head>
	{% block content %}
	<div class="container-app">
		<div class="container-header">Team Link</div>
	    <div class="container-body" id="app">
			<div class="panel panel-default">
				<div class="panel-heading">
					<h3 class="panel-title">Register</h3>
				</div>
				<div class="panel-body">
    				{{ wtf.quick_form(form) }}
				</div>	    
				{{ utils.flashed_messages() }}
				Click here to <a href="login">login</a>
			</div>
		</div>
	</div>
	{% endblock %}
</html>
Setup home __init__.py:

This creates the blueprint that we have in app.__init__.py.

from flask import Blueprint

home = Blueprint('home', __name__)

#This is the views.py from the home directory.
from . import views
Setup home views.py:

@login_required let’s flask know that you need to be logged in to get to this page. Don’t forget to see “render_template” method. How it has “script_name” in it and it uses base.html template. “script_name” was utilised in base.html. It brings in our js file for us on each page we go to.

from flask import render_template
from flask_login import login_required

#This is our blueprint we setup in __init__.py
from . import home

@home.route('/')
@login_required
def homepage():
    """
    Render the homepage template on the / route
    """
    return render_template('base.html', script_name='home.js', title="Welcome")
Setup React home JSX file:

React uses jsx files. So in my home directory I have a js folder and inside that we have our home.jsx file. Let’s set that up to something really basic. Remember above I said in the “render_template” we use the div id “app”. The ReactDOM will put our class in that spot. I will show you later how that is done.

var React = require("react");
var ReactDOM = require("react-dom");
var createReactClass = require("create-react-class");

var Home = createReactClass({
	render: function() {
		return (<div>Hi</div>);
	}
});

ReactDOM.render(<Home />, document.getElementById("app"));
Node Init:

We need to go to our root directory testApp from command prompt and run the following in the exact order.

npm init
npm install react react-dom --save
npm install webpack webpack-dev-server --save
npm install --save-dev babel babel-core babel-loader babel-preset-es2015 babel-preset-react
npm install create-react-class --save
npm install bootstrap --save
npm install jquery --save
npm install clean-webpack-plugin --save-dev
npm install manifest-revision-webpack-plugin --save-dev
npm install sync-exec --save-dev
npm install uglifyjs-webpack-plugin --save-dev

Things to Note:

  • npm init: Creates package.json file
  • npm install –save: will save the install to package.json
  • Each package install went into a new directory called “node_modules”.
Setup webpack.config.js:

We want to transition our jsx files to .js. Remember back in app.__init__.py we setup the static folder location. Checkout the “path” key below and now we know how it knows where it’s assets are going to be located. Our entry key value pair is the locations of each of our jsx files to create assets from. Then we have our loaders.

var path = require("path");
var webpack = require('webpack');
var ManifestRevisionPlugin = require('manifest-revision-webpack-plugin');
const CleanWebpackPlugin = require('clean-webpack-plugin');

module.exports = {
	entry: {
		"home": [path.resolve(__dirname, "./app/home/js/home.jsx")],
	},
	output: {
        path: path.join(__dirname, "static"),
		publicPath: "/static/",
		filename: "[name]-[hash].js"
	},
	plugins: [
                new CleanWebpackPlugin(["static/*.js", "static/manifest.json"], {root: __dirname, verbose: true, dry: false, exclude: ["base.css"]}),
		new ManifestRevisionPlugin(path.resolve(__dirname, "./manifest.json"), {rootAssetPath: './static', ignorePaths: ['./node_modules']}),
		new webpack.NoEmitOnErrorsPlugin(),
		new UglifyJsPlugin(),
		new webpack.optimize.AggressiveMergingPlugin(),
		new webpack.HotModuleReplacementPlugin()
	],
	module: {
		loaders: [
			{
				test: /\.jsx?$/,
				exclude: /(node_modules)/,
				loader: 'babel-loader',
				query: {
					presets: ['react','es2015']
				}
			}
		]
	}
};
.babelrc

Set the following in your file at the root directory.

{
 "presets": [ "es2015", "react" ]
}
Let’s Test Node & Webpack:

Open command prompt and navigate to our testApp folder and run “webpack”. You will notice that a “static” folder is created in our root directory. In it we will now see “home.js” file. Remember back above we set __init__.py static folder and in home.views.py file we said in render_template script_name “home.js”. This is how it all maps together.

Setup auth __init__.py:

This creates the blueprint that we have in app.__init__.py.

from flask import Blueprint

auth = Blueprint('auth', __name__)

#This is the views.py from the auth directory.
from . import views

Setup auth views.py:

from flask import flash, redirect, render_template, url_for
from flask_login import login_required, login_user, logout_user
from flask_wtf import FlaskForm
from wtforms import PasswordField, StringField, SubmitField, ValidationError
from wtforms.validators import DataRequired, Email, EqualTo
from .. import db, login_manager
from ..models import User

from . import auth

class RegistrationForm(FlaskForm):
    """
    Form for users to create new account
    """
    email = StringField('Email', validators=[DataRequired(), Email()])
    username = StringField('Username', validators=[DataRequired()])
    first_name = StringField('First Name', validators=[DataRequired()])
    last_name = StringField('Last Name', validators=[DataRequired()])
    password = PasswordField('Password', validators=[
                                        DataRequired(),
                                        EqualTo('confirm_password')
                                        ])
    confirm_password = PasswordField('Confirm Password')
    submit = SubmitField('Register')

    def validate_email(self, field):
        if User.query.filter_by(email=field.data).first():
            raise ValidationError('Email is already in use.')

    def validate_username(self, field):
        if User.query.filter_by(username=field.data).first():
            raise ValidationError('Username is already in use.')

class LoginForm(FlaskForm):
    """
    Form for users to login
    """
    email = StringField('Email', validators=[DataRequired(), Email()])
    password = PasswordField('Password', validators=[DataRequired()])
    submit = SubmitField('Login')

@login_manager.user_loader
def load_user(id):
    #This is the how we locate the user in our testApp database
    return User.query.get(int(id))

@auth.route('/register', methods=['GET', 'POST'])
def register():
    form = RegistrationForm()
    if form.validate_on_submit():
        user = User(email=form.email.data,
                            username=form.username.data,
                            first_name=form.first_name.data,
                            last_name=form.last_name.data,
                            password=form.password.data)

        # add user to the database
        db.session.add(user)
        db.session.commit()
        flash('You have successfully registered! You may now login.')

        # redirect to the login page
        return redirect(url_for('auth.login'))

    # load registration template
    return render_template('register.html', form=form, title='Register')

@auth.route('/login', methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        # check whether user exists in the database and whether
        # the password entered matches the password in the database
        user = User.query.filter_by(email=form.email.data).first()
        if user is not None and user.verify_password(
                form.password.data):
            # log user in
            login_user(user)

            # redirect to the dashboard page after login
            return redirect("/")

        # when login details are incorrect
        else:
            flash('Invalid email or password.')

    # load login template
    return render_template('login.html', form=form, title='Login')

@auth.route('/logout')
@login_required
def logout():
    """
    Handle requests to the /logout route
    Log an user out through the logout link
    """
    logout_user()
    flash('You have successfully been logged out.')

    # redirect to the login page
    return redirect(url_for('auth.login'))
Let’s Run our App:

Open command prompt navigate to our testApp folder and run “flask run”. If no mistakes were made you should now be able to navigate to our site. http://localhost:5000.

 

Resources:

In learning how to incorporate flask into python I used the following part one only tutorial as a guide. Very helpful.

Python: Flask SQLAlchemy

If you are using SQLAlchemy then no doubt you’ve ran into some questions. Find below some more common usages.

Installation:

pip install flask-sqlalchemy && pip freeze > requirements.txt

Table Definition:
You will notice that we are creating a unique constraint, primary keys, foreign keys.

from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql import BOOLEAN, BIT, DATE, JSON, DOUBLE_PRECISION, UUID
from sqlalchemy.sql.functions import current_timestamp
from sqlalchemy.schema import UniqueConstraint

class MyTable(db.Model):
    __tablename__ = 'mytable'
    __table_args__ =  (UniqueConstraint('column_name'),)
    
    primarykey_id = db.Column(db.Integer, primary_key=True, autoincrement="True")
    bit_column = db.Column(BIT)
    text_column = db.Column(db.Text)
    foreignkey_id = db.Column(db.Integer, db.ForeignKey('table.column'))
    foreignkey = relationship("table", foreign_keys=[foreignkey_id ])
    bool_column = db.Column(BOOLEAN)
    created_date = db.Column(db.DateTime, default=current_timestamp())
    guid_column = db.Column(UUID)
    bigint_column = db.Column(db.BigInteger)
    double_precision_column = db.Column(DOUBLE_PRECISION)
    json_column = db.Column(JSON)
    string_column = db.Column(db.String(200))
    
    def __init__(self, primarykey_id, bit_column):
        self.primarykey_id = primarykey_id
        self.bit_column = bit_column

    def __repr__(self):
        return '<MyTable primarykey_id %r Name %r bit_column %r>' % (self.primarykey_id, self.bit_column)

Database Connections:
The default connection is the “SQLALCHEMY_DATABASE_URI”. However you can attach to other DB by using the BINDS as demonstrated below.

SQLALCHEMY_DATABASE_URI = "postgresql://##USER##:##PASS##@##SERVER##/##DB##"

SQLALCHEMY_BINDS = {
	"NAME": "postgresql://##USER##:##PASS##@##SERVER##/##DB##",
}

b.session().execute("", params=dict(), bind=db.get_engine(app, 'NAME'))

Concatenation:

 from sqlalchemy.sql.expression import func

func.concat(Table.Column,'-',Table.Column2)

Case Statements:

 from sqlalchemy.sql.expression import case

case([(column1 == 'some_val', 1)], else_=0)

Ordering:
The example below does multiple columns but you can do just one if need be.

 order_by(Table.Column1.desc(), Table.Column2.asc())

Literals:
If you want to add NULL as a column output you must use literals. You could do this with a numerical data or string or whatever.

 from sqlalchemy import literal

literal(None).label('column_name')

Retrieving Records:

 .first()
.one()
.fetchone()

.all()
.fetchall()

Joins:

 join(Table2, and_(Table2.column_id==Table1.column_id))
outerjoin(Table2, and_(Table2.column_id==Table1.column_id))

Union:

 query1.union(query2)

Select Fields:
This just selects one column but you can comma separate it.

 .with_entities(Table.Column)

Where:
You don’t have to use and_ or or_. Use them as needed.

 from sqlalchemy.sql.expression import and_, or_

.filter(and_(Table.Column1, Table.Column2, or_(Table.Column3_, Table.Column4))))

Not:
Select data where value is not NULL

 Table.Column.isnot(None)

Coalesce:

 from sqlalchemy.sql.functions import  coalesce

coalesce(Table.Column, 'Unknown')

Sum:

 from sqlalchemy.sql.expression import func

func.sum(Table.Column)

Count:

 from sqlalchemy.sql.expression import func

func.count()
func.count('*')

Max:

 from sqlalchemy.sql.expression import func

func.max(Table.Column)

AVG:

 from sqlalchemy.sql.expression import func

func.avg(Table.Column)

Substring:

 from sqlalchemy.sql.expression import func

func.substring(Table.Column, REGEX)

Like:

 Table.Column.like("value")

Not Like:

 from sqlalchemy.sql.expression import not_

not_(Table.Column.like("value"))

Commit & Rollback:

 #commit
session.commit()

#rollback
session.rollback()

Inline SQL:

 .execute('SELECT * FROM SP(:param)', params=dict(param=1))

Cast:

 from sqlalchemy.sql.functions import Cast

Cast(Table.Column, BIT)

Array with Distinct:

 from sqlalchemy.sql.expression import distinct, func

func.array_agg(distinct(Table.Column)).label('column_name')