Search

Top 60 Oracle Blogs

Recent comments

Run #Python directly in the database

https://uhesse.files.wordpress.com/2020/10/pythonsql.png?w=150 150w, https://uhesse.files.wordpress.com/2020/10/pythonsql.png?w=300 300w, https://uhesse.files.wordpress.com/2020/10/pythonsql.png?w=768 768w" sizes="(max-width: 801px) 100vw, 801px" />

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:

select find_books('discworld');

https://uhesse.files.wordpress.com/2020/10/terry1.png?w=88 88w, https://uhesse.files.wordpress.com/2020/10/terry1.png?w=176 176w, https://uhesse.files.wordpress.com/2020/10/terry1.png 654w" sizes="(max-width: 602px) 100vw, 602px" />

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;

https://uhesse.files.wordpress.com/2020/10/terry2.png?w=111 111w, https://uhesse.files.wordpress.com/2020/10/terry2.png?w=221 221w, https://uhesse.files.wordpress.com/2020/10/terry2.png?w=768 768w, https://uhesse.files.wordpress.com/2020/10/terry2.png 821w" sizes="(max-width: 755px) 100vw, 755px" />

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 </p />
</p></div>

    	  	<div class=