All pages
Powered by GitBook
1 of 20

Other Connectors & Methods

Explore additional ways to connect to MariaDB, including less common client libraries, ODBC, and methods for various programming languages and tools beyond the main connectors.

Erlang

MySQL/OTP is a driver for connecting Erlang/OTP applications to MySQL and MariaDB databases. It is a native implementation of the MySQL protocol in Erlang.

MySQL/OTP (Erlang/OTP Client for MariaDB/MySQL)

MySQL/OTP is a native Erlang/OTP client designed for connecting Erlang applications to MySQL and MariaDB databases. It implements the MySQL protocol directly in Erlang, offering robust features for database interaction within an OTP framework.

1. What is MySQL/OTP?

MySQL/OTP acts as a client library, enabling Erlang applications to communicate with MySQL and MariaDB servers. It provides a native Erlang implementation of the MySQL protocol, allowing for efficient and idiomatic database operations within the Erlang/OTP ecosystem.

2. Key Features

  • Mnesia Style Transactions: Supports nestable transactions that align with Erlang's Mnesia database transaction model.

  • SSL Support: Secure connections using SSL/TLS.

  • Authentication Methods: Supports caching_sha2_password and mysql_native_password.

  • Parametrized Queries: Utilizes cached unnamed prepared statements for efficient and secure queries.

  • Interruptible Slow Queries: Allows interrupting slow queries without terminating the connection.

  • Protocol Support: Implements both the binary protocol (for prepared statements) and the text protocol (for plain queries).

3. Installation

You can add MySQL/OTP as a dependency in your Erlang/OTP project using erlang.mk, rebar (v2 or v3), or mix (for Elixir projects).

a. Example with rebar.config (rebar3):

Add the following to your rebar.config file in the deps section:

{deps, [
    {mysql, ".*", {git, "https://github.com/mysql-otp/mysql-otp.git", {tag, "2.0.0"}}} % Use the latest stable tag or master
]}.

Then run rebar3 compile or rebar3 deps get to fetch and compile the dependency.

4. Basic Usage

Here are common operations using the mysql module:

a. Connect to the Database:

Establish a connection to your MariaDB/MySQL server. SSL is optional.

% Basic connection
{ok, Pid} = mysql:start_link([{host, "localhost"}, {user, "myuser"}, {password, "mypass"}, {database, "mydb"}]).

% Connection with SSL (example with CA certificate)
% {ok, Pid} = mysql:start_link([{host, "localhost"},
%                                {user, "myuser"},
%                                {password, "mypass"},
%                                {database, "mydb"},
%                                {ssl, [{server_name_indication, disable}, {cacertfile, "/path/to/ca.pem"}]}
%                              ]).

Replace "localhost", "myuser", "mypass", and "mydb" with your database details.

b. Execute a SELECT Query (Parameterized):

% Prepared statement with parameters
{ok, ColumnNames, Rows} = mysql:query(Pid, <<"SELECT id, name FROM mytable WHERE status = ?">>, [<<"active">>]),
io:format("Columns: ~p~n", [ColumnNames]),
io:format("Rows: ~p~n", [Rows]).

c. Manipulate Data (INSERT/UPDATE/DELETE):

% Insert data
ok = mysql:query(Pid, "INSERT INTO mytable (col1, col2) VALUES (?, ?)", [<<"value1">>, 123]),
io:format("Insert successful!~n").

% Get info about the last query
LastInsertId = mysql:insert_id(Pid),
AffectedRows = mysql:affected_rows(Pid),
WarningCount = mysql:warning_count(Pid),
io:format("Last Insert ID: ~p, Affected Rows: ~p, Warnings: ~p~n", [LastInsertId, AffectedRows, WarningCount]).

d. Mnesia-style Transaction (Nestable):

% Example of a nested transaction
mysql:transaction(Pid, fun() ->
    ok = mysql:query(Pid, "UPDATE accounts SET balance = balance - 100 WHERE user_id = 1"),
    mysql:transaction(Pid, fun() ->
        ok = mysql:query(Pid, "UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")
    end),
    {atomic, ok} % or {atomic, YourResult}
end).
io:format("Transaction completed.~n").

e. Execute Multiple Queries:

% Example with multiple queries and multiple result sets
{ok, Results} = mysql:query(Pid, "SELECT 1 AS a; SELECT 'hello' AS b;"),
io:format("Multiple Query Results: ~p~n", [Results]).
% Results format: [{[ColumnNames], [Rows]}, {[ColumnNames], [Rows]}]

f. Graceful Timeout Handling:

You can specify a timeout for queries. If the query exceeds the timeout, it will be interrupted.

% Query with a 1000ms (1 second) timeout
{ok, ColumnNames, Rows} = mysql:query(Pid, <<"SELECT SLEEP(5)">>, 1000),
io:format("Query interrupted, result: ~p~n", [Rows]). % SLEEP() typically returns 1 when interrupted

g. Close the Connection:

Always close the connection when it's no longer needed.

mysql:stop(Pid).
io:format("Connection closed.~n").

Further Resources:

  • MySQL/OTP GitHub Repository

  • Erlang/OTP Documentation (if available on the official Erlang docs)

Excel Add-in for MariaDB

Devart Excel Add-in for MySQL allows you:

  • to connect Microsoft Excel to MySQL or MariaDB

  • quickly and easily load data from MySQL or MariaDB to Excel

  • instantly refresh data in an Excel workbook from the database

  • edit these data and save it back to MySQL

mysql.html

Perfect-MariaDB for Swift

Source Code & Library for MariaDB Connector in Swift:Perfect-MariaDB

Document:MariaDB.html

Perl DBI

  • The DBD-MariaDB driver is a DBI driver for connecting to MariaDB database server.

    • Documentation

    • DBD-MariaDB driver

    • Source code

    • Bug reporting

  • The DBD-mysql driver for MySQL should generally work with MariaDB as well, although it has a number of bugs that have not been attended to, hence the need for DBD::MariaDB:

PHP

The PHP connectors for MySQL generally work with MariaDB as well. See mysql.php and downloads.php

RMariaDB: MariaDB Driver for R

RMariaDB is a database interface and MariaDB driver for R. This version is aimed at full compliance with R's DBI specification.

The link to the package on CRAN (R Package Repository) can be accessed from: CRAN RMariaDB

The package can be installed in R with the following statement:

install.packages("RMariaDB")

And loaded in the R environment executing:

library(RMariaDB)

Basic notions on R Programming can be found in article:R Statistical Programming Using MariaDB as the Background Database

RMariaDB Package Function Examples

library(RMariaDB)
library(DBI)
# Connect to my-db as defined in ~/.my.cnf
con <- dbConnect(RMariaDB::MariaDB(), group = "my-db")

dbListTables(con)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)

dbListFields(con, "mtcars")
dbReadTable(con, "mtcars")

# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
dbClearResult(res)

# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
# Clear the result
dbClearResult(res)

# Disconnect from the database
dbDisconnect(con)

Ruby

The MySQL API module for Ruby should generally work with MariaDB as well:

  • mysql2

The MariaDB Jupyter kernel

The MariaDB Jupyter Kernel lets you run MariaDB directly in Jupyter notebooks. Execute SQL, visualize results with magic commands, and integrate with Python for data analysis.

About the MariaDB Jupyter Kernel

The MariaDB Jupyter Kernel is an Open Source kernel for Jupyter which enables users to run MariaDB in a Jupyter notebook.

Notebooks can be run in a variety of environments, ranging from your local computer for testing purposes via JupyterLab to complex Zero To JupyterHub Kubernetes systems running in the cloud.

The mariadb_kernel project is agnostic about the complexity of your Jupyter infrastructure, it can run on any of them thanks to the way Jupyter designed its kernel machinery. As long as MariaDB is installed on the host running the kernel and there is MariaDB Server running somewhere, things should work out as expected.

Motivation

We created the mariadb_kernel project with some simple goals in mind:

  • Help existing MariaDB users have an alternative to the classical mariadb command-line client based on Jupyter.

  • Bring MariaDB Server to Jupyter and Python users who would like to use our Open Source database for handling their datasets.

If you would love to be able to run SQL against MariaDB data from Jupyter notebooks or you want to run a training program and help your employees learn SQL, if you are a teacher and you’d love to use Jupyter for your SQL classes or you are a data scientist trying to quickly chart or explore your datasets, you should take a look at this project.

Contents

Installation

  • Quick Installation Steps

  • Complete Installation Steps

  • Platforms Coverage

Configuring the kernel

  • Config File Location

  • Config Example

  • The Full List of JSON Options

Using the Kernel

  • General Usage Information

  • SQL Autocompletion and Introspection

  • Magic Commands

  • Restrictions and Limitations

Main Components and Architecture

  • Architecture

  • Components

    • MariaDBKernel

    • ClientConfig

    • MariaDBClient

    • MariaDBServer

    • CodeParser

    • MagicFactory

    • MariaMagic

    • LineMagic

    • CellMagic

    • LSMagic

    • Line

    • DF

    • Bar

    • Pie

    • Delimiter

Extending the Kernel

Changelog

  • v0.2.2

  • v0.1.1

  • v0.1.0 First release!

Changes in MariaDB Jupyter Kernel

[v0.2.0] 02 November 2021

Features and improvements

  • SQL code autocompletion ( GSoC project 2021 by Xing-Zhi Jiang)

  • Code Instrospection ( GSoC project 2021 by Xing-Zhi Jiang)

  • Add debug option in ClientConfig

  • The MyBinder configurations are switched to Dockerfile to achieve more flexibility

  • The kernel is now able to start its own MariaDB Server instance, run mariadb-install-db to create a datadir and pass the right options to client and server so that first-time users only need to have the mariadb-server package installed (Contribution by Jonas Karlsson).

Bug fixes

  • Progress reporting for commands such as LOAD DATA is disabled

  • In multiple running notebooks with multiple MariaDB kernels, only the last alive notebook kills the MariaDB server (Contribution by Jonas Karlsson)

[v0.1.1] 29 March 2021

Features and improvements

  • Add pre-commit support in the GitHub Actions CI

  • Add two new example notebooks (contribution from Daniel Black)

  • Add more tests

  • Queries such as INSERT that don't return any result set should show a "Query OK" confirmation message (Contribution by XING-ZHI JIANG)

  • Add %%delimiter cell magic command to run a query under a different SQL delimiter

  • SQL error messages should only contain the relevant part of the error without the MariaDB error number

Bug fixes

  • Refactor CodeParser to address a number of bugs related to how the MariaDB kernel was parsing multi-line queries

  • Fixed bugs in MariaDB client that were leading to wrong multi-line outputs from functions such as JSON_DETAILED

[v0.1.0] 11 January 2021

First release!

Configuring the MariaDB Jupyter Kernel

Config File Location

The kernel can be configured via a JSON file called mariadb_config.json.

The kernel will look for this config file in one of these two locations:

  1. If the JUPYTER_CONFIG_DIR environment variable is defined (non-empty),mariadb_kernel will try to read $JUPYTER_CONFIG_DIR/mariadb_config.json

  2. If the environment variable is empty, the kernel will try to read $HOME/.jupyter/mariadb_config.json

Config Example

Here’s an example file containing some of the available options that you can pass to the kernel:

cat ~/.jupyter/mariadb_config.json
{
    "user": "root",
    "host": "localhost",
    "port": "3306",
    "password": "securepassword",
    "start_server": "True",
    "client_bin": "/usr/bin/mariadb",
    "server_bin": "/usr/bin/mariadbd"
}

By default the kernel starts a MariaDB server running at localhost, on port 3306 and connects to this instance using the user root with no password. The kernel also assumes that MariaDB Server is installed and its binaries are in PATH. You can change any of these options to fit your use-case. This also means you can run a notebook locally with a MariaDB kernel, and make the kernel connect to a server running in the cloud for instance.

The kernel, using the default configuration, looks for the mysql and mysqld binaries in your PATH. You can point (for example if you have a local MariaDB built from sources) the kernel to an exact location for these two binaries using the client_bin and server_bin options.

The start_server option tells the kernel to start a MariaDB Server instance for you, when the kernel is loaded, if it detects no running server given the configurations passed.

The Full List of JSON Options

If you suspect the documentation might not be up to date, you can check the complete list of available options at this link.

Option
Default
Explanation

user

“root”

Kernel passes --user=root to the MariaDB client

host

“localhost”

Kernel passes --host=localhost to the MariaDB client

socket

"/tmp/mariadb_kernel/mysqld.sock"

If socket authentication is enabled in MariaDB, you can configure this option to tell the kernel that it can use socket authentication for connecting to the server. --socket is passed to the MariaDB client and if the server is started by the kernel, the kernel passes the option to the MariaDB server as well

port

“3306”

Kernel passes --port=3306 to the MariaDB client

password

“”

Kernel passes --password=”your_pass” to the MariaDB client

server_datadir

"/tmp/mariadb_kernel/datadir"

Valid only if start_server=True. Tells the kernel the location of the datadir for the started server

server_pid

"/tmp/mariadb_kernel/mysqld.pid"

Valid only if start_server=True. Tells the kernel the location of the PID file for the started server

start_server

“True”

Start a server if no server running is detected for this config

client_bin

“mysql”

The name or path for the MariaDB client binary

server_bin

“mysqld”

The name or path for the MariaDB server binary

db_init_bin

“mysql_install_db”

Valid only if start_server=True. The name or path for the mysql_install_db binary. The kernel uses this tool to set up the MariaDB Server instance that it starts for you

extra_server_config

["--no-defaults", "--skip_log_error"]

Valid only if start_server=True. Extra arguments to pass to the MariaDB Server instance that the kernel starts for you

extra_db_init_config

["--auth-root-authentication-method=normal"]

Valid only if start_server=True. Extra arguments to pass the mysql_install_db script when the kernel sets up the server

debug

"False"

Enables debug logging which provides lots of internals information

code_completion

"True"

Enables SQL autocompletion and code introspection

Contributing to the MariaDB Jupyter Kernel Project

Welcome! <3 The MariaDB Jupyter Kernel is an Open Source project licensed under BSD-new. Feel free to create new Issues for feature requests or bug reports. Code, tests, and documentation contributions are welcome :-) Friendly advice or feedback about the project counts as a contribution too!

Please check out the CONTRIBUTING.md file for information on:

  1. How to set up development environment

  2. Running the tests

  3. Code formatting

  4. Adding a new magic command

The reasoning for having the Contributing documentation on GitHub is that it is closer to the actual code and it feels right to have it there. We also believe it fosters a cozy and welcoming environment for new contributors if both the code and the Contributing documentation sit near each other.

MariaDB Jupyter Kernel Installation

The mariadb_kernel project uses the MariaDB command-line client under the hood as a child process, thus having MariaDB installed on your system is a hard requirement in order to be able to use the kernel.

If you don’t have it already installed, you can download the binaries from here. You also need JupyterLab or Notebook

If your MariaDB binaries are not in PATH, see the Configuring section to see how you can point the kernel to the right binaries path.

Quick Installation Steps

Follow these steps if you already have a clean Python environment and JupyterLab installed on your computer.

  1. Install the kernel

python3 -m pip install mariadb_kernel
  1. Install the kernelspec so that the kernel becomes visible to JupyterLab

python3 -m mariadb_kernel.install

Complete Installation Steps

This guide helps you set up a fresh Miniconda environment where you can install the kernel and the Jupyter applications without interfering with your normal environment.

  1. Download and install miniconda

# After you downloaded the script run:
sh ./Miniconda3-latest-Linux-x86_64.sh
  1. Create a new environment

conda create -n maria_env python=3.7
  1. Activate the new env

# You should see the terminal prompt prefixed with (maria_env)
conda activate maria_env
  1. Install JupyterLab

conda install -c conda-forge jupyterlab
  1. Install the kernel

python3 -m pip install mariadb_kernel
  1. Install the kernelspec so that the kernel becomes visible to JupyterLab

python3 -m mariadb_kernel.install

Platform Coverage

Our plan is to make mariadb_kernel as platform independent as Jupyter is. That means, fully functional on at least Linux, macOS and Windows.

Unfortunately at the moment, we can only guarantee it should work fine on POSIX-compliant systems, i.e. most Linux distributions and macOS as long as the prerequisites of the project are present on your system. This limitation is inherited from particular Python APIs that use POSIX-specific system calls and also from our dependency package Pexpect which has certain limitations on Windows systems.

Please create an issue here or vote (just write a quick comment saying that issue affects you as well) for an existing one if you’re on a particular platform that mariadb_kernel doesn’t currently work on. This will help us understand how we should focus our development efforts.

The MariaDB Jupyter Kernel - Main Components and Architecture

Architecture

The mariadb_kernel project is made out of several components. These components were created to perform various functions such as: interfacing with the Jupyter protocol API, parsing the input texts that the user writes in the notebook cells, magic commands execution or even just abstracting away certain interactions that we suspect might change in the future.

Here’s a diagram displaying the relationship between all the main components of the kernel.

architecture

When you start a notebook in JupyterLab, Jupyter spawns an instance of MariaDBKernel. The kernel then creates a ClientConfig object to read mariadb_config.json. If the kernel detects based on the configuration settings that a MariaDB server is up and running, it creates a MariaDBClient object that is responsible for talking to the server. If no server is detected, based on the config options, the kernel might start a MariaDB Server instance for you. This server instance is abstracted via the MariaDBServer type.

Now the startup stage is done, the kernel is idle and waiting for the user to start writing code and executing the Notebook cells.

For each notebook that the user creates, Jupyter spawns a new instance of the MariaDB kernel.

When the user executes a code cell, MariaDBKernel receives a message from Jupyter. This message contains, along with other options, the code that the user wrote in the cell. The kernel then creates a CodeParser object and it gets from the parser the SQL statements found in the code text and a list of magic objects that the user tries to execute.

The kernel uses MariaDBClient to execute the SQL statements, get the results and display back in the notebook the output and then it executes the magic commands in the order they were found in the code.

The CodeParser object basically parses the code and puts all the SQL statements in a list. When the parser encounters a magic command, it uses a MagicFactory object to create the right magic object based on the name of the magic, the arguments passed to that magic and the current state saved in the kernel that can potentially be used by the magic when it is executed.

Now that you have the bottom-up picture of how the components of the kernel interact with each other internally, here's a diagram showing how the kernel fits within the entire picture:

jupyter_interaction

Components

MariaDBKernel

This class inherits from the ipykernel.kernelbase.Kernel type which implements the Jupyter client protocol and all the machinery that makes things move in the big picture.

ClientConfig

ClientConfig stores the default configuration of the kernel and it parses and loads the options passed by the user in mariadb_config.json, if one is found. ClientConfig looks for mariadb_config.json in ~/.jupyter/ by default or in the path provided by the user in the JUPYTER_CONFIG_DIR environment variable.

MariaDBKernel and other kernel components use this class for getting the arguments to pass to the MariaDB client or to alter some internal behaviors of the kernel.

MariaDBClient

This class is an abstraction of the MariaDB command line client. MariaDBClient is capable of controlling a sub-process running the mariadb client binary and it does so using the Pexpect package.

The kernel uses this class to start/stop MariaDB clients or to run SQL statements written by the users in notebooks. Basically any communication with the MariaDB database itself is handled via this class.

MariaDBServer

This component abstracts away a MariaDB Server instance. It provides mechanisms for starting or stopping a server instance and for synchronizing the kernel with a server instance that is either ready to receive client connections or is safely stopped.

This class is used by the kernel when the kernel is loaded and it doesn’t detect a running instance of MariaDB Server based on the default or custom configuration. In this case, depending on whether the user chose this behavior, the kernel will start a server to be used for quick testing scenarios when the user wants to rapidly try some statements in a notebook.

CodeParser

This class parses the code of one notebook cell and derives two types of data from it: a list of strings containing the SQL statements found in the code and a list of magic objects created based on the names and arguments of the magic commands written by the user in that cell.

CodeParser uses the MagicFactory component to create the right magic objects, be it line magics, cell magics, %line, %lsmagic, etc.

MagicFactory

This class creates the right magic objects based on the name and arguments of a magic command and a name:type mapping stored internally. This class also creates objects of type ErrorMagic when the user writes magic commands that are not supported by the kernel. The execution of such ErrorMagic objects in the kernel results in an error being displayed in the notebook.

MariaMagic

The parent class of all the magic commands. It is an interface that defines the API all magic commands should implement to be compatible with the kernel.

CellMagic

This class inherits from MariaMagic. It is an abstract class that is supposed to provide basic functionalities to all magic commands of type cell.

A cell magic is a shortcut a user can write into a notebook cell. The cell magic is written under the form %%magic and in principle it operates over the entire code of the cell it is written in, e.g:

------cell
%%magic_python
from matplotlib import pyplot
x = [1,2,3]
print(x)
pyplot.plot(x)
------end of cell

Delimiter

This component implements the %%delimiter cell magic command.

Its purpose is to run an SQL statement using a different delimiter than the default ";". The main usecase is stored procedures and stored functions, e.g:

--------cell
%%delimiter //
CREATE PROCEDURE proc ()
 BEGIN
  select 1;
 END;
//
--------end-of-cell

Note that the SQL statement needs to end with the delimiter specified by the magic command.

LineMagic

This class inherits from MariaMagic. It is an abstract class that is supposed to provide basic functionality to all magic commands of type line.

A line magic is a shortcut a user can write into a notebook cell. The line magic is written under the form %magic and in principle, it only sees what is passed as arguments by the user, e.g. %magic arg1 arg2

LSMagic

This magic command prints the list of magics supported by the kernel along with a help text guiding the user on how to use them.

Line

The whole purpose of this magic command is to allow the user to display the result of the last query (e.g. SELECT, SHOW,...) in a nice and simplematplotlib plot.

Internally, the Line class receives the data of the last query from the kernel as a Pandas DataFrame, it generates a plot PNG image, wraps the image into a nice display_data Jupyter message and then sends it further.

DF

The %df magic writes the result of the last query executed in the notebook into an external CSV formatted file.

The purpose of this magic command is to allow users to export query data from their MariaDB databases and then quickly import it into a Python Notebook where more complex analytics can be performed.

If no arguments are specified, the kernel writes the data into aCSV file named last_query.csv.

Bar

The purpose of this magic command is to allow the user to display the result of the last query (e.g. SELECT, SHOW,...) in a nice and simplematplotlib plot.

Internally, the Bar class receives the data of the last query from the kernel as a Pandas DataFrame, it generates a plot PNG image, wraps the image into a nice display_data Jupyter message and then sends it further.

Pie

The purpose of this magic command is to allow the user to display the result of the last query (e.g. SELECT, SHOW,...) in a nice and simplematplotlib plot.

Internally, the Pie class receives the data of the last query from the kernel as a Pandas DataFrame, it generates a plot PNG image, wraps the image into a nice display_data Jupyter message and then sends it further.

Using the MariaDB Jupyter Kernel

General usage information, available features, available magic commands

General MariaDB Jupyter Kernel Usage Information

If you installed the kernel, installed its kernelspec and you have MariaDB installed on your system, you just need to open JupyterLab, and when you create a new notebook, pick MariaDB as your kernel.

For some sample notebooks, check out the notebooks directory in our GitHub repository page. Those should guide through what you can do with mariadb_kernel.

You can also try mariadb_kernel using the amazing MyBinder platform by clicking this badge

or you can click on the Try MariaDB @ binder badge from the mariadb_kernel GitHub repo.

This takes absolutely no effort except one click; Binder does everything for you! It sets up a MariaDB server and all the installation of the kernel and its requirements. Once this process is done, you will be redirected to a live notebook where you can type your commands.

Be aware that MyBinder does not save any state, whatever you change in a notebook in a session (unless you save the notebook locally yourself) will be lost once you close the page. We recommend using the MyBinder platform to try the MariaDB kernel when you quickly want to try a statement, if you'd like to see the latest features of the kernel in action or if you want to reproduce some results that a colleague has sent you. For all other use-cases we recommend you install the kernel and edit your notebooks locally.

Please be a bit patient when you launch MyBinder, the more people are clicking the MyBinder link, the higher the chances are that when you launch it, the Docker image of the kernel is already cached. If by any chance you see a Docker image building in the MyBinder logs, it shouldn't take more than a minute. Please be patient, if you wait one minute, it means you're helping all the other people launching the kernel after you to wait less! :-)

MariaDB Jupyter Kernel Magic Commands

Below you'll find a table with all the available magic commands supported by the kernel. For more help information regarding the usage and parameters of each magic command, please run the %lsmagic command.

Magic
Type
Description

lsmagic

line

lists all available magic and help text for each

line

line

line-plots the result of the last query

df

line

write the result of the last query in a CSV file

bar

line

bar-plots the result of the last query

pie

line

pie-plots the result of the last query

load

line

loads the data from a CSV file into an existing table

delimiter

cell

run a SQL statement using a custom delimiter

MariaDB Jupyter Kernel Restrictions and Limitations

Restrictions and Limitations

Here's a list of the current restrictions and limitations of the MariaDB Jupyter kernel. Trying to stretch what we listed here is at your own risk, the results and the behavior of the kernel might be at the mercy of the universe :-)

  • One cell should only contain a single SQL statement. It can be multi-line, a one liner, it doesn't matter, but it needs to only be a single statement.

  • A notebook cell can only contain one line magic command (line magics start with %). Everything else within the same cell below the line magic command will be ignored.

  • Magic commands and SQL statements cannot be mixed within the same notebook cell.

  • The kernel does not officially support the DELIMITER (MariaDB specific) client command. It might work, but it shouldn't be used. Rather use the %%delimiter cell magic command (see %lsmagic for usage information).

  • Each SQL statement within a notebook cell should end with the ; MariaDB default delimiter, except in cells using the %%delimiter cell magic command where it should end with the user-specified delimiter.

SQL Autocompletion and Introspection

These features are the result of Xing-Zhi Jiang's work during Google Summer of Code 2021 and they are being fine-tuned and improved constantly to get a decent code completion and introspection experience in the MariaDB Jupyter Kernel.

Autocompletion

The MariaDB Jupyter kernel is able to provide SQL autocompletion with a basic internal understanding of the context within your statements. We are trying to make the suggestions as precise as possible, but any SQL autocompletion is imperfect unless you either duplicate the entire grammar logic of MariaDB Server or the MariaDB Server itself develops mechanisms for external tools to poke into its parsing logic.

Autocompletion in Jupyter can be triggered with the key TAB. In the classic Juptyter Notebook interface, it's possible to install some extensions to enable continuous hinting, but at the time this article is written there is no mechanism to enable continuous hinting in the new JupyterLab interface.

Here's a summary of our autocompletion capabilities, but we strongly recommend you play with the feature yourself and discover what it can do.

SQL keywords and functions completion

Completion of database names in USE statements

Completion of database names in constructs like database_to_autocomplete.table_name

Completion of table names in constructs like db.table_name_to_autocomplete

Completion of column names in the WHERE clause

Completion of column names in SELECT queries

Completion of column names in INSERT statements

Resolving aliases and completion of column names in constructs like alias.column_to_autocomplete

Completion of SHOW statments

Completion of user accounts

Completion of global and session variables

Introspection

Code introspection in Jupyter can be triggered with the SHIFT-TAB combination. This feature was designed to help you understand your database environment faster whilst typing SQL statements, for instance checking the table schema by inspecting on the table name before selecting a bunch of columns, or even checking the documentation of a SQL function to see the function signature and some practical examples and spare you an extra search on MariaDB Documentation.

Although we tried to make introspection look exactly the same in both classic Jupyter Notebook and Jupyterlab interfaces, it wasn't possible due to some fundamental difference in how Notebook renders the introspection tooltip in comparison to the newer JupyterLab interface. For the moment, to see the full introspection information in Notebook that the MariaDB kernel sends to the frontend, you'll need to hit shift-tab then click on the expand button from the tooltip to get the HTML representation of the introspection information.

Here's a visual summary of our currently supported introspection capabilities.

Database introspection

Table schema and data summary

Column datatype and sample data

SQL function documentation and examples

User accounts introspection