Essay: SQLAlchemy - Python's Database Toolkit, Critically Analyzed.

A critical analysis of SQLAlchemy - The Database Toolkit for Python

1.What the tool does ?
2.Where it fits in Software Development process?
3.What are its Competing products?
4.Who benefits / uses it?
5.How is its value ascertained?
6.Examples of usage?
7.Pros/Cons?

Description of SQLAlchemy

The SQLAlchemy is an open source toolkit of SQL. It can be also defined as object-relational mapper (ORM) that is designed mainly for the use in the Python programming language. It was released under the license of MIT. The SQLAlchemy toolkit can provide the developers of the application the flexibility and power of SQL (Bayer 2014).

The SQLAlchemy presents a technique of associating the user-defined classes of Python with database tables. They also associate the instances of the same classes with the rows of their corresponding tables. The tool also comprises of such a system, which can synchronize the state changes between the objects and the corresponding rows. This system could be used to express the database queries with respect to the user-defined classes and the associated relationships between them (Pedersen, Yang and De 2013).

The SQLAlchemy can contemplate the database of the system to be an engine of relational algebra. It is not a collection of tables. The rows could be selected from the tables as well as joins and select statements. These units could be composed to a large structure.

The SQLAlchemy provides full power and flexibility of SQL to the application developers. It also provides a complete suite of well-known patterns of enterprise level persistence. These patterns are designed for better efficiency and domain language of Python.  

The SQLAlchemy comprises of an ORM. It is an optional component, which can provide the pattern of data mapping. These can be helpful in the development of the software as the toolkit can map the classes to the database in single or multiple ways. This allows the database schema and the object model to be developed. A database schema can be defined as the structure of a system of a database. It comprises of tables, fields, columns and the relationships that are existing between them. The schema are defined using raw SQL or even with the use of the ORM feature of SQLAlchemy. Most of the latest releases of SQLAlchemy includes a core list of the developers of the software. It is also responsible to test a certain software (Horsburgh et al. 2015). 

The Python language has a lot of libraries for ORM. Some of the competiting products of SQLAlchemy are:

3.1 SQLObject: This is a type of ORM that can map the objects between Python and a Database of SQL. The popularity of SQLObject is because it is similar to Ruby. SQLObject was first released in October 2002 and it is licensed under LGPL. The concepts of database that are mapped into Python, has a similarity with SQLAlchemy. Here the tables are mapped as rows as instances, columns as attributes and classes (Parent 2014).

Use of SQLAlchemy in Software Development Process

3.2 Storm: It is a kind of ORM of python that is able to map the objects among different databases and Python. Unlike SQLAlchemy and SQLObject, this ORM is able to map tables to classes, columns to attributes and rows to instances.

3.3 Django’s ORM: It is a free and open source framework of web application. The ORM is strongly built into the system. This is one of the mostly popular web frameworks of Python that has their own dedicated ORM.  Django is dedicated towards direct object manipulation of SQL where the ORM is responsible for opening a direct and plain mapping between the tables of database and the classes of Python.

Although there are several ORMs, still SQLAlchemy is the most preferable option due to some reasons. They are:

4.1 It Defines the Schema of the Database

The SQLAlchemy is responsible for defining the schema of the database in Python. The schema, which is defined by Python code is called as the model. This can help in keeping the related functionality together (Parent 2014).

4.2 Automatic Synchronization of the Schema and the Model

Alembic is another SQLAlchemy, which is an add-on for management of the database. When changes are made to the Python model, it can update the database schema automatically. This could be helpful in making minor changes as adding a column or table. Alembic is also capable to automatically change the scripts that could be reviewed before applying to the system (Vanhala 2017). 

4.3 Integration with Web Frameworks

SQLAlchemy can help in maintaining an open connection with the database that are being provided with every web request. Every request is enveloped in a transaction. On successful request, the transaction is proceeded. This kind of arrangement can allow the methods of control to interact with the database (Grinberg 2018).

The SQLAlchemy has a clear and layered fashion of structure. The library can be customized according to the needs. Although most of the ORMs have a slow loading of performance, but the SQLAlchemy has a fast loading performance. When an object is first loaded, the relations could be used in SQLAlchemy in order to load the entire data into a single query.  

The SQLAlchemy is a database toolkit that can provide a general interface for the creation and execution of SQL statements. The SQLAlchemy allows developers to write their code in Python in order to map the database schema to their applications. The mapping could allow SQLAlchemy to handle the database system (Di Guida et al. 2015). 

The Competing Products of SQLAlchemy

SQLAlchemy depends on the specification of DBAPI in order to interact with the database systems. The mostly common database systems of management are supported with the help of SQLAlchemy. Apart from using SQLAlchemy, the most common engines that are used are MySQL, Oracle, SQLite , Microsoft SQL Server and PostgreSQL.

One of the traditional usage of SQLAlchemy is connection pooling method. Programs take advantages of this implementation method as to improve the performance. Opening and maintaining database connections is time consuming, expensive and wastage of resources. This pattern also allows an easy management of the number of connections that might be used by an application (Vasilescu, Serebrenik and Mens 2013).

The SQLAlchemy enables the developers of Python to create applications that would be able to communicate to different engines of database with the help of the same API. Most of the general relational databases that are available, stick to the standard defined by SQL. They also introduce patented variations. 

7.1 Advantages: The approach of SQLAlchemy is totally different from most of the other tools of ORM. It follows a complimentary oriented approach. In this type of approach, the processes are fully exposed instead of hiding the SQL and object relational details. The feature set of SQLAlchemy is large. It is the only ingle ORM that is developed for Python. It allows the developer to take a full advantage of the database. The SQLAlchemy ORM exposes an interface that is object-oriented and thus makes the queries for SQL. The APIs are based on the level of enterprise that is able to make the codes adaptable and robust (Fierro 2014).

7.2 Disadvantages: The disadvantages of SQLAlchemy is not too much but they can leak important portions of code, which can lead to serious issues. The SQLAlchemy is an enterprise worth framework that is able to work with the database. The concept of unit of work is not common. The SQLAlchemy toolkit is a very heavily weighted API, which can lead to a long curve of learning. 

References

Bayer, M., 2014. SQLAlchemy. The Architecture of Open Source Applications: Elegance, Evolution, and a Few More Fearless Hacks, 2.

Pedersen, B.S., Yang, I.V. and De, S., 2013. CruzDB: software for annotation of genomic intervals with UCSC genome-browser database. Bioinformatics, 29(23), pp.3003-3006.

Horsburgh, J.S., Reeder, S.L., Jones, A.S. and Meline, J., 2015. Open source software for visualization and quality control of continuous hydrologic and water quality sensor data. Environmental Modelling & Software, 70, pp.32-44.

Parent, M.A., 2014, October. Objects as results from graph queries using an ORM and generated semantic-relational binding. In International Semantic Web Conference (Posters & Demos) (pp. 293-296).

Vanhala, J., 2017. Implementing an Offline First Web Application.

Grinberg, M., 2018. Flask web development: developing web applications with python. " O'Reilly Media, Inc.".

Di Guida, S., Govi, G., Ojeda, M., Pfeiffer, A. and Sipos, R., 2015. The CMS Condition Database system. In Journal of Physics: Conference Series (Vol. 664, No. 4, p. 042024). IOP Publishing.

Vasilescu, B., Serebrenik, A. and Mens, T., 2013, May. A historical dataset of software engineering conferences. In Proceedings of the 10th Working Conference on Mining Software Repositories (pp. 373-376). IEEE Press.

Parent, M.A., 2014, October. Objects as results from graph queries using an ORM and generated semantic-relational binding. In International Semantic Web Conference (Posters & Demos) (pp. 293-296).

Fierro, G., 2014. Processing USPTO Patent Data. Coleman Fung Institute for Engineering Leadership.