database.database

Database class …

class database.database(config)

This is the main database handler class. As soon as an object of this class is initialized python automatically (tries) to connect to the database. The database class offers a bunch of methods to get/write data into the databae. Not all commands are in this class, some SQL statements are defined within the other classes and methods. Please see :py:meth:utils.readconfig for more details about the config object.

Parameters:config (utils.readconfig()) – Database handler

all_tournament_dates(cityID=None)

Returns all defined tournament dates ever payed in a city. Searches for all unique tournament dates in the bets table and returns them as a list.

Parameters:

• cityID (int) – ID of the city. If NONE all dates of all
• cities (unique) –

Returns:

A list wil be returned containing a set of integer values where each element represents one tournament played for the city. Dates in days since 1970-01-01.

Return type:

list

check_if_table_exists(table)

Checks wheter a table exists in the database. Note that the table name is the table name without prefix! For example: if you wanna check whether wp_users exists the the variable tablename has to be ‘users’ only. The prefix is used as specified in the config file.

Parameters:table (str) – Table name without prefix. Returns:True if table exists, False if not. Return type:bool

close(verbose=False)

Simple wrapper around MySQLdb.close.

Parameters:

• verbose (bool) – If set to True a small statement will
• printed to stdout. (be) –

commit()

Simple wrapper to MySQL.commit().

compute_stats(cityID, measures, userID=False, tdate=False, day=0, last_tdate=None, referenz=True, mitteltips=True, aliases=None, pout=25, pmid=100, midyear=2010, span=None, dates=None, verbose=False)

Computes all kinds of statistics e.g. the points for the eternal list plus min/max/median/mean of points for each user, city, tdate and so on.

create_group(name, desc)

Checks and/or creates group. Checks the Wetterturnier database table groups and checks if the group is already existing. If existing, the job of this method is done. Else a new group will be created. Used for the migration of the groups in the old Wetterturnier archive.

Parameters:

• name (str) – Group name.
• desc (str) – Group description.

create_groupuser(user, group, since, active)

Adds an existing user to an existing group. If the user is already an actie member of the group: don’t add him/her again.

Parameters:

• user (str) – User name.
• group (str) – Group name.
• since (datetime) – Usually current time. Add user to a group. the user was a member of the group.
• active (id) – If user is an actie member of the group or not. Typically 1 as you are adding the user in that split second.

create_user(name, password=None, email=None)

Check and/or create new Wetterturnier user. Creates a new user. If user already exists, the job of this method is done. Return. If not, create new user.

Please note: if a user does not exist this script creates a small php script and executes the php via console. This php script creates the new user via wordpress API.

As we are using wordpress we have to take care how we are creating users. To be on the save side I am using the wordpress internal function to add a user. Therefore a temporary php file will be created containing the necessary lines of code calling wp_create_user (in php) to add the user. The php file then will be called using the local php interpreter. After doing that the script is using @ref database.get_user_id to check if the user was added propperly. If not: stop.

Parameters:

• name (str) – User name.
• password (str, optional) – If set to None the user wont get a password (actually
• have to set a password, in this case the user password is just its user name (I) –
• E.g., Reto gets paswort oteR. (backwards.) –
• email (str) – Email address. If set to None we create pseudo-email (user@nomail.com)

current_tournament(verbose=False, status=1)

Returns tdate for current tournament. The tdate is the number of days since 1970-01-01. Loading the max(tdate) from the dates table which is smaller than the current date (utcnow).

Returns:Integer date (days since 1970-01-01) Return type:int

Todo

Reto just take care of the idea that we cold start two tournaments in a row. Can this method then handle the requests?

cursor()

Simple wrapper to MySQL.cursor

Returns:Object, database cursor. Return type:MySQLdb.curser

delete_bet(userID, cityID, tdate)

delete bet and the corresponding betdata for a user in a city/tdate TODO: proper docstring

execute(sql)

Simple wrapper to MySQL.execute

Parameters:sql (string) – SQL statement to be executed. Returns:Return from MySQLdb.execute. Return type:tuple

executemany(sql, data)

Simple wrapper to MySQL.executemany.

Parameters:

• sql (str) – MySQL query statement.
• data (tuple or list) – Object to be forwarded to MySQL.executemany.

Returns:

Return from MySQLdb.executemany().

find_missing_bets(cityID, tdate=False)

Find incomplete bets for a given tdate and cityID

find_missing_obs(cityID, tdate=False)

Check whether too many obs are missing for a given city (and tdate) to compute Moses. Returns True if obs are missing and False if everything is OK.

get_all_users(typ='ID')

Returns all user IDs in database :param user: User name. :type user: str

Returns:False if user does not exist, else the integer user ID. Return type:bool or int

get_bet_data(typ, ID, cityID, paramID, tdate, bdate)

Returns bet data used to compute e.g., Petrus. Returns a set of bets for a given city, parameter, and bet date for a specified tournament date. Note: has different modes.

Parameters:

• typ (str) – If typ == 'all': all users (human forecasters, automated forecasters and group bets) EXCLUDING the Sleepy will be returned. If type == 'user' the bet of a specific user will be returned. If type == 'group' the bet of a specific group will be returned. User or group (if type == ‘user’ or ‘group’) are defined by the input argument ID which is the userID or the groupID.
• typ – Any of type 'all', 'user', or 'group'.
• ID (int) – Ignored when type = 'all'. Else the input has to be of type ineger defining the userID (for type = 'user') or groupID (for type = 'group').
• cityID (int) – Numeric ID of the city.
• paramID (int) – Numeric parameter ID.
• tdate (int) – Tournament date (if tournament starts on Friday, this is the date of this Friday). Days since 1970-01-01.
• betdate (int) – If value is lower equal 6: assume that the real betdate is tdate + betdate (1: next day, 2: two days ahead, …). If betdate is bigger than 6 betdate is just taken as set.

Returns:

Returns a list containing all the bets.

Return type:

list

Todo

Reto the sleepy does not get bets - he just gets points. Maybe I can disable/remove the ‘all’ function if I am not using it anymore.

get_cities(sort=True, active=True)

Loading city information from the database. Loads all active cities from the database which can then be used to loop over or whatever you’ll do with it.

Parameters:

• sort (bool) – True if the cities should be sorted by their cityID.
• active (bool) – True if only active cities should be shown.

Returns:

A list containing one dict per city where each dict consists of the keys ‘name’, ‘hash’ and ‘ID’.

Return type:

list

Todo

Would be nice to return cityclass objects or something. However, needs some effort as I have to change a few lines of code.

get_city_ID_by_name(city)

Returns the city ID by a given name or hash, both is accepted :param city: Name or 3 letter hash of city :type city: str

Returns

int: City ID (int): Numeric city ID or False (bool) if the city name or hash could not be found in the database.

get_city_name_by_ID(cityID)

Returns the full city name given a valid cityID. If the city cannot be found in the database False will be returned.

Parameters:cityID (int) – Numeric city ID.

Returns

str: City name (str) or False (bool) if the city could not be found in the database.

get_city_names()

Loading city information from the database. Loads all names of currently active cities from the database.

Returns:A list containing the names of all active cities. Return type:list

get_cityall_bet_data(cityID, paramID, tdate, day, nosleepy=True, nullonly=False)

Returns bets for a given city/parameter/date/day. Returns all bets for a given city, parameter for a given bet day of a specified tournament. Note that there is no userID. This method returns the bets for all users.

Parameters:

• cityID (int) – Numeric ID of the city.
• paramID (int) – Integer, parameter ID.
• tdate (int) – date of this friday). Days since 1970-01-01.
• day (int) – [1/2] where 1 means tdate+1 (leading to a Saturday if tdate is a Friday). 2 means Sunday. Value between 0 and 5, however, only 1 and 2 are useful as we do not have bets for the other days.

Returns:

Returns a set of lists containing the ‘unique key identifier’ for the database and the ‘value’ to update the database. Namely: userID, cityID, paramID, tdate, betdate and values.

get_group_id(group)

Returns group ID given a group name.

Parameters:group (str) – Group name. Returns:False if the group cannot be found, else the integer group ID Return type:bool or int

get_groups(active=False)

Returns all active group names from database.

Returns:List of strings containing all group names (of active groups). Return type:list

get_moses_coefs(cityID, tdate)

TODO: docstring

get_obs_data(cityID, paramID, tdate, bdate, wmo=None)

Loading observation data from the obs database (the obs which are already in the format as they are used for the judging). If input wmo == None: return all obs for all stations for a given city/parameter/tdate/bdate. If input wmo is an integer value, only the observation for this specific station will be returned.

Parameters:

• userID (int) – User ID of the @b Sleepy user.
• cityID (int) – City ID.
• paramID (int) – Parameter ID.
• tdate (int) – Tournament date. Days since 1970-01-01.
• betdate (int) – Bet (forecast) date. Days since 1970-01-01.
• wmo (int or None) – None (which is default) or WMO station number.

Returns:

Returns either a list containing numeric values (if wmo == None) or a single numeric value. If there are no data at all, a boolean False will be returned.

Return type:

list or float

get_parameter_id(param)

Returns parameter ID given a parameter Name.

Parameters:param (str) – Parameter short name (e.g., TTm) Returns:False if the parameter cannot be found in the database or the corresponding integer parameter ID.

get_parameter_names(active=False, sort=False)

Returns all parameter names. If input active is set, only active parametres will be returned.

Parameters:

• active (bool, optional) – If True only active parameters will
• returned. (be) –
• sort (bool) – True if the params should be sorted by the sort column in the wetterturnier_params table.

Returns:

False if no parameters can be found. Else a list will be returned containing the parameter shortnames as strings.

Return type:

list or bool

get_participants_in_city(cityID, tdate=False, human=False, sort=False, what='display_name')

All users participating in a tournament. Getting all players who take place in a certain tournament for a given city and tdate.

Parameters:

• cityID (int) – ID of the current city.
• tdate (int) – tournament date as integer representation.
• human (bool) – Load only human players.
• sort (int) – Order users by their…
• what (str) – display_name (default). Other possible arguments:
• "nicename" ("user_login",) –

Returns:

List containing the user IDs of the players participating in the tournament for a given cityID and tdate. Can be a list of length 0 as well.

Return type:

list

get_participants_in_group(groupID, cityID, tdate, playing=True)

Active players in a specific group. Getting active players from a certain group for a certain city and weekend.

Parameters:

• groupID (int) – ID of the group.
• cityID (int) – ID of the current city.
• tdate (int) – tournament date as integer representation.

Returns:

List containing the user ID’s of the players from that specific group who were participating in the tournament for the city given. Can be a list of length 0 as well.

Return type:

list

get_sleepy_points(cityID, tdate, ignore)

Loading the points from the Sleepy user. Returns tuple including betdate, parameterID, sleepy points for a given city/tournament date. Points are computed as follows: Sleepy Points = Average(Points) - Standarddeviation(Points) where Points are all Group/User points gained in the tournament EXCLUDING the Sleepy. If the Sleepy would not be excluded the points would drift towards -Inf as the average decreases iteratively and the standard deviation increases iteratively.

Parameters:

• cityID (int) – Numeric city ID.
• tdate (int) – Tournament date, days since 1970-01-01.
• ignore (int) – Numeric user ID, ID of the user which should be ignored (e.g., Sleepy’s ID).

Returns:

Tuple tuple as fetched from database. Typically including two tuples, first for Saturday, second for Sunday.

Return type:

tuple tuple

get_stations_for_city(cityID, active=False, tdate=False)

Loading all stations mached to a certain city.

Parameters:cityID (int) – ID of the city in the database. Returns:List object containing N stationclass.stationclass objects. Return type:list

get_stats(cityID, measure=None, measures=None, userID=None, tdate=None, tdates=None, day=None)

Get statistics from tables citystats, userstats, tdatestats

get_user_id(user)

Returns user ID given a username. If the user cannot be found, the method returns False. There is a vice versa function called database.get_username_by_id(). :param user: User name. :type user: str

Returns:False if user does not exist, else the integer user ID. Return type:bool or int

get_user_id_and_create_if_necessary(name)

Returns user ID and create user if necessary. All-in-one wonder method. Searching for the user ID of a given user. If the user does not exist: create the user first and then return the user ID. See also: database.create_user(), database.get_user_id()

Parameters:name (str) – User name. Returns:Numeric userID. Return type:int

get_username_by_id(userID, which='user_login')

Returns username given a user ID. Returns the username for a given user ID. If the user cannot be found, the return value will be False. There is a vice versa function called database.get_user_id().

Parameters:userID (int) – Numeric user ID. Returns:False if user cannot be identified, else string username. Return type:bool or str

get_users_in_group(groupID=None, group=False, active=True, sort=False)

Returns all members of a given groupID or group name.

Parameters:

• groupID (int) – groupID.
• group (str) – The group name.
• active (bool) – True if only active members should be returned.
• sort (bool) – True if the user should be sorted by the sort column in the wetterturnier_groupusers table.

Returns:

List of userIDs for the given group.

sql_tuple(IDs, strings=False)

Format a list of integers (IDs) or strings to a tuple fitting the SQL IN(…) statement

upsert_bet_data(userID, cityID, paramID, tdate, bdate, value)

Helper function to update the bets database. Upserts the bets database setting a new ‘value’ for a given player.

Parameters:

• userID (int) – User ID.
• cityID (int) – City ID.
• paramID (int) – Parameter ID.
• tdate (int) – Tournament date. Days since 1970-01-01.
• betdate (int) – Bet (forecast) date. Days since 1970-01-01.
• value (int) – Bet/forecast value. Note that the value has to be scaled already as we store e.g. temperature in 1/10th of degrees celsius.

upsert_moses_coefs(cityID, tdate, moses)

TODO: docstring

upsert_points_data(userID, cityID, paramID, tdate, bdate, points)

Helper function to update the bets database. Upserts the bets database updating the points for a given player. This is for the payers. They get points for each of the parameters.

Parameters:

• userID (int) – user ID.
• cityID (int) – City ID.
• paramID (int) – Parameter ID.
• tdate (int) – Tournament date. Days since 1970-01-01.
• betdate (int) – Bet (forecast) date. Days since 1970-01-01.
• points (float) – Points the player got for that specific entry.

upsert_sleepy_points(userID, cityID, tdate, points)

Helper function to update the points for the Sleepy player. Upserts the betstat database updating the points for the Sleepy. Actually for any player. But the userID should be the one from the Sleepy here. The Sleepy just gets points for the full weekend, not points for specific parameters.

Parameters:

• userID (int) – User ID of the Sleepy user.
• cityID (int) – City ID.
• paramID (int) – Parameter ID.
• tdate (int) – Tournament date. Days since 1970-01-01.
• betdate (int) – Bet (forecast) date. Days since 1970-01-01.
• points (float) – Points the player got for that specific entry.

upsert_stats(cityID, stats, userID=False, tdate=False, day=0)

Insert stats dict into database