Python app.db() Examples

def ping(service_id):
     
    from app import db, models
    from models import Service
    finding = Service.query.filter_by(serviceid=service_id).first()
    if finding is not None:
        image_name = finding.imagename
        uploadn = finding.uploadname
        usern = finding.username
        firstcreatetime = finding.firstcreatetime
        u = Service(serviceid = service_id, createdtime = str(time.time()), imagename = image_name, uploadname = uploadn, username = usern, firstcreatetime = firstcreatetime)
        db.session.add(u) 
        db.session.commit() 
        db.session.delete(finding)
        db.session.commit()
    else:
        return "The service "+service_id+" has been removed!"
    
    return "There are existing service:"+service_id 

def update_emails_to_remove_gsi(service_id):
    users_to_update = """SELECT u.id user_id, u.name, email_address, s.id, s.name
                           FROM users u
                           JOIN user_to_service us on (u.id = us.user_id)
                           JOIN services s on (s.id = us.service_id)
                          WHERE s.id = :service_id
                            AND u.email_address ilike ('%.gsi.gov.uk%')
    """
    results = db.session.execute(users_to_update, {'service_id': service_id})
    print("Updating {} users.".format(results.rowcount))

    for user in results:
        print('User with id {} updated'.format(user.user_id))

        update_stmt = """
        UPDATE users
           SET email_address = replace(replace(email_address, '.gsi.gov.uk', '.gov.uk'), '.GSI.GOV.UK', '.GOV.UK'),
               updated_at = now()
         WHERE id = :user_id
        """
        db.session.execute(update_stmt, {'user_id': str(user.user_id)})
        db.session.commit() 

def check_pdf_urls(pdf_urls):
    for url in pdf_urls:
        make_transient(url)

    # free up the connection while doing net IO
    safe_commit(db)
    db.engine.dispose()

    req_pool = get_request_pool()

    checked_pdf_urls = req_pool.map(get_pdf_url_status, pdf_urls, chunksize=1)
    req_pool.close()
    req_pool.join()

    row_dicts = [x.__dict__ for x in checked_pdf_urls]
    for row_dict in row_dicts:
        row_dict.pop('_sa_instance_state')

    db.session.bulk_update_mappings(PdfUrl, row_dicts)

    start_time = time()
    commit_success = safe_commit(db)
    if not commit_success:
        logger.info(u"COMMIT fail")
    logger.info(u"commit took {} seconds".format(elapsed(start_time, 2))) 

def add_pubs_or_update_crossref(pubs):
    if not pubs:
        return []

    pubs_by_id = dict((p.id, p) for p in pubs)

    existing_pub_ids = set([
        id_tuple[0] for id_tuple in db.session.query(Pub.id).filter(Pub.id.in_(pubs_by_id.keys())).all()
    ])

    pubs_to_add = [p for p in pubs if p.id not in existing_pub_ids]
    pubs_to_update = [p for p in pubs if p.id in existing_pub_ids]

    if pubs_to_add:
        logger.info(u"adding {} pubs".format(len(pubs_to_add)))
        db.session.add_all(pubs_to_add)

    if pubs_to_update:
        row_dicts = [{'id': p.id, 'crossref_api_raw_new': p.crossref_api_raw_new} for p in pubs_to_update]
        logger.info(u"updating {} pubs".format(len(pubs_to_update)))
        db.session.bulk_update_mappings(Pub, row_dicts)

    safe_commit(db)
    return pubs_to_add 

def populate_annual_billing(year):
    """
    add annual_billing for given year.
    """
    sql = """
        Select id from services where active = true
        except
        select service_id
        from annual_billing
        where financial_year_start = :year
    """
    services_without_annual_billing = db.session.execute(sql, {"year": year})
    for row in services_without_annual_billing:
        latest_annual_billing = """
            Select free_sms_fragment_limit
            from annual_billing
            where service_id = :service_id
            order by financial_year_start desc limit 1
        """
        free_allowance_rows = db.session.execute(latest_annual_billing, {"service_id": row.id})
        free_allowance = [x[0]for x in free_allowance_rows]
        print("create free limit of {} for service: {}".format(free_allowance[0], row.id))
        dao_create_or_update_annual_billing_for_year(service_id=row.id,
                                                     free_sms_fragment_limit=free_allowance[0],
                                                     financial_year_start=int(year)) 

def update_jobs_archived_flag(start_date, end_date):
    current_app.logger.info('Archiving jobs created between {} to {}'.format(start_date, end_date))

    process_date = start_date
    total_updated = 0

    while process_date < end_date:
        start_time = datetime.utcnow()
        sql = """update
                    jobs set archived = true
                where
                    created_at >= (date :start + time '00:00:00') at time zone 'Europe/London'
                    at time zone 'UTC'
                    and created_at < (date :end + time '00:00:00') at time zone 'Europe/London' at time zone 'UTC'"""

        result = db.session.execute(sql, {"start": process_date, "end": process_date + timedelta(days=1)})
        db.session.commit()
        current_app.logger.info('jobs: --- Completed took {}ms. Archived {} jobs for {}'.format(
            datetime.now() - start_time, result.rowcount, process_date))

        process_date += timedelta(days=1)

        total_updated += result.rowcount
    current_app.logger.info('Total archived jobs = {}'.format(total_updated)) 

def add_pmh_record(self, **kwargs):
        endpoint_id = kwargs.get("id", None)
        record_id = kwargs.get("recordid")
        my_repo = Endpoint.query.get(endpoint_id)
        print "my_repo", my_repo
        my_pmh_record = my_repo.get_pmh_record(record_id)
        my_pmh_record.mint_pages()

        # for my_page in my_pmh_record.pages:
        #     print "my_page", my_page
        #     my_page.scrape()
        my_pmh_record.delete_old_record()
        db.session.merge(my_pmh_record)
        # print my_pmh_record.pages

        safe_commit(db) 

def update_notification_international_flag():
    """
    DEPRECATED. Set notifications.international=false.
    """
    # 250,000 rows takes 30 seconds to update.
    subq = "select id from notifications where international is null limit 250000"
    update = "update notifications set international = False where id in ({})".format(subq)
    result = db.session.execute(subq).fetchall()

    while len(result) > 0:
        db.session.execute(update)
        print('commit 250000 updates at {}'.format(datetime.utcnow()))
        db.session.commit()
        result = db.session.execute(subq).fetchall()

    # Now update notification_history
    subq_history = "select id from notification_history where international is null limit 250000"
    update_history = "update notification_history set international = False where id in ({})".format(subq_history)
    result_history = db.session.execute(subq_history).fetchall()
    while len(result_history) > 0:
        db.session.execute(update_history)
        print('commit 250000 updates at {}'.format(datetime.utcnow()))
        db.session.commit()
        result_history = db.session.execute(subq_history).fetchall() 

def run_update(parsed_args):
    update = update_registry.get(parsed_args.fn)

    start = time()

    #convenience method for handling an orcid
    if parsed_args.orcid:
        from models.person import Person
        my_person = db.session.query(Person).filter(Person.orcid_id==parsed_args.orcid).first()
        parsed_args.id = my_person.id

    update.run(
        use_rq=parsed_args.rq,
        obj_id=parsed_args.id,  # is empty unless updating just one row
        min_id=parsed_args.after,  # is empty unless minimum id
        num_jobs=parsed_args.limit,
        chunk_size=parsed_args.chunk
    )

    db.session.remove()
    print "finished update in {}sec".format(elapsed(start)) 

def save_repo_request_rows(rows):

    with open('out.csv','wb') as f:

        w = csv.DictWriter(f, fieldnames=RepoRequest.list_fieldnames(), encoding='utf-8-sig')

        for row in rows[1:]:  # skip header row
            my_repo_request = RepoRequest()
            my_repo_request.set_id_seed(row[0])
            column_num = 0
            for fieldname in RepoRequest.list_fieldnames():
                if fieldname != "id":
                    setattr(my_repo_request, fieldname, row[column_num])
                    column_num += 1

            w.writerow(my_repo_request.to_dict())
            print u"adding repo request {}".format(my_repo_request)
            db.session.merge(my_repo_request)

        safe_commit(db) 

def add_all_new_packages(package_class):

    all_current_package_id_rows = db.session.query(package_class.id).all()
    all_current_package_ids = [row[0] for row in all_current_package_id_rows]

    all_names = package_class.get_all_live_package_names()

    for package_name in all_names:
        new_package = package_class(project_name=package_name)
        if new_package.id not in all_current_package_ids:
            print "\n\nadded new package:", new_package.id
            # new_package.refresh()
            db.session.add(new_package)
            safe_commit(db)

    print len(all_names) 

def backfill_notification_statuses():
    """
    DEPRECATED. Populates notification_status.

    This will be used to populate the new `Notification._status_fkey` with the old
    `Notification._status_enum`
    """
    LIMIT = 250000
    subq = "SELECT id FROM notification_history WHERE notification_status is NULL LIMIT {}".format(LIMIT)
    update = "UPDATE notification_history SET notification_status = status WHERE id in ({})".format(subq)
    result = db.session.execute(subq).fetchall()

    while len(result) > 0:
        db.session.execute(update)
        print('commit {} updates at {}'.format(LIMIT, datetime.utcnow()))
        db.session.commit()
        result = db.session.execute(subq).fetchall() 

def accuracy_report():
    reports = []
    subset_q = "select distinct input_batch_name from accuracy_from_mturk"
    subsets = get_sql_answers(db, subset_q)
    # subsets = ["articlelike_all_years"]

    for subset in subsets:
        reports.append(AccuracyReport(test_set=subset, no_rg_or_academia=True))
        reports.append(AccuracyReport(test_set=subset, genre='journal-article', no_rg_or_academia=True))
        reports.append(AccuracyReport(test_set=subset, since_2017=True, no_rg_or_academia=True))
        reports.append(AccuracyReport(test_set=subset, before_2008=True, no_rg_or_academia=True))

    for report in reports:
        report.build_current_report()

    return jsonify({"response": [report.to_dict() for report in reports]}) 

def clean_programs_timeout_to_compile_or_test():
    for _ in range(5):
        try:
            utils.console(
                'Try to clean programs timeout to compile or test...'
            )
            Program.clean_programs_which_timeout_to_compile_or_test()
            db.session.commit()
            return True
        except:
            utils.console('Exception catched, trying again in 2sec')
            print_exc()
            time.sleep(2)

    utils.console('Could not clean programs which failed to compile or test')
    return False 

def from_bq_overwrite_data(db_tablename, bq_tablename):
    temp_data_filename = 'data_export.csv'

    column_names = from_bq_to_local_file(temp_data_filename, bq_tablename, header=False)
    print "column_names", column_names
    print "\n"

    cursor = db.session.connection().connection.cursor()

    cursor.execute(u"truncate {};".format(db_tablename))

    with open(temp_data_filename, "rb") as f:
        cursor.copy_from(f, db_tablename, sep='\t', columns=column_names, null="")

    # this commit is necessary
    safe_commit(db)


# python bigquery_import.py --db pmh_record --bq pmh.pmh_record 

def after_request_stuff(resp):

    #support CORS
    resp.headers['Access-Control-Allow-Origin'] = "*"
    resp.headers['Access-Control-Allow-Methods'] = "POST, GET, OPTIONS, PUT, DELETE, PATCH"
    resp.headers['Access-Control-Allow-Headers'] = "origin, content-type, accept, x-requested-with"

    # remove session
    db.session.remove()

    # without this jason's heroku local buffers forever
    sys.stdout.flush()

    # log request for analytics
    log_request(resp)

    return resp 

def signup():
    from forms import SignupForm
   
    form = SignupForm()
    if form.validate_on_submit():
        user = User.query.filter_by(email=form.email.data.lower()).first()
        if user is not None:
            form.email.errors.append("The Email address is already taken.")
            return render_template('signup.html', form=form)

        newuser = User(form.firstname.data,form.lastname.data,form.email.data,form.password.data)
        db.session.add(newuser)
        db.session.commit()

        session['email'] = newuser.email
        return redirect(url_for('login'))
   
    return render_template('signup.html', form=form) 

def run_update(parsed_args):
    update = update_registry.get(parsed_args.fn)

    start = time()

    #convenience method for handling an doi
    if parsed_args.doi:
        from pub import Pub
        from util import clean_doi

        my_pub = db.session.query(Pub).filter(Pub.id==clean_doi(parsed_args.doi)).first()
        parsed_args.id = my_pub.id
        logger.info(u"Got database hit for this doi: {}".format(my_pub.id))

    update.run(**vars(parsed_args))

    db.session.remove()
    logger.info(u"finished update in {} secconds".format(elapsed(start))) 

def parse_update_optional_args(parser):
    # just for updating lots
    parser.add_argument('--limit', "-l", nargs="?", type=int, help="how many jobs to do")
    parser.add_argument('--chunk', "-ch", nargs="?", default=10, type=int, help="how many to take off db at once")
    parser.add_argument('--after', nargs="?", type=str, help="minimum id or id start, ie 0000-0001")
    parser.add_argument('--rq', action="store_true", default=False, help="do jobs in this thread")
    parser.add_argument('--order', action="store_true", default=True, help="order them")
    parser.add_argument('--append', action="store_true", default=False, help="append, dont' clear queue")
    parser.add_argument('--name', nargs="?", type=str, help="name for the thread")

    # just for updating one
    parser.add_argument('--id', nargs="?", type=str, help="id of the one thing you want to update")
    parser.add_argument('--doi', nargs="?", type=str, help="doi of the one thing you want to update")

    # parse and run
    parsed_args = parser.parse_args()
    return parsed_args 

def deleteImage(image_name):
    logging.info('Delete the image %s', image_name)
    try:
        docker_client = docker.from_env()
        registry_imagename = registry + '/' + image_name
        docker_client.images.remove(image=registry_imagename,force=True)
        image = models.Image.query.filter_by(imagename=image_name).first()
        db.session.delete(image)
        db.session.commit()
    except docker.errors.APIError as e:
        image = models.Image.query.filter_by(imagename = image_name).first()
        db.session.delete(image)
        db.session.commit()
        error_string = 'Unable to delete the image {}. \nReason: {}. Delete the record'.format(registry_imagename, str(e))
        logging.error(error_string)
        return error_string
    
    return None 

def run_update(parsed_args):
    update = update_registry.get(parsed_args.fn)

    start = time()

    #convenience method for handling an orcid
    if parsed_args.orcid:
        from models.person import Person
        my_person = db.session.query(Person).filter(Person.orcid_id==parsed_args.orcid).first()
        parsed_args.id = my_person.id

    update.run(
        use_rq=parsed_args.rq,
        obj_id=parsed_args.id,  # is empty unless updating just one row
        min_id=parsed_args.after,  # is empty unless minimum id
        num_jobs=parsed_args.limit,
        chunk_size=parsed_args.chunk
    )

    db.session.remove()
    print "finished update in {}sec".format(elapsed(start)) 

def removeServices(serviceid):
    logging.info('Remove the service %s', serviceid)
    
    try:
        docker_client = docker.from_env()
        docker_remove = docker_client.services.get(serviceid)
        docker_remove.remove()
        remove_ser = models.Service.query.all()
        for i in remove_ser:
            if (i.serviceid == serviceid):
                db.session.delete(i)
                db.session.commit()
                break
               
    except docker.errors.APIError as e:
        if e.status_code == 404:
            remove_ser = models.Service.query.all()
            for i in remove_ser:
                if (i.serviceid == serviceid):
                    db.session.delete(i)
                    db.session.commit()
                    break
        else:
            logging.error('Unable to remove the service %s. \nReason: %s', serviceid, str(e)) 

def make_shell_context():
    return dict(app=app, db=db, User=User) 

def make_shell_context():
    return dict(app=app, db=db, User=User, Follow=Follow, Role=Role,
                Permission=Permission, Post=Post, Comment=Comment) 

def extcloseaccess(pid, extaccess):
    """Close a connection determined by the PID"""
    #Call the external script
    process = Popen([config.OPEN_ACCESS_PATH, 
                    "db-close",
                    str(pid)], stdout=PIPE)

    (output, err) = process.communicate()
    exit_code = process.wait()
    
    if exit_code != 0:
        app.logger.error('External script return ' + str(exit_code))
        app.logger.error('Output message was' + str(output))
        return utils.response('ERROR: external script return ' + \
                               str(exit_code), 500)

    if output:
        # Transform the ouput on Dict
        try:
            output = eval(output)
        except:
            app.logger.error("Error on openaccess return: " + str(output))
            return utils.response('Openaccess script is broken', 400)

        if output["execution_status"] != "OK":
            app.logger.error("Error on openaccess return: " + str(output))
            return utils.response('ERROR: connection can not be closed.',
                                   200)

    # Set the exttargetaccess proxy_pid to 0
    extaccess.set_proxy_pid(0)

    try:
        db.session.commit()
    except exc.SQLAlchemyError as e:
        return utils.response('ERROR:  impossible to change the pid ' + \
                    'on extarget with pid: "' + pid + '" -> ' + e.message, 409)

    response = "Connection closed. Click to reopen."
    return utils.response(response, 200) 

def fix_notification_statuses_not_in_sync():
    """
    DEPRECATED.
    This will be used to correct an issue where Notification._status_enum and NotificationHistory._status_fkey
    became out of sync. See 979e90a.

    Notification._status_enum is the source of truth so NotificationHistory._status_fkey will be updated with
    these values.
    """
    MAX = 10000

    subq = "SELECT id FROM notifications WHERE cast (status as text) != notification_status LIMIT {}".format(MAX)
    update = "UPDATE notifications SET notification_status = status WHERE id in ({})".format(subq)
    result = db.session.execute(subq).fetchall()

    while len(result) > 0:
        db.session.execute(update)
        print('Committed {} updates at {}'.format(len(result), datetime.utcnow()))
        db.session.commit()
        result = db.session.execute(subq).fetchall()

    subq_hist = "SELECT id FROM notification_history WHERE cast (status as text) != notification_status LIMIT {}" \
        .format(MAX)
    update = "UPDATE notification_history SET notification_status = status WHERE id in ({})".format(subq_hist)
    result = db.session.execute(subq_hist).fetchall()

    while len(result) > 0:
        db.session.execute(update)
        print('Committed {} updates at {}'.format(len(result), datetime.utcnow()))
        db.session.commit()
        result = db.session.execute(subq_hist).fetchall() 

def __call__(self, func):
        # we need to call the flask with_appcontext decorator to ensure the config is loaded, db connected etc etc.
        # we also need to use functools.wraps to carry through the names and docstrings etc of the functions.
        # Then we need to turn it into a click.Command - that's what command_group.add_command expects.
        @click.command(name=self.name)
        @functools.wraps(func)
        @flask.cli.with_appcontext
        def wrapper(*args, **kwargs):
            return func(*args, **kwargs)

        command_group.add_command(wrapper)

        return wrapper 

def images():
    from app import db, models         
    images = models.Image.query.all()
    result = []
    part_line = {'imagename':'default','uploadname':'default','uploaduser':'default','comments':'default'}
    for i in images:
        part_line['imagename'] = i.imagename
        part_line['uploadname'] = i.uploadname
        part_line['uploaduser'] = i.uploaduser
        part_line['comments'] = i.comments
        result.append(part_line)
        part_line = {}
    return render_template('images.html',imagetables = result) 

def setting():
    from app.forms import SetForm
    
    form = SetForm()
    if form.validate_on_submit():
        servers = models.ServerIP.query.all()
        for server in servers:
            db.session.delete(server)
            db.session.commit()
        serverip = form.ip.data
        u = models.ServerIP(serverip = serverip)
        db.session.add(u)
        db.session.commit()
        return render_template('setting.html',form=form, succeed = True)
    return render_template('setting.html',form=form) 

def make_shell_context():
    return dict(app=app, db=db, User=User, Role=Role, Post=Post, \
                Follow=Follow, Permission=Permission, Admin=Admin)