def count_query(cls, request, *args, **kwargs): # Query that returns the number of records in the report. # It implements filtering, but no paging or sorting. conn = None if not hasattr(request, "report"): request.report = (SQLReport.objects.all().using( request.database).get(pk=args[0])) if request.report and request.report.sql: try: conn = create_connection(request.database) with conn.cursor() as cursor: sqlrole = settings.DATABASES[request.database].get( "SQL_ROLE", "report_role") if sqlrole: cursor.execute("set role %s" % (sqlrole, )) if not hasattr(request, "filter"): request.filter = cls.getFilter(request, *args, **kwargs) cursor.execute( "select count(*) from (%s) t_subquery %s" % ( request.report.sql.replace("%", "%%"), "where %s" % request.filter[0] if request.filter[0] else "", ), request.filter[1], ) return cursor.fetchone()[0] finally: if conn: conn.close() else: return 0
def executeSQLfile(self, ifile): """ Execute statements from a text with SQL statements. """ if ifile.lower().endswith(".gz"): file_open = gzip.open else: file_open = open conn = None try: conn = create_connection(self.database) with conn.cursor() as cursor: if self.SQLrole: cursor.execute("set role %s", (self.SQLrole,)) cursor.execute(file_open(ifile, "rt").read()) return 0 except Exception as e: logger.error( "%s Error executing SQL: %s" % (datetime.now().replace(microsecond=0), e) ) return 1 finally: if conn: conn.close()
def runQuery(): conn = None sqlrole = settings.DATABASES[request.database].get( "SQL_ROLE", "report_role") try: conn = create_connection(request.database) with conn.cursor() as cursor: with transaction.atomic(): if sqlrole: cursor.execute("set role %s", (sqlrole, )) sql = request.read().decode( request.encoding or settings.DEFAULT_CHARSET) cursor.execute(sql) if cursor.description: columns = [desc[0] for desc in cursor.description] yield """{ "rowcount": %s, "status": "ok", "columns": %s, "data": [ """ % ( cursor.rowcount, json.dumps(columns), ) first = True for result in cursor.fetchall(): if first: yield json.dumps( dict( zip(columns, [str(i) for i in result]))) first = False else: yield ",%s" % json.dumps( dict( zip(columns, [str(i) for i in result]))) yield "]}" elif cursor.rowcount: yield '{"rowcount": %s, "status": "Updated %s rows"}' % ( cursor.rowcount, cursor.rowcount, ) else: yield '{"rowcount": %s, "status": "Done"}' % cursor.rowcount except GeneratorExit: pass except Exception as e: yield json.dumps({"status": str(e)}) finally: if conn: conn.close()
def refreshColumns(self): from freppledb.common.middleware import _thread_locals req = getattr(_thread_locals, "request", None) if req: db = getattr(req, "database", DEFAULT_DB_ALIAS) else: db = getattr(_thread_locals, "database", DEFAULT_DB_ALIAS) SQLColumn.objects.filter(report=self).using(db).delete() if self.sql: conn = None try: conn = create_connection(db) with conn.cursor() as cursor: sqlrole = settings.DATABASES[db].get( "SQL_ROLE", "report_role") if sqlrole: cursor.execute("set role %s" % (sqlrole, )) # The query is wrapped in a dummy filter, to avoid executing the # inner real query. It still generates the list of all columns. cursor.execute("select * from (%s) as Q where false" % self.sql) if self.id: cols = [] seq = 1 for f in cursor.description: if f[0] in cols: raise Exception("Duplicate column name '%s'" % f[0]) cols.append(f[0]) if f[1] == 1700: fmt = "number" elif f[1] == 1184: fmt = "datetime" elif f[1] == 23: fmt = "integer" elif f[1] == 1186: fmt = "duration" elif f[1] == 1043: fmt = "text" else: fmt = "character" SQLColumn(report=self, sequence=seq, name=f[0], format=fmt).save(using=db) seq += 1 finally: if conn: conn.close()
def _generate_spreadsheet_data(cls, request, out, report, *args, **kwargs): # Create a workbook wb = Workbook(write_only=True) ws = wb.create_sheet(title=report.name) # Create a named style for the header row readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle") readlonlyheaderstyle.fill = PatternFill(fill_type="solid", fgColor="d0ebfb") wb.add_named_style(readlonlyheaderstyle) # Run the query conn = None try: conn = create_connection(request.database) comment = CellComment(force_text(_("Read only")), "Author", height=20, width=80) with conn.cursor() as cursor: sqlrole = settings.DATABASES[request.database].get( "SQL_ROLE", "report_role") if sqlrole: cursor.execute("set role %s" % (sqlrole, )) cursor.execute(sql=cls.getSQL(report.sql)) if cursor.description: # Write header row header = [] for f in cursor.description: cell = WriteOnlyCell(ws, value=f[0]) cell.style = "readlonlyheaderstyle" cell.comment = comment header.append(cell) ws.append(header) # Add an auto-filter to the table ws.auto_filter.ref = "A1:%s1048576" % get_column_letter( len(header)) # Write all output rows for result in cursor.fetchall(): ws.append( [_getCellValue(i, request=request) for i in result]) # Write the spreadsheet wb.save(out) finally: if conn: conn.close()
def _generate_csv_data(cls, request, report, *args, **kwargs): sf = StringIO() decimal_separator = get_format("DECIMAL_SEPARATOR", request.LANGUAGE_CODE, True) if decimal_separator == ",": writer = csv.writer(sf, quoting=csv.QUOTE_NONNUMERIC, delimiter=";") else: writer = csv.writer(sf, quoting=csv.QUOTE_NONNUMERIC, delimiter=",") # Write a Unicode Byte Order Mark header, aka BOM (Excel needs it to open UTF-8 file properly) yield cls.getBOM(settings.CSV_CHARSET) # Run the query conn = None try: conn = create_connection(request.database) with conn.cursor() as cursor: sqlrole = settings.DATABASES[request.database].get( "SQL_ROLE", "report_role") if sqlrole: cursor.execute("set role %s" % (sqlrole, )) cursor.execute(sql=cls.getSQL(report.sql)) if cursor.description: # Write header row writer.writerow([f[0] for f in cursor.description]) yield sf.getvalue() # Write all output rows for result in cursor.fetchall(): # Clear the return string buffer sf.seek(0) sf.truncate(0) writer.writerow([ cls._getCSVValue(i, request=request, decimal_separator=decimal_separator) for i in result ]) yield sf.getvalue() except GeneratorExit: pass finally: if conn: conn.close()
def data_query(cls, request, *args, page=None, **kwargs): # Main query that will return all data records. # It implements filtering, paging and sorting. conn = None if not hasattr(request, "report"): request.report = ( SQLReport.objects.all().using(request.database).get(pk=args[0]) ) if request.report and request.report.sql: try: conn = create_connection(request.database) with conn.cursor() as cursor: sqlrole = settings.DATABASES[request.database].get( "SQL_ROLE", "report_role" ) if sqlrole: cursor.execute("set role %s" % (sqlrole,)) if not hasattr(request, "filter"): request.filter = cls.getFilter(request, *args, **kwargs) cursor.execute( "select * from (%s) t_subquery %s order by %s %s %s" % ( request.report.sql.replace("%", "%%"), "where %s" % request.filter[0] if request.filter[0] else "", cls._apply_sort_index(request), ("offset %s" % ((page - 1) * request.pagesize + 1)) if page and page > 1 else "", "limit %s" % request.pagesize if page else "", ), request.filter[1], ) for rec in cursor.fetchall(): result = {} idx = 0 for f in request.rows: result[f.name] = rec[idx] idx += 1 yield result finally: if conn: conn.close()
def _generate_json_data(cls, database, sql): conn = None try: conn = create_connection(database) with conn.cursor() as cursor: sqlrole = settings.DATABASES[database].get("SQL_ROLE", "report_role") if sqlrole: cursor.execute("set role %s" % (sqlrole,)) cursor.execute(sql=cls.getSQL(sql)) if cursor.description: counter = 0 columns = [] colmodel = [] for f in cursor.description: columns.append(f[0]) colmodel.append(cls.getColModel(f[0], f[1], counter)) counter += 1 yield """{ "rowcount": %s, "status": "ok", "columns": %s, "colmodel": %s, "data": [ """ % ( cursor.rowcount, json.dumps(columns), json.dumps(colmodel), ) first = True for result in cursor.fetchall(): if first: yield json.dumps( dict( zip( columns, [ i if i is None else i.total_seconds() if isinstance(i, timedelta) else str(i) for i in result ], ) ) ) first = False else: yield ",%s" % json.dumps( dict( zip( columns, [ i if i is None else i.total_seconds() if isinstance(i, timedelta) else str(i) for i in result ], ) ) ) yield "]}" elif cursor.rowcount: yield '{"rowcount": %s, "status": "Updated %s rows"}' % ( cursor.rowcount, cursor.rowcount, ) else: yield '{"rowcount": %s, "status": "Done"}' % cursor.rowcount except GeneratorExit: pass except Exception as e: yield json.dumps({"status": str(e)}) finally: if conn: conn.close()