SQL (Structured Query Language) is one of the oldest and most popular languages for database access. BeakerX has first-class support for SQL, including syntax highlighting, autocompletion, and autotranslation (coming soon). Most of the languages in BeakerX have their own SQL APIs, and you can use them as well.
In order to be completely self-contained, this demo notebook uses an in-memory H2 database that's created and populated here before we query it.
Normally you would connect to an existing database by putting its JDBC configuration
in the %defaultDatasource
magic. You can access more than one database from the same notebook with the %datasources
magic.
For example, if you want to connect to a MySQL running locally then use
jdbc:mysql://localhost/dbName
, where dbName would be replaced as appropriate.
The BeakerX SQL kernel comes with drivers for H2, PostgreSQL, MySQL, Derby, HyperSQL, SQLite, Microsoft SQL Server, and Amazon Redshift built in.
To access another database, you can load the JDBC driver with the %classpath add jar
magic first.
%defaultDatasource jdbc:h2:mem:db1
drop table if exists color;
CREATE TABLE color (
id int(11) NOT NULL,
name varchar(45) NOT NULL,
code varchar(10),
PRIMARY KEY (id)
);
INSERT INTO color (id, name, code) VALUES (1001,'AliceBlue','#F0F8FF');
INSERT INTO color (id, name, code) VALUES (1002,'AntiqueWhite','#FAEBD7');
INSERT INTO color (id, name, code) VALUES (1003,'Aqua','#00FFFF');
INSERT INTO color (id, name, code) VALUES (1004,'Aquamarine','#7FFFD4');
INSERT INTO color (id, name, code) VALUES (1005,'Azure','#F0FFFF');
INSERT INTO color (id, name, code) VALUES (1006,'Beige','#F5F5DC');
INSERT INTO color (id, name, code) VALUES (1007,'Bisque','#FFE4C4');
INSERT INTO color (id, name, code) VALUES (1008,'Black','#000000');
SELECT * FROM color WHERE name LIKE 'A%';
SELECT * into ${colorTable} from color;
%%javascript
var tableStart = "<table border>"
var tableTemplate = (id, value) => `<tr><td>${id}</td><td>${value}</td></tr>`
var tableFinish = "</table>"
beakerx.displayHTML(this, tableStart +
beakerx.colorTable.values.reduce((tmp, item) => tmp + tableTemplate(...item), "") +
tableFinish)
s
SELECT * FROM color WHE
INSERT INTO color (id, name, code) VA