Search This Blog

Sunday, August 12, 2018

Joins in Mongo

In SQL join is widely used for finding related data from one table w.r.t, to other.
In mongo joins can be performed by using lookup functionality.
   To demonstrate lookup i will create two simple collection one for student & other for course.student collection hold courseId which is kind of foreign key pointing to record in course collection.
   I had populated student & course collection with some dummy data.
For
    db.student.find()
    Output:
        {
            "_id" : ObjectId("5b703776b7542ef044bc3f9f"),
            "id" : 1.0,
            "name" : "sangram",
            "courseId" : 1.0
        }
        {
            "_id" : ObjectId("5b703781b7542ef044bc3fa0"),
            "id" : 2.0,
            "name" : "sagar",
            "courseId" : 1.0
        }
        {
            "_id" : ObjectId("5b70378ab7542ef044bc3fa1"),
            "id" : 3.0,
            "name" : "sachin",
            "courseId" : 2.0
        }
        {
            "_id" : ObjectId("5b703793b7542ef044bc3fa2"),
            "id" : 4.0,
            "name" : "sachin",
            "courseId" : 3.0
        }

and
    db.course.find()
    Output:
        {
            "_id" : ObjectId("5b70379db7542ef044bc3fa3"),
            "id" : 4.0,
            "name" : "c"
        }
        {
            "_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
            "id" : 1.0,
            "name" : "cpp"
        }
        {
            "_id" : ObjectId("5b7037afb7542ef044bc3fa5"),
            "id" : 2.0,
            "name" : "VB 6.0"
        }
        {
            "_id" : ObjectId("5b7037bdb7542ef044bc3fa6"),
            "id" : 3.0,
            "name" : "V C#"
        }

we can join on course id as follows

    db.student.aggregate([
       {
       
          $lookup:
           {
         from: "course",
         localField: "courseId",
         foreignField: "id",
         as: "student_courses"
           }   
      }
    ])


    Output:
        {
            "_id" : ObjectId("5b703776b7542ef044bc3f9f"),
            "id" : 1.0,
            "name" : "sangram",
            "courseId" : 1.0,
            "student_courses" : [
            {
                "_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
                "id" : 1.0,
                "name" : "cpp"
            }
            ]
        }
        {
            "_id" : ObjectId("5b703781b7542ef044bc3fa0"),
            "id" : 2.0,
            "name" : "sagar",
            "courseId" : 1.0,
            "student_courses" : [
            {
                "_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
                "id" : 1.0,
                "name" : "cpp"
            }
            ]
        }
        {
        "_id" : ObjectId("5b70378ab7542ef044bc3fa1"),
        "id" : 3.0,
        "name" : "sachin",
        "courseId" : 2.0,
        "student_courses" : [
        {
            "_id" : ObjectId("5b7037afb7542ef044bc3fa5"),
            "id" : 2.0,
            "name" : "VB 6.0"
        }
        ]
    }
    {
        "_id" : ObjectId("5b703793b7542ef044bc3fa2"),
        "id" : 4.0,
        "name" : "sachin",
        "courseId" : 3.0,
        "student_courses" : [
        {
            "_id" : ObjectId("5b7037bdb7542ef044bc3fa6"),
            "id" : 3.0,
            "name" : "V C#"
        }
        ]
    }
Here output shows student collection data along with referenced course record.

Gambas building simple calculator

Gambas is an Ide available on linux that let us to develop window & web application using Visual Basic syntax.We can visually design design
window form add required widgets & code for required events of widget just like Visual Basic studio.Gambas 3.11 is latest release.Lets build simple calculator using gambas.

I designed calculator form using textbox & buttons. My screen look like below

Code behind for Fmain i.e. Fmail.class look like




' Gambas class file

 v1 As Float = 0
 opr As String = ""

Public Sub Form_Open()

End

Public Sub BtnZero_Click()
  vbDisplay.Text = vbDisplay.Text & "0"
End

Public Sub BtnSeven_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "7")
End

Public Sub BtnEight_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "8")
End

Public Sub BtnNine_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "9")
End

Public Sub BtnFour_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "4")
End

Public Sub BtnFive_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "5")
End

Public Sub BtnSix_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "6")
End

Public Sub BtnOne_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "1")
End

Public Sub BtnTwo_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "2")
End

Public Sub BtnThree_Click()
  vbDisplay.Text = Val(vbDisplay.Text & "3")
End

Public Sub BtnPlus_Click()
  v1 = vbDisplay.Text
  opr = "a"
  vbDisplay.Text = "0"
End

Public Sub BtnEqual_Click()
  Select opr
   Case "a"
      vbDisplay.Text = v1 + Val(vbDisplay.Text)
   Case "s"
      vbDisplay.Text = v1 - Val(vbDisplay.Text)
     
   Case "m"
    vbDisplay.Text = v1 * Val(vbDisplay.Text)
   Case "p"
       vbDisplay.Text = (v1 * Val(vbDisplay.Text)) / 100
   Case "d"
     If Val(vbDisplay.Text) <> 0 Then
         vbDisplay.Text = v1 / Val(vbDisplay.Text)
     Endif
     
  End Select

End

Public Sub BtnSubstract_Click()
 v1 = vbDisplay.Text
  opr = "s"
  vbDisplay.Text = ""
End

Public Sub BtnMultiply_Click()
  v1 = vbDisplay.Text
  opr = "m"
  vbDisplay.Text = ""
End

Public Sub BtnDivide_Click()
  v1 = vbDisplay.Text
  opr = "d"
  vbDisplay.Text = ""
End

Public Sub BtnDot_Click()
  vbDisplay.Text = vbDisplay.Text & "."
End

Public Sub BtnPercent_Click()
  v1 = vbDisplay.Text
  opr = "p"
  vbDisplay.Text = ""
End

Public Sub BtnClear_Click()
  v1 = 0
  opr = ""
  vbDisplay.Text = ""
End

Public Sub BtnSign_Click()
  vbDisplay.Text = -1 * Val(vbDisplay.Text)
End

Go to Debug > Run and execute our Calc.

Now we have functional simple calculator in Gambas.

You can create executable & then create package, In my case i choose  .deb package from list of available options,Then installed new package for test purpose .

I got my new calc with default Duck icon visible in my gnome menu by searching for calc.I was able to run the calc.

 My calc's package name was conflux-gambas.deb.I was able to remove package from using apt-get.

apt-get remove conflux-gambas

Thus we can create a desktop application  using Gambas & distribute it across various linux platform.

Source Code can be found at https://github.com/gitsangramdesai/gambas-execuatable

Saturday, August 11, 2018

Flask SQLAlchemy CRUD Demo

To demonstrate Flask CRUD functionality we will use simple sqlite table having fields
name,city,addr,pin.Assuming name is primary key ,although we are not enforcing it in database design or code.

The class corresponding to our student table look like

class students(db.Model):
   id = db.Column('student_id', db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   city = db.Column(db.String(50))
   addr = db.Column(db.String(200))
   pin = db.Column(db.String(10))

   def __init__(self, name, city, addr,pin):
      self.name = name
      self.city = city
      self.addr = addr
      self.pin = pin

complete app.py looks like below

from flask import Flask, request, flash, url_for, redirect, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'
app.config['SECRET_KEY'] = "random string"

db = SQLAlchemy(app)

class students(db.Model):
   id = db.Column('student_id', db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   city = db.Column(db.String(50))
   addr = db.Column(db.String(200))
   pin = db.Column(db.String(10))

   def __init__(self, name, city, addr,pin):
      self.name = name
      self.city = city
      self.addr = addr
      self.pin = pin

@app.route('/')
def show_all():
   return render_template('show_all.html', students = students.query.all() )

@app.route('/delete')
def delete():
   sname = request.args.get('name')
   s = students.query.filter_by(name=sname).first()
   db.session.delete(s)
   db.session.commit()
   return redirect("/", code=302)

@app.route('/edit', methods = ['GET', 'POST'])
def edit():
      if request.method == 'POST':
            name = request.form['name']
            s = students.query.filter_by(name=name).first()
            s.city = request.form['city']
            s.addr = request.form['addr']
            s.pin = request.form['pin']
         
            db.session.commit()
            flash('Record was successfully added')
            return redirect(url_for('show_all'))
      else:
            sname = request.args.get('name')
            s = students.query.filter_by(name=sname).first()
            return render_template('edit.html',data = s)



@app.route('/new', methods = ['GET', 'POST'])
def new():
   if request.method == 'POST':
      if not request.form['name'] or not request.form['city'] or not request.form['addr']:
         flash('Please enter all the fields', 'error')
      else:
         student = students(request.form['name'], request.form['city'],request.form['addr'], request.form['pin'])
        
         db.session.add(student)
         db.session.commit()
         flash('Record was successfully added')
         return redirect(url_for('show_all'))

   s = students('','','','')
   return render_template('new.html',data=s)

if __name__ == '__main__':
   db.create_all()
   app.run(debug = True)

then we have template folder inside which we have 3 templates
edit.html,new.html & show_all.html.

show_all.html looks like below

<!DOCTYPE html>
<html lang = "en">
   <head></head>
   <body>
     
      <h3>
         <a href = "{{ url_for('show_all') }}">Comments - Flask
            SQLAlchemy example</a>
      </h3>
     
      <hr/>
      {%- for message in get_flashed_messages() %}
         {{ message }}
      {%- endfor %}
       
      <h3>Students (<a href = "{{ url_for('new') }}">Add Student
         </a>)</h3>
     
      <table border="1">
         <thead>
            <tr>
               <th>Name</th>
               <th>City</th>
               <th>Address</th>
               <th>Pin</th>
               <th></th>
               <th></th>
            </tr>
         </thead>
        
         <tbody>
            {% for student in students %}
               <tr>
                  <td>{{ student.name }}</td>
                  <td>{{ student.city }}</td>
                  <td>{{ student.addr }}</td>
                  <td>{{ student.pin }}</td>
                  <td>
                        <a href="/delete?name={{ student.name }}">delete</a>
                  </td>
                  <td>
                        <a href="/edit?name={{ student.name }}">edit</a>
                  </td>
               </tr>
            {% endfor %}
         </tbody>
      </table>
     
   </body>
</html>

it is simple html to display record in tabular format.

new.html template is html for adding new record

<!DOCTYPE html>
<html>
   <body>
  
      <h3>Students - Flask SQLAlchemy example</h3>
      <hr/>
     
      {%- for category, message in get_flashed_messages(with_categories = true) %}
         <div class = "alert alert-danger">
            {{ message }}
         </div>
      {%- endfor %}
     
      <form action = "{{ request.path }}" method = "post">
         <label for = "name">Name</label><br>
         <input type = "text" name = "name" placeholder = "Name" /><br>
         <label for = "email">City</label><br>
         <input type = "text" name = "city" placeholder = "city"  /><br>
         <label for = "addr">addr</label><br>
         <textarea name = "addr" placeholder = "addr"></textarea><br>
         <label for = "PIN">Pin</label><br>
         <input type = "text" name = "pin" placeholder = "pin" /><br>
         <input type = "submit" value = "Submit" />
      </form>
     
   </body>
</html>

edit.html is html template for editing existing record where name property is not editable.

<!DOCTYPE html>
<html>
   <body>
  
      <h3>Students - Flask SQLAlchemy example</h3>
      <hr/>
     
      {%- for category, message in get_flashed_messages(with_categories = true) %}
         <div class = "alert alert-danger">
            {{ message }}
         </div>
      {%- endfor %}
     
      <form action = "{{ request.path }}" method = "post">
         <label for = "name">Name</label><br>
         <input type = "text" name = "name" placeholder = "Name" readonly=true value="{{data.name}}" /><br>
         <label for = "email">City</label><br>
         <input type = "text" name = "city" placeholder = "city" value="{{data.city}}" /><br>
         <label for = "addr">addr</label><br>
         <textarea name = "addr" placeholder = "addr">{{data.addr}}</textarea><br>
         <label for = "PIN">Pin</label><br>
         <input type = "text" name = "pin" placeholder = "pin" value="{{data.pin}}" /><br>
         <input type = "submit" value = "Submit" />
      </form>
     
   </body>
</html>

For adding we need to create student object & add it to db & then commit

     student = students(request.form['name'], request.form['city'],request.form['addr'], request.form['pin'])
         db.session.add(student)
         db.session.commit()

For editing we have to first take clean object from database and then update its property & then commit
        s = students.query.filter_by(name=name).first()
            s.city = request.form['city']
            s.addr = request.form['addr']
            s.pin = request.form['pin']
            db.session.commit()

For deleting we need to call db.session.delete on student object to be deleted
    s = students.query.filter_by(name=sname).first()
    db.session.delete(s)
    db.session.commit()

Projects code can be viewed at https://github.com/gitsangramdesai/flask-orm-crud