Why Python is getting more popular...and how to use it with SQL databases

DB2 for LUW Offering Manager, IBM

For the past nine years, Stack Overflow, a question-and-answer website for programmers, has polled developers to understand what technologies they are using and to find out what technologies they want to work with next. This year, the nearly 90,000 survey participants revealed that, once again, Python has risen in the ranks of language popularity.

Python earned fourth place, edging out Java and underscoring the claim that it is the fastest-growing programming language in use today. SQL, a standardized language used to work with relational databases, came in third. 

Taken together, these results highlight not only the need to know Python, but also the need to be able to use Python and SQL together in application development. Two audiences will benefit greatly from learning how to connect the two: developers wanting to use information in a SQL database; and SQL database users wanting to learn how to get value out of data with Python applications.

What is driving Python’s popularity

Python is popular in part because it’s open source. Anyone can download and use it to develop applications. And this can be done on a wide variety of platforms, including Microsoft Windows, macOS, and most Linux distributions. Applications written on one platform can easily be executed on another.

Another reason the language is appealing is that Python code is easy-to-read and thus easy to learn and maintain. Python has a relatively simple structure, and its syntax is clearly defined. It uses English keywords where other languages rely on punctuation. There are no curly braces to balance or semicolons to remember—indentation is used to mark where code blocks begin and end.

Python also uses libraries to provide functionality, which helps keep the core language simple and lightweight. Developers only need to add a basic set of libraries to their code to get the functionality they desire. Python is also very flexible. It can be used with functional, object-oriented, and imperative coding styles, making it useful to different types of programmers. 

Five core elements of applications

At the most basic level, an application is a computer program that has been designed to perform a group of coordinated operations to solve a particular problem. Applications are usually constructed around five basic elements:

  1. Input
  2. Logic (decision control)
  3. Memory (data storage and retrieval)
  4. Arithmetic operations (calculations or processing)
  5. Output

Input is defined as the way an application receives the information it needs to produce solutions for the problems it has been designed to solve. Once the appropriate input is received, logic takes over and controls what information should be placed in or taken out of memory and what arithmetic operations should be performed on that information. Because data placed into or taken out of memory is not persistent and can be lost if not stored elsewhere, a simple application might interact with the operating system to move data to and from a character- or byte-oriented file. Then, when the application has generated a solution to the problem it was designed to solve, it provides output in the form of an answer or specific action. 

Why connecting Python applications with SQL is vital

Much of an organizations data sits within SQL-based data management solutions. Take for example IBM Db2. Db2 consists of a set of formally-described data objects that are used to store and link data values by establishing some type of relationship between them. Typically, data is organized into tables, which are user-defined objects that present data as a collection of unordered rows with a fixed number of columns. Each column contains values of the same data type, and each row contains a set of values for one or more columns. This results in a very efficient way to store data, as values only have to be stored once. Data in tables can be accessed and assembled in a variety of ways to facilitate any number of operations. 

The Db2 family of products makes the storage and management of both structured and unstructured data easy, regardless of where the data resides. Database, data warehouse, data lake, and fast data solutions that are built on a common SQL engine are available across on-premises bare metal and virtual servers, as well as in private, public and hybrid clouds.

The differences inherent in using Python with SQL

Applications that work with databases like Db2 still contain the five basic elements discussed earlier. The only real difference is the way in which persistent data is stored and retrieved, and in some cases, the way logic is exercised. File input/output (I/O) operations are replaced with SQL operations, and in some cases, decision control can be built directly into a database using special objects like triggers, stored procedures and constraints.

Because of this difference, Python applications that use a database for persistent storage must perform three distinct tasks that are not normally required:

  1. Establish a connection to a database or database server
  2. Perform any transaction processing required, using SQL
  3. Terminate the connection when it is no longer needed

Such applications may also perform other tasks like obtaining information about a specific database or retrieving database-specific error messages when a desired operation fails.

Building Python applications with SQL in mind

Because databases often serve as a company’s data repository, Python applications need to be built so they can interact with database servers and solutions. But how? Recently, I explored this topic in a new e-book titled “QuickStart Guide to Db2 Development with Python.” This book brings Python, SQL, and Db2 together with the goal of illustrating how these technologies can successfully be used to build applications that store and manage data.

After introducing Db2, SQL, Python, and the Python libraries and drivers IBM provides for Db2, the book offers a step-by-step guide for setting up a Python-Db2 development environment on RedHat or Ubuntu Linux. It also provides in-depth information on how to structure and build Python applications that interact with Db2 along with information about a GitHub repository that contains more than 70 sample Python programs and 60 Jupyter Notebooks.

Whether you’re Python developer who wants to build applications that work with Db2, or you’re a Db2 user who wants to know how to build Python applications that interact with Db2 servers and databases, you’ll find the information in this book indispensable. Download it at no cost today.