SQL in Python and Emacs

Table of Contents

Introduction

There are several ways to query SQL data. Each have their pros and cons.

Table 1: PROS and CONS
Method Pros Cons
Python Versatile Python knowledge
CLI Easy to get started Error Prone
Emacs Documentation Emacs knowledge
sqlitebrowser User Friendly Not programmatic
Google Sheets User Friendly Cloud usage only

Emacs SQLite

For demonstration purposes of how we can perform sql queries using org babel source blocks in Emacs we will use SQLite.

Getting Started

You will need the following:

  • sqlite3 program
  • sqlitebrowser (optional)
  • A database to query

Add this in your emacs configuration: 

(org-babel-do-load-languages
 'org-babel-load-languages
 '((sql . t)))

To avoid a prompt asking to evaluate the code block add this to your configuration. You only need the sqlite part. 

(defun my/org-confirm-babel-evaluate (lang body)
  (and (not (string= lang "emacs-lisp"))
       (not (string= lang "elisp"))
       (not (string= lang "bash"))
       (not (string= lang "sqlite"))
       (not (string= lang "lua"))
       (not (string= lang "plantuml"))
       (not (string= lang "python"))
       (not (string= lang "gnuplot"))))
(setq org-confirm-babel-evaluate #'my/org-confirm-babel-evaluate)

Examples

Your org source block should look like this. Notice how we can use variables. 

#+begin_src sqlite :db /path/to/your.db :var data="exerciseLogs" :colnames yes
SELECT * FROM $data LIMIT 5;
#+end_src

This shows my Leg Press exercise logs for the last 3 years, this is the same data in the image below in the Python Chart section. 

--SELECT mydate,ename,record FROM $data LIMIT 15;
SELECT mydate,ename,record 
FROM $data 
WHERE ename = 'Leg Press' 
  AND mydate >= date('now', '-3 years')
  ORDER BY mydate ASC;
Table 2: Results
mydate ename record
2022-06-21 Leg Press 240.0
2022-09-06 Leg Press 228.0
2022-09-16 Leg Press 266.67
2022-09-30 Leg Press 337.0
2022-10-06 Leg Press 360.0
2022-10-14 Leg Press 386.67
2022-10-25 Leg Press 448.0
2022-11-02 Leg Press 405.0
2022-11-09 Leg Press 405.0
2022-11-16 Leg Press 480.0
2022-11-22 Leg Press 506.0
2022-11-29 Leg Press 533.0
2022-12-07 Leg Press 546.67
2022-12-27 Leg Press 546.0
2023-01-04 Leg Press 570.0
2023-01-13 Leg Press 633.33
2023-01-17 Leg Press 633.33
2023-01-23 Leg Press 658.67
2023-02-04 Leg Press 570.0
2023-02-28 Leg Press 525.0

Python Chart

Emacs SQLite is great for basic queries but what if we wanted to do more complex things? This is where the power of database analysis using Python modules NumPy, Pandas and Matplotlib comes into play.

Python Virtual Environment

In order to use our chart.py program we must install our modules preferably in a virtual environment. I use Linux with fish shell, modify to your OS. I have provided a makefile for convenience with the source code below. 

python3 -m venv ~/path/to/python/chart/env-chart
source          ~/path/to/python/chart/env-chart/bin/activate.fish

Confirm you are in the virtual environment. 

which python3
# ~/path/to/python/chart/env-chart/bin/python3
which pip
# ~/path/to/python/chart/env-chart/bin/pip

Python Modules Install

pip install matplotlib pandas numpy

Source Code

You can download the source code to the project here. This gives you an idea of how you can manipulate SQL data in python.

sql-python-chart-data.png

Figure 1: Chart

Validate