• Overview
• What is a Database?
• Relational Databases
• Structured Query Language (SQL)
• Database Products
• Production Environments
• Development Environments

Overview

Databases are ubiquitous; that is, they are everywhere. There are databases on your smartphone. There are databases behind every social media platform (Facebook, Twitter, Snapchat, etc.). There are databases in nearly every modern computer game. Databases are used in almost every business that exists today. The list goes on and on about where you will find databases. As a professional today it is critical to gain an understanding of what databases are, how to work with them, and how software and businesses use them to manage their operations.

In this module, we will learn how to connect to a database from Python, how to read data from the database, and how to add data to a database from Python. These skills are valuable for professionals in the Information Systems industry.

Databases were invented over 50 years ago. Today, many different companies produce database products and solutions. But what is a database?

This is a very generic definition of a database. It is essential to understand that there are many types of databases, it is difficult to provide a single all-encompassing definition. So, rather than arrive at a detailed definition, we will focus on relational databases, which are very common in business applications. Remember though that while we will focus on relational, you may find yourself working with any of the other types in your career.

Relational databases store data in structures called tables, and the data in those tables are (usually) related in some way. For example, a system that tracks students at a college might have a table called students that stores student information (name, address, etc.). We might also have another table called majors that holds majors that each student declares (majors here plural because a student can major in more than one field of study). We could also have a table called registration that tracks what classes each student has registered for each semester. The entire system would contain many tables, all related to each other in various ways. Those tables would be related by ID values (called keys) so that we can easily find any student, their majors, the classes they have registered for, and other detailed relationships. We can visualize this example in a diagramming method called an Entity Relationship Diagram (ERD like this:

Relational databases use a programming language called Structured Query Language (SQL) to create queries (questions) that we ask to obtain results from the database, like the example I used above of the customer and their orders. Inside Python (and other programming languages), we can connect to a relational database, send it a SQL statement (query, update, insert, etc.), and the database will run that SQL statement and return any results to our program. This process is widespread in software development for desktop applications, mobile applications, web applications, etc. That process looks like this:

In this example, the grading system data is stored in the grading_system.db SQLite database. In the Python code, the program connects to the database and passes a SELECT statement (a query) to the database asking for all students with a last name of 'Parks'. The database processes the query and returns a result set, a set of all records that match the query specification (last_name = 'Parks'). In this example, only one student with that last name so the result set contains one record.

Don't worry about understanding the above code yet; we will cover programmatically connecting to and querying databases in detail throughout the book.

So far, all of the references above to databases have been generic, but where do these databases come from? A database resides in a database engine (a.k.a. "database server"), which is software that usually runs on a hardware server or in a cloud-based service. Numerous vendors produce database server software. Here is a short list of common database vendors and their database products. There are many other vendors and databases available; those listed below have versions available for free that you can download and practice with as you learn more about programming.

Vendor	Product(s) *
Microsoft	SQL Server SQL Server is Microsoft's flagship database system widely used in business. They make a developer edition available for free download. You can find it here--be sure to choose the Developer Edition from that download page. Also, they provide an Integrated Development Environment (IDE) for interactively working with the database server called SQL Server Management Studio (SSMS), which you can download for free here. Also, to interact with SQL Server from Python, you'll want to download and install the Python SQL Driver as well.
Open Source	MariaDB MariaDB is an open-source database system widely used and a good choice for developers and students. You can download MariaDB from here. For an IDE, you can use MySQL Workbench or dbForge Studio. Also, to interact with MariaDB from Python, you'll want to download the Python connector as well.
Open Source	PostgreSQL PostgreSQL is also a popular open-source database system, widely used and well-supported. You can download PostgreSQL from here. The most common IDE for PostgreSQL is pgAdmin, which you can download for free here.
Open Source	SQLite SQLite is a database engine that developers commonly use to embed database functionality into their applications. SQLite is not an application in the traditional sense that you download and install; rather you download a set of library files and then use those libraries from within a programming language. You can also use an additional tool called SQLite Studio to interact with a SQLite database using a GUI front end. You can download SQLite from here, and SQLite Studio from here.
Oracle	MySQL MySQL is a very popular database system, particularly for web applications. Most hosting companies include MySQL databases as part of the tools they make available when hosting websites on their platforms. You can download MySQL Community Edition (free) from here. Also, I recommend using MySQL Workbench for working with MySQL datatabases, which you can download from here.

The word environment is used to refer to the combination of hardware and software working together for a particular purpose. A production environment is a term used to indicate the hardware and software used during the actual operations of a business or system. In production systems, databases are installed and configured on one or more servers on a network or in cloud-based systems. The database engine then usually runs 24/7 so that it is always available to processes, users, and administrators. Those systems are typically configured with tight security and access to them is restricted by database administrators to keep the data secure.

With the database engine running, applications can then interact with the database engine as needed and configured. Some examples of applications that might interact with the database include an accounting clerk using Microsoft Excel to calculate sales summaries and generate graphs. Another may be a data analyst using Tableau to create a management dashboard to display real-time trends based on transaction data in the operational database. A web developer writes Python script code to process web form data entered by users that are added to the database. A Software Developer writes an application in Python used inside the company by the Marketing Department to allow sales agents to enter data about potential clients into the database. The company also maintains a data warehouse populated once per night by an automation process that collects daily transaction data from the operational database and loads the analytical data warehouse. Each morning, an automated reporting process generates summary reports using Microsoft PowerBI which are automatically emailed to executives for review. As a programmer, you may be involved in developmenting applications and processes similar to these examples, or others. Here is a visual representation of the scenarios described above:

In contrast to a production environment, a development environment is a combination of hardware and software software developers use to write and test applications they create. Development environments are often set up to simulate the production environment where the software developer's application will run once it is complete. We will create a development environment that will combine your computer (laptop or desktop) with a database engine and an SQL editor to work with the database in conjunction with your Python code. All of this is combined in your computer to create your development environment and with that configuration you will be able to create databases and interact with them, simulate a production environment similar to the diagram above. Development environments look something like these:

In this example, the development environment includes the Python programming language, Oracle MySQL database, its Workbench (GUI) tool, the PyCharm IDE, Tableau, Excel, and LucidChart.

In this example, the development environment includes the Python programming language, Microsoft SQL Server database, its Management Studio (GUI) tool, the PyCharm IDE, Tableau, Excel, and LucidChart.