Fully managed cloud. 30-day FREE full featured trial. Start Now
Showing results for 
Search instead for 
Did you mean: 

virtual schema - python

Rising Star

I found the OpenWeather python/virtual schema sample and it is excellent:


Is there another sample showing local database access?   

With the java libraries introspection and interfaces make development more straight-forward.



Exasol Alumni

Hi @drumcircle
Let me check internally if someone can answer your question. Though, It's rather late here in Germany, so don't get your hopes up to get an answer today. 

With all of your virtual schema questions, we might need a virtual schema tag soon. (not a criticism, keep it up!)

Exasol Alumni

Hello @drumcircle ,
What do you mean exactly when you say `local database access`?
Our officially supported Virtual Schema's dialects can connect to a local database via JDBC connection.
Exasol dialect also has a special IS_LOCAL parameter for a local connection.

We have one more python Virtual Schema sample for a Redis database: https://github.com/exasol/redis-virtual-schema
All other adapters are production code, they are written in Java. For example, Exasol Virtual Schema dialect: https://github.com/exasol/exasol-virtual-schema 
And here you can find the documentation about all dialects: https://github.com/exasol/virtual-schemas/blob/master/doc/user-guide/dialects.md

Please let me know if you still have any questions about Virtual Schema. 🙂 


Hi @drumcircle 

Not sure if it's what you are looking for but if you mean local access from a Python virtual schema it would look something like this: 

    import sys
    import glob
    # Your bucketfs path below might be different

    # EXASOL is the default basic Python Exasol client implementation from
    # https://github.com/exasol/websocket-api/tree/master/python
    # You may instead want to use the more powerful client at
    # https://github.com/exasol/pyexasol by importing it into your bucketfs
    import EXASOL

    # Create a connection object in Exasol having the local access credentials and
    # then pass in that connection's name as a virtual schema property
    connection_name = vsrequest['schemaMetadataInfo']['properties']['CONNECTION_NAME']

    # exa.get_connection is a global function available to all python scripts
    c = exa.get_connection(connection_name)
    with EXASOL.connect('ws://'+c.address, c.user, c.password) as connection:
        with connection.cursor() as cursor:
            cursor.execute('SELECT col FROM localtable')
            res = []
            for row in cursor:

Accessing the local DB from within the "createVirtualSchema" or "refresh" phases of a virtual schema can only be done via a new connection. There's unfortunately no way to access the connection that is currently running the createVirtualSchema/refresh command.

Hope that helps.


Rising Star

Peter this is very helpful, I'm so appreciative of your participation and support.

Rising Star

I'm expecting shortcuts, as in the java vs context, for handling connections to the local host where the VS script is running.

The REDIS adapter example is most helpful as well.  Thank you Anastasia!

I hear there are LUA things under development and expect that there will be advantages to using LUA since it is running in the database itself... anxious to check that out as well.

Rising Star

I'm having trouble with my SQL editor mangling the python code during execution. 

The OpenWeather example shows getting the script from GitHub at run-time which is problematic unless it were cached (yes?).

Is there a way to stage the adapter script on BucketFs like we do for java, such as this?

%python /buckets/bfsdefault/python/our_bank_adapter.py;

Rising Star

Note for friends...

If your python code contains ":<something" without a space after the ":" character, the code gets mangled when you save it.

if name == "BOOLEAN" or name == "DATE" or name == "TIMESTAMP" or name == "DOUBLE":
return {"type": ‌‌name}

So put a space after the colon and life is much better.