Example #1
0
class Mymysql(object):
    def __init__(self):
        print('连接数据库...')
        self.engine = create_engine(MYSQL_URL,
                                    pool_recycle=2400,
                                    pool_size=20,
                                    max_overflow=10)
        metadata = MetaData(self.engine)
        Session = sessionmaker(bind=self.engine)
        self.session = Session()
        self.Post_table = Table("posts", metadata,
                                autoload=True)  # autoload=True这个是关键

    def insert(self, arr, table):
        print('插入数据库...')
        try:
            self.session.execute(self.__dict__[table].insert(), arr)
            self.session.commit()
            print('数据已插入', table)
        except Exception as e:
            print('数据插入异常,正在回滚', e)
            self.session.rollback()
        finally:
            print('关闭session')
            self.session.close()

    def close(self):
        print('关闭数据库引擎...')
        self.engine.dispose()
Example #2
0
def push_data(data_in_dict):
    if not data_in_dict or len(data_in_dict) == 0:
        raise Exception('Empty_data_from_HA')
    connection_str = f'mysql+pymysql://{SETTINGS["Database"]["username"]}:{SETTINGS["Database"]["password"]}@{SETTINGS["Database"]["ip"]}:{SETTINGS["Database"]["port"]}/{SETTINGS["Database"]["base"]}'
    engine = create_engine(connection_str,
                           echo=SETTINGS['Database']['echo_debug'])
    Base.metadata.create_all(bind=engine)
    Session = sessionmaker(bind=engine, autocommit=False)
    session = Session()
    session.execute(sa_text('''Truncate table HAhosts'''))
    counter_insert = 0
    for h in data_in_dict:
        if h['svname'] in ('BACKEND', 'FRONTEND'):
            continue
        if h['pxname'] not in SETTINGS['HAproxy']['pxname_filter']:
            continue
        host1 = Host()
        host1.pxname = h['pxname']
        host1.svname = h['svname']
        host1.status = h['status']
        host1.scur = h['scur']
        host1.addr, host1.port = h['addr'].split(':')
        host1.algo = h['algo']
        session.add(host1)
        counter_insert += 1
    session.commit()
    logging.info(f'Inserted {counter_insert} lines')
    session.close()
Example #3
0
    def get(request):
        """Get a forced photometry request result from ZTF.

        Parameters
        ----------
        request : skyportal.models.FollowupRequest
            The request to retrieve ZTF forced photometry.
        """

        from ..models import (
            DBSession,
            FollowupRequest,
            FacilityTransaction,
            Allocation,
            Instrument,
        )

        Session = scoped_session(
            sessionmaker(bind=DBSession.session_factory.kw["bind"]))
        session = Session()

        req = (session.query(FollowupRequest).filter(
            FollowupRequest.id == request.id).one())

        instrument = (Instrument.query_records_accessible_by(
            req.requester).join(Allocation).join(FollowupRequest).filter(
                FollowupRequest.id == request.id).first())

        altdata = request.allocation.altdata

        if not altdata:
            raise ValueError('Missing allocation information.')

        keys = ['ra', 'dec', 'jdstart', 'jdend']

        content = req.transactions[-1].response["content"]
        df_request = pd.read_html(content)[0]
        if df_request.shape[0] == 0:
            raise ValueError(
                'Missing response from forced photometry service.')
        df_request.columns = df_request.columns.str.lower()
        if not set(keys).issubset(df_request.columns):
            raise ValueError(
                "RA, Dec, jdstart, and jdend required in response.")
        df_request = df_request.iloc[0]
        df_request = df_request.replace({np.nan: None})

        requestgroup = {
            "email": altdata["ipac_email"],
            "userpass": altdata["ipac_userpass"],
            "option": "All recent jobs",
            "action": "Query Database",
        }
        params = urllib.parse.urlencode(requestgroup)
        url = f"{ZTF_FORCED_URL}/cgi-bin/getForcedPhotometryRequests.cgi?{params}"

        r = requests.get(
            url,
            auth=HTTPBasicAuth(altdata['ipac_http_user'],
                               altdata['ipac_http_password']),
        )
        if r.status_code == 200:
            df_result = pd.read_html(r.text)[0]
            df_result.rename(inplace=True,
                             columns={
                                 'startJD': 'jdstart',
                                 'endJD': 'jdend'
                             })
            df_result = df_result.replace({np.nan: None})
            if not set(keys).issubset(df_result.columns):
                raise ValueError(
                    "RA, Dec, jdstart, and jdend required in response.")
            index_match = None
            for index, row in df_result.iterrows():
                if not all(
                    [np.isclose(row[key], df_request[key]) for key in keys]):
                    continue
                index_match = index
            if index_match is None:
                raise ValueError(
                    'No matching response from forced photometry service. Please try again later.'
                )
            else:
                row = df_result.loc[index_match]
                if row['lightcurve'] is None:
                    raise ValueError(
                        'Light curve not yet available. Please try again later.'
                    )
                else:
                    lightcurve = row['lightcurve']
                    dataurl = f"{ZTF_FORCED_URL}/{lightcurve}"
                    IOLoop.current().run_in_executor(
                        None,
                        lambda: commit_photometry(
                            dataurl,
                            altdata,
                            df_request,
                            req.id,
                            instrument.id,
                            request.requester.id,
                        ),
                    )
        else:
            req.status = f'error: {r.content}'

        transaction = FacilityTransaction(
            request=http.serialize_requests_request(r.request),
            response=http.serialize_requests_response(r),
            followup_request=req,
            initiator_id=req.last_modified_by_id,
        )

        session.add(transaction)
        session.commit()
Example #4
0
def commit_photometry(url, altdata, df_request, request_id, instrument_id,
                      user_id):
    """
    Commits ZTF forced photometry to the database

    Parameters
    ----------
    url : str
        ZTF forced photometry service data file location.
    altdata: dict
        Contains ZTF photometry api_token for the user
    df_request: pandas.DataFrame
        DataFrame containing request parameters (ra, dec, start jd, end jd)
    request_id : int
        FollowupRequest SkyPortal ID
    instrument_id : int
        Instrument SkyPortal ID
    user_id: int
        User SkyPortal ID
    """

    from ..models import (
        DBSession,
        FollowupRequest,
        Instrument,
        User,
    )

    Session = scoped_session(
        sessionmaker(bind=DBSession.session_factory.kw["bind"]))
    session = Session()

    try:
        request = session.query(FollowupRequest).get(request_id)
        instrument = session.query(Instrument).get(instrument_id)
        user = session.query(User).get(user_id)

        r = requests.get(
            url,
            auth=HTTPBasicAuth(altdata['ipac_http_user'],
                               altdata['ipac_http_password']),
        )
        df = ascii.read(r.content.decode(),
                        header_start=0,
                        data_start=1,
                        comment='#').to_pandas()

        df.columns = df.columns.str.replace(',', '')
        desired_columns = {
            'jd',
            'forcediffimflux',
            'forcediffimfluxunc',
            'diffmaglim',
            'zpdiff',
            'filter',
        }
        if not desired_columns.issubset(set(df.columns)):
            raise ValueError('Missing expected column')
        df['ra'] = df_request['ra']
        df['dec'] = df_request['dec']
        df.rename(
            columns={'diffmaglim': 'limiting_mag'},
            inplace=True,
        )
        df = df.replace({"null": np.nan})
        df['mjd'] = astropy.time.Time(df['jd'], format='jd').mjd
        df['filter'] = df['filter'].str.replace('_', '')
        df['filter'] = df['filter'].str.lower()
        df = df.astype({
            'forcediffimflux': 'float64',
            'forcediffimfluxunc': 'float64'
        })

        df['mag'] = df['zpdiff'] - 2.5 * np.log10(df['forcediffimflux'])
        df['magerr'] = 1.0857 * df['forcediffimfluxunc'] / df['forcediffimflux']

        snr = df['forcediffimflux'] / df['forcediffimfluxunc'] < 5
        df['mag'].loc[snr] = None
        df['magerr'].loc[snr] = None

        iszero = df['forcediffimfluxunc'] == 0.0
        df['mag'].loc[iszero] = None
        df['magerr'].loc[iszero] = None

        isnan = np.isnan(df['forcediffimflux'])
        df['mag'].loc[isnan] = None
        df['magerr'].loc[isnan] = None

        df = df.replace({np.nan: None})

        drop_columns = list(
            set(df.columns.values) - set([
                'mjd', 'ra', 'dec', 'mag', 'magerr', 'limiting_mag', 'filter'
            ]))

        df.drop(
            columns=drop_columns,
            inplace=True,
        )
        df['magsys'] = 'ab'

        data_out = {
            'obj_id': request.obj_id,
            'instrument_id': instrument.id,
            'group_ids': [g.id for g in user.accessible_groups],
            **df.to_dict(orient='list'),
        }

        from skyportal.handlers.api.photometry import add_external_photometry

        add_external_photometry(data_out, request.requester)

        request.status = "Photometry committed to database"
        session.add(request)
        session.commit()

        flow = Flow()
        flow.push(
            '*',
            "skyportal/REFRESH_SOURCE",
            payload={"obj_key": request.obj.internal_key},
        )
    except Exception as e:
        return log(f"Unable to commit photometry for {request_id}: {e}")
    finally:
        Session.remove()
metadata = MetaData(engine)
people = Table(
    'people',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('count', Integer),
)
Session = sessionmaker(bind=engine)
session = Session()
metadata.create_all(engine)

people_insert = people.insert().values(name='test', count=2)
print(str(people_insert))
session.execute(people_insert)
session.commit()

session.execute(people_insert, [{
    'name': 'jill',
    'count': 15
}, {
    'name': 'joe',
    'count': 10
}])
session.commit()
result = session.execute(select([people]))
for row in result:
    print(row)

# c 表示 name 是people資料表中的欄位(column)
result1 = session.execute(select([people]).where(people.c.name == 'jill'))