def create_connection(username, password, database, database_schema=None):
'''
Given the parameters returns a connection to an exasol database
Parameters
----------
username: str
password: str
database: str
database_schema: str
Returns
-------
Returns a connection to an exasol database
'''
if database_schema is not None:
return pyexasol.connect(dsn=database,
user=username,
password=password,
schema=database_schema,
compression=True)
else:
return pyexasol.connect(dsn=database,
user=username,
password=password,
compression=True)
def run(self):
C = pyexasol.connect(dsn=args.dsn,
user=args.user,
password=args.password,
schema=args.schema,
autocommit=False)
self.queue.put(C.session_id())
sql_comment = f'/* bench_run_id={self.proc_num} */'
try:
C.execute(f"ALTER SESSION SET QUERY_CACHE='OFF' {sql_comment}")
for sql_query in sql_text.split(';'):
formatted_sql_query = sql_query.strip("\n ")
if len(formatted_sql_query) == 0:
continue
C.execute(f"{formatted_sql_query} {sql_comment}")
C.execute(f"COMMIT {sql_comment}")
except pyexasol.ExaQueryError as e:
self.queue.put(str(e))
finally:
C.close()
def wait_for_connection():
while True:
try:
return pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, query_timeout=QUERY_TIMEOUT)
except pyexasol.ExaConnectionFailedError as e:
print(e.message)
time.sleep(SLEEP_TIMEOUT)
def write_exasol(query, df, schema, tablename):
C = pyexasol.connect(dsn='90.92.980.290..634',
user=user_name,
password=pwd)
C.execute(query)
C.import_from_pandas(df, (schema, tablename))
print('Done. U should now find the table in Exasol', "\n")
def run(
self,
target_schema: str = None,
target_table: str = None,
data: Iterable = None,
import_params: dict = None,
autocommit: bool = False,
commit: bool = True,
**kwargs,
):
"""
Task run method. Executes a query against Postgres database.
Args:
- target_schema (str, optional): target schema for importing data
- target_table (str, optional): target table for importing data
- data (Iterable, optional): an iterable which holds the import data
- import_params (dict, optional): custom parameters for IMPORT query
- autocommit (bool, optional): turn autocommit on or off (default: False)
- commit (bool, optional): set to True to commit transaction, defaults to false
(only necessary if autocommit = False)
- **kwargs (dict, optional): additional connection parameter (connection_timeout...)
Returns:
- Nothing
Raises:
- ValueError: if query parameter is None or a blank string
- Exa*Error: multiple exceptions raised from the underlying pyexasol package
(e.g. ExaQueryError, ExaAuthError..)
"""
if not data or len(data) == 0:
raise ValueError("Import Data must be provided.")
if not target_table:
raise ValueError("Target table must be provided.")
if not target_schema:
target = target_table
else:
target = (target_schema, target_table)
con = pyexasol.connect(
dsn=self.dsn,
user=self.user,
password=self.password,
autocommit=autocommit,
**kwargs,
)
# try to execute query
# context manager automatically rolls back failed transactions
with con as db:
db.import_from_iterable(data, target, import_params)
if not autocommit:
if commit:
con.commit()
else:
con.rollback()
return
def connect(self):
""" Attempt to connect to exasol."""
schema = self.config.schema if ("schema" in self.config) else None
params = {"schema": schema, "compression": True}
params.update(self.config.params)
try:
self.connection = pyexasol.connect(
dsn=self.dsn,
user=self.config.user,
password=self.config.password,
fetch_dict=True,
fetch_mapper=pyexasol.exasol_mapper,
**params,
)
except pyexasol.exceptions.ExaCommunicationError:
log.error(
f"Could not connect to Exasol: Bad dsn [dsn={self.dsn}, user={self.config.user}]"
)
raise
except pyexasol.exceptions.ExaRequestError:
log.error(
f"Could not connect to Exasol: Wrong user or password [dsn={self.dsn}, user={self.config.user}]"
)
raise
return self.connection
def run(self):
C = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema)
print(f'START ID: {self.id:03} S: {C.session_id()} SLEEP: {self.sleep_interval:02}, T:{C.login_time} A:{C.ws_req_time}', flush=True)
C.execute('SELECT sleep_java({sleep_interval!d})', {'sleep_interval': self.sleep_interval})
print(f'STOP ID: {self.id:03} S: {C.session_id()} SLEEP: {self.sleep_interval:02}', flush=True)
C.close()
def run(
self,
user: str,
password: str,
dsn: str = "",
query: str = None,
query_params: dict = None,
autocommit: bool = False,
commit: bool = True,
**kwargs,
):
"""
Task run method. Executes a query against Exasol database.
Args:
- user (str): user name used to authenticate
- password (str): password used to authenticate; should be provided from a `Secret` task
- dsn (str, optional): dsn string of the database (server:port)
- query (str, optional): query to execute against database
- query_params (dict, optional): Values for SQL query placeholders
- autocommit (bool, optional): turn autocommit on or off (default: False)
- commit (bool, optional): set to True to commit transaction, defaults to True
(only necessary if autocommit = False)
- **kwargs (dict, optional): additional connection parameter (connection_timeout...)
Returns:
- ExaStatement object
Raises:
- ValueError: if dsn string is not provided
- ValueError: if query parameter is None or a blank string
- Exa*Error: multiple exceptions raised from the underlying pyexasol package
(e.g. ExaQueryError, ExaAuthError..)
"""
if not dsn:
raise ValueError("A dsn string must be provided.")
if not query:
raise ValueError("A query string must be provided.")
con = pyexasol.connect(
dsn=dsn,
user=user,
password=password,
autocommit=autocommit,
**kwargs,
)
# try to execute query
# context manager automatically rolls back failed transactions
with con as db:
result = db.execute(query, query_params)
if not autocommit:
if commit:
con.commit()
else:
con.rollback()
return result
def setUpClass(cls):
cls.conn = pyexasol.connect(dsn=config.dsn,
user=config.user,
password=config.password,
autocommit=False,
fetch_dict=True,
lower_ident=True)
setup(cls.conn)
cls.schema_name = config.schema.upper()
def _connect(self):
import pyexasol
logger.info("Connecting to Exasol ...")
self.__exasol = pyexasol.connect(
dsn="{host}:{port}".format(host=self.host, port=self.port),
user=self.username,
password=self.password,
**self.engine_opts
)
def run(
self,
fetch: str = "one",
fetch_size: int = 10,
query: str = None,
query_params: dict = None,
**kwargs,
):
"""
Task run method. Executes a query against Exasol database and fetches results.
Args:
- fetch (str, optional): one of "one" "many" "val" or "all", used to determine how many
results to fetch from executed query
- fetch_size (int, optional): if fetch = 'many', determines the number of results
to fetch, defaults to 10
- query (str, optional): query to execute against database
- query_params (dict, optional): Values for SQL query placeholders
- **kwargs (dict, optional): additional connection parameter
(autocommit, connection_timeout...)
Returns:
- records (None, str, tuple, list of tuples, dict, or list of dicts):
records from provided query
Raises:
- ValueError: if query parameter is None or a blank string
- Exa*Error: multiple exceptions raised from the underlying pyexasol package
(e.g. ExaQueryError, ExaAuthError..)
"""
if not query:
raise ValueError("A query string must be provided.")
if fetch not in {"one", "many", "val", "all"}:
raise ValueError(
"The 'fetch' parameter must be one of the following - ('one', 'many', 'val', 'all')."
)
con = pyexasol.connect(
dsn=self.dsn,
user=self.user,
password=self.password,
**kwargs,
)
# try to execute query
# context manager automatically rolls back failed transactions
with con as db:
query = db.execute(query, query_params)
if fetch == "all":
return query.fetchall()
elif fetch == "many":
return query.fetchmany(fetch_size)
elif fetch == "val":
return query.fetchval()
else:
return query.fetchone()
def exec_statement(statement):
c = px.connect(dsn='192.168.56.101:8563', user='******', password='******')
try:
stmt = c.execute(statement)
for row in stmt:
print(row)
except px.ExaRuntimeError:
if 'Attempt to fetch from statement without result set' in str(
px.ExaRuntimeError):
pass
return True
def return_exa_conn(exa_user='******', exa_pwd='DWHEXA_PASSWORD', exa_dsn='DWHEXA_HOST'):
"""
:return: connection object
"""
logger = set_logging()
exasol_user = os.environ.get(exa_user)
exasol_pwd = os.environ.get(exa_pwd)
exasol_dsn = os.environ.get(exa_dsn)
conn = pyexasol.connect(user=exasol_user, password=exasol_pwd, dsn=exasol_dsn)
logger.info('Successfully connected to Exasol.')
return conn
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,
)
def read_input_data(username, password, database, this_monday):
query = ''' SELECT * FROM DS_PROJECTS.V_SKU_FCST_SEASONALITY_INPUT
WHERE BEGIN_OF_WEEK < '{}' '''.format(this_monday)
connection_prod = pyexasol.connect(dsn=database,
user=username,
password=password,
compression=True)
df = connection_prod.export_to_pandas(query)
df['BEGIN_OF_WEEK'] = pd.to_datetime(df['BEGIN_OF_WEEK'])
return df
def exa_connect(dsn, user, password):
try:
exa_conn = pyexasol.connect(dsn=dsn, user=user, password=password)
#TODO add right exceptions for Exasol
except:
print ("Can't connect to Exasol DB at", dsn, "Skiping...")
return None
return exa_conn
def wait_for_java():
while True:
try:
connection = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema, query_timeout=QUERY_TIMEOUT)
connection.execute(TEST_JAVA_UDF)
connection.execute("SELECT test_java(true)")
return
except pyexasol.ExaError as e:
print(e.message)
time.sleep(SLEEP_TIMEOUT)
def run(
self,
destination: Union[str, Path],
query_or_table: Union[str, tuple] = None,
query_params: dict = None,
export_params: dict = None,
**kwargs,
):
"""
Task run method. Executes a query against Postgres database.
Args:
- destination ([str, Path], optional): Path to file or file-like object
- query_or_table (str, optional): SQL query or table for export
could be:
1. SELECT * FROM S.T
2. tablename
3. (schemaname, tablename)
- query_params (dict, optional): Values for SQL query placeholders
- export_params (dict, optional): custom parameters for EXPORT query
- **kwargs (dict, optional): additional connection parameter (connection_timeout...)
Returns:
- Nothing
Raises:
- ValueError: if query parameter is None or a blank string
- Exa*Error: multiple exceptions raised from the underlying pyexasol package
(e.g. ExaQueryError, ExaAuthError..)
"""
if not destination:
raise ValueError("A destination must be provided.")
if not query_or_table:
raise ValueError("A query or a table must be provided.")
con = pyexasol.connect(
dsn=self.dsn,
user=self.user,
password=self.password,
**kwargs,
)
# try to execute query
# context manager automatically rolls back failed transactions
with con as db:
db.export_to_file(
destination,
query_or_table,
query_params,
export_params,
)
return
def get_table_cols(TABLE):
#--------------------------------------
try:
C = pyexasol.connect(dsn=(config.dsn + ":" + str(config.port)), user=config.user, password=config.password, schema=config.schema, compression=True)
print("INFO: Successfully connected to database using schema", config.schema)
logging.info("# " + my_pgm + " Successfully connected to database using schema " + config.schema)
except Exception as e:
print("Unable to connect using ", SYS, SYS_PW, "with schema:", SCHEMA)
logging.info("# " + my_pgm + "Unable to connect using " + SYS + " " + SYS_PW + " with schema: " + SCHEMA)
print(e)
logging.info(e)
sys.exit(-1)
try:
dict_cols = (C.meta.sql_columns("Select * from " + TABLE))
sort_cols = sorted(dict_cols)
print(sort_cols)
for col in sort_cols:
logging.info("# " + my_pgm + " returning " + col)
logging.info("#--------------------------------------#")
logging.info("# " + my_pgm + " successfully exiting and returning columns")
logging.info("#--------------------------------------#")
return sort_cols
except Exception as e:
print(os.path.basename(__file__), "unable to READ table", TABLE, "skipping with no action taken!")
print(e)
sys.exit(0)
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,
)
def query(request):
"""Helper to query Exasol to connect to and query Exasol"""
import pyexasol
conn = pyexasol.connect(dsn="localhost:%s" % DOCKER_PORT,
user="******",
password="******")
def _query(query_str):
result = conn.export_to_pandas(query_str)
print(result)
return result
return _query
def get_db():
log.info("Connecting to Exasol")
dsn = os.environ.get("HOST", "127.0.0.1:8888")
user = os.environ.get("USER", "sys")
db = pyexasol.connect(
dsn=dsn,
user=user,
password=os.environ.get("PASSWORD", "exasol"),
debug=False,
fetch_dict=True,
socket_timeout=30,
compression=True,
)
log.info("Connected successfully to %s, user %s", dsn, user)
return db
def run(self):
# Show full debug for only one sub-connection
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()
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=False,
schema=self.configuration.get("schema", None),
json_lib="rapidjson",
fetch_mapper=_exasol_type_mapper,
query_timeout=self.configuration.get("timeout", 180),
client_name=CLIENT_NAME)
def get_conn(self):
conn_id = getattr(self, self.conn_name_attr)
conn = self.get_connection(conn_id)
conn_args = dict(
dsn='%s:%s' % (conn.host, conn.port),
user=conn.login,
password=conn.password,
schema=self.schema or conn.schema)
# check for parameters in conn.extra
for arg_name, arg_val in conn.extra_dejson.items():
if arg_name in ['compression', 'encryption', 'json_lib', 'client_name']:
conn_args[arg_name] = arg_val
conn = pyexasol.connect(**conn_args)
return conn
def connect(self):
""" Attempt to connect to exasol."""
schema = self.config.schema if ("schema" in self.config) else None
params = {"schema": schema, "compression": True}
params.update(self.config.params)
try:
self.connection = pyexasol.connect(
dsn=self.dsn,
user=self.config.user,
password=self.config.password,
fetch_dict=True,
**params,
)
except pyexasol.exceptions.ExaError as err:
log.exception(err)
return self.connection
def init_exasol_test():
host = sys.argv[1]
printer = pprint.PrettyPrinter(indent=4, width=140)
# Basic connect
C = pyexasol.connect(dsn=host, user='******', password='******')
# Create schema
stmt = C.execute("CREATE SCHEMA IF NOT EXISTS test")
C.open_schema("test")
# Set up go
stmt = C.execute(
"ALTER SESSION SET SCRIPT_LANGUAGES = 'PYTHON=builtin_python R=builtin_r JAVA=builtin_java GO=localzmq+protobuf:///bfsdefault/default/go/GolangImage?#buckets/bfsdefault/default/go/src/exago.sh'"
)
return C
def connect(self, schema=None):
# revlibs.connections doesnt support schema = None.
# Ideally we'd want to fix revlibs and use them instead of this.
params = dict(compression=True)
if schema:
params["schema"] = schema
connection = pyexasol.connect(
dsn=environ.get("SHREQT_DSN", "localhost:8999"),
user=environ.get("SHREQT_USER", "sys"),
password=environ.get("SHREQT_PASS", "exasol"),
fetch_dict=True,
fetch_mapper=pyexasol.exasol_mapper,
**params,
)
try:
yield connection
finally:
connection.close()
"""
Example 1
Open connection, run simple query, close connection
"""
import pyexasol as E
import _config as config
import pprint
printer = pprint.PrettyPrinter(indent=4, width=140)
# Basic connect
C = E.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 7
Export and import from Exasol to objects
"""
import pyexasol
import _config as config
import tempfile
import shutil
import os
# Connect with compression enabled
C = pyexasol.connect(dsn=config.dsn,
user=config.user,
password=config.password,
schema=config.schema,
compression=True)
# Prepare empty tables
C.execute("TRUNCATE TABLE users_copy")
C.execute("TRUNCATE TABLE payments_copy")
# Create temporary file
file = tempfile.TemporaryFile()
# Export to temporary file
C.export_to_file(file, 'users', export_params={'with_column_names': True})
file.seek(0)
print(file.readline())
