With Python integrated to the database, you can utilize the power of SQL together with one of the most popular programming languages. And you run the program where the data is: Inside the database. Instead of having to bring the data to the program.
The Exasol database integrates the popular programming languages Python, Java and R as UDF scripting languages, together with the less known but powerful and elegant programming language Lua.
Let’s look at an example how Python can be used in Exasol, together with SQL:
--/ create or replace python3 scalar script find_books(keyword varchar(2000000)) emits (title varchar(2000000)) as import urllib.request import urllib.parse import json import ssl
ssl._create_default_https_context = ssl._create_unverified_context def run(ctx): with urllib.request.urlopen('https://www.googleapis.com/books/v1/volumes?maxResults=40&q='+ urllib.parse.quote_plus(ctx.keyword)) as url: s = url.read() data = json.loads(s) for item in data["items"]: ctx.emit(item["volumeInfo"]["title"]) /
We’re using the free google api here to access book titles. That script is then called like this:
How about sorting that list after length of title? That’s something SQL can do very well:
select length(title), title from (select find_books('discworld')) order by 1;
This should give you an idea about the endless and powerful options you have by combining Python with SQL, both integrated in the database.
By the way: We’re happy to educate you on this for free with our online learning course Exasol Advanced Analytics 🙂