def table_fields(self, table_name: str, schema=None) -> List[FieldRecord]:
"""
Get fields of table
:param table_name:
:param schema:
:return:
"""
if schema is None:
schema = self.SCHEMA_DEFAULT
columns = {
'column_name': 'field',
'data_type': 'type',
Literal('false'): 'primary'
}
qry = Select(PgSqlDialect()) \
.from_('columns', columns, schema='information_schema') \
.where('table_schema', '=', schema) \
.where('table_name', '=', table_name) \
.order('ordinal_position')
idx = self.table_pk(table_name, schema)
with self._db.cursor() as c:
fields = c.fetchall(*qry.assemble(),
cls=FieldRecord) # type:list[FieldRecord]
if idx is not None:
for f in fields:
f.primary = f.field == idx.field
return fields
def views(self, schema=None) -> List:
if schema is None:
schema = self.SCHEMA_DEFAULT
result = []
qry = Select(PgSqlDialect()).from_('pg_views', ['viewname']).where(
'schemaname', '=', schema)
with self._db.cursor() as c:
for r in c.fetchall(*qry.assemble()):
result.append(r['viewname'])
return result
def view_exists(self, view_name: str, schema=None) -> bool:
"""
Check if a given view exists
:param view_name: table name
:param schema: optional schema
:return:
"""
qry = Select(Sqlite3SqlDialect()) \
.from_('sqlite_master', ['name']) \
.where('name', '=', view_name) \
.where('type', '=', 'view')
with self._db.cursor() as c:
return len(c.fetchall(*qry.assemble())) > 0
def views(self, schema=None) -> List:
"""
List all available views on the indicated schema. If no schema is specified, assume public schema
:param schema: optional schema name
:return: list of tablenames
"""
qry = Select(Sqlite3SqlDialect()).from_('sqlite_master').where(
'type', '=', 'view')
result = []
with self._db.cursor() as c:
for r in c.fetchall(*qry.assemble()):
if not r['name'].startswith('sqlite_'):
result.append(r['name'])
return result
def view_exists(self, view_name: str, schema=None) -> bool:
"""
Check if a given view exists
:param view_name: table name
:param schema: optional schema
:return:
"""
if schema is None:
schema = self.SCHEMA_DEFAULT
qry = Select(PgSqlDialect()).from_('pg_views', ['viewname']) \
.where('schemaname', '=', schema) \
.where('viewname', '=', view_name)
with self._db.cursor() as c:
return len(c.fetchall(*qry.assemble())) > 0
def tables(self, schema=None) -> List:
"""
List all available tables on the indicated schema. If no schema is specified, assume public schema
:param schema: optional schema name
:return: list of tablenames
"""
if schema is None:
schema = self.SCHEMA_DEFAULT
result = []
qry = Select(PgSqlDialect()).from_('pg_tables', ['tablename']).where(
'schemaname', '=', schema)
with self._db.cursor() as c:
for r in c.fetchall(*qry.assemble()):
result.append(r['tablename'])
return result
def user_groups(self, user_name: str) -> List[str]:
"""
List all groups associated with a given user
:param user_name: user name to check
:return: list of group names
"""
qry = Select(PgSqlDialect()) \
.from_('pg_user', {'rolname': 'name'}) \
.join('pg_auth_members', 'member', 'pg_user', 'usesysid') \
.join('pg_roles', 'oid', 'pg_auth_members', 'roleid') \
.where('usename', '=', user_name)
result = []
with self._db.cursor() as c:
for r in c.fetchall(*qry.assemble()):
result.append(r['name'])
return result
def select(self, cols=None) -> Select:
"""
Return a Select() builder instance for the current table
:param cols: optional columns
:return: Select
"""
return Select(self._dialect).from_(self._tablename,
cols=cols,
schema=self._schema)
def databases(self) -> List:
"""
List all available databases
:return: list of database names
"""
with self._db.cursor() as c:
result = []
for r in c.fetchall(*Select(PgSqlDialect()).from_(
'pg_database', ['datname']).assemble()):
result.append(r['datname'])
return result
def fetch_one(self, qry: Select) -> Optional[object]:
"""
Retrieve a single row
:param qry: query to execute
:return: record object of self._record or None
Example:
r.fetch_one(r.select().where('login', '=', 'gandalf@lotr')) # fetch record if exists, else returns None
"""
with self._db.cursor() as c:
sql, values = qry.limit(1).assemble()
return c.fetchone(sql, values, cls=self._record)
def schemas(self) -> List:
"""
List all available schemas
:return: list of schema names
"""
with self._db.cursor() as c:
result = []
for r in c.fetchall(*Select(PgSqlDialect()).from_(
'schemata', ['schema_name'],
'information_schema').assemble()):
result.append(r['schema_name'])
return result
def list(self, qry: Select, limit=None, offset=None, cls=None):
"""
Performs a query with offset and limit
Returns a tuple with total record count for the query, and the rows returned by applying offset and limit
The original query object is left intact
:param qry: query to use
:param limit:
:param offset:
:param cls: optional Record class to use for rows
:return: (total_rows, selected_row_list)
"""
total = 0
qry = copy.deepcopy(qry)
sql, values = qry.assemble()
qry_count = Select(self._dialect).from_(
{Literal(sql): "qry"}, cols={Literal('COUNT(*)'): 'total'})
if limit:
qry.limit(limit, offset)
with self._db.cursor() as c:
# count total rows
sql, _ = qry_count.assemble()
count_record = c.fetchone(sql, values)
if count_record:
total = count_record['total']
# fetch rows
rows = self.fetch(qry, cls=cls)
return total, rows
def fetch_raw(self, qry: Select) -> Optional[list]:
"""
Fetch a list of rows
Result is not serialized to record, instead it returns a list of dict-like records directly from the DB driver
:param qry: query to execute
:return: list of dict-like result
Example:
r.fetch_raw(r.select().where('name', 'like', 'gandalf%')) # fetch records that match query
"""
with self._db.cursor() as c:
sql, values = qry.assemble()
return c.fetchall(sql, values)
def users(self) -> List[UserRecord]:
"""
List all available users
:return:
"""
fields = {
'usename': 'name',
'usesuper': 'superuser',
'usecreatedb': 'createdb'
}
with self._db.cursor() as c:
return c.fetchall(
*Select(PgSqlDialect()).from_('pg_user', fields,
'pg_catalog').assemble(),
UserRecord)
def fetch(self, qry: Select, cls=None) -> Optional[list]:
"""
Fetch a list of rows
:param qry: query to execute
:param cls: optional record class (useful for joins that may return different record structures)
:return: list of record object or empty list
Example:
r.fetch(r.select().where('name', 'like', 'gandalf%')) # fetch records that match query
"""
with self._db.cursor() as c:
sql, values = qry.assemble()
if cls is None:
cls = self._record
return c.fetchall(sql, values, cls=cls)
def run(self,
qry: Select = None,
search_text: str = None,
match_fields: dict = None,
limit: int = None,
offset: int = None,
sort_fields: dict = None) -> tuple:
"""
Executes a query and returns the total row count matching the query, as well as the records within the specified
range.
If no query specified, a default query is built; If no sort dict specified, the default sort is used;
If limit is omitted, all results are returned
search_text is applied to all fields present in search_fields and are OR'ed;
match_fields contains fieldnames and values to be matched for equality; they are AND'ed;
:param qry: optional Select query
:param search_text: optional search string
:param match_fields: optional field filter
:param limit: optional limit
:param offset: optional offset (ignored if no limit)
:param sort_fields: optional sort fields in the format {field_name: order}
:return: tuple(total_row_count, filtered_rows)
"""
if not qry:
qry = self.default_query()
if not sort_fields:
sort_fields = self.default_sort()
if match_fields:
qry.where_and()
for field, value in match_fields.items():
if field not in self._fields:
raise ValueError(
"field '%s' used in match_field does not exist on Record"
% field)
qry.where(field, '=', value)
qry.where_end()
if search_text:
qry.where_and()
if self._search_type == self.SEARCH_NONE:
raise RuntimeError('search is not allowed')
if len(self._search_fields) == 0:
raise RuntimeError(
"no available fields are mapped as searchable")
mask = self.search_map[self._search_type].format(str(search_text))
operand = 'LIKE'
psql_mode = False
if not self._case_sensitive:
if self._ilike:
operand = 'ILIKE'
psql_mode = True
else:
psql_mode = True
if psql_mode:
for field in self._search_fields:
qry.orwhere(field, operand, mask.format(str(search_text)))
else:
mask = mask.upper()
for field in self._search_fields:
qry.orwhere(Literal('UPPER({})'.format(field)), operand,
mask)
qry.where_end()
if sort_fields:
if not isinstance(sort_fields, Mapping):
raise ValueError("'sort_fields' parameter must be a dict")
for field, order in sort_fields.items():
if field in self._fields:
qry.order(field, order.upper())
else:
raise ValueError(
"field '%s' used for sorting does not exist on Record"
% field)
# perform queries
return self._repo.list(qry, limit=limit, offset=offset)
from rick_db.sql import Update, Select, Literal
@fieldmapper(tablename='test_table')
class SomeTable:
field = 'field'
other = 'other_field'
@fieldmapper(tablename='other_table', schema='public')
class SchemaTestTable:
field = 'field'
other = 'other_field'
sample_query = Select().from_('test', ['id']).where('field', '=', 'abcd')
sample_record = SomeTable(field='value', other='other_value')
update_cases = [
['table1', {'field1': 'value1'}, None, None, 'UPDATE "table1" SET "field1"=?'],
['table1', {'f1': 'v1', 'f2': 2}, None, 'public', 'UPDATE "public"."table1" SET "f1"=?, "f2"=?'],
['table1', {'field1': 'value1'}, [('id', '=', '5')], None, 'UPDATE "table1" SET "field1"=? WHERE "id" = ?'],
['table1', {'f1': 'v1', 'f2': 2}, [('id', '=', '5')], 'public',
'UPDATE "public"."table1" SET "f1"=?, "f2"=? WHERE "id" = ?'],
['table1', {'f1': 'v1', 'f2': 2}, [('id', 'in', sample_query)], None,
'UPDATE "table1" SET "f1"=?, "f2"=? WHERE "id" in (SELECT "id" FROM "test" WHERE ("field" = ?))'],
['table1', {'field1': 'value1'}, [('id', 'is null', None), (Literal('LENGTH(name)'), '>', 3)], None,
'UPDATE "table1" SET "field1"=? WHERE "id" is null AND LENGTH(name) > ?'],
[SomeTable, sample_record, [('id', '=', '5')], None,
'UPDATE "test_table" SET "field"=?, "other_field"=? WHERE "id" = ?'],
[SomeTable, {SomeTable.field: 'value1'}, [('id', '=', '5')], None,
