def to_table(command, eq_list, keyword_list, prop_dct=None):
CONFIG = utils.load_yaml(utils.AUCTION_CONFIG)
special_cols = ['thread', 'link', 'date'
] # these have to be added last for formatting reasons
# special formatting
def format_stats(c):
c.max_width = CONFIG[command]['stat_col_width']
return c
def format_price(c):
c.data = [str(int_to_price(x)) for x in c.data]
return c
format_rules = dict(stats=format_stats, price=format_price)
# get cols
col_names = misc.get_col_names(
keyword_list=keyword_list,
default_cols=CONFIG[command]['default_cols'],
key_map=CONFIG['key_map'])
cols = misc.get_cols(data=eq_list,
special_cols=special_cols,
col_names=col_names,
format_rules=format_rules,
CONFIG=CONFIG)
# add date col
if 'date' in col_names:
data = []
for x in eq_list:
x['date'] = epoch_to_date(x['time'])
tmp = utils.render(CONFIG['date_template'], x)
data.append(tmp)
cols.append(Column(data=data, header=CONFIG['equip_headers']['date']))
# add link col
if 'link' in col_names:
cols.append(
Column(data=[x['link'] for x in eq_list],
header=CONFIG['equip_headers']['link'],
is_link=True))
# add thread col
if 'thread' in col_names:
cols.append(
Column(data=[x['thread'] for x in eq_list],
header=CONFIG['equip_headers']['thread'],
is_link=True))
# add attrs if requested
ret = Table(cols)
if prop_dct:
for x in prop_dct:
ret.__dict__[x] = prop_dct[x]
return ret
def __init__(self, name, columns, pk):
ncols = []
for col in columns:
if col != pk:
ncols.append(Column(col, BIGNAME))
else:
pkcol = Column(col, BIGNAME)
pkcol.constraint.pk = 1
ncols[0:0] = [pkcol]
Table.__init__(self, name, ncols)
class Difficulty(Base):
__tablename__ = tb_difficulty
id = Column(Integer, Sequence(difficulty_id), primary_key=True)
code = Column("code", String(10), nullable=False)
description = Column("description", Text)
sport_id = Column(Integer, ForeignKey("%s.id" % (tb_sport)))
sport = relationship("Sport")
# self representation
def __repr__(self):
return "<Difficulty (id = '%s', code='%s', sport='%s', description='%s')>" % (
self.id, self.code, self.sport.name, self.description)
# Query for all and return as panda
@classmethod
def queryAll(self):
return pd.read_sql_table(tb_difficulty, Engine, index_col="id")
# new Difficulty
@classmethod
def new():
print()
newD = Difficulty()
# Ask for SportID
newD.sport_id = selectSport()
# Ask for difficulty code
newD.code = input("Enter Difficulty Code: ")
# Ask for description
newD.description = input("Enter Description: ")
# Print what was entered
horizontalSeperator()
print("New Sport:\n%s" % (newD.name))
# Ask if User wants to save new sport
saveNewInput("sport", newD)
# Query for all and return as panda
@classmethod
def select(sport, self):
# Query from postgreSQL
dificulties = pd.read_sql_table(tb_difficulty, Engine, index_col="id")
# Panda comperision
difficultyBool = difficulty["sport_id"] == sport
difficulty = difficulty[difficultyBool]
#print dificulties for Sport
print(tabulate(difficulty, headers="keys", tablefmt="psql"))
#Ask for intput
selection = input("Select difficulty: ")
selection = int(selection)
return selection
class RouteRun(Base):
__tablename__ = tb_routes
id = Column(Integer, Sequence(route_id), primary_key=True)
name = Column("name", String(50), nullable=False)
location = Column("location", String(30), nullable=False)
distance = Column("distance", Float, nullable=False)
# self representation
def __repr__(self):
return "New Route for Running:\n%s\n%s\n%.2f" % (
newR.name, newR.location, newR.distance)
# Query for all and return as panda
@classmethod
def queryAll(self):
return readTableFromDB(tb_routes)
# new Route for running
@classmethod
def new(self):
print()
newR = RouteRun()
# Ask for Name of Route
newR.name = input("Enter Name of Route: ")
# Ask for Location
newR.location = input("Enter Location: ")
# Ask for distance of Route & convert distance to Float
newR.distance = convertStringToFloat(
input("Enter Distance of Route in km: "))
# Print what was entered
horizontalSeperator()
print("New Route for Running:\n%s\n%s\n%.2f" %
(newR.name, newR.location, newR.distance))
# Ask if User wants to save new route
saveNewInput("Route for Running", newR)
# selecting a Sport from the list in postgreSQL
@classmethod
def select(self):
routes = readTableFromDB(tb_routes)
print(tabulate(routes, headers="keys", tablefmt="psql"))
while True:
selection = input("Select Route: ")
selection = int(selection)
if selection in sports:
break
else:
invalidInput()
return selection
def get_summary_table(lst, CONFIG, name_key="name", price_key="price"):
# inits
groups = CONFIG['summary_groups']
header_dict = CONFIG['summary_headers']
# tallies
values, counts = {}, {}
for x in groups:
values[x] = 0
counts[x] = 0
unknown = [0, 0]
for x in lst:
matched = False
for y in groups:
for z in groups[y]:
if contains(to_search=x[name_key], to_find=z):
counts[y] += 1
values[y] += int(x[price_key])
matched = True
break
if not matched:
unknown[0] += 1
unknown[1] += int(x[price_key])
# sum tallies
total_count = sum(counts.values())
total_value = int_to_price(sum(values.values()))
# convert to lists
vals = [int_to_price(values[x]) for x in groups]
cnts = [counts[x] for x in groups]
if unknown[0]:
vals.append(int_to_price(unknown[1]))
cnts.append(unknown[0])
groups[header_dict['unknown']] = []
return Pprint.Table([
Column(data=groups,
header=header_dict['category'],
trailer=header_dict['total']),
Column(data=cnts,
header=header_dict['total_count'],
trailer=total_count),
Column(data=vals,
header=header_dict['total_credits'],
trailer=total_value),
])
class Sport(Base):
__tablename__ = tb_sport
id = Column(Integer, Sequence(sport_id), primary_key=True)
name = Column("Sport", String(30), nullable=False)
# self representation
def __repr__(self):
return "New Sport:\n%s" % (self.name)
# new Sport
@classmethod
def new(self):
print()
newSport = Sport()
# Ask for Name of Sport
newSport.name = input("Enter Name of Sport: ")
# Print what was entered
horizontalSeperator()
print("New Sport:\n%s" % (newSport.name))
# Ask if User wants to save new sport
saveNewInput("sport", newSport)
# Query for all and return as panda
@classmethod
def queryAll(self):
return readTableFromDB(tb_sport)
# selecting a Sport from the list in postgreSQL
@classmethod
def select(self):
print(tabulate(sports, headers="keys", tablefmt="psql"))
while True:
selection = input("Select Sport: ")
selection = int(selection)
if selection in sports:
break
else:
invalidInput()
return selection
# selecting a Sport from the list in postgreSQL
@classmethod
def selectName(self, ID):
selection = readTableFromDB(tb_sport)
selection = selection.iloc[ID - 1]
selection = selection['Sport']
return selection
def _get_equip_cols(percentiles, CONFIG):
# categorize
max_len = max(len(str(percentiles[x])) for x in percentiles)
cats = {c: [] for c in CONFIG['table_categories']}
for stat in percentiles:
abbrv = CONFIG['abbreviations'][stat] if stat in CONFIG[
'abbreviations'] else stat
# add stat to cat if mentioned in that cat, else "other"
for c in CONFIG['table_categories']:
if any(contains(stat, x) for x in CONFIG['table_categories'][c]):
cats[c].append((str(percentiles[stat]), abbrv))
break
else:
cats['other'].append((str(percentiles[stat]), abbrv))
# convert entries to strings
for c in cats:
if not cats[c]: continue
max_len = max(len(x[0]) for x in cats[c])
cats[c] = [f"{x[0].rjust(max_len)}% {x[1]}" for x in cats[c]]
# get table columns
tmp = []
for x, y in CONFIG['table_headers'].items():
if cats[x]:
tmp.append(dict(data=cats[x], header=y))
# ensure cols same length
max_len = max(len(x['data']) for x in tmp)
for x in tmp:
x['data'] += [""] * (max_len - len(x['data']))
cols = [Column(**x) for x in tmp]
return cols
class Country(Base):
__tablename__ = tb_country
id = Column(Integer, Sequence(country_id), primary_key=True)
name = Column("name", String(80), nullable=False)
code = Column("code", String(5), nullable=False)
# self representation
def __repr__(self):
return "<Country (id = '%s', name='%s', countrycode='%s')>" % (
self.id, self.name, self.code)
# Query for all and return as panda
@classmethod
def queryAll(self):
return readTableFromDB(tb_country)
def AutoId(name):
column = Column(name, NUM)
c = column.constraint
c.pk = 1
c.unique = 1
c.auto = 1
c.null = 0
return column
class TrackRun(Base):
__tablename__ = tb_tracksrun
id = Column(Integer, Sequence(trackrun_id), primary_key=True)
date_duration = Column('date_duration', DateTime, nullable=False)
route_id = Column(Integer, ForeignKey("%s.id" % (tb_routes)))
route = relationship("RouteRun")
# self representation
def __repr__(self):
return "<Running Track (id = '%s', date_duration='%s', pace='%s', speed='%s')>" % (
self.id, self.date_duration, self.pace(), self.speed())
# Query for all and return as panda
@classmethod
def queryAll(self):
return readTableFromDB(tb_tracksrun)
# Calculate pace
def pace(self):
distance = float(self.route.distance)
# Return pace in min/km
return round(
(self.date_duration.hour * 60 + self.date_duration.minute +
self.date_duration.second / 60) / distance, 3)
# Calculate speed
def speed(self):
# Return Speed in km/h
return round(60 / self.pace(), 2)
@classmethod
def new(self):
newT = TrackRun()
# Ask for Date
newT.date_duration = enterDateDuration()
# Ask for route
newT.route_id = RouteRun.select()
# Print what was entered
horizontalSeperator()
print("New Track for Running:\n%s" %
(newT.name, newT.date_duration, newT.route_id))
# Ask if User wants to save new running track
saveNewInput("Track for Running", newT)
class MountainType(Base):
__tablename__ = tb_mtype
id = Column(Integer, Sequence(mtype_id), primary_key=True)
name = Column("name", String(50), nullable=False)
# self representation
def __repr__(self):
return "<Mountain Type (id = '%s', name='%s')>" % (self.id, self.name)
# new Mountain Type
@classmethod
def new(self):
print()
newType = MountainType()
# Ask for Name of Route
newType.name = input("Enter Type of Mountain: ")
# Print what was entered
horizontalSeperator()
print("New Mountain Type:\n%s" % (newType.name))
# Ask if User wants to save new Monutain Type
saveNewInput("mountain type", newType)
# Query for all and return as panda
@classmethod
def queryAll(self):
return readTableFromDB(tb_mtype)
# selecting a Sport from the list in postgreSQL
@classmethod
def select(self):
mtype = readTableFromDB(tb_mtype)
print(tabulate(routes_in, headers="keys", tablefmt="psql"))
while True:
selection = input("Select Mountain Type: ")
selection = int(selection)
if selection in mtype:
break
else:
invalidInput()
return selection
def to_table(command, lst, keyword_list, prop_dct=None):
CONFIG = utils.load_yaml(utils.ITEM_CONFIG)
special_cols = ['date'
] # these have to be added last for formatting reasons
# special formatting
def format_stats(c):
c.max_width = CONFIG[command]['stat_col_width']
return c
def format_price(c):
c.data = [str(int_to_price(x)) for x in c.data]
return c
def format_quant(c):
c.data = [f"{int(x):,}" for x in c.orig_data]
return c
format_rules = dict(stats=format_stats,
price=format_price,
unit_price=format_price,
quantity=format_quant)
# get cols
col_names = misc.get_col_names(
default_cols=CONFIG[command]['default_cols'],
key_map=CONFIG['key_map'],
keyword_list=keyword_list)
cols = misc.get_cols(data=lst,
special_cols=special_cols,
col_names=col_names,
format_rules=format_rules,
CONFIG=CONFIG)
# add date col
if 'date' in col_names:
data = []
for x in lst:
x['date'] = epoch_to_date(x['time'])
tmp = utils.render(CONFIG['date_template'], x)
data.append(tmp)
cols.append(Column(data=data, header=CONFIG['equip_headers']['date']))
# add attrs if requested
ret = Table(cols)
if prop_dct:
for x in prop_dct:
ret.__dict__[x] = prop_dct[x]
# return
return ret
class Mountain(Base):
__tablename__ = tb_mountains
id = Column(Integer, Sequence(mountain_id), primary_key=True)
name = Column("name", String(80), nullable=False)
mrange = Column("mountainrange", String(50))
elevation = Column("elevation", Integer, nullable=False)
mtype_id = Column(Integer, ForeignKey("%s.id" % (tb_mtype)))
mtype = relationship("MountainType")
country_id = Column(Integer, ForeignKey("%s.id" % (tb_country)))
country = relationship("Country")
# self representation
def __repr__(self):
return "<Mountains (id = '%s', name='%s', country='%s', mountainrange='%s', elevation='%s', mountaintype='%s')>" % (
self.id, self.name, self.country.name, self.mrange, self.elevation,
self.mtype.name)
# Query for all and return as panda
@classmethod
def queryAll(self):
countries = readTableFromDB(tb_country)
mountains = readTableFromDB(tb_mountains)
mtype = readTableFromDB(tb_mtype)
mountains["mname"] = mountains.mtype_id.map(mtype.name.to_dict())
mountains["country"] = mountains.country_id.map(
countries.code.to_dict())
return mountains
# new Mountain
@classmethod
def new(self):
print()
newM = Mountain()
# Ask for Name of Route
newM.name = input("Enter Name of Summit: ")
# Ask for Location
newM.countrycode = input("Enter Countrycode: ")
# Ask for distance of Route
newM.mrange = input("Enter Mountainrange: ")
# Ask for distance of Route
newM.elevation = input("Enter Elevation: ")
# Ask for distance of Route
newM.mtype = input("Enter Mountaintype (Pass, Mountain, Vulcan): ")
# Print what was entered
horizontalSeperator()
print("New Mountain:\n%s" % (newM.name))
saveNewInput("mountain", newM)
def get_cols(data, special_cols, col_names, CONFIG, format_rules=None):
# inits
if format_rules is None: format_rules= {}
header_dict= CONFIG['equip_headers']
# @TODO: handle key-errors
# create columns
cols= []
for x in col_names:
if x in special_cols: continue # these are handled later
# create col
d= [eq[x] for eq in data]
c= Column(data=d, header=header_dict[x])
# special formatting
if x in format_rules:
c= format_rules[x](c)
cols.append(c)
return cols
class Weight(Base):
__tablename__ = tb_weight
id = Column(Integer, Sequence(weight_id), primary_key=True)
date = Column("date", Date, nullable=False)
weight = Column("weight", Float, nullable=False) # in kg
neck = Column("neck", Float, nullable=False) # in cm
waist = Column("waist", Float) # in cm
hip = Column("hip", Float) # in cm
user_id = Column(Integer, ForeignKey("%s.id" % (tb_users)))
user = relationship("User", back_populates="weights")
# self representation
def __repr__(self):
return "<Weight (username='******', date='%s', weight='%s', avgBodyfat='%.1f')>" % (
self.user.name, self.date, self.weight, self.bodyfat())
# Query for all and return as panda
@classmethod
def queryAll(self):
# create empty Dataframe
d = pd.DataFrame(columns=[
"date", "weight", "waist", "neck", "hip", "bf", "male", "height",
"user_id"
])
users = pd.DataFrame()
# Append postgreSQL Table
d = d.append(readTableFromDB(tb_weight), sort=False)
users = users.append(readTableFromDB(tb_users), sort=False)
# extract user height, male
d["male"] = d.user_id.map(users.male.to_dict())
d["height"] = d.user_id.map(users.height.to_dict())
# iterate through DataFrame
for index, row in d.iterrows():
bodyf = bodyfat(row['waist'], row['neck'], row['hip'],
row['height'], row['male'])
d.at[index, "bf"] = bodyf
return d
def Pk(name, type):
column = Column(name, type)
column.constraint.pk = 1
return column
def Oid(name):
return Column(name, OID)
def DateTime(name):
dt = Column(name, DATETIME)
dt.constraint.default = 'now()'
return dt
def Bigname(name):
return Column(name, BIGNAME)
class User(Base):
__tablename__ = tb_users
id = Column(Integer, Sequence(user_id), primary_key=True)
name = Column("name", String(50), nullable=False)
birthday = Column("birthday", Date, nullable=False)
male = Column("male", Boolean, nullable=False)
height = Column("height", Integer, nullable=False) # in cm
weights = relationship("Weight", back_populates="user")
# self representation
def __repr__(self):
return "New User:\nName: %s\nBirthday: %s\nMale: %s\nHeight: %s" % (
self.name, self.birthday, self.male, self.height)
# Query for all and return as panda
@classmethod
def queryAll(self):
return readTableFromDB(tb_users, Engine, parse_dates="birthday")
# Add new user
@classmethod
def new(self):
print()
newU = User()
# Ask for Name of User
newU.name = input("Enter Name of User: "******"Birthday of user %s: " % (newU.name))
newU.birthday = enterDate()
# Ask for male or female
while True:
i = input("Is the user female or male? (f/m): ")
if i == "f" or i == "F":
newU.male = False
break
elif i == "m" or i == "M":
newU.male = True
break
else:
print("Invalid Input! Try again!")
# Ask for heigt of user
newU.height = int(
input("Enter height of user %s (in cm): " % (newU.name)))
# Print what was entered
horizontalSeperator()
print("New User:\nName: %s\nBirthday: %s\nMale: %s\nHeight: %s cm" %
(newU.name, newU.birthday, newU.male, newU.height))
# Ask to save or delete
saveNewInput("User", newU)
pass
# selecting a User from the list in postgreSQL
@classmethod
def select(self):
users = readTableFromDB(tb_users, Engine, parse_dates="birthday")
print(tabulate(users, headers="keys", tablefmt="psql"))
while True:
selection = int(input("Select User: "******"Enter weight (in kg): "))
# Ask for neck circumference
newW.neck = convertStringToFloat(
input("Enter neck circumference (in cm): "))
# Ask for waist circumference
newW.waist = convertStringToFloat(
input("Enter waist circumference (in cm): "))
# Ask for hip circumference
## Check if user is male or female
if self.male == False:
newW.hip = convertStringToFloat(
input("Enter hip circumference (in cm): "))
else:
newW.hip = 0
# Print what was entered
horizontalSeperator()
if self.male == True:
print(
"New Measurements:\nuserID: %s\nDate: %s\nWeight: %.1f kg\nneck: %.1f cm\nwaist: %.1f cm"
%
(newW.user_id, newW.date, newW.weight, newW.neck, newW.waist))
else:
print(
"New Measurements:\nUserID: %s\nDate: %s\nWeight: %.1f kg\nNeck: %.1f cm\nWaist: %.1f cm\nHip: %.1f cm"
% (newW.user_id, newW.date, newW.weight, newW.neck, newW.waist,
newW.hip))
# Ask to save or delete
while True:
i = input("Do you want to save the new measurements (Y/N)? ")
if i == "J" or i == "j" or i == "y" or i == "Y":
self.weights.append(newW)
moveToDatabase(self)
break
elif i == "n" or i == "N":
break
else:
print("Invalid Input! Try again!")
pass
pass
def Num(name):
return Column(name, NUM)
def Name(name):
return Column(name, NAME)
def DefaultNamed(name, default):
column = Column(name, NAME)
column.constraint.default = default
return column
def Text(name):
return Column(name, TEXT)
def Bool(name, default=False):
column = Column(name, BOOL)
column.constraint.default = default
column.constraint.null = False
return column
class AlpineTrack(Base):
__tablename__ = tb_tracksalpine
id = Column(Integer, Sequence(trackalpine_id), primary_key=True)
date_duration = Column('date_duration', DateTime, nullable=False)
conditions = Column("conditions", Text)
summit = Column("Including Summit", Boolean, nullable=False)
distance = Column("distance", Float, nullable=False)
ascend = Column("ascend", Integer, nullable=False)
descend = Column("descend", Integer, nullable=False)
mountain_id = Column(Integer, ForeignKey("%s.id" % (tb_mountains)))
mountain = relationship("Mountain")
sport_id = Column(Integer, ForeignKey("%s.id" % (tb_sport)))
sport = relationship("Sport")
difficulty_id = Column(Integer, ForeignKey("%s.id" % (tb_difficulty)))
difficulty = relationship("Difficulty")
# self representation
def __repr__(self):
return "<Mountains (id = '%s', name='%s', country='%s', mountainrange='%s', elevation='%s', mountaintype='%s')>" % (
self.id, self.name, self.country.name, self.mrange, self.elevation,
self.mtype.name)
# Query for all and return as panda
@classmethod
def queryAll(self):
return readTableFromDB(tb_tracksalpine)
# new AlpineTrack
@classmethod
def new(self):
print()
newAT = AlpineTrack()
# Ask for Date & Duration
newAT.date_duration = enterDateDuration()
horizontalSeperator()
# Select mountain
newAT.mountain_id = selectMountain(Engine)
horizontalSeperator()
# Enter if Summit was climbed or not
while True:
i = input("Does this include the summit? (Y/N)? ")
if (i == "J" or i == "j" or i == "y" or i == "Y"):
# Summit was climbed
newAT.summit = True
break
elif (i == "N" or i == "n"):
# Summit was NOT climbed
newAT.summit = False
break
else:
horizontalSeperator(string="!")
print("%s is an invalid Option. Try again!" % option)
# Select Sport and Difficulty
newAT.sport_id = selectSport()
newAT.difficulty_id = selectDifficulty(sportID)
horizontalSeperator()
# Enter Conditions & Weather
newAT.conditions = input("How were the conditions & weather? ")
horizontalSeperator()
# Enter Horizontal Distance
newAT.distance = convertStringToFloat(
input("Enter the horizontal distance in km: "))
# Enter Meters of Ascend
newAT.ascend = int(input("Enter vertical meters ascend: "))
# Enter Meters of Descend
newAT.descend = int(input("Enter vertical meters descend: "))
# Print what was entered
horizontalSeperator()
print("New Alpine Tour:\n%s" % (newAT.name))
# Ask if User wants to save new alpine track
saveNewInput("Alpine Tour", newAT)
def PerformanceSpeed(self):
return ((self.ascend + self.descend) / 100 +
self.distance) / (self.date_duration.hour +
(self.date_duration.minute +
(self.date_duration.second) / 60) / 60)
class RelationalTable(Table):
def __init__(self, tablename, related_table, primary_column,
other_columns):
primary_column.set_fk(related_table)
Table.__init__(self, tablename, [primary_column] + other_columns)
class LogTable(Table):
def __init__(self, table):
if not issubclass(table.__class__, Table):
raise Error, 'need to pass a Table'
table = deepcopy(table)
#remove constraints from original table's columns
for col in table.columns:
col.constraint.clear()
table.columns += make_default_log_columns()
tablename = 'lp_log_' + table.name
Table.__init__(self, tablename, table.columns)
if __name__ == '__main__':
f = AutoId
c = Column('fname', NAME)
#t = Table()
dn = DefaultNamed('foo', 'bar')
ai = AutoId('dd')
cols = ['a', 'b', 'c', 'd']
pkt = PkBNameNullDefaults