Best lightweight database for python

The choice of database is a very important one if you are planning to develop serious applications. While 20 years ago relational databases were used primarily by large enterprises, nowadays it can be considered as well-known standards. Open source solutions like the MySQL and PostreSQL are by far the most popular choices.

You have to admit, the python library has a lot to offer. It’s fast, has a massive library, and is easy to use. Nevertheless, it would be a mistake to overlook just how heavy the database is from an overhead perspective. When it comes down to it, your application runs best when its database fits snug into laptop RAM . Fortunately for python developers, there are several lightweight databases that are free and open source,best lightweight database,best database for python flask.

Best lightweight database for python

The support for a large number of databases is one of the many features that make Python a favourite language for software developers. Apart from supporting general-purpose database systems such as MySQL, Oracle, PostgreSQL, Informix, etc, it has support for many other specific systems also. For example, it has support for embedded databases such as SQLite and ThinkSQL. And it supports graph databases such as Neo4J.

This article explores three Python databases — pickleDB, TinyDB and ZODB. These three are implemented in Python and used for specific purposes.

pickleDB

pickleDB is a simple and lightweight data store. It stores the data as a key-value store. It is based on the Python module named SimpleJSON, which allows developers to handle JSON (JavaScript Object Notation) in a simple and faster manner. SimpleJSON is a pure Python implementation with no dependencies; it functions as an encoder and decoder for Python versions 2.5+.

Developed by Harrison Erd, pickleDB is available with the BSD three-clause licence. It may be installed effortlessly with the following command:

$ pip installpickledb

The name pickleDB is inspired by a Python module named pickle, which pickleDB was using earlier. Though the later versions of pickleDB started using the SimpleJSON module, the name pickle was retained.

The following code segment illustrates the basics of using pickleDB.

>>> importpickledb>>> db = pickledb.load('example.db', False)>>> db.set('key', 'value') True>>> db.get('key') 'value'>>> db.dump() True

Some of the popularly used commands of pickleDB are explained below.

  • LOAD path dump: This is used to load a database from a file.
  • SET key value: This is the value of a key with the string.
  • GET key: Used to retrieve the value of the key.
  • GETALL: Used to fetch all the keys in a database.
  • REM key: Deletes the key.
  • DUMP: Saves the database from the memory into the file specified with the load command.

Apart from the above mentioned commands there are various other commands.

pickleDB can be used for those scenarios in which the key-value store type of format is suitable.

Figure 1: Python databases

TinyDB

As the name indicates, TinyDB is a compact, lightweight database and is document-oriented. It is written 100 per cent in Python and has no external dependencies. As the official documentation says, TinyDB is a database optimised for your happiness.

The applications which are best suited to TinyDB are small apps for which a traditional SQL-DB server based approach would be an overload. The major features of TinyDB are listed below:

  • As the name indicates, TinyDB is very small. The complete source code is only 1200 lines.
  • TinyDB is based on document-oriented storage. This type of storage is adopted in popular tools such as MongoDB.
  • The API of TinyDB is very simple and clean. Primarily, TinyDB has been designed keeping ease of usage in mind.
  • Another likeable feature of TinyDB is the non-dependency. In addition, TinyDB supports all the recent versions of Python. It works on Python 2.6, 2.7, 3.3 – 3.5.
  • Extensibility is another major feature of TinyDB. With the help of middleware, TinyDB behaviour can be extended to suit specific needs.

Though TinyDB has various advantages, it is not a single-size-fits-all solution for problems. It has certain limitations as listed below:

  • TinyDB is not suitable for those scenarios in which high speed data retrieval is the key.
  • If you need to access the database from multiple processes or threads, then TinyDB won’t be optimal. Similarly, HTTP server based access is another scenario in which it won’t be suitable.

Having seen the pros and cons of TinyDB, let us look at how to use it. As is the case with many other packages, TinyDB can be installed simply with the following command:

$ pip installtinydb

The following code snippet explores how to create and store the values in TinyDB:

from tinydb importTinyDB, Querydb = TinyDB('db.json')db.insert({'type': 'OSFY', 'count': 700})db.insert({'type': 'EFY', 'count': 800})
Figure 2: Tiny DB features

After the successful execution of the above mentioned snippet, you can retrieve the values as shown below.
To list all values, give the following commands:

db.all()[{'count': 700, 'type': 'OSFY'}, {'count': 800, 'type': 'EFY'}]

To search and list values, type:

Magazine = Query()db.search(Magazine.type== 'OSFY')[{'count': 700, 'type': 'OSFY'}]db.search(Magazine.count > 750)[{'count': 800, 'type': 'EFY'}]

For updating the values, use the following commands:

db.update({'count': 1000}, Magazine.type== 'OSFY')db.all()[{'count': 1000, 'type': 'OSFY'}, {'count': 800, 'type': 'EFY'}]

To remove values, type:

db.remove(Magazine.count < 900)db.all()[{'count': 800, 'type': 'EFY'}]

To delete all the values, give the following commands:

db.purge()db.all()[]

TinyDB enables developers to handle data in two different ways, as listed below:

  • JSON
  • In-memory

The default value is JSON, and if you want to change it to in-memory, you need to specify that explicitly.

from tinydb.storages importMemoryStoragedb = TinyDB(storage=MemoryStorage)

ZODB

ZODB is a native object database for Python. Its major features are:

  • Seamless integration between code and database.
  • No separate language is needed for operations related to the database.
  • No database mapper is required.

ZODB is better suited for the following scenarios:

  • When the developer wants to focus more on the application rather than building lots of database code.
  • When the application has lots of complex relationships and data structures.
  • When the data read operations are comparatively larger than the write operations.

At the same time, ZODB is not suitable for scenarios in which the application requires a high volume of data write operations.
To install ZODB, use the following command:

$ pip installZODB

A simple code snippet to establish connection with a database is shown below:

importZODB, ZODB.FileStoragestorage = ZODB.FileStorage.FileStorage('mydata.fs')db = ZODB.DB(storage)connection = db.open()root = connection.root

ZODB allows developers to use a wide variety of storage options, as listed below:

  • In-memory databases
  • Local files
  • Databases on remote servers
  • Advanced options such as compressed and encrypted storage

The members of the object can be directly stored using ZODB functions. ZODB has support for transactions also.

This article has attempted to provide an insight into the world of Python databases. There are many other databases apart from the three narrated in this article, such as buzhug and CodernityDB. The bottomline is that all these database tools provide a Pythonic ambience when you work with them.

best lightweight database

A Database is a systematic collection of data which supports storage and manipulation of information. It is usually managed by a Database Management System (DBMS). Data within a database is typically modeled in rows and columns in tables to make data querying and processing more efficient.

Following is the best Database Software list, with popular features and download links. This comparison database name list contains open-source tools that may have freemium features of the Top Free database.

List of the Best SQL Database Software:

#1) InterBase

InterBase is a full-featured, high-performance, scalable, lightweight, embeddable and encryptable relational database that can be embedded into applications on Android, iOS, Windows, OS X, Linux, and Solaris.

Platforms: Windows, iOS, macOS, Android, Linux

Languages: Java, C, C++, . NET, Delphi, Object Pascal, PHP and Ruby.

Features:

  • Security: Cross-Platform Encryption, Separate Security Login, Reduced Exposure to Risk, Role-Based User Security, Encrypted Backups
  • Admin-Free: Near-Zero Maintenance, Rapid Crash Recovery, Disaster Recovery
  • Resilience: Live Backups, Distinguished Data Dumps, Fast Restores, Write-Ahead Logging, Point-in-Time Recovery
  • Efficiency: Log-Less Replication, Safe Data Change Subscriptions, Best-in-Class Change Tracking, Role-Based User Security, Early Fetch Data Deltas
  • Flexibility: Multi-Language Support, Cross-Platform Support, Server Data Storage, Client Data Storage, Single On-Disk Format
  • Lightweight: Small Footprint, Fast Install, Faster Data, Write-Ahead Logging

More Information >>


#2) Microsoft SQL

SQL Server is RDBMS developed by Microsoft. SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its implementation of the SQL language, T-SQL (Transact-SQL).

Platform: Docker Engine, Ubuntu, SUSE Linux Enterprise Server, and Red Hat Enterprise Linux.

Languages: C, C++, Java, and C#

Cloud Version: Yes

Features:

  • It provides integration of structured and unstructured data with the power of SQL Server and Spark.
  • The tool offers scalability, performance, and availability for mission-critical, intelligent applications, data warehouses, and data lakes.
  • It offers advanced security features to protect your data.
  • Access to rich, interactive Power BI reports, to make a faster and better decision.

More Information >>


#3) Database Performance Analyzer

SolarWinds Database Performance Analyzer is performance monitoring and analysis tool for database admin. It also finds the reason of bottlenecks and reduces the overall cost of database operations.

Features:

  • Monitor capacity constraints, queries, and server health performance
  • Expert database and SQL query tuning advisors
  • Quickly find the cause of complex problems
  • Identify database problems in real-time
  • Cross-vendor database support from a single interface
  • Low overhead on monitored databases
  • Blocking and Deadlock analysis
  • Analyze every factor that impacts SQL Server execution
  • Monitors database in hybrid environments, on-premises, virtualized, and in the cloud
  • Helps proactively tune queries so applications can respond faster

More Information >>


#4) MySQL

MySQL is an open-source relational database which runs on a number of different platforms such as Windows, Linux, and Mac OS, etc.

Platform: Linux, Windows, and Mac.

Languages: C, Java, SQL, C++, Perl, Python, Tcl, and PHP.

Cloud Version: Yes

Features:

  • This open source database tool provides Scalability and Flexibility
  • This free SQL database tool has web and data warehouse strengths
  • It provides high Performance
  • This free database software for Windows 10 has Robust Transactional Support

Verdict: MySQL can be used for packaged software, and business-critical systems and high-volume websites.

Link: https://www.mysql.com/


#5) PostgreSQL

PostgreSQL is an enterprise-class open source database management system. It is one of the best free databases that supports both SQL for relational and JSON for non-relational queries. It is backed by an experienced community of developers who have made a tremendous contribution to make it highly reliable Database management software.

Platform: Mac, Windows, and Linux.

Languages: PL/Tcl, PL/pgSQL, PL/Python, and PL/Perl.

Cloud Version: No.

Features:

  • Compatible with various platforms using all major languages and middleware
  • Standby server and high availability
  • The tool has mature server-side programming Functionality
  • Log-based and trigger-based replication SSL.
  • It offers a most sophisticated locking mechanism.
  • Support for multi-version concurrency control
  • It provides support for client-server network architecture
  • The tool is Object-oriented and ANSI-SQL2008 compatible
  • PostgreSQL allows linking with other data stores like NoSQL, which act as a federated hub for polyglot databases.

Verdict: PostgreSQL enables you to create custom data types and range of query methods. You can run a store procedure in different programming languages.

best database for python flask

Flask and Databases

To add database functionality to a Flask app, we will use SQLAlchemy.

SQLAlchemy is a Python SQL toolkit and object relational mapper (ORM) that enables Python to communicate with the SQL database system you prefer: MySQL, PostgreSQL, SQLite, and others. An ORM converts data between incompatible systems (object structure in Python, table structure in SQL database). SQLAlchemy is basically a bridge between Python and a SQL database.

Flask-SQLAlchemy is an extension for Flask that adds SQLAlchemy to your Flask app.

  • SQLAlchemy documentation
  • Flask-SQLAlchemy documentation
  • Code for this chapter

Setup: Flask-SQLAlchemy

We will install the Flask-SQLAlchemy extension to enable us to work with a SQL database in Flask. There are many extensions for Flask; each one adds a different set of functions and capabilities. See the list of Flask extensions for more.

In Terminal, change into your Flask projects folder and activate your virtual environment there. Then install the extension at the command prompt — where you see $ (Mac) or C:\Users\yourname> (Windows) —

pip install flask-sqlalchemy

We will use SQLite for database examples here. Although it’s not necessary to use SQLAlchemy to interact with a SQLite database, learning to use SQLAlchemy gives you a skill set that can be applied to any SQL database system.

SQLAlchemy can bridge between Python and various different SQL database systems — some of which need an additional module, or library, to be installed. SQLite does not require an additional module — the sqlite3 module is included in Python 3.x.

  • Find other modules for other SQL databases.

Important

If you’re using a MySQL or PostgreSQL database, you will need to install a DBAPI module such as psycopg2 (PostgreSQL) or PyMySQL (MySQL).

Basics of using a database with Flask

You’ll connect your Flask app to an existing SQL database. Connecting will require your own database username and database password, unless using SQLite.

Note

You can create the SQL database using Python, but that is not required. If you already have a database, all you need to worry about is how to connect it. If you do use Python to create a SQL database (and that’s an “if,” not a necessity), you will only do it once. You don’t create the same database again and again. Yes, this seems like a no-brainer — but you need to think about what your code is doing.

Your database may have one table, or more than one table. That depends on what you need, or the structure of the existing SQL database. You’ll need to know the table name(s). You’ll need to know the field names (column headings) in each table.

Your app might only read from your SQL database. You can write SQL queries to accomplish this — using Flask-SQLAlchemy commands to do so. Note that you won’t write a straightforward SQL query. Here is an example of Flask-SQLAlchemy syntax:

socks = Sock.query.filter_by(style='knee-high').order_by(Sock.name).all()

The Flask-SQLAlchemy statement to the right of the equals sign, above, is equivalent to this standard SQL statement:

SELECT * FROM socks WHERE style="knee-high" ORDER BY name

It is assumed you are familiar with how to write basic SQL queries.

  • Details about writing queries with Flask-SQLAlchemy.

In addition to reading from your SQL database, your Flask app might allow people to write to the database. In that case, you will probably want people to log in securely. Alternatively, you might set up a Python script that updates your database on a regular schedule (e.g., writing in new records from a monthly data dump).

You might write a Python script to populate your database from the contents of a CSV file. This would be fairly simple if you only need to run it once. If you need to add records repeatedly (say, once per month) to an existing database, you might need to check whether you are duplicating records that are already there. If you need to check for existing records and update them, that’s more challenging. You can handle each of these tasks within Flask, using route functions.

If people are writing into your database, you will want to give them a web form, or forms, for doing so. See Flask: Web Forms if you need to create a web form in your Flask app.

You will not necessarily need forms if your app only reads from the database, but it is possible you’ll want to allow people to search for content, or to choose content from a menu using a <select> element in a form that queries the database. Then a form or forms will be required. Again, you will handle these tasks within Flask, using route functions.

Of course, you’ll be using templates and all the other aspects of Flask covered in previous chapters here.

This overview should get you thinking about what you will need your Flask app to do, and how you will be querying the database, inserting new records, etc.

For all Python and SQL commands, refer to the links listed under “User’s Guide” in the Flask-SQLAlchemy documentation.

How to connect a database to a Flask app

The first step, assuming you have a database, is getting your app (or a starter script for your app) to connect to the database. Do this first.

Here’s a starter script for testing whether you can connect:

../python_code_examples/flask/databases/test_local_sqlite_db.py

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37“”” test a SQLite database connection locally assumes database file is in same location as this .py file “”” from flask import Flask from flask_sqlalchemy import SQLAlchemy from sqlalchemy.sql import text app = Flask(__name__) # change to name of your database; add path if necessary db_name = ‘sockmarket.db’ app.config[‘SQLALCHEMY_DATABASE_URI’] = ‘sqlite:///’ + db_name app.config[‘SQLALCHEMY_TRACK_MODIFICATIONS’] = True # this variable, db, will be used for all SQLAlchemy commands db = SQLAlchemy(app) # NOTHING BELOW THIS LINE NEEDS TO CHANGE # this route will test the database connection and nothing more @app.route(‘/’) def testdb(): try: db.session.query(text(‘1’)).from_statement(text(‘SELECT 1’)).all() return ‘<h1>It works.</h1>’ except Exception as e: # e holds description of the error error_text = “<p>The error:<br>” + str(e) + “</p>” hed = ‘<h1>Something is broken.</h1>’ return hed + error_text if __name__ == ‘__main__’: app.run(debug=True)

The script above assumes that a SQLite database file (see filename on line 14) is in the same directory as the .py file. The script will work with any SQLite database file.

  • Starter scripts for a MySQL database connection are here. More about this below.

Note that line 16 is the key to the connection — it contains the database connection string, which will be different depending on which SQL database system you are connecting to. The SQLite connection string is simpler than the others, so be sure to read on if you’re using MySQL, PostgreSQL, or another system.

Run the script above:

python test_local_sqlite_db.py

Open http://localhost:5000 in your web browser. If you see the text “It works.” — then all is well. Otherwise, you’ll see an error message that should enable you to resolve the problem.

SQLite resources

SQLite is a SQL database engine that is especially easy to work with because the database — regardless of its size or how many tables it might include — is in a single .db file. You can copy the file, upload it to a server, and so on — it is a standalone file.

Unlike other database systems, a SQLite database does not have a username or password for access to the database itself.

  • SQLite homepage
  • Download and install SQLite for your operating system (note: MacOS already has SQLite)
  • Download the free DB Browser for SQLite (you can easily create tables by importing CSV files) — use this to create a new database, add tables, set data types for columns, etc.
  • SQLite Tutorial
  • SQLite can be used without SQLAlchemy: Using SQLite3 with Flask

Connecting to a MySQL database

Two scripts are provided to test a connection to a MySQL database. They are here. An additional Python module must be installed — PyMySQL — and a username and password must be included in the connection string (even an empty password has a place).

In addition, when running the MySQL database locally, a socket string must be included. This string will be very different on MacOS and Windows.

If you do not want to run the database locally, but instead you have the database on a remote server — while you are writing your Flask app (and testing it) on your own computer — you will need to remotely connect to your MySQL database.

  • See other examples of connection strings.

The connection string

The database connection string requires a strict syntax, or it will not work. The SQLite string is by far the simplest:

'sqlite:///' + db_name

That assumes the .db file is adjacent to your .py file. If not, write the path as needed.

For other database systems, the connection string will be more complex:

'mysql+pymysql://' + username + ':' + password + '@' + server + database

Note, that example is for MySQL only; the protocol will be different for, say, PostgreSQL.

Setting environment variables

Instead of including username, password, and the whole database connection string within a Python script, you can set the complete string as an environment variable. Note that the connection string must be complete and correct for your configuration, as discussed in the previous section.

If you set an environment variable for the connection string, then add the following lines near the top of your script:

import os
# check for environment variable
if not os.getenv("DATABASE_URL"):
    raise RuntimeError("DATABASE_URL is not set")

Eliminate all lines that refer to username, password, server, and database name.

Change the app.config statement to this:

app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv("DATABASE_URL")

Look up how to set an environment variable for your operating system.

Note

os is a built-in Python module.

When setting up a Flask app on a server, there will be an option to set environment variables there. The lines in the Flask script referring to the environment variable will not change.

Important

Only one environment variable on your computer, or in an app, can be named DATABASE_URL — it’s a variable name, so you can change the string to something else, and you can set as many different variables as you need.

Note

If you’re using a SQLite database, don’t bother with environment variables.

What could go wrong?

If you cannot get your Flask app to connect to your database, check the following:

  • You forgot to install something (Flask-SQLAlchemy, or PyMySQL, etc.) in your Python virtual environment.
  • Your virtual environment has not been activated.
  • Your username and/or password for the database are wrong.
  • Your database name is incorrect.
  • On a remote server, permissions for the database user are not set correctly.
  • For a local database, the socket does not match what you need on your computer.
  • For a local MySQL database, you have not started the MySQL server.

Summary

This has been a basic introduction to getting started with Flask-SQLAlchemy and databases in Flask. The first step is to make sure you are able to connect successfully to the database you want to use.

A successful connection depends on which type of SQL system your database was built in. In this chapter, SQLite and MySQL are covered. PostgreSQL is another popular option but not covered here.

Leave a Comment