Exploring Flask SQLite
In this article, we will dive into Flask SQLite and explore how it simplifies database integration in your Flask applications. By using Flask SQLite, you can harness the power of a relational database without the need for complex setup or administration.
SQLite is a self-contained, file-based database management system that requires no configuration or setup. It is lightweight, fast, and efficient, making it an excellent choice for smaller web applications that don’t require the full power of a traditional database management system like MySQL or PostgreSQL.
Setting Up Flask SQLite
Before you can start using Flask SQLite, you need to install the required dependencies. Flask SQLite is built on top of the SQLite3 module, which is included in the Python standard library. Hence, no additional installation is necessary.
You can make an SQLite database named ‘database.db‘ and then make a table named ‘students‘ in it.
import sqlite3
my_conn = sqlite3.connect('flaskdatabase.db')
print("Opened database successfully")
my_conn.execute('CREATE TABLE employees (ename TEXT, address TEXT, city TEXT, age TEXT, salary TEXT)')
print("Table created successfully")
my_conn.close()Your Flask application includes three View functions. The initial new_employee() function is linked to the URL rule (‘/addnew‘), and it displays an HTML file that contains a form for entering employee information.
@app.route('/enternew')
def new_employee():
return render_template(‘employee.html')The code for ‘employee.html‘ HTML document is presented below.
<html>
<body>
<form action = "{{ url_for('addrec') }}" method = "POST">
<h3>Student Information</h3>
Name<br>
<input type = "text" name = "ename" /></br>
Address<br>
<textarea name = "add" ></textarea><br>
City<br>
<input type = "text" name = "city" /><br>
Age<br>
<input type = "text" name = "age" /></br>
Salary<br>
<input type = "text" name = "salary" /><br>
<input type = "submit" value = "submit" /><br>
</form>
</body>
</html>
You can observe that the form data is sent to the ‘/addrec’ URL, which is associated with the addrec() function. The addrec() function receives the form data through the POST method and then inserts it into the students table. After the insert operation, a message indicating success or error is displayed in the ‘output.html’ file.
@app.route('/addrec',methods = ['POST', 'GET'])
def addrec():
if request.method == 'POST':
try:
ename = request.form['ename']
addr = request.form['add']
city = request.form['city']
age = request.form['age']
salary = request.form['salary']
with sql.connect("flaskdatabase.db") as con:
cur = con.cursor()
cur.execute("INSERT INTO employees (ename,addr,city,age,salary) VALUES (?,?,?,?,?)",(ename,addr,city,age,salary) )
con.commit()
msg = "Record successfully added"
except:
con.rollback()
msg = "error in insert operation"
finally:
return render_template("output.html",msg = msg)
con.close()
In the HTML code of ‘output.html‘, there is an escape statement {{msg}} that exhibits the outcome of the insertion operation to you.
<!doctype html>
<html>
<body>
Output of addition : {{ msg }}
<h2><a href = "\">go back to home page</a></h2>
</body>
</html>Another function called list() is present in the application, which is represented by the ‘/list’ URL. This function populates ‘rows’ as a MultiDict object that comprises all the records present in the employee table. Later, this object is transferred to the list.html template.
@app.route('/list')
def list():
con = sql.connect("flaskdatabase.db")
con.row_factory = sql.Row
cur = con.cursor()
cur.execute("select * from employees")
rows = cur.fetchall();
return render_template("list.html",rows = rows)
The ‘list.html‘ serves as a template, that goes through the row set and presents the data in an HTML table format.
<!doctype html>
<html>
<body>
<table border = 4>
<thead>
<td>Name</td>
<td>Address>/td<
<td>city</td>
<td>Age</td>
<td>Salary</td>
</thead>
{% for row in rows %}
<tr>
<td>{{row["ename"]}}</td>
<td>{{row["addr"]}}</td>
<td> {{ row["city"]}}</td>
<td> {{ row["age"]}}</td>
<td>{{row['salary']}}</td>
</tr>
{% endfor %}
</table>
<a href = "/">Go back to home page</a>
</body>
</html>Finally, when you access the URL rule ‘/‘ in your browser, it displays a first.html‘ file that serves as the starting point of the application.
@app.route('/')
def first():
return render_template('first.html')To execute this script from the Python shell, run it, and then begin the development server. Afterwards, open your web browser and type http://localhost:5000/ in the address bar to access a straightforward menu that appears as follows:
To open the Employee Information Form, click on the link that says ‘Add New Record’.
Enter the necessary information in the form fields and then submit it. After that, the relevant function will add the record to the employee table.
Return to the home page and click on the ‘Show List’ link. This will exhibit the table containing the sample data.
Flask SQLite Benefits
- SQLite is a lightweight database engine that operates without the need for a separate server process. It stores data in a single file, making it easy to manage and deploy. Flask SQLite leverages the advantages of SQLite, providing a serverless and efficient database solution.
- Flask SQLite eliminates the need for complex database setup and administration. With SQLite, you don’t have to install and configure a separate database server. The integration with Flask is seamless, allowing you to focus on building your application rather than dealing with database setup intricacies.
- Flask SQLite doesn’t require any additional configuration or external dependencies. Since SQLite is included in the Python standard library, you can start using it immediately within your Flask project without the need for separate installations or configurations.
- SQLite databases are self-contained within a single file. This simplifies database management as you only need to manage a single file for your application’s data. Flask SQLite provides convenient methods to establish connections, execute queries, and manage transactions, allowing for efficient data management operations.
- SQLite databases are highly portable and can be easily moved across different environments or platforms. You can develop your Flask application using SQLite locally and then seamlessly deploy it to different servers without worrying about database compatibility or migration issues.
- SQLite is designed for simplicity and efficiency. It performs well for small to medium-sized applications and can handle a substantial amount of data and concurrent connections. Flask SQLite provides a performant interface to interact with SQLite databases, ensuring efficient data retrieval and manipulation operations.
- SQLite databases are compatible with various platforms and programming languages. This means that if you decide to switch from Flask to another framework or language in the future, you can easily migrate your SQLite database without any major changes. Flask SQLite ensures compatibility and smooth transitions, providing flexibility for your application’s future growth.
Conclusion
To sum up, if you are looking for a lightweight and user-friendly framework for developing web applications, Flask SQLite is an excellent option to consider. This combination provides flexibility, ease of use, and good performance for small to medium-sized projects. Flask’s intuitive syntax and structure make it easy to understand and use. SQLite’s small memory footprint and built-in encryption capabilities ensure the security and efficiency of the database for web applications. Moreover, SQLite’s portability allows you to deploy your application easily in different environments. Overall, Flask SQLite is a reliable and popular choice for developing web applications that require flexibility, ease of use, and good performance.
