Python SQLAlchemy

August 23, 2017 By globalsqa No comments yet Python

Python SQLAlchemy and Object-Relational Mapping

Whenever it comes to programming a web service, you will require a solid database backend. In the past, programmers used to write raw SQL statements, pass them to the database engine and parse the returned results as a normal array of records. Nowadays, programmers can write Object-Relational Mapping (ORM) programs to remove the necessity of writing tedious and error-prone raw SQL statements.

WHAT IS ORM?

Most programming language platforms are object oriented. ORM is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Data in RDBMS (Relational Database Management System) servers on the other hand is stored as tables. Object relation mapping is a technique of mapping object parameters to the underlying RDBMS table structure. An ORM API provides methods to perform create, read, update, delete (CRUD) operations without having to write raw SQL statements. So basically, an ORM takes care of these issues while you can focus on programming the logics of the system.

Installation:

In order to run the python script with SQLAlchemy codes in it, you need to install flask-sqlalchemy extension first for that run the command :

pip install flask-sqlalchemy

1	pip install flask-sqlalchemy

Let’s Get To Know SQLAlchemy

For using functions from SQLAlchemy in your program, you need to import it first using the statement :

from flask_sqlalchemy import SQLAlchemy

1	from flask_sqlalchemy import SQLAlchemy

Now you can create your Flask application and make surem you set the URI for the database which is to be used in your program.

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///persons.sqlite3'

1 2	app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///persons.sqlite3'

Now, you need to create an object of SQLAlchemy class with the application object provided as parameter. The object provides helper functions for ORM operations and also provides a Model class using which user-defined models for database is declared. In the following code snippet a persons model is created:

db = SQLAlchemy(app)
class persons(db.Model):
   id = db.Column(db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   phone = db.Column(db.String(10))  
def __init__(self, name, phone):
   self.name = name
   self.phone = phone

db = SQLAlchemy(app)

class persons(db.Model):

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

name = db.Column(db.String(100))

phone = db.Column(db.String(10))

def __init__(self, name, phone):

self.name = name

self.phone = phone

To create or use the database that we specified in the URI run the create_all() method.

db.create_all()

1	db.create_all()

To add an object data to the database we can use the following code:

db.session.add(model object)

1	db.session.add(model object)

For deleting just replace the add function with delete function and if you need to retrieve the records of the table use:

model.query.all()

1	model.query.all()

You can also apply filters while retrieving records. For eg:

model.query.filter_by(name = ‘abc’).all()

1	model.query.filter_by(name = ‘abc’).all()

will return the set of table rows whose city attribute is equal to ‘Mumbai’.

SAMPLE PROGRAM

Now, let’s see an entire program based on what we’ve learned so far:

First let’s setup an html page to display our database contents.

<!DOCTYPE html>
<html lang = "en">
   <head></head>
   <body>
      
      <h3>
         <a>SQLAlchemy TEST</a>
      </h3>
      
      <table>
         <thead>
            <tr>
               <th>Name</th>
               <th>Phone</th>
            </tr>
         </thead>
         
         <tbody>
            {% for person in persons %}
               <tr>
                  <td>{{ person.name }}</td>
                  <td>{{ person.phone }}</td>
               </tr>
            {% endfor %}
         </tbody>
      </table>
      
   </body>
</html>

<!DOCTYPE html>

<body>

<h3>

<a>SQLAlchemy TEST</a>

</h3>

<table>

<thead>

<tr>

<th>Phone</th>

</tr>

</thead>

<tbody>

{% for person in persons %}

<tr>

<td>{{ person.name }}</td>

<td>{{ person.phone }}</td>

</tr>

{% endfor %}

</tbody>

</table>

</body>

</html>

We’ve set an HTML page with a heading SQLAlchemy Test and it has a table with two columns to display a persons name and phone number. Now we need to code a python script to do our job.

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:///persons.sqlite3'
app.config['SECRET_KEY'] = "random string"

db = SQLAlchemy(app)

class persons (db.Model):
   id = db.Column(db.Integer, primary_key = True)
   name = db.Column(db.String(100))
   phone = db.Column(db.String(10))
   

   def __init__(self, name, phone):
      self.name = name
      self.phone = phone

@app.route('/')
def show_all():
   person = persons("abc","9847068114")
   db.session.add(person)
   person = persons ("efg","9847068118")
   db.session.add(person)
   person = persons ("hijk","7012665458")
   db.session.add(person)
   person = persons ("lmno","7012898456")
   db.session.add(person)
   db.session.commit()
   return render_template('show_all.html', persons = persons.query.all() )


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

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:///persons.sqlite3'

app.config['SECRET_KEY'] = "random string"

db = SQLAlchemy(app)

class persons (db.Model):

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

name = db.Column(db.String(100))

phone = db.Column(db.String(10))

def __init__(self, name, phone):

self.name = name

self.phone = phone

@app.route('/')

def show_all():

person = persons("abc","9847068114")

db.session.add(person)

person = persons ("efg","9847068118")

db.session.add(person)

person = persons ("hijk","7012665458")

db.session.add(person)

person = persons ("lmno","7012898456")

db.session.add(person)

db.session.commit()

return render_template('show_all.html', persons = persons.query.all() )

if __name__ == '__main__':

db.create_all()

app.run(debug = True)

In the above python script, we create a model class for person which can hold values for a person’s name and phone number. We set the route of out app to the homepage of localhost. So after running the app, when you open the browser and visit your localhost homepage by typing 127.0.0.1:5000 in your browser’s address bar, you can see the html page we’ve setup. It will show all function finally returns the rendered template of show_all.html populated with the contents in the database.

Now, we can checkout how we add contents to the database. For that we need to create objects of our model class and provide values for its attributes i.e. name and phone of a person. Now we can add it to our database using SQLAlchemy_object.session.add(model object) function. After adding all the values make sure you commit the changes to the database in order to prevent data corruption. Now, we have our database ready with contents, we can display it on our html page using render template(‘html_file_name’, model = model.query.all()) function.

Save the python file and place the html file in a folder named templates. If no such folder exists create one. Now you can run the python script and visit 127.0.0.1:5000 in your browser to see your app running.

Benefits of SQLAlchemy:

According to http://pajhome.org.uk/blog/10_reasons_to_love_sqlalchemy.html here are the top 10 reasons to love SQLAlchemy

Let’s you define the database schema in your code
Automatically synchronise the model and schema
Easy to read
Simple queries.
Seamless integration with web frameworks
Fast loading, Better performance
Transparent Polymorphism
Works with legacy frameworks
Easy to customise the library
Great Documentation

Challenges of SQLAlchemy.

The concept of unit of work is not very known among the developer community
A heavy-weight API

Leave a Reply Cancel reply

Recent Posts

About Us

Quick Menu

Contact Info

Python SQLAlchemy

Python SQLAlchemy and Object-Relational Mapping

WHAT IS ORM?

Installation:

Let’s Get To Know SQLAlchemy

SAMPLE PROGRAM

Benefits of SQLAlchemy:

Challenges of SQLAlchemy.

Sharing is Caring:

Related

Leave a Reply Cancel reply

Recent Posts

About Us

Quick Menu

Contact Info