def select_join_objects(left: str, right: str,
on: str) -> Iterable: # (OrderedAttrDict, )
return (OrderedAttrDict(
zip(
list(get_table_schema(left).keys())[:-1] +
list(get_table_schema(right).keys())[:-1], row))
for row in select_join(left, right, on))
def select_objects(table: str, *columns,
**where) -> Iterable: # (OrderedAttrDict, )
return (OrderedAttrDict(
zip(
list(f for f in get_table_schema(table).keys()
if not columns or f in columns), row))
for row in select(table, *columns, **where))
def existing(table, by_schema=True, **where) -> bool:
if by_schema:
by_schema = get_table_schema(table)
where_sql = by_schema.new(**where).for_where(**where)
else:
where_sql = ' AND '.join(f'{k}{where_op_value(str(v))}'
for k, v in where.items() if v)
sql = f"SELECT 1 FROM {table} WHERE {where_sql} LIMIT 1"
try:
values = next(_select(sql))
except StopIteration:
values = None
except sqlite3.OperationalError as exc:
values = None
if str(exc) != f'no such table: {table}':
raise exc
exists = values is not None and len(values) > 0
m_logger.debug(table + ' ' + ' '.join(f'{k}={v}'
for k, v in where.items()) +
f" {'does' if exists else 'does not'} exist")
return exists
def select(table: str,
*columns,
by_schema=True,
**where) -> Iterable: # yield row
sql = f"SELECT {','.join(columns) if columns else '*'} FROM {table}"
if 'order_by' in where:
order_by = ' ORDER BY ' + where['order_by']
del where['order_by']
else:
order_by = ''
if where:
sql += ' WHERE '
if by_schema:
sql += get_table_schema(table).new(**where).for_where(**where)
else:
sql += ' '.join(f"{k}={_quoted(v)}" for k, v in where.items())
sql += order_by
for row in _select(sql):
yield row
def update(table, **kwargs):
schema = get_table_schema(table)
keys = table_keys_dict(table, kwargs, schema)
if not existing(table, **keys):
raise NameError(f"table {table} is missing {keys}")
record = schema.new(**kwargs)
where = schema.new(**keys).for_where(**keys)
_set = record.for_update(**kwargs)
sql = f'UPDATE {table} SET {_set} WHERE {where}'
cursor = m_conn.cursor()
cursor.execute(sql)
m_conn.commit()
m_logger.debug(f'updated at {table} {sql}')
def read(table, **kv) -> TableSchema:
where = get_table_schema(table).new(**kv).for_where(**kv)
sql = f"SELECT * FROM {table} WHERE {where}"
m_logger.debug('reading: ' + sql)
try:
values = next(_select(sql))
except StopIteration:
raise NameError('missing from {}: {}={} "{}"'.format(
table,
*list(kv.items())[0], sql))
record = _new_schema(table, values)
m_logger.debug(f'read from {table} {repr(record)}')
return record
def dump(*outs, cwd: str = '') -> [str]:
assert outs, 'expected one or more out files, formats: ' + ', '.join(
DUMPERS.keys())
dumped = []
tables = list(get_table_schemas().keys())
for table in tables:
dump_files = [_new_dump_file(out, cwd, table) for out in outs]
for row in select_objects(table):
[
file.dump(get_table_schema(table).new(**row))
for file in dump_files
]
dumped.extend(_close_dump_files(dump_files))
return dumped
def delete(table, lenient=False, by_schema=True, **where):
if by_schema:
by_schema = get_table_schema(table)
for_where = by_schema.new(**where).for_where(**where)
else:
for_where = ' '.join(f"{k}={_quoted(v)}" for k, v in where.items())
sql = f'DELETE FROM {table}'
if not lenient and where:
assert existing(table,
**where), f"table {table} is missing {for_where}"
if where:
sql += ' WHERE ' + for_where
cursor = m_conn.cursor()
cursor.execute(sql)
m_conn.commit()
m_logger.debug('Done ' + sql)
def create(table, lenient=False, **kwargs) -> TableSchema:
schema = get_table_schema(table)
if not lenient:
try:
keys = table_keys_dict(table, kwargs, schema)
assert not existing(table, **
keys), f"{keys} already exists at {table}"
except KeyError:
pass
record = schema.new(**kwargs)
sql = 'INSERT INTO {} ({}) VALUES ({})'.format(table, *record.for_insert())
m_logger.debug(sql)
cursor = m_conn.cursor()
cursor.execute(sql)
m_conn.commit()
m_logger.info(f'created at {table} {repr(record)}')
return record
def _new_schema(table, values) -> TableSchema:
return get_table_schema(table).new(
**dict(zip(m_table_columns[table].names, values)))
def _drop_create_table(tname):
cursor = m_conn.cursor()
cursor.execute('DROP TABLE IF EXISTS ' + tname)
cursor.execute('CREATE TABLE {} ({})'.format(tname,
str(get_table_schema(tname))))
m_logger.info('initialized table: ' + tname)