Simple Web Based Content Management System using SQL Server, Python and Flask

By:   |   Updated: 2022-11-08   |   Comments (1)   |   Related: More > Python


Problem

As a developer, it is never a good idea to rely on third-party software to handle sensitive data, industries like financial services and healthcare are subject to strict data protection regulations and thus require a CMS that protects effectively from unauthorized third-party data access. Say you are tasked to create an internally managed web application and give users CRUD functionality.

There are a lot of options when it comes to CMS tools to help with the process of creating a CMS along with APIs, but they also generally have restrictions and limits as to what coding languages they work with and which data sources they support.

Nowadays, for you to create a CMS, it is either you learn a third-party CMS like WordPress or Joomla, even after learning them, you will only be exposed to basic free templates and expensive advanced templates which are not required to be maintained for you and you get to pay for training and support.

Solution

Create your own Content Management System using Python flask with SQL Server providing the database and use the good old HTML to handle your views. Having your own CMS is very liberating because you have absolute control of the whole system. You have direct and secure access to your data. You get to adhere to the requirements of your task and have a flexible web development process.

Creating a Database

Step 1: Open Microsoft SQL Server Management Studio.

Step 2: Click on New Query and add the SQL code as shown below.

CREATE DATABASE MssqlTipsDB

Step 3: Select Tables under MssqlTipsDB and click on New Query to write your SQL statement.

New query

Step 4: Use the SQL code below to create a simple table Blogs with the following columns: "Blogid", "Username", "BlogTitle", "BlogDescription".

CREATE TABLE Blogs (
   BlogID int,
   Username varchar(255),
   BlogTitile varchar(255),
   BlogDescription varchar(255)
);
Create simple table

Click on execute once done to apply the changes.

Execute changes

You should be able to see the newly created table. If not, right click and select Refresh.

Step 5: Refresh your local cache.

Refresh local cache

Step 6: Create another Query to add sample data to the database using the SQL code below.

INSERT INTO Blogs (BlogID,Username,BlogTitle,BlogDescription)
VALUES (1, 'Greg', 'First Title', 'First Description');
INSERT INTO Blogs (BlogID,Username,BlogTitle,BlogDescription)
VALUES (2, 'Jeremy', 'Second Title', 'Second Description');
INSERT INTO Blogs (BlogID,Username,BlogTitle,BlogDescription)
VALUES (3, 'Bob', 'Third Title', 'Third Description');

Creating a Data Connection

Step 1: Create a Python file named connection.py and write the code shown below.

Create python file

This will be the connection string you will refer to later. Make sure you use your server's Data Source Name and Server Name.

Create a Flask Application

Step 1: Open your documents in Windows File Explorer and create a folder to host your API project.

Step 2: Right Click on the folder and click "Open with Code".

Create a folder to host API

Step 3: Once in Visual Studio, press ctrl + shift + ` to open the cli terminal.

Step 4: On the terminal, use the npm command npm init -y to initialize a node project.

Initialize a node project

This command will create a package.json file under your folder.

Step 5: Use the pip command pip install flask to install flask on your machine.

Install flask

Step 6: Create a file APIApp.py and write the code as shown below to define your Flask app.

from flask import Flask, render_template, redirect, request
from connection import coxn
from sqlalchemy import text
 
blogs = Flask(__name__)
 
if(__name__ == "__main__"):
    blogs.run()

Step 7: Click the RUN button on Visual Studio to run this script.

RUN button in Visual Studio

Step 8: The terminal will show the output as shown below.

terminal window

Step 9: Run the server on your browser by holding CTRL and clicking http://127.0.0.1:5000 or go to your browser and manually enter http://127.0.0.1:5000 on the URL.

Run server on browser

Even if you receive the 404 error, you also just got a confirmation that Flask has handled Setting up the server. It is time to create the HTML file for the View and create Routing for our pages.

Creating HTML Templates and Routes

Step 1: Create a new folder named templates under your main MSSQL_API folder.

Create templates folder

Step 2: Create an HTML file named BlogList.html under the newly created templates folder and the code shown below.

<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Blogs List</title>
    <link rel="stylesheet" crossorigin="anonymous"
   href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" >
    
   <script crossorigin="anonymous"
   src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" >
   </script>
</head>
 
<body>
    <table class="table">
        <thead>
            <tr>
            <th scope="col">BlogId</th>
            <th scope="col">UserName</th>
            <th scope="col">Titile</th>
            <th scope="col">Desc</th>
            </tr>
        </thead>
        <tbody>
            {% for tip in mssqltips %}
                <tr>
                    <td>{{ tip.BlogId }}</th>
                    <td>{{ tip.Username }}</td>
                    <td>{{ tip.BlogTitle }}</td>
                    <td>{{ tip.BlogDescription }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
  
</body>

Step 3: Open the APIApp.py file and add the code shown below.

from flask import Flask, render_template, redirect, request
from connection import coxn
from sqlalchemy import text
 
blogs = Flask(__name__)
 
@blogs.route("/") #For default route
def main():
    mssqltips = []
    result = coxn.execute(text("SELECT * FROM dbo.Blogs"))
    for row in result.fetchall():
         mssqltips.append({"BlogId": row[0], "Username": row[1], "BlogTitle": row[2], "BlogDescription": row[3]})
  
    return render_template("BlogList.html",  mssqltips =  mssqltips)
 
if(__name__ == "__main__"):
    blogs.run() 

Great! You created the first HTML file and routed it.

Step 4: Run the Script again.

Run the script

If you still have the previous session running on Visual Studio Code, press CTRL+C to close the session before running the script again.

Step 5: Run the server on your browser by holding CTRL and clicking http://127.0.0.1:5000 or go to your browser and manually enter http://127.0.0.1:5000 on the URL to run.

Run server on browser

Nice! You can now see the database content we created in the first section.

Database content visual

This covers the GET ALL aspect of your API. Next, you will create a template to update the content in your database from an HTML page.

Update Database Content from HTML Page

Step 1: Create a file named AddBlog.HTML under your templates folder and write the code shown below.

<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Blog Details</title>
    
    <link rel="stylesheet" crossorigin="anonymous"
   href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" >
    
   <script crossorigin="anonymous"
   src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" >
   </script>
 
</head>
 
<body>
    <h2>Blog Details</h2>
 
    <form action="" method = "POST">
        <div class="form-group row">
          <label for="id">Id</label>
          <input type="text" class="form-control" name="BlogId" id="BlogId" >
        </div>
        <div class="form-group row">
            <label for="name">UserName</label>
            <input type="text" class="form-control" name="Username" id="Username" >
        </div>
        <div class="form-group row">
            <label for="title">Title</label>
            <input type="text" class="form-control" name="BlogTitle" id="BlogTitle" >
        </div>
        <div class="form-group row">
            <label for="desc">Description</label>
            <input type="text" class="form-control" name="BlogDescription" id="BlogDescription" >
        </div>
        <button type="btnAdd" class="btn btn-primary">Submit</button>
        <a href="/" class="btn btn-primary">Cancel</a>
      </form>
</body>
</html>

Step 2: Open the APIApp.py file and the code shown below to route your AddBlog.html template.

from flask import Flask, render_template, redirect, request
from connection import coxn
from sqlalchemy import text
 
blogs = Flask(__name__)
 
@blogs.route("/") #For default route
def main():
    mssqltips = []
    result = coxn.execute(text("SELECT * FROM dbo.Blogs"))
    for row in result.fetchall():
         mssqltips.append({"BlogId": row[0], "Username": row[1], "BlogTitle": row[2], "BlogDescription": row[3]})
  
    return render_template("BlogList.html",  mssqltips =  mssqltips)
 
@blogs.route("/addblog", methods = ['GET','POST'])
def addblog():
    if request.method == 'GET':
        return render_template("AddBlog.html")
    if request.method == 'POST':
        BlogId = int(request.form["BlogId"])
        Username = request.form["Username"]
        BlogTitle = request.form["BlogTitle"]
        BlogDescription =request.form["BlogDescription"]
        conn = coxn.raw_connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO dbo.Blogs (BlogId, Username, BlogTitle, BlogDescription) VALUES          (?, ?, ?, ?)", BlogId, Username, BlogTitle, BlogDescription)
        cursor.commit()
       
        return redirect('/')
 
if(__name__ == "__main__"):
    blogs.run()

Step 3: Close the previous session by pressing CTRL+C and run the script again. Go to your browser and insert http://127.0.0.1:5000/addblog and enter.

Blog details

Great! The addblog route is working! Insert details for a blog post and click Submit.

After submitting, you will get redirected to the home page with the newly added entries included in the list.

Homepage

Step 4: Go to SSMS to verify this entry on the database. Right-click on the table used and refresh and then click Select Top 1000 Rows.

Select Top 1000 Rows

As you can see, the entry is in the database.

confirm entry

You can now view and add content to your database. You only need to include updates and deletion of content.

Updates and Deletion of Content via Web App

Step 1: Add a link for updating and deleting on your BlogList.html file as shown below.

<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>Blogs List</title>
    <link rel="stylesheet" crossorigin="anonymous"
   href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" >
    
   <script crossorigin="anonymous"
   src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" >
   </script>
</head>
 
<body>
    <table class="table">
        <thead>
            <tr>
            <th scope="col">BlogId</th>
            <th scope="col">UserName</th>
            <th scope="col">Titile</th>
            <th scope="col">Desc</th>
            </tr>
        </thead>
        <tbody>
            {% for tip in mssqltips %}
                <tr>
                    <td>{{ tip.BlogId }}</th>
                    <td>{{ tip.Username }}</td>
                    <td>{{ tip.BlogTitle }}</td>
                    <td>{{ tip.BlogDescription }}</td>
         <td><a href='updateBlog/{{ tip.BlogId}}'>Edit</a></td>
                    <td><a href='deleteBlog/{{ tip.BlogId }}'>Delete</a></td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
   <button><a href="/addblog">ADD BLOG</a></button>
</body>

Note: The links have the BlogId parameter to identify the individual Blog to be deleted or updated.

Step 2: Create an HTML file named updateBlog.html and write the code as shown below.

<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
    <title>updateBlog</title>
    
    <link rel="stylesheet" crossorigin="anonymous"
   href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" >
    
   <script crossorigin="anonymous"
   src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" >
   </script>
 
</head>
 
<body>
    <h2>Update Blog</h2>
 
    <form action="" method = "POST">
        <div class="form-group row">
          <label for="id">Id</label>
          <input type="text" class="form-control" name="BlogId" id="BlogId" value="{{ tip.BlogId }}">
        </div>
        <div class="form-group row">
            <label for="name">UserName</label>
            <input type="text" class="form-control" name="Username" id="Username" value="{{ tip.Username }}">
        </div>
        <div class="form-group row">
            <label for="title">Title</label>
            <input type="text" class="form-control" name="BlogTitle" id="BlogTitle" value="{{ tip.BlogTitle }}">
        </div>
        <div class="form-group row">
            <label for="desc">Description</label>
            <input type="text" class="form-control" name="BlogDescription" id="BlogDescription" value="{{ tip.BlogDescription }}">
        </div>
        <button type="btnAdd" class="btn btn-primary">Submit</button>
        <a href="/" class="btn btn-primary">Cancel</a>
      </form>
</body>
</html>

The input has values tied to the current data row using the row's ID.

Step 3: Open your APIApp.py file and add the code shown below to add your update logic.

from flask import Flask, render_template, redirect, request
from connection import coxn
from sqlalchemy import text
 
blogs = Flask(__name__)
 
@blogs.route("/") #For default route
def main():
    mssqltips = []
    result = coxn.execute(text("SELECT * FROM dbo.Blogs"))
    for row in result.fetchall():
         mssqltips.append({"BlogId": row[0], "Username": row[1], "BlogTitle": row[2], "BlogDescription": row[3]})
  
    return render_template("BlogList.html",  mssqltips =  mssqltips)
 
@blogs.route("/addblog", methods = ['GET','POST'])
def addblog():
    if request.method == 'GET':
        return render_template("AddBlog.html")
    if request.method == 'POST':
        BlogId = int(request.form["BlogId"])
        Username = request.form["Username"]
        BlogTitle = request.form["BlogTitle"]
        BlogDescription =request.form["BlogDescription"]
        conn = coxn.raw_connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO dbo.Blogs (BlogId, Username, BlogTitle, BlogDescription) VALUES          (?, ?, ?, ?)", BlogId, Username, BlogTitle, BlogDescription)
        cursor.commit()
       
        return redirect('/')
 
@blogs.route('/updateBlog/<int:id>',methods = ['GET','POST'])
def updateBlog(id):
    cr = []
    conn = coxn.raw_connection()
    cursor = conn.cursor()
    if request.method == 'GET':
        cursor.execute("SELECT * FROM dbo.Blogs WHERE BlogId = ?", id)
        for row in cursor.fetchall():
            cr.append({"BlogId": row[0], "Username": row[1], "BlogTitle": row[2], "BlogDescription": row[3]})
        conn.close()
        return render_template("updateBlog.html", tip = cr[0])
    if request.method == 'POST':
        
        Username = request.form["Username"]
        BlogTitle = request.form["BlogTitle"]
        BlogDescription =request.form["BlogDescription"]
        cursor.execute("UPDATE dbo.Blogs SET Username = ?, BlogTitle = ?, BlogDescription = ? WHERE BlogId = ?", Username, BlogTitle, BlogDescription, id)
        conn.commit()
        conn.close()
        return redirect('/')
 
if(__name__ == "__main__"):
    blogs.run() 

Step 4: Run the Script again.

Run script

If you still have the previous session running on Visual Studio Code, press CTRL+C to close the session before running the script again and open your browser to add 127.0.0.1:5000 to run.

Step 5: The List page should have the edit and delete links.

Edit and delete links

Click the Edit link. It will direct you to updateBlog/{{ tip.BlogId}}. This opens the updateBlog.html file and passes the Id value to the updateBlog funct.

Step 5: Edit rows and click submit.

Update blog

After submission, you should be redirected to the main list with your edits applied.

Edits applied

Great! Updates done! Now, it is time to implement the delete logic.

Delete Logic Implementation via Web App

Step 1: Open the APIApp.py file and add the code shown below to add the delete logic to your script.

from flask import Flask, render_template, redirect, request
from connection import coxn
from sqlalchemy import text
 
blogs = Flask(__name__)
 
@blogs.route("/") #For default route
def main():
    mssqltips = []
    result = coxn.execute(text("SELECT * FROM dbo.Blogs"))
    for row in result.fetchall():
         mssqltips.append({"BlogId": row[0], "Username": row[1], "BlogTitle": row[2], "BlogDescription": row[3]})
  
    return render_template("BlogList.html",  mssqltips =  mssqltips)
 
@blogs.route("/addblog", methods = ['GET','POST'])
def addblog():
    if request.method == 'GET':
        return render_template("AddBlog.html")
    if request.method == 'POST':
        BlogId = int(request.form["BlogId"])
        Username = request.form["Username"]
        BlogTitle = request.form["BlogTitle"]
        BlogDescription =request.form["BlogDescription"]
        conn = coxn.raw_connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO dbo.Blogs (BlogId, Username, BlogTitle, BlogDescription) VALUES          (?, ?, ?, ?)", BlogId, Username, BlogTitle, BlogDescription)
        cursor.commit()
       
        return redirect('/')
 
@blogs.route('/updateBlog/<int:id>',methods = ['GET','POST'])
def updateBlog(id):
    cr = []
    conn = coxn.raw_connection()
    cursor = conn.cursor()
    if request.method == 'GET':
        cursor.execute("SELECT * FROM dbo.Blogs WHERE BlogId = ?", id)
        for row in cursor.fetchall():
            cr.append({"BlogId": row[0], "Username": row[1], "BlogTitle": row[2], "BlogDescription": row[3]})
        conn.close()
        return render_template("updateBlog.html", tip = cr[0])
    if request.method == 'POST':
        
        Username = request.form["Username"]
        BlogTitle = request.form["BlogTitle"]
        BlogDescription =request.form["BlogDescription"]
        cursor.execute("UPDATE dbo.Blogs SET Username = ?, BlogTitle = ?, BlogDescription = ? WHERE BlogId = ?", Username, BlogTitle, BlogDescription, id)
        conn.commit()
        conn.close()
        return redirect('/')
 
@blogs.route('/deleteBlog/<int:id>')
def deleteBlog(id):
    conn = coxn.raw_connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM dbo.Blogs WHERE BlogId = ?", id)
    conn.commit()
    conn.close()
    return redirect('/')
 
if(__name__ == "__main__"):
    blogs.run()
 

Step 2: Run the Script again.

Run script

If you still have the previous session running on Visual Studio Code, press CTRL+C to close the session before running the script again and open your browser to add 127.0.0.1:5000 to run.

Step 3: Click on the Delete link to delete an entry.

Delete an entry

The Second blog entry should be deleted.

Entry deleted

Fantastic Work! You now have a fully functional CRUD API built on node, flask, Python, and SQL.

Conclusion

With so many third-party options for web applications content management systems, some companies require a simple and secure CMS, building a system internally boosts the security of the system.

This tutorial shows a very basic, but functional CMS you can use as a template to create even bigger projects. The tutorial did not include CSS styling.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Levi Masonde Levi Masonde is a developer passionate about analyzing large datasets and creating useful information from these data. He is proficient in Python, ReactJS, and Power Platform applications. He is responsible for creating applications and managing databases as well as a lifetime student of programming and enjoys learning new technologies and how to utilize and share what he learns.

View all my tips


Article Last Updated: 2022-11-08

Comments For This Article




Tuesday, November 8, 2022 - 12:34:43 AM - Venkataraman Back To Top (90669)
Superb article. Thanks for writing














get free sql tips
agree to terms