def count(login, table, filename, max_rows):
error = None
try:
jdbc = lwetl.Jdbc(login)
except (lwetl.ServiceNotFoundException, lwetl.DriverNotFoundException, ConnectionError) as login_error:
print('ERROR: ' + str(login_error))
error = login_error
if error is not None:
return
cur = None
try:
cur = jdbc.execute("SELECT * FROM %s WHERE 0=1" % table)
columns = jdbc.get_columns()
except Exception:
columns = None
jdbc.close()
if columns is None:
print('ERROR: cannot find information on table: ' + table)
return 1
sql_base = 'SELECT {0}, COUNT(*) AS N FROM {1} WHERE {0} IS NOT NULL GROUP BY {0} HAVING COUNT(*) > 1 ORDER BY ' \
'COUNT(*) DESC,{0} '
sql_count = 'SELECT COUNT(*) AS N FROM {1} WHERE {0} IS NOT NULL'
with lwetl.XlsxFormatter(cursor=cur, filename_or_stream=filename) as xls:
sheet1 = xls.sheet
sheet1.append(['COLUMN NAME', 'DISTINCT', 'TOTAL', 'TOTAL NON DISTINCT'])
for column_name in columns.keys():
tot = jdbc.get_int(sql_count.format(column_name, table))
try:
cur = jdbc.execute(sql_base.format(column_name, table))
tds = 0
cnt = 0
new_table = True
for row in jdbc.get_data(cur):
cnt += 1
tds += int(row[1])
if (max_rows <= 0) or (cnt <= max_rows):
if new_table:
xls.next_sheet(cur, column_name)
xls.header()
new_table = False
xls.write(row)
print('Parsed: %-30s d = %6d, t = %6d, s = %6d' % (column_name, cnt, tot, tds))
except SQLExcecuteException:
cnt = None
tds = None
sheet1.append([column_name, cnt, tot, tds])
print('Done.')
return 0
def test_connection_noconnection(jdbc: lwetl.Jdbc):
"""
Test a connection to a known jdbc server, with an erroneous username.
"""
wrong_alias = 'xscott/tiger@scott_' + jdbc.type
print('\nTest login fail (connection failed) with: ' + wrong_alias)
error = None
try:
lwetl.Jdbc(wrong_alias)
except Exception as e:
print('******' + str(e) + '********')
error = e
if (error is not None) and (not isinstance(error, (ConnectionError, lwetl.ServiceNotFoundException))):
raise error
def main():
# if (len(sys.argv) > 1) and (sys.argv[1].lower() == '--version'):
# print('%s, version: %s' % (os.path.basename(sys.argv[0]), __version__))
# sys.exit(0)
# args = parser.parse_args()
# if args.version:
# print('%s, version: %s' % (os.path.basename(sys.argv[0]), __version__))
# sys.exit(0)
included_tables = []
# if args.tables is not None:
# for t in args.tables.split(','):
# t = t.strip().upper()
# if (len(t) > 0) and (t not in included_tables):
# included_tables.append(t)
# excluded_tables = []
# n_excluded = 0
# if args.exclude is not None:
# try:
# n_excluded = int(args.exclude)
# except ValueError:
# for t in args.exclude.split(','):
# t = t.strip().upper()
# if (len(t) > 0) and (t not in excluded_tables):
# excluded_tables.append(t)
jdbc = {SRC: args.login_source, TRG: args.login_target}
# information on table constraints and references
table_info = dict()
# information on the primary keys of tables
pk_info = dict()
for key in [SRC, TRG]:
login = jdbc[key]
try:
con = lwetl.Jdbc(login)
except (lwetl.ServiceNotFoundException, lwetl.DriverNotFoundException,
ConnectionError) as login_error:
print("ERROR for '%s': %s" % (jdbc[key], str(login_error)))
sys.exit(1)
jdbc[key] = con
tag_connection(key, con)
if con.type not in content_queries:
print("ERROR: database type '%s' not supported." % con.type)
sys.exit(1)
sql = content_queries[con.type]
if '@SCHEMA@' in sql:
sql = sql.replace('@SCHEMA@', con.schema)
print('Query %s database: %s' % (key.upper(), login))
try:
con.execute(sql)
except lwetl.SQLExcecuteException as exec_error:
print('ERROR: cannot retrieve database info for: ' + login)
print(exec_error)
sys.exit(1)
tables = dict()
pk_col = dict()
for d in con.get_data(return_type=OrderedDict):
table_name = d['TABLE_NAME'].upper()
if table_name not in tables:
tables[table_name] = dict()
if not is_empty(d.get('FK_TABLE', None)):
tables[table_name][d['COLUMN_NAME'].upper(
)] = d['FK_TABLE'].upper(), d['CONSTRAINT_NAME'].upper()
if d.get('KEY_TYPE', None) == 'PK':
pk_col[table_name] = d['COLUMN_NAME'].upper()
table_info[key] = tables
pk_info[key] = pk_col
table_admin = dict()
for t in [COMMON, EMPTY, IGNORED, MISSING, NO_SOURCE]:
table_admin[t] = []
table_count = dict()
for t in sorted(
[k for k in table_info[SRC].keys() if k in table_info[TRG]]):
sql = 'SELECT COUNT(*) FROM ' + t
n1 = jdbc[SRC].get_int(sql)
n2 = jdbc[TRG].get_int(sql)
table_count[t] = n1, n2
if (t not in excluded_tables) and \
((len(included_tables) == 0) or (t in included_tables)):
if n1 == 0:
table_admin[EMPTY].append(t)
else:
if (n2 == 0) or (args.mode != COPY_EMPTY):
table_admin[COMMON].append(t)
else:
table_admin[IGNORED].append(t)
else:
table_admin[IGNORED].append(t)
print_list('Tables to copy', table_admin[COMMON], table_count)
print_list('Tables ignored (not empty or marked)', table_admin[IGNORED],
table_count)
print_list('Empty source', table_admin[EMPTY], table_count)
missing_tables = sorted(
[k for k in table_info[SRC].keys() if k not in table_info[TRG]])
print_list('Tables not defined on target', missing_tables)
nosource_tables = sorted(
[k for k in table_info[TRG].keys() if k not in table_info[SRC]])
print_list('Missing source:', nosource_tables)
common_tables = table_admin[COMMON]
if len(common_tables) < 2:
copy_list = common_tables
else:
# re-order the list of tables, to avoid FK violations
copy_list = []
while len(copy_list) < len(common_tables):
not_added = [t for t in common_tables if t not in copy_list]
refered_tables = referring_tables(not_added, table_info[TRG],
copy_list)
while len(refered_tables) > 0:
not_added = [t for t in refered_tables]
refered_tables = referring_tables(not_added, table_info[TRG],
copy_list)
copy_list += not_added
if n_excluded > 0:
print_list('Skipped tables', copy_list[:n_excluded], table_count)
copy_list = copy_list[n_excluded:]
copy_list = [t for t in copy_list if t not in table_admin[IGNORED]]
print_list('Copy process will use the following order', copy_list,
table_count)
if args.list:
clean_exit(jdbc, args, 0)
elif len(copy_list) == 0:
print('No tables to copy found: exiting.')
clean_exit(jdbc, args, 0)
commit_mode = lwetl.UPLOAD_MODE_ROLLBACK
if args.activate:
print('Activating upload.')
commit_mode = lwetl.UPLOAD_MODE_COMMIT
if (args.mode in [COPY_AND_UPDATE, COPY_AND_SYNC]) and (args.driver
== DRIVER_MULTI):
print(
'WARNING: multi mode not supported for updates. Switching to single.'
)
args.mode = DRIVER_SINGLE
counters = {CNT_COPIED_TABLES: 0, CNT_FAIL: 0}
too_many_errors = False
is_update = args.mode in [COPY_AND_UPDATE, COPY_AND_SYNC]
for t in copy_list:
counters[CNT_COPIED_TABLES] += 1
n, n2 = table_count[t]
print("CC %3d. of %d: copy %-30s n = %6d values (PK = %s) ......." %
(counters[CNT_COPIED_TABLES], len(copy_list), t, n,
pk_info[SRC][t]))
existing_records = []
# target primary key
pk_trg = pk_info[TRG][t]
if (n2 > 0) and (args.mode != COPY_EMPTY):
for r in jdbc[TRG].query("SELECT {0} FROM {1} ORDER BY {0}".format(
pk_trg, t)):
existing_records.append(r[0])
print('Found %d existing records from %s to %s' %
(len(existing_records), min(existing_records),
max(existing_records)))
existing_records = set(existing_records)
try:
if args.reverse_insert or args.update_fast:
pk_order = 'DESC'
else:
pk_order = 'ASC'
cursor = jdbc[SRC].execute(
'SELECT * FROM {} ORDER BY {} {}'.format(
t, pk_info[SRC][t], pk_order),
cursor=None)
except lwetl.SQLExcecuteException as exec_error:
print('ERROR: table %s skipped on SQL retrieve error: ' +
str(exec_error))
too_many_errors = True
cursor = None
if too_many_errors:
break
row_count = 0
skp_count = 0
upd_count = 0
new_count = 0
found_records = []
t0_table = datetime.now()
try:
with UPLOADERS[args.driver](jdbc[TRG],
t.lower(),
commit_mode=commit_mode) as uploader:
for d in jdbc[SRC].get_data(cursor=cursor,
return_type=dict,
include_none=is_update):
row_count += 1
pk = d[pk_trg]
if args.mode == COPY_AND_SYNC:
found_records.append(pk)
record_exists = (pk in existing_records)
if record_exists and (not is_update):
skp_count += 1
if args.update_fast:
print(
'Huristic fast update of %s. Skipping at rowcount %d'
% (t, row_count))
break
else:
try:
if record_exists:
del d[pk_trg]
uploader.update(d, {pk_trg: pk})
upd_count += 1
else:
if is_update:
none_keys = [
c for c, v in d.items() if v is None
]
for k in none_keys:
del d[k]
uploader.insert(d)
new_count += 1
except lwetl.SQLExcecuteException as insert_exception:
counters[CNT_FAIL] += 1
print('Insert error (%d) on row %d: %s' %
(counters[CNT_FAIL], row_count,
str(insert_exception)))
if (args.max_fail >= 0) and (counters[CNT_FAIL] >
args.max_fail):
print('Too many errors: terminating.')
too_many_errors = True
if too_many_errors:
raise TooMayErrorsException(
'Insert or Update failed %d times' %
counters[CNT_FAIL])
has_commit = False
if uploader.row_count >= args.commit_nr:
uploader.commit()
has_commit = True
if has_commit or ((row_count % args.commit_nr) == 0):
print(
'%8d. %5.1f %% of %d records, new: %8d, upd: %8d, ign: %8d. %s. Est. remaining time: %s'
% (row_count, (100.0 * row_count / n), n,
new_count, upd_count, skp_count, t,
estimate_remaining(t0_table, row_count, n)))
if (args.max_rows > 0) and (
(new_count + upd_count) > args.max_rows):
print('Terminating after %d uploads on user request.' %
row_count)
break
if uploader.row_count > 0:
uploader.commit()
print(
'%8d. %5.1f %% of %d records, new: %8d, upd: %8d, ign: %8d. %s. finished'
% (row_count, (100.0 * row_count / n), n, new_count,
upd_count, skp_count, t))
else:
print(
'Update of %s finished, No further commits. rc = %d' %
(t, row_count))
if (new_count + upd_count) > 0:
dt = datetime.now() - t0_table
dt_sec = dt.total_seconds()
if dt_sec > 0:
rec_per_sec = int(round(1.0 * n / dt_sec))
else:
rec_per_sec = 0
print(
'%8d. %5.1f %% of %d records, new: %8d, upd: %8d, ign: %8d. %s. Used time: %s (%d rec/s)'
% (row_count,
(100.0 * row_count / n), n, new_count, upd_count,
skp_count, t, timedelta_to_string(dt), rec_per_sec))
if args.mode == COPY_AND_SYNC:
to_delete = list(existing_records - set(found_records))
if len(to_delete) > 0:
print('Sync: removing %d obsolete records in %s (target)' %
(len(to_delete), t))
while len(to_delete) > 0:
if len(to_delete) > 500:
delete_list = to_delete[0:500]
to_delete = to_delete[500:]
else:
delete_list = [pk for pk in to_delete]
to_delete = []
par_list = ['?'] * len(delete_list)
sql = 'DELETE FROM {0} WHERE {1} IN ({2})'.format(
t, pk_trg, ','.join(par_list))
try:
jdbc[TRG].execute(sql, delete_list, cursor=None)
except lwetl.SQLExcecuteException as delete_exception:
counters[CNT_FAIL] += len(delete_list)
print(delete_exception)
print('Delete error (%d) in table %s on rows %s' %
(counters[CNT_FAIL], t, ', '.join(
[str(pk) for pk in delete_list])))
if (args.max_fail >= 0) and (counters[CNT_FAIL] >
args.max_fail):
print('Too many errors: terminating.')
too_many_errors = True
if too_many_errors:
raise TooMayErrorsException(
'Insert, Update, and Delete failed %d times'
% counters[CNT_FAIL])
if commit_mode == lwetl.UPLOAD_MODE_COMMIT:
jdbc[TRG].commit()
else:
jdbc[TRG].rollback()
except lwetl.CommitException as ce:
counters[CNT_FAIL] += 1
if not (args.ignore_commit_errors and (args.max_fail > 0) and
(counters[CNT_FAIL] <= args.max_fail)):
print(
'Upload encountered a commit exception row {}. Further processing ignored: {}'
.format(row_count, str(ce)),
file=sys.stderr)
too_many_errors = True
except TooMayErrorsException as tee:
print(
'Upload encountered on row {}. Further processing ignored: {}'.
format(row_count, str(tee)),
file=sys.stderr)
too_many_errors = True
if too_many_errors:
break
if counters[CNT_FAIL] > 0:
print('WARNING: not all data has been transfered. Errors = %d' %
counters[CNT_FAIL])
rc = 1 if too_many_errors else 0
clean_exit(jdbc, args, rc)
def main():
if (len(sys.argv) > 1) and (sys.argv[1].lower() == '--version'):
# skip parsing
return show_version()
args = parser.parse_args()
if args.version:
return show_version()
if args.activate:
args.commit_mode = lwetl.UPLOAD_MODE_COMMIT
if args.login.strip().lower() == 'list':
lwetl.print_info()
return 0
# check input command or login alias
if len(args.login.strip()) == 0:
print('ERROR: login credentials not defined.')
parser.print_help()
return 1
try:
jdbc = lwetl.Jdbc(args.login)
except (lwetl.ServiceNotFoundException, lwetl.DriverNotFoundException,
ConnectionError) as login_error:
print('ERROR - %s - %s' %
(type(login_error).__name__, str(login_error)),
file=sys.stderr)
return 1
sql = None
if is_empty(args.command_or_sql):
print('Command or SQL not specified: using the stdin')
elif args.command_or_sql.strip().lower() == 'jdbc_info':
return show_jdbc_info(args.login)
elif args.command_or_sql.strip().lower() == 'table_info':
try:
sql = get_table_info_sql(jdbc)
except LookupError as le:
print(le)
return 1
elif os.path.isfile(args.command_or_sql):
input_file = args.command_or_sql
elif ' ' not in args.command_or_sql.strip():
# the input might be a table name -> test this
table_name = args.command_or_sql.strip()
try:
jdbc.query("SELECT * FROM {0} WHERE 0=1".format(table_name))
except lwetl.SQLExcecuteException:
table_name = None
if table_name is not None:
if args.file_name is None:
sql = 'SELECT * FROM ' + table_name
elif os.path.isfile(args.file_name):
return upload_table(jdbc, args.commit_mode, args.commit_nr,
args.max_rows, table_name, args.file_name,
args.format, args.separator, args.log_file)
else:
print('ERROR: specified input file not found: ' +
args.file_name)
return 1
else:
sql = args.command_or_sql
if sql is None:
sql = sys.stdin
return parse_sql_commands(jdbc, sql, args)
def parse_output(cursors: list, args):
if len(cursors) < 1:
return
kwargs = {'append': False, 'filename_or_stream': args.output_file}
if args.format == 'csv':
kwargs['delimiter'] = args.separator
elif args.format == 'text':
kwargs['column_width'] = args.column_width
elif args.format == 'xmlp':
kwargs['pretty_print'] = True
elif args.format == 'sql':
jdbc = getattr(cursors[0], lwetl.jdbc.PARENT_CONNECTION)
kwargs['connection'] = jdbc
kwargs['columns'] = jdbc.get_columns(cursors[0])
if args.target_db is not None:
if '?' in args.target_db:
lst = args.target_db.split('?')
alias = lst.pop(0)
if alias in JDBC_DRIVERS:
kwargs['type'] = alias
else:
try:
jdbc2 = lwetl.Jdbc(alias)
kwargs['connection'] = jdbc2
except Exception:
try:
jdbc2 = lwetl.jdbc.DummyJdbc(alias)
kwargs['connection'] = jdbc2
except Exception:
pass
table_spec = '?'.join(lst)
else:
table_spec = args.target_db
if ',' in table_spec:
lst = [s.strip() for s in table_spec.split(',')]
kwargs['table'] = lst.pop(0)
columns = OrderedDict()
for col in lst:
columns[col] = lwetl.jdbc.COLUMN_TYPE_STRING
kwargs['columns'] = columns
else:
kwargs['table'] = table_spec
sql_count = 0
f = FORMATTERS[args.format](**kwargs)
for cursor in cursors:
sql_count += 1
jdbc = getattr(cursor, lwetl.jdbc.PARENT_CONNECTION, None)
if not isinstance(jdbc, lwetl.Jdbc):
raise ValueError('Cursor is not created by a Jdbc connection.')
kwargs['cursor'] = cursor
if sql_count == 1:
f.open(**kwargs)
elif args.format in ['xlsx', 'xml', 'xmlp']:
f.next_sheet(cursor, 'Sheet%d' % sql_count)
else:
f.close()
kwargs['append'] = True
f.open(**kwargs)
rc = 0
rc_max = args.max_rows
f.header()
try:
for row in jdbc.get_data(cursor):
f.write(row)
rc += 1
if (rc_max > 0) and (rc >= rc_max):
print('Output trucated on user request.', file=sys.stdout)
jdbc.close(cursor)
break
f.footer()
except lwetl.SQLExcecuteException as exec_error:
print('ERROR: cannot retrieve the data: ' + str(exec_error))
f.close()
def main():
if (len(sys.argv) > 1) and (sys.argv[1].lower() == '--version'):
# skip parsing
return show_version()
args = parser.parse_args()
if args.version:
return show_version()
# load the configuration file
configuration = dict()
count_cfg_files = 0
for fn in [f for f in CFG_FILES if os.path.isfile(f)]:
try:
with open(fn) as fh:
cfg = yaml.load(fh, Loader=yaml.FullLoader)
configuration = merge(cfg, configuration)
count_cfg_files += 1
except PermissionError:
pass
except yaml.YAMLError as pe:
print('ERROR: cannot parse the configuration file %s' % fn, file=sys.stderr)
print(pe, file=sys.stderr)
sys.exit(1)
pw_encrypted = configuration.get('encrypt', True)
if not isinstance(pw_encrypted, bool):
pw_encrypted = True
for a in configuration.get('alias', {}).keys():
configuration['alias'][a] = parse_credentials(configuration['alias'][a], pw_encrypted)
if args.test:
keys = sorted(configuration.get('alias', {}).keys())
for indx, k in enumerate(keys, start=1):
try:
jdbc = lwetl.Jdbc(k)
if jdbc.type in table_count_queries:
sql = table_count_queries[jdbc.type].replace('@SCHEMA', jdbc.schema)
r = 'OK: {:>4} tables found.'.format(jdbc.get_int(sql))
else:
r = 'Unsupported db type: {}'.format(jdbc.type)
except Exception as e:
r = 'Failed: {}'.format(e)
print('{:>3}/{}. {:.<30} {}'.format(indx, len(keys), k, r))
else:
if args.change:
new_password = None
if args.no_interaction:
new_password = os.environ.get('LWETL')
if new_password is None:
print('Enter new password: '******'encrypt'] = True
elif args.remove:
configuration['encrypt'] = False
new_alias = dict()
for a, c in configuration.get('alias', {}).items():
if isinstance(c, Credentials):
if configuration['encrypt']:
c = Credentials(c.username, encrypt(c.password), c.server)
new_alias[a] = '{}/{}@{}'.format(c.username, c.password, c.server)
else:
new_alias[a] = c
configuration['alias'] = new_alias
if args.output_file is None:
print(yaml.dump(configuration))
else:
with open(args.output_file, 'w') as f:
yaml.dump(configuration, f)
print('Configuration written to: ' + args.output_file)
def test_connection_noservice():
"""
Test a connection to an undefined server. Should raise the appropriate exceptions
"""
with pytest.raises(lwetl.ServiceNotFoundException):
lwetl.Jdbc('scott/tiger@noservice')
import pytest
import sys
from tests import TEST_CONFIGURATION, OUTPUT_DIR, TEST_DIR, I_CAN_EAT_GLASS
from contextlib import redirect_stdout
from decimal import Decimal
from lwetl.queries import content_queries
DRIVER_KEYS = sorted(TEST_CONFIGURATION.keys())
# I do not want a new connection for each test
JDBC_CONNECTIONS = dict()
for k in DRIVER_KEYS:
JDBC_CONNECTIONS[k] = lwetl.Jdbc('scott_' + k, auto_commit=False, upper_case=True)
@pytest.fixture(params=DRIVER_KEYS)
def jdbc(request):
alias = 'scott_' + request.param
print('CONNECTING WITH: ' + alias)
return JDBC_CONNECTIONS[request.param]
def get_tables(jdbc: lwetl.Jdbc) -> list:
"""
Get the tables defined in the connected database schema
@param jdbc: lwetl.Jdbc - database connection
@return: list of table names in uppercase
"""