def get_last_year_prcp(date):
#Sample execution http://127.0.0.1:5000/api/v1.0/tobs/20160101
try:
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
Base = automap_base()
Base.prepare(engine, reflect=True)
Measurement = Base.classes.measurement
Station = Base.classes.station
session = Session(engine)
fmt_date = datetime.datetime(int(date[0:4]), int(date[4:6]),
int(date[6:8]))
print(fmt_date)
end_date = fmt_date - datetime.timedelta(days=365)
print(end_date)
result_list = []
for row in session.query(Measurement.date, Measurement.station,
Measurement.tobs).filter(
Measurement.date.between(
end_date, fmt_date)).all():
result = {}
result[row[0]] = {"Station": row[1], "TOBS": row[2]}
result_list.append(result)
session.close_all()
return (jsonify(result_list), 200)
except Exception:
return ("", 404)
def get_precipitation(date):
#Sample execution http://127.0.0.1:5000/api/v1.0/precipitation/20170801
try:
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
Base = automap_base()
Base.prepare(engine, reflect=True)
Measurement = Base.classes.measurement
Station = Base.classes.station
session = Session(engine)
fmt_date = datetime.datetime(int(date[0:4]), int(date[4:6]),
int(date[6:8]))
print(fmt_date)
end_date = fmt_date + datetime.timedelta(days=1)
print(end_date)
result_list = []
#for row in session.query(Measurement.station, Measurement.prcp).filter(Measurement.date == fmt_date).all():
for row in session.query(Measurement.station, Measurement.prcp).filter(
Measurement.date.between(fmt_date, end_date)).all():
result = {}
result[row[0]] = row[1]
result_list.append(result)
session.close_all()
return (jsonify(result_list))
except Exception:
return ("", 404)
def get_temp_status(start):
#Sample execution http://127.0.0.1:5000/api/v1.0/20160101
try:
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
Base = automap_base()
Base.prepare(engine, reflect=True)
Measurement = Base.classes.measurement
Station = Base.classes.station
session = Session(engine)
start_date = datetime.datetime(int(start[0:4]), int(start[4:6]),
int(start[6:8]))
print(start_date)
result = {}
for row in session.query(func.min(Measurement.tobs),
func.avg(Measurement.tobs),
func.max(Measurement.tobs)).filter(
Measurement.date >= start_date).all():
print(row[0], row[1], row[2])
result = {
"Duration Begin Date": start_date,
"TimeFrame Minimum Temperature": row[0],
"TimeFrame Average Temperature": row[1],
"TimeFrame Maximum Temperature": row[2]
}
session.close_all()
return (jsonify(result), 200)
except Exception as e:
print(type(e).__name__)
return ("", 404)
def delete_tables(engine: Engine, session: Session): # noqa
print("\nRemoving Tables...\n")
session.close_all()
with contextlib.closing(engine.connect()) as con:
trans = con.begin()
for table in reversed(Base.metadata.sorted_tables):
con.execute(table.delete())
for table in reversed(Base.metadata.sorted_tables):
con.execute(
f"TRUNCATE TABLE public.{table.name} RESTART IDENTITY CASCADE;"
)
trans.commit()
print("\nTables removed")
def get_station_list():
#Sample execution http://127.0.0.1:5000/api/v1.0/stations
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
Base = automap_base()
Base.prepare(engine, reflect=True)
Measurement = Base.classes.measurement
Station = Base.classes.station
session = Session(engine)
result_list = []
for row in session.query(Station.station, Station.name).all():
result = [row[0], row[1]]
result_list.append(result)
session.close_all()
return (jsonify(result_list))
def test_integrate(self):
Director = self.classes.Director
Movie = self.classes.Movie
session = Session(testing.db)
rscott = Director(name=u"Ridley Scott")
alien = Movie(title=u"Alien")
brunner = Movie(title=u"Blade Runner")
rscott.movies.append(brunner)
rscott.movies.append(alien)
session.add_all([rscott, alien, brunner])
session.commit()
session.close_all()
d = session.query(Director).options(subqueryload("*")).first()
assert len(list(session)) == 3
class DatabaseClass:
""" Generic class for instantiating a python database, regardless of database provider
Methods
-------
connectDb(newDatabase = False)
Connect to a database according to the configuration file used.
"""
def __init__(self, section, conf):
"""
Parameters
----------
section: section of .ini configuration archive
conf: name of .ini configuration archive
"""
try:
self.__conf = conf
self.__ip = self.__conf.ReadConfigFile(section, 'ip')
self.__port = self.__conf.ReadConfigFile(section, 'port')
self.__instancename = self.__conf.ReadConfigFile(
section, 'instancename')
self.__username = self.__conf.ReadConfigFile(section, 'username')
self.__password = self.__conf.ReadConfigFile(section, 'password')
self.__driver = self.__conf.ReadConfigFile(section, 'driver')
except Exception as e:
generalExceptionTreatment(e, "Failed to create Database object")
def connectDb(self, newDatabase=False):
""" Connect to a database according to the configuration file used.
Parameters
----------
newDatabase == false -> Existing Database Reflection
newDatabase == true -> Creating schema according Base derivated class on project
"""
try:
if (self.__driver == DbDriverType.postgresql.name):
self.driver = DbDriverType.postgresql.value
elif (self.__driver == DbDriverType.mysql.name):
self.driver = DbDriverType.mysql.value
elif (self.__driver == DbDriverType.oracle.name):
self.driver = DbDriverType.oracle.value
elif (self.__driver == DbDriverType.sqlserver.name):
self.driver = DbDriverType.sqlserver.value
conStr = "%s://%s:%s@%s/%s" % (self.driver, self.__username,
self.__password, self.__ip,
self.__instancename)
self.__engine = create_engine(conStr)
if not database_exists(self.__engine.url):
create_database(self.__engine.url)
else:
pass
if newDatabase:
Base.metadata.create_all(self.__engine)
Base.prepare(self.__engine, reflect=True)
self.session = Session(self.__engine)
return Status.initializing
except Exception as e:
generalExceptionTreatment(e, "Failed to connect to Database")
return Status.dbConnFailed
def closeSession(self):
""" Discconect and cloese session of that database instance
"""
try:
self.session.close_all()
except Exception as e:
generalExceptionTreatment(e, "Failed to connect to Database")
raise e
def tearDown(self):
Session.close_all()
self.drop_tables()
def teardown(self):
Session.close_all()
clear_mappers()
Base.metadata.drop_all()
def teardown(self):
Session.close_all()
clear_mappers()
Base.metadata.drop_all()
class MysqlData(BaseApi):
"""
使用sqlalchemy、automap连接现有数据库查询数据
"""
def __init__(self, dbname, dbconfig):
self.uri = 'mysql+pymysql://' + dbconfig['user'] + ':' + dbconfig['password'] + \
'@' + dbconfig['host'] + ':' + str(dbconfig['port']) + '/' + dbname + '?charset=utf8mb4'
self.engine = create_engine(self.uri, echo=False)
self.Base = automap_base()
self.Base.prepare(self.engine, reflect=True)
self.Base.classes.keys()
self.session = Session(self.engine)
def close_session(self):
"""关闭当前的session"""
self.session.close_all()
def get_table(self, table_name):
"""获取table"""
table = self.Base.classes.__getitem__(key=table_name)
return table
def find(self, table, **kwargs):
"""单条查询"""
#agent_num='123456'
result = self.session.query(table).filter_by(**kwargs).first()
return result
def find_all(self, table, *criterion):
"""多条查询"""
#agent_num=='123456'
result = self.session.query(table).filter(*criterion).all()
return result
def find_count(self, table, *criterion):
"""获取条件查询后的list长度"""
result = self.session.query(table).filter(*criterion).count()
return result
def get_sub_agent_list(self, agent_num):
table = self.get_table('agent')
my_agent_list_1 = []
my_agent_list_2 = []
my_agent_list_3 = []
for agent in self.session.query(table).filter(table.p_num == agent_num).all():
my_agent_list_1.append(agent.agent_num)
for agent in self.session.query(table).filter(table.p_num.in_(my_agent_list_1)).all():
my_agent_list_2.append(agent.agent_num)
for agent in self.session.query(table).filter(table.p_num.in_(my_agent_list_2)).all():
my_agent_list_3.append(agent.agent_num)
sub_agent_list = my_agent_list_1 + my_agent_list_2 + my_agent_list_3
return sub_agent_list
def get_merchant_photo_url(self, table, merchant_num, code):
data = self.find(table, code=code, merchant_num=merchant_num)
if data == None:
photo_url = ''
else:
photo_url = data.photo_url
return photo_url
def get_chk_status(self, chk_section, merchant_num):
"""返回商户每个部分的资料状态"""
merchant_table = self.get_table('merchants')
merchant_details_data = self.get_table('merchant_details')
status = 0
if chk_section == 'contact_chk_status':
key = self.find(merchant_details_data, merchant_num=merchant_num)
key = key.contact_name
elif chk_section == 'leader_chk_status':
key = self.find(merchant_table, merchant_num=merchant_num)
key = key.leader_name
elif chk_section == 'base_chk_status':
key = self.find(merchant_table, merchant_num=merchant_num)
key = key.full_name
elif chk_section == 'bank_chk_status':
key = self.find(merchant_details_data, merchant_num=merchant_num)
key = key.bank_account_no
else:
key = ''
if key != '':
status = 20
return status
def get_areaname_by_code_merchant(self, table, area: list):
"""返回area对应的name"""
result = ''
for code in area:
name = self.find(table, code=code)
if name != None:
name = name.alias
result = result + name + '-'
return result[:-1]
def get_id_type_name(self, type):
"""获取证件的name"""
name = ''
if type == 1:
name = '身份证'
elif type == 2:
name = '港澳台通行证'
elif type == 3:
name = '台湾身份证'
elif type == 4:
name = '香港身份证'
elif type == 5:
name = '澳门身份证'
elif type == 9:
name = '其它'
return name
def get_photo_code_list(self, data_list):
code_list = []
if data_list != None:
for i in data_list:
code_list.append(i.code)
return code_list
