SQLite is the world’s most widely installed database engine, deployed in application software such as browsers for local or client data storage. Being free and open source, it offers users the liberty to modify the source code according to their needs, and has the benefits of support from a large community, portability and zero licensing costs. This article outlines how to interface Python with this popular database engine.
The role of back-end databases is very important in today’s scenario of cloud or desktop based applications. Without the integration of an effective and high performance database engine, the software suite can behave abnormally or can crash. The database engines or database management systems always need to be considered when developing any real world application because the overall data and transactions are stored in the back-end database. The main role of the database engine is to provide an effective mechanism to create, manipulate, store and retrieve records from back-end database tables without any delay.
A number of database applications are available in the software market but open source database systems are becoming very popular because of the cost factor, their portability, licence-free distribution and multi-featured aspects that come without any complexities.
SQLite: A transactional database engine for assorted applications
SQLite is a powerful and multi-featured database engine, which is the most used in the world. Billions of SQLite installations have taken place in different regions for numerous applications. The latest release of SQLite is Version 3.28.0.
SQLite possesses assorted features and computer properties including speed, compactness, being self-contained, and many others, which make this database engine quite unique and effectual as compared to other traditional database systems. SQLite is cross-platform and stable, with backward compatibility. The official portal of SQLite asserts that its developers pledge to maintain the existing properties and power of the database engine till 2050.
The source code of SQLite is available in the public domain at https://sqlite.org/src and is free for everyone. It can be used for any type of real world application. The complete code of SQLite is original – it has been written without taking any source code from other unknown sources.
SQLite provides the features of serverless and self-contained database engines in which there is zero-configuration with powers of transactional SQL. It is also used as the embedded database engine for different applications; many hardware applications are deployed using SQLite.
SQLite is popular because the in-process library implements the high performance database applications without any overheads, and does not require specialised hardware. The database created using SQLite is lightweight (in the .db file extension) and can be ported to any other location without specific requirements like export or import. The SQLite database system is fully ACID compliant and it allows safe access to integrity and consistency based computing.
Scenarios and case studies of SQLite
SQLite is not only used for the small applications but is also widely implemented by corporate giants (Table 1).
Interfacing Python with SQLite
The Python programming language has in-built support for the SQLite database engine. With the default installation of the Python distribution, the database engine of SQLite is automatically integrated and there is no need to install it separately. During the installation of Python using the Anaconda platform (https://www.anaconda.com/), SQLite3 is integrated and can be used directly.
Just as Python is a package based programming language in which a large number of packages can be called, SQLite can be called in Python code using the following instruction:
Python Prompt > import sqlite3
Python code will import all the classes and methods of SQLite, and the database connection can be created further.
A database connection can be created as follows:
import sqlite3 sqliteconnection = sqlite3.connect(‘DatabaseName.db’) print (“Opened database successfully”); sqliteconnection.execute(‘’’CREATE TABLE DATABASETABLE (ID INT PRIMARY KEY NOT NULL, AGE INT NOT NULL, SALARY REAL);’’’) print (“Table created successfully”); sqliteconnection.close()
The database connection is finished with the creation of a table in SQLite. The created table can be viewed in the SQLite browser. The DB Browser for SQLite is available at https://sqlitebrowser.org/.
This software can be used as the front-end application to create, manipulate and visualise the data of SQLite. If the database is created and gets updated using Python or PHP code, then the updated records and data can be displayed and confirmed using the DB Browser for SQLite.
Records can be inserted in a table as follows:
import sqlite3 sqliteconnection = sqlite3.connect(‘DatabaseName.db’) sqliteconnection.execute(“INSERT INTO DATABASETABLE (ID, AGE, SALARY) \ VALUES (1, 22, 89000)”); sqliteconnection.execute(“INSERT INTO DATABASETABLE (ID, AGE, SALARY) \ VALUES (2, 23, 92000)”); sqliteconnection.commit() print (“Records Inserted”); sqliteconnection.close()
Records can be displayed from a database table by using the following code:
import sqlite3 sqliteconnection = sqlite3.connect(‘DatabaseName.db’) cursor = sqliteconnection.execute(“SELECT id, age, salary from DATABASETABLE”) for row in cursor: print (“ID = “, row[0]) print (“AGE = “, row[1]) print (“SALARY = “, row[2], “\n”) sqliteconnection.close()
Developing applications using Python and SQLite
The interfacing of Python and SQLite can be done in numerous ways. These include GUI based Tkinter programming, Web app development using the Flask framework, and desktop or real-time transaction based applications so that the records can be maintained without any delay as well as with a higher degree of availability and consistency.
Real-time Web based applications using Python and SQLite can be developed and deployed with advanced modules and packages, to help develop secure applications.
In addition to the traditional Web based applications, the integration of advanced libraries can be done for weather forecasting, one time password (OTP) based applications, and many others.
The following example shows how to create a snippet to generate a weather report using the OpenWeatherMap library (https://openweathermap.org) with the integration of SQLite and Python. The OpenWeatherMap library provides the application programming interface (API) so that real-time weather predictions and reports can be generated and presented to the client. First, the keys are generated using https://openweathermap.org/appid#get, and the following code can be used for the weather report of a specific city or location, with association of the pyowm package.
import pyowm owm = pyowm.OWM(‘xxxxxxxxxxxxxxxxxxxxxxxx’) observation = owm.weather_at_place(‘Fatehgarh Sahib, IN’) w = observation.get_weather() print(w) observation_list = owm.weather_around_coords(-22.57, -43.12)
On executing the code, the real-time weather is presented; predictive mining can be used in a similar way.
For the purpose of R&D, the performance of SQLite can be compared with other database systems. The code of database connections and retrieval can be implemented on multiple databases including SQLite, CockroachDB, MongoDB, CouchDB, Cassandra, etc. Then the real-time performance in terms of execution time and complexity can be evaluated on the test data sets and records, in order to analyse the effectiveness and metrics of assorted database engines.
