Run #Python directly in the database

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 

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:

select find_books('discworld');

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 🙂

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: