How to talk with your relational database

<

Image Source: Screenshot of phpmyadmin taken by Martin Thoma

Databases are the core of storing state for almost all web applications. For that reason taking care of the interactions with the database is crucial to make sure the application keeps running. The way to interact with most relational databases is SQL — the S_tructured _Q_uery _Language. SQL makes it incredibly simple to switch the actual database system or the client using that database. It’s just SQL everywhere. You need a database driver and then you can do the typical CRUD interactions: Create, Read, Update and Delete data.

After reading this article, you will know when to use raw SQL, a query builder and an ORM. You will also know how to use each of them in Python.

All of the code in this article is runnable. You just need to initialize the database and add environment variables. I use direnv to set environment variables:

# direnv needed; alternatively: "source .envrc"
	export DB_USER=root
	export DB_PASSWORD=idontthinkso

Raw SQL

_Raw SQL, _sometimes also called native SQL, is the most basic, most low-level form of database interaction. You tell the database what to do in the language of the database. Most developers should know basics of SQL. This means how to CREATE tables and views, how to SELECT and JOIN data, how to UPDATE and DELETE data. For more complex things like stored procedures, T-SQL, PL-SQL, in-depth knowledge about indices and their effect you will have a significantly harder time to find knowledgeable people. SQL is far more powerful than many developers think. I wouldn’t know how to create the Mandelbrot set with SQL, for example.

In order to illustrate the problems of raw SQL statements, take the example of a book portal. The users can see data about books, for example their title, original language and the author:

Every book has exactly one author, but every author might have an arbitrary number of books.

For an author page, we are given the authors.id and want to see a list of allbooks.title written by that author:

import os
	from typing import List

	import pymysql.cursors

	def db_connection(f):
	    """
	    Supply the decorated function with a database connection.

	    Commit/rollback and close the connection after the function call.
	    """

	    def with_connection_(*args, **kwargs):
	        con = pymysql.connect(
	            host="localhost",
	            user=os.environ["DB_USER"],
	            password=os.environ["DB_PASSWORD"],
	            db="books",
	        )
	        try:
	            rv = f(con, *args, **kwargs)
	        except Exception:
	            con.rollback()
	            raise
	        else:
	            con.commit()
	        finally:
	            con.close()

	        return rv

	    return with_connection_

	@db_connection
	def get_titles_by_author(con, author_id: int) -> List[str]:
	    cur = con.cursor(pymysql.cursors.DictCursor)
	    cur.execute(f"SELECT * FROM books WHERE author_id = %s", author_id)
	    titles = [row["title"] for row in cur.fetchall()]
	    return titles

	if __name__ == "__main__":
	    print(get_titles_by_author(1))

You need to install the driver pymysql via “pip install pymysql”

The decorator is utility code that the project might use a lot.

On the positive side, it is pretty clear what happens with raw SQL. You only need knowledge of Python and SQL. No need to be deep in third party software.

However, there are six negative aspects about using raw SQL to be aware of.

#object-relational-mapping #query-builder #python #sql #software-engineering