virtual schema - python

SQL-Fighter

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

https://github.com/exasol/openweather-virtual-schema

Is there another sample showing local database access?   

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

 

5 REPLIES 5

Xpert

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
    sys.path.extend(glob.glob('/buckets/bfsdefault/default/ws/*'))

    # 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:
                res.append(row[0])

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.

Peter

SQL-Fighter

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

Team Exasol
Team Exasol

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. 🙂 

SQL-Fighter

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.

Community Manager
Community Manager

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!)