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 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 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 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 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 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