def upload_batch(jdbc: Jdbc, batch_nr, id_start, word_list, use_paramers):
cursor = None
pk = id_start
n_ascii = 0
n_other = 0
for w in word_list:
try:
w.encode().decode('ascii')
n_ascii += 1
except UnicodeDecodeError:
n_other += 1
if use_paramers:
for w in word_list:
pk += 1
# if batch_nr == 37: print('%8d %s' % (pk,w))
cursor = jdbc.execute(
"INSERT INTO {0} (ID,STR_VALUE) VALUES(?,?)".format(
TABLE_NAME), [pk, w],
cursor=cursor)
else:
for w in word_list:
pk += 1
# if batch_nr == 37: print('%8d %s' % (pk,w))
cursor = jdbc.execute(
"INSERT INTO %s (ID,STR_VALUE) VALUES(%d,'%s')" %
(TABLE_NAME, pk, w.replace("'", "''")),
cursor=cursor)
jdbc.commit(cursor)
print('%4d. done ascii = %4d, other = %4d.' % (batch_nr, n_ascii, n_other))
return n_ascii, n_other
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
"""
if jdbc.type not in content_queries:
raise LookupError('Database type %s not supported.' % jdbc.type)
sql = content_queries[jdbc.type]
if '@SCHEMA@' in sql:
sql = sql.replace('@SCHEMA@', jdbc.schema)
tables = []
error = None
try:
jdbc.query(sql)
for r in jdbc.get_data():
if (len(r) > 0) and (r[0].upper() not in tables):
tables.append(r[0].upper())
except lwetl.SQLExcecuteException as exec_error:
print('ERROR in get_tables()', sys.stderr)
error = exec_error
if error is None:
return tables
else:
raise lwetl.SQLExcecuteException(str(error))
def commit(jdbc: lwetl.Jdbc, cursor, mode, row_count, tot_count):
if mode == lwetl.UPLOAD_MODE_COMMIT:
jdbc.commit(cursor)
n_rollback = 0
else:
jdbc.rollback(cursor)
n_rollback = row_count
print('%s for %3d rows (%6d total)' % (mode.upper(), row_count, tot_count))
return n_rollback
def init_connections(login_source, login_target):
# no handling of wrong credentials
src = Jdbc(login_source)
tag_connection('SRC', src)
trg = Jdbc(login_target)
tag_connection('TRG', trg)
# create the target table if not existing
create_target_table(trg)
return src, trg
def test_formatters(jdbc: lwetl.Jdbc):
print('\nRunning formatter test: (%s,%s)' % (jdbc.login, jdbc.type))
formatters = {
'txt': lwetl.TextFormatter,
'csv': lwetl.CsvFormatter,
'xml': lwetl.XmlFormatter,
'xlsx': lwetl.XlsxFormatter,
'sql': lwetl.SqlFormatter
}
output_dir = os.path.join(TEST_DIR, 'output', jdbc.type)
if not os.path.isdir(output_dir):
os.mkdir(output_dir)
extensions = sorted(formatters.keys())
has_errors = False
for table in [t.upper() for t in get_tables(jdbc) if t.upper().startswith('LWETL')]:
if has_errors:
break
print('\nQuery: ' + table)
sql = "SELECT * FROM {0}".format(table)
for ext in extensions:
formatter = formatters[ext]
print("\n\nTesting (1): " + formatter.__name__)
kwargs = {
'cursor': jdbc.execute(sql),
'filename_or_stream': os.path.join(output_dir, '%s-1.%s' % (table, ext))
}
if ext == 'sql':
kwargs['connection'] = jdbc
kwargs['table'] = table
elif ext == 'xml':
kwargs['pretty_print'] = True
with formatter(**kwargs) as f:
f.header()
for r in jdbc.get_data():
f.write(r)
f.footer()
for ext in extensions:
kwargs = {
'jdbc': jdbc,
'sql': sql,
'filename_or_stream': os.path.join(output_dir, '%s-2.%s' % (table, ext))
}
if ext == 'sql':
kwargs['connection'] = jdbc
kwargs['table'] = table
elif ext == 'xml':
kwargs['pretty_print'] = True
formatter = formatters[ext]()
print("\n\nTesting (2): " + type(formatter).__name__)
formatter(**kwargs)
def test_access(jdbc: lwetl.Jdbc):
class Upload:
def __init__(self, jdbc: lwetl.Jdbc):
self.stm_count = 0
self.row_count = 0
self.jdbc = jdbc
def __call__(self, sql, raise_error_on_error=True):
print(sql)
error_msg = None
try:
c = self.jdbc.execute(sql, cursor=None)
except lwetl.SQLExcecuteException as sql_error:
self.jdbc.rollback()
error_msg = str(sql_error)
else:
self.stm_count += 1
if c.rowcount > 0:
self.row_count += c.rowcount
self.jdbc.commit()
if error_msg is not None:
if raise_error_on_error:
raise lwetl.SQLExcecuteException(error_msg)
else:
print('SQL ERROR ignored: ' + error_msg)
cfg = get_test_configuration(jdbc)
upload = Upload(jdbc)
for key in [k for k in ['drop', 'create', 'insert'] if k in cfg]:
raise_error = (key != 'drop')
for skey in sorted(cfg[key].keys()):
upload(cfg[key][skey], raise_error)
jdbc.connection.commit()
if 'check' in cfg:
sql_1 = cfg['check']['sql_1']
sql_n = cfg['check']['sql_n']
d1 = jdbc.query_single_value(sql_1)
if isinstance(d1, float):
d1 = Decimal(str(d1))
d2 = Decimal('0.0')
for r in jdbc.query(sql_n):
price = r[0]
if isinstance(price, str):
price = Decimal(price)
d2 += price
print('Test found d1,d2 = {0},{1}'.format(d1, d2))
assert d1 == d2
del jdbc
def test_binary_io(jdbc: lwetl.Jdbc):
print('\nRunning binary insert test: (%s,%s)' % (jdbc.login, jdbc.type))
cfg = get_test_configuration(jdbc).get('binary', None)
if cfg is None:
print('No binary io test defined. Skipping test.')
return
fname = os.path.join(TEST_DIR, 'resources', cfg['file'])
print('Testing upload from: ' + fname)
with open(fname, mode='rb') as file:
img = file.read()
table = cfg['table']
column = cfg['column']
id_pk = cfg['id']
uploader = lwetl.ParameterUploader(jdbc, table, fstream=sys.stdout, commit_mode=lwetl.UPLOAD_MODE_COMMIT)
error = None
try:
uploader.update({column: img}, {'ID': id_pk})
uploader.commit()
except lwetl.SQLExcecuteException as exec_error:
error = exec_error
print('TEST SKIPPED: unsupported feature.')
print(exec_error)
else:
fname = os.path.join(OUTPUT_DIR, '%s.%s.jpg' % (jdbc.type, table.lower()))
print('Testing download to: ' + fname)
with open(fname, 'wb') as g:
g.write(jdbc.query_single_value('SELECT {0} FROM {1} WHERE ID = {2}'.format(column, table, id_pk)))
def mt_upload(login, batch_nr, sqls):
global UPL_POOL_CONNECTIONS
if is_empty(sqls):
print('UPL: %3d, sqls = %4d, rows = %6d' % (batch_nr, -1, -1))
return 0, 0
name = multiprocessing.current_process().name
if name not in UPL_POOL_CONNECTIONS:
print('CREATING TRG DB CONNECTION FOR: ' + name)
UPL_POOL_CONNECTIONS[name] = Jdbc(login)
jdbc = UPL_POOL_CONNECTIONS[name]
rc1 = 0
rc2 = 0
with InputParser(sqls) as parser:
cursor = None
for sql in parser.parse():
rc1 += 1
try:
sql.encode().decode('ascii')
c = jdbc.execute(sql, cursor=cursor)
cursor = c
except Exception as ex:
rc2 += 1
print('%2d %3d %s' % (batch_nr, rc1, sql))
#if cursor is not None:
# rc2 = cursor.rowcount
print('UPL: %3d, sqls = %4d, rows = %6d' % (batch_nr, rc1, rc2))
if cursor is not None:
jdbc.commit()
return rc1, rc2
def process_clients(jdbc: Jdbc, batch_nr: int, clients: dict(),
usernames: dict):
row_count = 0
ign_count = 0
n_users_1 = len(usernames)
if len(clients) > 0:
inverse_lookup = dict()
for id_client, id_person in clients.items():
if id_person in inverse_lookup:
inverse_lookup[id_person].append(id_client)
else:
inverse_lookup[id_person] = [id_client]
sql = SQL_GET_USERNAME.format(','.join(
[str(k) for k in inverse_lookup.keys()]))
for id_person, username in jdbc.query(sql):
row_count += 1
for id_client in inverse_lookup[int(id_person)]:
if id_client in usernames:
if username in usernames[id_client]:
ign_count += 1
else:
usernames[id_client].append(username)
else:
usernames[id_client] = [username]
n_users_2 = len(usernames)
print(
'Batch %3d finished: rows = %4d, ignored = %4d, users: %6d, delta = %4d'
% (batch_nr, row_count, ign_count, n_users_2, n_users_2 - n_users_1))
return row_count, ign_count
def test_encoding(jdbc: lwetl.Jdbc):
print('\nRunning ldif insert encoding test: (%s,%s)' % (jdbc.login, jdbc.type))
table = 'LWETL_ENC'
# read ldif and dump in table
fn = os.path.join(os.path.dirname(__file__), 'resources', 'utf8.ldif')
cnt = 0
with lwetl.ParameterUploader(jdbc, table, commit_mode=lwetl.UPLOAD_MODE_COMMIT) as upl:
with lwetl.LdifImport(fn) as ldif:
for rec in ldif.get_data():
dd = {
'ID': int(rec['indx']),
'LANG1': rec['sn'],
'VAL': rec['value']
}
if 'cn' in rec:
dd['LANG2'] = rec['cn']
upl.insert(dd)
cnt += 1
upl.commit()
# check if the number of rows is currect
cnt2 = jdbc.get_int("SELECT COUNT(1) FROM {0}".format(table))
print('Inserted %d of %d values' % (cnt2,cnt))
assert cnt == cnt2
# reread the table and verify with origininal
cnt_ok = 0
cnt_fail = 0
for lg1, lg2, val in jdbc.query("SELECT LANG1, LANG2, VAL FROM {0} ORDER BY LANG1, LANG2".format(table)):
if lg2:
r = '%s.%s' % (lg1,lg2)
s = I_CAN_EAT_GLASS.get(lg1,dict()).get(lg2,'NN')
else:
r = lg1
s = I_CAN_EAT_GLASS.get(lg1,'NN')
if val == s:
cnt_ok += 1
else:
cnt_fail += 1
print('FAILED %s s=(%s) db=(%s)' % (r,s,val))
assert cnt_fail == 0
def mt_process_clients(login, batch_nr: int, clients: dict(), usernames: dict):
global DWN_POOL_CONNECTIONS
name = multiprocessing.current_process().name
if name not in DWN_POOL_CONNECTIONS:
print('CREATING SRC DB CONNECTION FOR: ' + name)
DWN_POOL_CONNECTIONS[name] = Jdbc(login)
return process_clients(DWN_POOL_CONNECTIONS[name], batch_nr, clients,
usernames)
def upload_batch_mt(login, batch_nr, id_start, word_list, use_paramers, lock):
global CONNECTIONS
name = multiprocessing.current_process().name
if name not in CONNECTIONS:
CONNECTIONS[name] = Jdbc(login)
lock.acquire()
r = upload_batch(CONNECTIONS[name], batch_nr, id_start, word_list,
use_paramers)
lock.release()
return r
def parse_sql_commands(jdbc: lwetl.Jdbc, sql_input, args) -> int:
has_error = False
with lwetl.InputParser(sql_input) as inp:
row_count = 0
tot_count = 0
tot_rollb = 0
has_update = False
cursors = []
cursor = None
for sql in inp.parse():
if len(cursors) > 20:
print(
'WARNING: you can only parse a maximum of 20 queries with a table result. Terminating buffer.'
)
break
try:
cursor = jdbc.execute(sql, cursor=cursor)
except lwetl.SQLExcecuteException as exec_error:
print("ERROR in parsing: " + sql, file=sys.stderr)
print(exec_error)
has_error = True
break
else:
if cursor.rowcount < 0:
cursors.append(cursor)
cursor = None
else:
has_update = True
rc = cursor.rowcount
row_count += rc
tot_count += rc
if row_count >= args.commit_nr:
tot_rollb += commit(jdbc, cursor, args.commit_mode,
row_count, tot_count)
row_count = 0
cursor = None
if row_count > 0:
tot_rollb += commit(jdbc, cursor, args.commit_mode, row_count,
tot_count)
if has_update:
print('Finished. %6d rows updated.' % (tot_count - tot_rollb))
if len(cursors) > 0:
parse_output(cursors, args)
return has_error
def create_target_table(jdbc: Jdbc):
"""
Creates the target table if it dows not exist
@param jdbc: target database connection
@return: True if created, False otherwise
"""
try:
jdbc.execute(SQL_EXISTS)
created = False
except SQLExcecuteException:
if jdbc.type not in CREATE_TABLE:
raise LookupError('Database type %s not in known list: %s' %
(jdbc.type, ', '.join(CREATE_TABLE.keys())))
print('CREATING TABLE:' + TARGET_TABLE)
jdbc.execute(CREATE_TABLE[jdbc.type].format(TARGET_TABLE))
jdbc.commit()
created = True
return created
parser.add_argument('-a',
'--activate',
action='store_true',
help='Activate commit. Use rollback otherwise.')
parser.add_argument(
'-n',
'--commit_nr',
type=int,
default=1000,
help='Commit to database every nth statement. Defaults to 1000.')
args = parser.parse_args()
jdbc = Jdbc(args.login)
print('Connected to: ' + jdbc.type)
if args.activate:
rollback_msg = ''
commit = jdbc.commit
else:
rollback_msg = ' (with rollback)'
commit = jdbc.rollback
with InputParser(args.sql) as inp:
cnt = 0
cur = None
for sql in inp.parse():
cnt += 1
cur = jdbc.execute(sql, cursor=cur)
args = parser.parse_args()
# set the commit mode
commit_mode = UPLOAD_MODE_COMMIT if args.activate else UPLOAD_MODE_ROLLBACK
# enable logging, if desired
logf = None
if args.log is not None:
if args.log.lower() == 'stdin':
logf = sys.stdin
elif args.log.lower() == 'stdout':
logf = sys.stdout
else:
logf = open(args.log, 'w')
jdbc = Jdbc(args.login)
print('Connected to: ' + jdbc.type)
with NativeUploader(jdbc,
args.table,
fstream=logf,
commit_mode=commit_mode,
exit_on_fail=True) as upl:
with CsvImport(args.csv, delimiter=args.delimiter) as inp:
cnt = 0
for row in inp.get_data():
cnt += 1
upl.insert(row)
if upl.row_count >= args.commit_nr:
upl.commit()
upl.commit()
help='''login credentials or alias of the source database.
Use 'sql-query list' to view possible options.
Credentials are in ORACLE format: <username>/<password>@server''')
parser.add_argument('-a', '--activate', action='store_true',
help='Activate commit. Use rollback otherwise.')
parser.add_argument('-l', '--list', action='store_true',
help='List the affected sequences only.')
parser.add_argument('-n', '--commit_nr', type=int, default=1000,
help='Commit to database every nth statement. Defaults to 1000.')
args = parser.parse_args()
jdbc = Jdbc(args.login)
print('Connected to: ' + jdbc.type)
SQL_LIST_SEQUENCES = '''
SELECT
SEQUENCE_NAME, LAST_NUMBER
FROM
USER_SEQUENCES
ORDER BY SEQUENCE_NAME'''
SQL_LIST_TABLES_COLUMNS = '''
SELECT
c.table_name,
c.column_name
FROM
user_constraints s
'Extract image files from a database into the current directory.')
parser.add_argument('login',
help='''login credentials or alias of the source database.
Use 'sql-query list' to view possible options.
Credentials are in ORACLE format: <username>/<password>@server''')
parser.add_argument('sql',
help='''SQL to extract the images returning two columns:
1. the file-name for the image,
2. the field with the image content.
Example:
SELECT FILE_NAME, PHOTO FROM MY_PHOTOS ORDER BY FILE_NAME''')
args = parser.parse_args()
jdbc = Jdbc(args.login)
print('Connected to: ' + jdbc.type)
cnt1 = 0
cnt2 = 0
for fname, logo in jdbc.query(args.sql):
cnt1 += 1
print('%6d. Parsing %s' % (cnt1, fname))
if isinstance(fname, str) and (len(fname.strip()) > 0):
with open(fname.strip(), 'wb') as f:
f.write(logo)
cnt2 += 1
print('Done: extracted %d files (%d skipped).' % (cnt1, (cnt1 - cnt2)))
parser.add_argument(
'-t',
'--threads',
type=check_positive,
default=0,
help='Number of threads. Defaults to 0 (no threadpool used)')
parser.add_argument('-p',
'--parameters',
action='store_true',
help='Use parameters in SQL.')
args = parser.parse_args()
login = args.login
jdbc = Jdbc(login)
try:
id_start = jdbc.get_int("SELECT MAX(ID) FROM {0}".format(TABLE_NAME))
print('Start inserting with: %d' % id_start)
except SQLExcecuteException:
if jdbc.type not in CREATE_TABLE:
raise LookupError('Database type %s not in known list: %s' %
(jdbc.type, ', '.join(CREATE_TABLE.keys())))
print('CREATING TABLE:' + TABLE_NAME)
jdbc.execute(CREATE_TABLE[jdbc.type].format(TABLE_NAME))
jdbc.commit()
id_start = 0
batch_size = args.batch_size
n_switch = args.n_switch