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()
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()
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()
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'))