def get_scheduled_data_docs_by_user(uid,
environment_id,
offset,
limit,
filters={},
session=None):
query = (
session.query(DataDoc, TaskSchedule).join(
TaskSchedule,
TaskSchedule.name == func.concat("run_data_doc_", DataDoc.id),
isouter=(not filters.get("scheduled_only", False)),
).filter(DataDoc.owner_uid == uid).filter(
DataDoc.archived == False) # noqa: E712
.filter(DataDoc.environment_id == environment_id).order_by(
DataDoc.id.desc()))
if "name" in filters:
query = query.filter(DataDoc.title.contains(filters.get("name")))
count = query.count()
docs_with_schedules = query.offset(offset).limit(limit).all()
docs_with_schedules_and_records = get_task_run_record_run_with_schedule(
docs_with_schedules, session=session)
return docs_with_schedules_and_records, count
class QualityEvent(Base):
__tablename__ = "quality_events"
quality_event_id = Column(Integer,
autoincrement=True,
nullable=False,
primary_key=True)
when = Column(DateTime, nullable=False, unique=True)
kind = Column(QualityEventType.db_type(), nullable=False)
description = Column(String, nullable=False, default="")
who_id = Column(Integer,
ForeignKey('employees.employee_id'),
nullable=False)
who = relationship(Employee,
primaryjoin=Employee.employee_id == who_id,
uselist=False)
order_part_id = Column(Integer,
ForeignKey(OrderPart.order_part_id),
nullable=False)
order_part = relationship(
OrderPart,
primaryjoin=OrderPart.order_part_id == order_part_id,
backref=backref('quality_events',
cascade="all, delete, delete-orphan"))
human_identifier = column_property(
select([
func.concat("NC-", OrderPart.human_identifier, "-",
quality_event_id)
]).where(OrderPart.order_part_id == order_part_id).correlate_except(
OrderPart))
def render(self):
self.request.response.setHeader('Content-Type', 'application/json')
session = Session()
from fernlehrgang import models
from sqlalchemy import func, or_, cast, String
print "*" * 20
print self.term
res = session.query(models.Teilnehmer).filter(or_(
cast(models.Teilnehmer.id, String).like(self.term+"%"),
cast(models.Teilnehmer.unternehmen_mnr, String).like(self.term+"%"),
func.concat(func.concat(models.Teilnehmer.name, " "),
models.Teilnehmer.vorname
).like("%" + self.term + "%")))
terms = []
for x in res:
terms.append({'id': x.id, 'text': "%s - %s %s - %s" %(x.id, x.name, x.vorname, x.unternehmen_mnr)})
return json.dumps({'q': self.term, 'results': terms})
def get_bounded_overall_compare_data_for_project_name(
self, project_name, data, test_id_min, test_id_max):
log.debug("Get bounded overall data for project_name: " +
project_name + ", test_id_min: " + str(test_id_min) +
"; test_id_max: " + str(test_id_max) + ";")
if data == 'agg_response_times':
stmt = select([
tests.c.start_time,
func.row_number().over(order_by=tests.c.start_time).label('rown'),
tests.c.display_name,
func.avg(aggregate.c.average).label('Average'),
func.avg(aggregate.c.median).label('Median')
]).where(tests.c.project == project_name) \
.where(aggregate.c.test_id == tests.c.id) \
.where(between(tests.c.id, int(test_id_min), int(test_id_max))) \
.group_by(tests.c.display_name) \
.group_by(tests.c.start_time) \
.order_by(asc(tests.c.start_time))
s = stmt.alias('s')
statement = select([
s.c.start_time,
func.concat(s.c.rown, '. ', s.c.display_name).label('Release'),
s.c.Average, s.c.Median
])
elif data == 'agg_cpu_load':
stmt = select([
tests.c.start_time,
tests.c.display_name.label('Release'),
tests_monitoring_data.c.server_name,
func.avg(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD')
]).where(tests.c.project == project_name) \
.where(tests_monitoring_data.c.test_id == tests.c.id) \
.where(between(tests.c.id, int(test_id_min), int(test_id_max))) \
.group_by(tests.c.display_name) \
.group_by(tests_monitoring_data.c.server_name) \
.group_by(tests.c.start_time) \
.order_by(asc(tests.c.start_time))
s = stmt.alias('s')
statement = select([s.c.start_time, s.c.server_name, s.c.CPU_LOAD])
return self.execute_statement(statement, True)
from sqlalchemy.sql.expression import func
user = "******"
password = "******"
host = "localhost"
port = 3306
db = "inet"
# setup engine and bound metadata
engine = create_engine("mysql+mysqlconnector://%s:%s@%s:%d/%s"
% (user, password, host, port, db))
metadata = MetaData(engine)
# reflect the needed tables
persons = Table("P_Person", metadata, autoload=True)
employee = Table("E_Employee", metadata, autoload=True)
employee_data = Table("ED_EmployeeData", metadata, autoload=True)
location = Table("LOC_Location", metadata, autoload=True)
employee_training = Table("ET_EmployeeTraining", metadata, autoload=True)
employee_training_status = Table("Value_EmployeeTrainingStatus", metadata, autoload=True)
# prepare the query from which to select the values
query = select([func.concat(persons.c.P_Name, ", ", persons.c.P_FirstNames)],
persons.c.P_ID==employee.c.E_P_ID).limit(1)
# create a update which takes the values from the query
update = employee.update().values(E_Login=query)
print str(update)
update.execute()
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.sql import select, update
from sqlalchemy.sql.expression import func
user = "******"
password = "******"
host = "localhost"
port = 3306
db = "inet"
# setup engine and bound metadata
engine = create_engine("mysql+mysqlconnector://%s:%s@%s:%d/%s"
% (user, password, host, port, db))
metadata = MetaData(engine)
# reflect the needed tables
persons = Table("p_person", metadata, autoload=True)
employee = Table("e_employee", metadata, autoload=True)
# prepare the query from which to select the values
query = select([func.lower(
func.concat(persons.c.P_Name, ".", persons.c.P_FirstNames))],
persons.c.P_ID==employee.c.E_P_ID).limit(1)
# create a update which takes the values from the query
update = employee.update().values(E_Login=query)
print str(update)
update.execute()
