Usage¶
Shillelagh implements the Python DB API 2.0 specification and a custom SQLAlchemy dialect. It also comes with a simple command-line utility to run queries from the console.
DB API 2.0¶
The DB API 2.0 specification defines standard mechanisms to create database connections and to work with cursors. Here’s a simple example that creates a table, inserts some data, and queries it:
from shillelagh.backends.apsw.db import connect
connection = connect(":memory:")
cursor = connection.cursor()
query = "CREATE TABLE a_table (A int, B string)"
cursor.execute(query)
query = "INSERT INTO a_table VALUES (?, ?)"
cursor.execute(query, (1, "one"))
cursor.execute(query, (2, "two"))
query = "SELECT * FROM a_table"
for row in cursor.execute(query):
print(row)
You can use a file instead of :memory::
from shillelagh.backends.apsw.db import connect
connection = connect("/path/to/file.sqlite")
Note that using a file is not recommended for security reasons. Shillelagh works by creating virtual tables, and if a given resource requires credentials for access they will be stored in the table name.
Configuration¶
By default all available adapters are loaded by Shillelagh. It’s possible to limit the adapters that you want to load by passing a list of strings to the adapters argument when creating the connection:
from shillelagh.adapters.registry import registry
from shillelagh.backends.apsw.db import connect
# show names of available adapters
print(registry.loaders.keys())
# enable on the CSV and the WeatherAPI adapters
connection = connect(":memory:", adapters=["csvfile", "weatherapi"])
Some adapters allow optional configuration to be passed via keyword arguments. For example, we can specify an API key for WeatherAPI:
from shillelagh.backends.apsw.db import connect
connection = connect(":memory:", adapter_kwargs={"weatherapi": {"api_key": "XXX"}})
You can find the accepted arguments in the documentation of each one the Adapters.
When loading adapters, you can also specify the safe keyword. When set to true, this will ensure that only “safe” adapters are loaded — ie, adapters that have no access to the filesystem. The keyword also ensures that an exception is raised whenever there are repeated adapters with the same name, to prevent a malicious party from introducing an adapter with the same name as an authorized one:
from shillelagh.adapters.registry import registry
from shillelagh.adapters.file.csvfile import CSVFile
from shillelagh.backends.apsw.db import connect
registry.add('csvfile', CSVFile)
registry.add('csvfile', FakeAdapter)
connect(':memory:', adapters=['csvfile'], safe=True)
The code above will raise an exception saying “Multiple adapters found with name csvfile”. This is needed because adapters can be loaded from third-party libraries via entry points, and not just from the Shillelagh library.
Registering new adapters¶
Shillelagh uses a plugin registry similar to SQLAlchemy’s. Adapters that are registered via entry points are registered automatically, but you can manually register adapter classes:
from shillelagh.adapters.base import Adapter
from shillelagh.adapters.registry import registry
class CustomAdapter(Adapter):
...
# add an adapter class directly
registry.add('customadapter', CustomAdapter)
# add an adapter class by passing the module path and class name
registry.register('someotheradapter', 'path.to.module', 'ClassName')
SQLAlchemy¶
Shillelagh implements a SQLAlchemy dialect called shillelagh:
from sqlalchemy.engine import create_engine
engine = create_engine("shillelagh://")
connection = engine.connect()
query = "SELECT * FROM a_table"
for row in connection.execute(query):
print(row)
Configuration¶
The SQLAlchemy engine can be configured in the same way as the DB API 2.0 connect function, defining the adapters to be loaded, passing custom keyword arguments to the adapters, or loading only safe adapters. For example, if you want to connect only to Google Spreadsheets, using credentials from a service account:
from sqlalchemy.engine import create_engine
engine = create_engine(
"shillelagh://",
adapters=["gsheetsapi"],
adapter_kwargs={
"gsheetsapi": {
"service_account_file": "/path/to/credentials.json",
"subject": "user@example.com",
},
},
)
Alternatively, Shillelagh also comes with a custom Google Sheets dialect for SQLAlchemy. See Google Sheets for more details.
Command-line utility¶
Shillelagh comes with a simple command-line utility aptly named shillelagh:
$ shillelagh
sql> SELECT * FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0";
country cnt
--------- -----
BR 1
BR 3
IN 5
ZA 6
CR 10
sql>
The shillelagh application has very simple autocomplete, and runs the query on return.
Configuration¶
The command-line utility can be configured through a YAML file stored in ~/.config/shillelagh/shillelagh.yaml. The contents of the file correspond to the adapter_kwargs argument, eg:
gsheetsapi:
service_account_file: /path/to/credentials.json
subject: user@example.com
weatherapi:
api_key: XXX
Custom functions¶
Shillelagh exposes a few custom functions that can be called via SQL.
Sleep¶
The sleep function is useful to create long-running queries, in order to test time outs in your application.
sql> SELECT sleep(120);
This will return None after 2 minutes.
Retrieving metadata¶
The get_metadata function returns metadata about a special table as a JSON string, including which adapter handles it:
sql> SELECT GET_METADATA("https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0");
GET_METADATA("https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0")
--------------------------------------------------------------------------------------------------------------
{
"extra": {
"Spreadsheet title": "Shillelagh GSheets",
"Sheet title": "Simple sheet"
},
"adapter": "GSheetsAPI"
}
Finding out the version¶
Sometimes it’s useful to identify the version of Shillelagh that’s running on a system, in order to troubleshoot it. You can do that with version():
sql> SELECT VERSION();
VERSION()
-----------
1.0.0