Learn SQL and database design with Raspberry Pi

By PJ Evans. Posted

SQLite is the perfect database for learning SQL (Structured Query Language, used to get data in and out of a database). It is unique as it does not rely on a running server: instead, the database is stored in a regular file. So, you can avoid all the sysadmin stuff and get straight into writing SQL. Like all the database platforms covered here, it is open-source and a breeze to install on Raspbian.
Despite its diminutive nature, SQLite is fast and powerful, making it suitable for smaller projects.

SQLite

SQLite’s site is basic, but sqlitetutorial.net provides free interactive training. As well as covering SQL for SQLite (all platforms have slightly different implementations of SQL), there is a Python-specific course. Here, you can learn how to use PySQLite, the Python bindings for SQLite. Not only is the SQL language covered, but how to create databases and tables in Python. This makes for a comprehensive combination of courses.

MariaDB

MariaDB/MySQL

The next logical step from SQLite is to move to a full relational database server. A popular choice is MariaDB, an open-source project based on, and fully compatible with, MySQL. MySQL is probably one of the most popular database servers of all time. Both variants are free, but MariaDB boasts a new ‘engine’ that provides significant speed increases. Installing the server is straightforward and it runs surprisingly fast, even on older Raspberry Pi hardware. Once installed, you’ve more to think about than with SQLite, such as users and permissions. Thankfully, MariaDB offers online courses for free - a nicer way to learn than ploughing through dry documentation. This is a powerful platform: it can take tables containing millions of rows in its stride. It’s possible to use MariaDB/MySQL for anything from your own logging project, to enterprise-grade applications.

MongoDB

MongoDB

Certain applications or projects require a different type of database. If you’re looking at storing lots and lots of simple data and don’t require the kind of data extraction that relational databases offer, then a NoSQL (or document) database may be for you. This family of databases specialise in high-volume, fast data storage and retrieval. They are especially popular when the job is to ‘just get the data and store it as quickly as possible’. Rather than using the traditional model of tables, columns, and rows, document databases use schemas and key-pairs to store data. MongoDB is a popular open-source server. It’s very easy to install, and you can undertake a full course.

The Definitive Guide to SQLite

Grant Allen and Michael Owen’s book on SQLite is a comprehensive look at this ‘micro’ database. Despite its small footprint, the authors reveal an incredibly powerful engine and some surprising applications including embedded systems.

MySQL for Python

Python being the preferred choice of language of the Raspberry Pi Foundation, this book is a perfect introduction to using Python’s MySQL wrappers. The MySQL for Python book is formatted as a series of tutorials that increase with complexity.

MongoDB and Python

Like the aforementioned MySQL for Python book, this one gets right to the point. It uses the ‘recipe’ format, so you can find a close match for the problem you want to solve, and see how using MongoDB can help.

Udemy SQL Video courses

Intro To SQLite Databases for Python Programming

A great ‘back to basics’ video course which takes you through all the steps of installing and using SQLite. Intro to SQLite Databases for Python Programming is 90 minutes of video in bite-size pieces.

Using MySQL Databases With Python

Just like for SQLite, Udemy offers a full nothing-to-something course on using MySQL Databases with Python. The two hours of video usefully cover installation of the server.

MongoDB and Python: Quick start

Crucially, this MongoDB and Python: Quick Start two-hour video course covers when to choose a NoSQL (or document) database and the key differences between this and traditional SQL engines.


https://www.twitter.com/mrpjevans

Subscribe