Python Session.execute方法代码示例

# 需要导入模块: from chellow.models import Session [as 别名]
# 或者: from chellow.models.Session import execute [as 别名]

#.........这里部分代码省略.........
                latest_hhdc_bill_date = 'No HHDC'
            else:
                hhdc_contract_name = hhdc_contract.name
                hhdc_account = era.hhdc_account
                latest_hhdc_bill_date = sess.query(Bill.finish_date) \
                    .join(Batch).filter(
                        Bill.start_date <= date, Bill.supply == supply,
                        Batch.contract == hhdc_contract).order_by(
                        Bill.finish_date.desc()).first()

                if latest_hhdc_bill_date is not None:
                    latest_hhdc_bill_date = hh_format(latest_hhdc_bill_date[0])

            channel_values = []
            for imp_related in [True, False]:
                for channel_type in CHANNEL_TYPES:
                    if era.find_channel(
                            sess, imp_related, channel_type) is None:
                        channel_values.append('false')
                    else:
                        channel_values.append('true')

            imp_avg_months = None
            exp_avg_months = None
            for is_import in [True, False]:
                if metering_type == 'nhh':
                    continue

                params = {
                    'supply_id': supply.id, 'year_start': year_start,
                    'year_finish': date,
                    'is_import': is_import}
                month_mds = tuple(
                    md[0] * 2 for md in sess.execute("""

    select max(hh_datum.value) as md
    from hh_datum join channel on (hh_datum.channel_id = channel.id)
        join era on (channel.era_id = era.id)
    where era.supply_id = :supply_id and hh_datum.start_date >= :year_start
        and hh_datum.start_date <= :year_finish
        and channel.channel_type = 'ACTIVE'
        and channel.imp_related = :is_import
    group by extract(month from (hh_datum.start_date at time zone 'utc'))
    order by md desc
    limit 3

    """, params=params))

                avg_months = sum(month_mds)
                if len(month_mds) > 0:
                    avg_months /= len(month_mds)
                    if is_import:
                        imp_avg_months = avg_months
                    else:
                        exp_avg_months = avg_months

            if (imp_avg_months is not None and imp_avg_months > 100) or \
                    (exp_avg_months is not None and exp_avg_months > 100):
                mandatory_hh = 'yes'
            else:
                mandatory_hh = 'no'

            imp_latest_supplier_bill_date = None
            exp_latest_supplier_bill_date = None
            for is_import in [True, False]:
                if is_import:

# 需要导入模块: from chellow.models import Session [as 别名]
# 或者: from chellow.models.Session import execute [as 别名]
def content(
        start_date, finish_date, supply_id, mpan_cores, is_zipped, user):
    if is_zipped:
        file_extension = ".zip"
    else:
        file_extension = ".csv"

    base_name = "hh_data_row_" + start_date.strftime("%Y%m%d%H%M") + \
        file_extension

    titles = ','.join('"' + v + '"' for v in (
        "Site Code", "Imp MPAN Core", "Exp Mpan Core", "Start Date",
        "Import ACTIVE", "Import ACTIVE Status", "Import REACTIVE_IMP",
        "Import REACTIVE_IMP Status", "Import REACTIVE_EXP",
        "Import REACTIVE_EXP Status", "Export ACTIVE",
        "Export ACTIVE Status", "Export REACTIVE_IMP",
        "Export REACTIVE_IMP Status", "Export REACTIVE_EXP",
        "Export REACTIVE_EXP Status")) + "\n"

    running_name, finished_name = chellow.dloads.make_names(base_name, user)

    if is_zipped:
        zf = zipfile.ZipFile(running_name, 'w')
    else:
        tmp_file = open(running_name, "w")
    sess = None
    try:
        sess = Session()
        supplies = sess.query(Supply).join(Era).filter(
            Era.start_date <= finish_date,
            or_(
                Era.finish_date == null(), Era.finish_date >= start_date),
            ).order_by(Era.supply_id, Era.start_date).distinct()
        if supply_id is not None:
            sup = Supply.get_by_id(sess, supply_id)
            supplies = supplies.filter(Era.supply == sup)

        if mpan_cores is not None:
            supplies = supplies.filter(
                or_(
                    Era.imp_mpan_core.in_(mpan_cores),
                    Era.exp_mpan_core.in_(mpan_cores)))

        if not is_zipped:
            tmp_file.write(titles)

        for supply in supplies:
            site, era = sess.query(
                Site, Era).join(Era.site_eras).filter(
                Era.supply == supply, Era.start_date <= finish_date,
                SiteEra.site_id == Site.id,
                or_(
                    Era.finish_date == null(),
                    Era.finish_date >= start_date),
                SiteEra.is_physical == true()).order_by(Era.id).first()

            outs = []

            for hh_start_date, imp_active, imp_active_status, \
                imp_reactive_imp, imp_reactive_imp_status, \
                imp_reactive_exp, imp_reactive_exp_status, \
                exp_active, exp_active_status, exp_reactive_imp, \
                exp_reactive_imp_status, exp_reactive_exp, \
                exp_reactive_exp_status in sess.execute("""
select hh_base.start_date, max(imp_active.value), max(imp_active.status),
    max(imp_reactive_imp.value), max(imp_reactive_imp.status),
    max(imp_reactive_exp.value), max(imp_reactive_exp.status),
    max(exp_active.value), max(exp_active.status),
    max(exp_reactive_imp.value), max(imp_reactive_imp.status),
    max(exp_reactive_imp.value), max(imp_reactive_exp.status)
from hh_datum hh_base
    join channel on hh_base.channel_id = channel.id
    join era on channel.era_id = era.id
    left join hh_datum imp_active
        on (imp_active.id = hh_base.id and channel.imp_related is true and
            channel.channel_type = 'ACTIVE')
    left join hh_datum imp_reactive_imp
        on (imp_reactive_imp.id = hh_base.id
            and channel.imp_related is true and
            channel.channel_type = 'REACTIVE_IMP')
    left join hh_datum imp_reactive_exp
        on (imp_reactive_exp.id = hh_base.id
            and channel.imp_related is true and
            channel.channel_type = 'REACTIVE_EXP')
    left join hh_datum exp_active
        on (exp_active.id = hh_base.id and channel.imp_related is false and
            channel.channel_type = 'ACTIVE')
    left join hh_datum exp_reactive_imp
                on (exp_reactive_imp.id = hh_base.id
                    and channel.imp_related is
                false and channel.channel_type = 'REACTIVE_IMP')
    left join hh_datum exp_reactive_exp
                on (exp_reactive_exp.id = hh_base.id
                and channel.imp_related is false
                and channel.channel_type = 'REACTIVE_EXP')
where supply_id = :supply_id
    and hh_base.start_date between :start_date and :finish_date
group by hh_base.start_date
order by hh_base.start_date
    """, params={
#.........这里部分代码省略.........

# 需要导入模块: from chellow.models import Session [as 别名]
# 或者: from chellow.models.Session import execute [as 别名]
def content(contract_id, end_year, end_month, months, user):
    caches = {}
    sess = f = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            'displaced.csv', user)
        f = open(running_name, mode='w', newline='')
        writer = csv.writer(f, lineterminator='\n')
        titles = [
            'Site Code', 'Site Name', 'Associated Site Ids', 'From', 'To',
            'Gen Types', 'CHP kWh', 'LM kWh', 'Turbine kWh', 'PV kWh']

        finish_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) + \
            relativedelta(months=1) - HH

        start_date = Datetime(end_year, end_month, 1, tzinfo=pytz.utc) - \
            relativedelta(months=months-1)

        forecast_date = chellow.computer.forecast_date()

        contract = Contract.get_supplier_by_id(sess, contract_id)
        sites = sess.query(Site).join(SiteEra).join(Era).join(Supply). \
            join(Source).filter(
                or_(Era.finish_date == null(), Era.finish_date >= start_date),
                Era.start_date <= finish_date,
                or_(
                    Source.code.in_(('gen', 'gen-net')),
                    Era.exp_mpan_core != null())).distinct()
        bill_titles = chellow.computer.contract_func(
            caches, contract, 'displaced_virtual_bill_titles', None)()

        for title in bill_titles:
            if title == 'total-msp-kwh':
                title = 'total-displaced-msp-kwh'
            titles.append(title)
        writer.writerow(titles)

        for site in sites:
            month_start = start_date
            month_finish = month_start + relativedelta(months=1) - HH
            while not month_finish > finish_date:
                for site_group in site.groups(
                        sess, month_start, month_finish, True):
                    if site_group.start_date > month_start:
                        chunk_start = site_group.start_date
                    else:
                        chunk_start = month_start
                    if site_group.finish_date > month_finish:
                        chunk_finish = month_finish
                    else:
                        chunk_finish = site_group.finish_date

                    displaced_era = chellow.computer.displaced_era(
                        sess, site_group, chunk_start, chunk_finish)
                    if displaced_era is None:
                        continue
                    supplier_contract = displaced_era.imp_supplier_contract
                    if contract is not None and contract != supplier_contract:
                        continue

                    linked_sites = ','.join(
                        a_site.code for a_site in site_group.sites
                        if not a_site == site)
                    generator_types = ' '.join(
                        sorted(
                            [
                                supply.generator_type.code for supply in
                                site_group.supplies
                                if supply.generator_type is not None]))
                    vals = [
                        site.code, site.name, linked_sites,
                        hh_format(chunk_start), hh_format(chunk_finish),
                        generator_types]

                    total_gen_breakdown = {}

                    results = iter(
                        sess.execute(
                            "select supply.id, hh_datum.value, "
                            "hh_datum.start_date, channel.imp_related, "
                            "source.code, generator_type.code as "
                            "gen_type_code from hh_datum, channel, source, "
                            "era, supply left outer join generator_type on "
                            "supply.generator_type_id = generator_type.id "
                            "where hh_datum.channel_id = channel.id and "
                            "channel.era_id = era.id and era.supply_id = "
                            "supply.id and supply.source_id = source.id and "
                            "channel.channel_type = 'ACTIVE' and not "
                            "(source.code = 'net' and channel.imp_related "
                            "is true) and hh_datum.start_date >= "
                            ":chunk_start and hh_datum.start_date "
                            "<= :chunk_finish and "
                            "supply.id = any(:supply_ids) order "
                            "by hh_datum.start_date, supply.id",
                            params={
                                'chunk_start': chunk_start,
                                'chunk_finish': chunk_finish,
                                'supply_ids': [
                                    s.id for s in site_group.supplies]}))
#.........这里部分代码省略.........

# 需要导入模块: from chellow.models import Session [as 别名]
# 或者: from chellow.models.Session import execute [as 别名]
    def run(self):
        sess = None
        try:
            sess = Session()
            self._log(
                "Starting to parse the file with '" + self.parser_name + "'.")
            set_read_write(sess)
            batch = Batch.get_by_id(sess, self.batch_id)
            raw_bills = self.parser.make_raw_bills()
            self._log(
                "Successfully parsed the file, and now I'm starting to "
                "insert the raw bills.")
            for self.bill_num, raw_bill in enumerate(raw_bills):
                try:
                    with sess.begin_nested():
                        sess.execute(
                            "set transaction isolation level serializable "
                            "read write")
                        bill_type = BillType.get_by_code(
                            sess, raw_bill['bill_type_code'])
                        bill = batch.insert_bill(
                            sess, raw_bill['account'], raw_bill['reference'],
                            raw_bill['issue_date'], raw_bill['start_date'],
                            raw_bill['finish_date'], raw_bill['kwh'],
                            raw_bill['net'], raw_bill['vat'],
                            raw_bill['gross'],
                            bill_type, raw_bill['breakdown'])
                        sess.flush()
                        for raw_read in raw_bill['reads']:
                            tpr_code = raw_read['tpr_code']
                            if tpr_code is None:
                                tpr = None
                            else:
                                tpr = Tpr.get_by_code(sess, tpr_code)

                            prev_type = ReadType.get_by_code(
                                sess, raw_read['prev_type_code'])
                            pres_type = ReadType.get_by_code(
                                sess, raw_read['pres_type_code'])
                            bill.insert_read(
                                sess, tpr, raw_read['coefficient'],
                                raw_read['units'], raw_read['msn'],
                                raw_read['mpan'], raw_read['prev_date'],
                                raw_read['prev_value'], prev_type,
                                raw_read['pres_date'], raw_read['pres_value'],
                                pres_type)
                        self.successful_bills.append(raw_bill)
                except BadRequest as e:
                    raw_bill['error'] = str(e.description)
                    self.failed_bills.append(raw_bill)

            if len(self.failed_bills) == 0:
                sess.commit()
                self._log(
                    "All the bills have been successfully loaded and attached "
                    "to the batch.")
            else:
                sess.rollback()
                self._log(
                    "The import has finished, but there were " +
                    str(len(self.failed_bills)) + " failures, and so the "
                    "whole import has been rolled back.")

        except:
            sess.rollback()
            self._log("I've encountered a problem: " + traceback.format_exc())
        finally:
            if sess is not None:
                sess.close()