How to use pyexasol - 10 common examples

"""
Example 23
Snapshot transactions

Explanations about locks: https://www.exasol.com/support/browse/SOL-214
"""

import pyexasol
import _config as config

import pprint
printer = pprint.PrettyPrinter(indent=4, width=140)

# First connection, read first table, update second table
C1 = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema)
C1.set_autocommit(False)

C1.execute("SELECT * FROM TAB1")
C1.execute("INSERT INTO TAB2 VALUES (1)")

# Second connection, update first table
C2 = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema)
C2.set_autocommit(False)

C2.execute("INSERT INTO TAB1 VALUES(1)")
C2.commit()

# Third connection, read second table
C3 = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema,
                      snapshot_transactions=True, debug=True)

for part in reversed(dsn.split(',')):
            if len(part) == 0:
                continue

            m = dsn_re.search(part)

            if not m:
                raise ExaConnectionDsnError(self, f'Could not parse connection string part [{part}]')

            # Optional port was specified
            if m.group('port'):
                current_port = int(m.group('port'))

            # If current port is still empty, use default port
            if current_port is None:
                current_port = constant.DEFAULT_PORT

            # Hostname or IP range was specified, expand it
            if m.group('range_start'):
                if int(m.group('range_start')) > int(m.group('range_end')):
                    raise ExaConnectionDsnError(self,
                                                f'Connection string part [{part}] contains an invalid range, '
                                                f'lower bound is higher than upper bound')

                zfill_width = len(m.group('range_start'))

                for i in range(int(m.group('range_start')), int(m.group('range_end')) + 1):
                    host = f"{m.group('host_prefix')}{str(i).zfill(zfill_width)}{m.group('host_suffix')}"
                    result.extend(self._resolve_host(host, current_port))
            # Just a single hostname or single IP address
            else:
                result.extend(self._resolve_host(m.group('host_prefix'), current_port))

def _get_connection(self):
        exahost = "%s:%s" % (
            self.configuration.get("host", None),
            self.configuration.get("port", 8563),
        )
        return pyexasol.connect(
            dsn=exahost,
            user=self.configuration.get("user", None),
            password=self.configuration.get("password", None),
            compression=True,
            json_lib="rapidjson",
            fetch_mapper=_exasol_type_mapper,
        )

import pyexasol
import time
import _config as config

check_timeout = 180
bucket_fs_extra_timeout = 30
start_ts = time.time()

while True:
    try:
        C = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password)
        print(f"Exasol was started in {time.time() - start_ts}s")
        print(f"Sleeping extra {bucket_fs_extra_timeout}s to allow BucketFS to startup")

        time.sleep(bucket_fs_extra_timeout)
        break
    except pyexasol.ExaConnectionError:
        if (time.time() - start_ts) > check_timeout:
            raise RuntimeError(f"Exasol did not start in {check_timeout}s, aborting test")

        time.sleep(2)

if self.shard_id == 2:
            dbg = True
        else:
            dbg = False

        C = E.connect(dsn=self.dsn, user=config['user'], password=config['password']
                      , subc_id=self.shard_id, subc_token=self.token, debug=dbg)

        st = C.subc_open_handle(self.handle_id)

        print(f"Shard {self.shard_id}, rows: {st.rowcount()}")
        st.fetchall()
        st.close()


C = E.connect(dsn=config['dsn'], user=config['user'], password=config['password'], schema=config['schema'], debug=True)

token, nodes = C.enter_parallel(config['num_parallel'])
st = C.execute("SELECT * FROM {table!i} LIMIT 100000", {'table': config['table_name']})

pool = list()
i = 0

for node in nodes:
    i += 1

    proc = SelectProc(i, node, token, st.result_set_handle)
    pool.append(proc)
    proc.start()

for proc in pool:
    proc.join()

"""
Example 15
Connection with SSL encryption enabled
It works both for WebSocket communication (wss://) and HTTP(S) Transport
"""

import pyexasol
import _config as config

import pprint
printer = pprint.PrettyPrinter(indent=4, width=140)

# Connect with encryption
C = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema, encryption=True)

# Basic query
stmt = C.execute("SELECT * FROM users ORDER BY user_id LIMIT 5")
printer.pprint(stmt.fetchall())

# Export to list
users = C.export_to_list("SELECT * FROM users ORDER BY user_id LIMIT 5")
stmt = C.last_statement()
print(f'EXPORTED {stmt.rowcount()} rows in {stmt.execution_time}s')

print(users[0])
print(users[1])

# Import from list
C.import_from_iterable(users, 'users_copy')
stmt = C.last_statement()

"""
Example 1
Open connection, run simple query, close connection
"""

import pyexasol
import _config as config

import pprint
printer = pprint.PrettyPrinter(indent=4, width=140)

# Basic connect
C = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema)

# Basic query
stmt = C.execute("SELECT * FROM users ORDER BY user_id LIMIT 5")
printer.pprint(stmt.fetchall())

# Disconnect
C.close()

"""
Example 24
Script output server

Exasol should be able to open connection to the host where current script is running
"""

import pyexasol
import _config as config

import pprint
printer = pprint.PrettyPrinter(indent=4, width=140)

C = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema,
                     query_timeout=5)

stmt, log_files = C.execute_udf_output("""
    SELECT echo_java(user_id)
    FROM users
    GROUP BY CEIL(RANDOM() * 4)
""")

printer.pprint(stmt.fetchall())
printer.pprint(log_files)

print(log_files[0].read_text())

"""
Example 6
Export and import from Exasol to Pandas DataFrames

Please make sure you enable compression for office wifi!
"""

import pyexasol
import _config as config

# Connect with compression enabled
C = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema,
                     compression=True)

C.execute('TRUNCATE TABLE users_copy')

# Export from Exasol table into pandas.DataFrame
pd = C.export_to_pandas('users')
pd.info()

stmt = C.last_statement()
print(f'EXPORTED {stmt.rowcount()} rows in {stmt.execution_time}s')

# Import from pandas DataFrame into Exasol table
C.import_from_pandas(pd, 'users_copy')

stmt = C.last_statement()
print(f'IMPORTED {stmt.rowcount()} rows in {stmt.execution_time}s')

"""
Example 2
Fetching data as tuples
"""

import pyexasol
import _config as config

import pprint
printer = pprint.PrettyPrinter(indent=4, width=140)

# Basic connect (default mapper)
C = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema)

# Fetch tuples row-by-row as iterator
stmt = C.execute("SELECT * FROM users ORDER BY user_id LIMIT 5")

for row in stmt:
    printer.pprint(row)

# Fetch tuples row-by-row with fetchone
stmt = C.execute("SELECT * FROM users ORDER BY user_id LIMIT 5")

while True:
    row = stmt.fetchone()

    if row is None:
        break