DBMS — Key Aspects of Web Development in Python

• What is a DBMS?
• Relationships within the database
• SQL Language
• Driver
• What novice developers need to know

Suppose we decided to implement a site with ads and want to allow our users to create ads. We already know how to output a page with a form to add an ad. We need to generate HTML using the templating tool. But what do we do after the form data arrives at the server? Where do we store them? We'll get to the bottom of this question in this lesson.

What is a DBMS?

The easiest way to look at it is by using files as an example. We can write all events to a file and read from it when output. This approach has many drawbacks and is very inconvenient to work with. The correct way to work with data is with a database. It is stored in files too, but this process is 100% controlled by a DBMS (database management system). It is the one you need to install before you start work.

The most common systems on the web are PostgreSQL and MySQL. We won't discuss it here because configuring and installing a DBMS isn't as trivial as installing an interpreter. You can find many ready-made instructions on DBMS installation on your operating system.

DBMSs start as separate programs and have a life of their own. These systems can serve many different databases. Usually, one site uses one database, but we can store many databases from several sites in one DBMS. You can interact with a particular database in two ways:

• Run the DBMS command shell, which allows you to interactively work with commands, write and read data, add a user to the system, and so on. When connecting or after connecting, you need to select the database you want to interact with:

• Connect to the DBMS from your program using a driver — a library that communicates with the DBMS. It has a relatively simple interface for performing queries and getting results. Below we'll see how this approach works in practice.

Relationships within the database

PostgreSQL and MySQL belong to the class of relational databases because these DBMSs work based on relational algebra. Without going into theory, we'll say that the data in relational databases store their data in the form of tables. This format is familiar to everyone who has used Microsoft Excel or Google Sheets.

Each table in such a database has its name and a set of named columns. We can refer to columns in a database as fields. For example, we can create a table with ads, give it the name ads and place the following data:

Each row in the table is independent and represents a complete data set. It is a single declaration in our case. Programmers commonly refer to lines as records.

Note the id field. We use it to identify a particular line. DBMSs contain mechanisms that allow us to automatically generate the identifier when we insert data into the database. Typically, we use identifiers in page addresses. For example, the http://myavito.ru/ads/53 address will display the ad with an id of 53.

SQL Language

Any manipulation of tables in relational databases is done using SQL (Structured Query Language). Once you open the DBMS command shell, you can try to execute the following queries. Let's take this example and assume that we have already created the ads table:

SELECT * FROM ads; -- select all records
SELECT title FROM ads WHERE id = 4; -- select title for a record with an id equal to 4
SELECT telephone FROM ads ORDER BY id DESC; -- select all phone numbers by sorting them by id field in reverse order

INSERT INTO ads (telephone, title) VALUES ("392503", "I'll buy an elephant"); -- Insert a new entry in the ads table

DELETE FROM ads WHERE id = 5; -- delete record with id equal to 5

As you can see, SQL is a simple language. It is easy to understand without unnecessary explanations. Of course, you'll have to sweat a lot over complex SQL queries, but in most cases, SQL is simple enough.

Driver

While we can execute SQL queries directly from within the base shell, we can't do it from within the code. We need a driver that will pass our SQL to the database and return a response. But before sending a request, you must enter the correct username and password to connect to the database.

DBMSs are multi-user systems with an advanced rights system, so security is essential when working with the database. Data is the most valuable thing in an app. If the data is lost, the business may cease to exist. Let's take the postgres driver (third-party package). It allows you to work with PostgreSQL somehow:

from postgres import Postgres
db = Postgres("postgres://user@localhost/test")

db.run("CREATE TABLE foo (bar text, baz int)")
db.run("INSERT INTO foo VALUES ('buz', 42)")
db.run("INSERT INTO foo VALUES ('bit', 537)")

db.one("SELECT * FROM foo WHERE bar='buz'")
# Record(bar='buz', baz=42)

db.all("SELECT * FROM foo ORDER BY bar")
# [Record(bar='bit', baz=537), Record(bar='buz', baz=42)]

db.all("SELECT baz FROM foo ORDER BY bar")
# [537, 42]

What novice developers need to know

Don't feel bad if you don't understand most of the things described here.

The main thing is that you realize how difficult it is to jump into real-life development. A novice developer needs a lot of preparation beforehand. Here are some new topics that you need to learn to write code without having issues:

• Databases — installation and configuration, user management, operating sockets
• Operating networks
• SQL, including the basics of set theory
• Concepts of normalizing and denormalizing data — normalized forms, keys, and indices
• Serialization and deserialization
• Idempotency
• Fluent Interface
• Iterator
• Shielding, SQL Injection, and other security-related topics