Database Class¶

Interface¶

class pysk.db.Database(host='localhost', user='startkladde', password=None, dbName='startkladde')¶

Interface for MySQL database used by Startkladde

If password is not None, a new connection to the database will be attempted.

Parameters:	host (str) – Hostname. Defaults to ‘localhost‘ user (str) – MySQL username. Defaults to ‘startkladde‘. password (str) – Password for user. Defaults to None. dbName (str) – Name of Database to open. Defaults to ‘startkladde‘.

connect(host='localhost', user='startkladde', password=None, dbName='startkladde')¶

Connect to MySQL server

Parameters:	host (str) – Hostname. Defaults to ‘localhost‘. user (str) – MySQL username. Defaults to ‘startkladde‘. password (str) – Password for user. Defaults to None. dbName (str) – Name of Database to open. Defaults to ‘startkladde‘.

disconnect()¶

Disconnect from database

commit()¶

Commit all changes to the database

listTables()¶

Get list of tables

Returns:	List of tables

getTables()¶

Get information about tables

Returns:	Dictionary with table name as key and db.Table instance as value

iterate(cls, filter=None, order=None)¶

Iterate over the rows of a given table

Parameters:	cls – Class specifying the table. Must provide a static method tableName, which returns the name of the selected table and a constructor which accepts the returned tuple. filter (str) – Any filter string in the format passed to SQL WHERE command. If None, no filter is applied. Defaults to None. order (str) – Optional order key. Passed verbatim to SQL ORDER BY statement. Defaults to None.
Returns:	Generator yielding an instance of cls for each table row

iterPlanes(filter=None)¶

Iterate over all airplanes in database

Parameters:	filter (str) – Any filter string accepted by SQL‘s WHERE command. If None, no filter is applied. Defaults to None.
Returns:	Generator yielding an db.model.Airplane instance for each airplane in database matching the filter criteria.

iterPilots(filter=None)¶

Iterate over all pilots in database

Parameters:	filter (str) – Any filter string accepted by SQL‘s WHERE command. If None, no filter is applied. Defaults to None.
Returns:	Generator yielding an db.model.Pilot instance for each airplane in database matching the filter criteria.

iterUsers(filter=None)¶

Iterate over all users in database

Parameters:	filter (str) – Any filter string accepted by SQL‘s WHERE command. If None, no filter is applied. Defaults to None.
Returns:	Generator yielding an db.model.User instance for each airplane in database matching the filter criteria.

iterFlights(filter=None, order=None)¶

Iterate over all flights in database

Parameters:	filter (str) – Any filter string accepted by SQL‘s WHERE command. If None, no filter is applied. Defaults to None. order (str) – Parameter by which to order. Passed verbatim to SQL‘s ORDER BY statement. Defaults to None.
Returns:	Generator yielding an db.model.Flight instance for each airplane in database matching the filter criteria.

iterSimultaneousFlights(flight)¶

Iterate over all flights, which overlap with the given flight

Raises an exception if either landing or departure time are not specified in flight.

Parameters:	flight (db.model.Flight) – Flight for which to get overlapping flights
Returns:	Generator yielding a db.model.Flight instance for each flight in database overlapping with flight.

iterSimilarFlights(flight, filter=None)¶

Get all flights from database, which are similar to a given flight

A flight is similar to another flight, if it is conducted by the same pilot or with the same airplane in an overlapping time span.

Raises an exception if either landing or departure time are not specified in flight.

Parameters:	flight (db.model.Flight) – Flight for which to get overlapping flights
Returns:	Generator yielding a db.model.Flight instance for each flight in database simlar to flight.

getDictionary(iterable, key='id')¶

Creates a dictionary of a given table

Parameters:	iterable (iterable) – Iterable containing table rows key (str) – The key used in the dictionary to return. Can be the name of any attribute provided by the iterable’s elements. If a tuple of attribute strings is passed, the key will be the tuple of the associated attributes. Defaults to ‘id’.
Returns:	Dictionary containing a row in the specified table as value. The associated key is the respective member chosen through parameter key.

insert(cls, rows, force=False)¶

Insert new values into a table

Parameters:	cls (class) – Class specifying the table. Must provide a static method tableName, which returns the name of the selected table rows – Rows to insert into the table. Each element shall provide an attribute for each table column with the same name as the column, i.e. db.Table.toTuple (row) must be well defined for each element.

insertUsers(users, force=False)¶

Insert users.

Shortcut for

self.insert(cls=User, table=users, force=force)

Parameters:	users (iterable) – List of users to insert force (bool) – Overwrite existing users. Defaults to False.

insertFlights(flights, force=False)¶

Insert flights.

Shortcut for

self.insert(cls=Flight, table=flights, force=force)

Parameters:	flights (iterable) – List of flights to insert force (bool) – Overwrite existing flights. Defaults to False.

insertPilots(pilots, force=False)¶

Insert pilots.

Shortcut for

self.insert(cls=Pilot, table=flights, force=force)

Parameters:	pilots (iterable) – List of pilots to insert force (bool) – Overwrite existing pilots. Defaults to False.

orderTable(cls)¶

Orders a given table

Warning

“Deprecated. Raises RuntimeError”

Reorders the ids in a given table by the natural sort order.

Parameters:	cls (class) – Class containing table name

delete(cls, filter=None, data=None)¶

Delete records from table

Parameters:

cls (class) – Class representing the table from which to delete. Must provide a static method tableName, which returns the name of the selected table filter (str) – Passed verbatim to SQL‘s WHERE clause to identify the rows to be deleted. If filter is None or the empty string, all rows will be deleted. Defaults to None. data – Data argument passed verbatim to cursor.execute. as second argument (param). Defaults to None.

deleteById(cls, ids)¶

Delete records by id

Parameters:	cls (class) – Class specifying the table. Must provide a static method tableName returning the name of the selected table ids (iterable) – ids to be deleted. Each element should be convertible to an integer.

deleteFlights(ids)¶

Delete flights by id

Parameters:	ids (iterable) – IDs to delete

deletePilots(ids)¶

Delete pilots by id

Parameters:	ids (iterable) – IDs to delete

deleteUsers(ids)¶

Delete users by id

Parameters:	ids (iterable) – IDs to delete

createUsersFromPilots()¶

Generate a user account for each pilot in database

Generates a user account with auto-generated password for each pilot, which does not have a standard account yet. The username is of the form <first_name>.<last_name> and the password will be autogenerated to a random initial value.

The user account is not added to the database.

Parameters:	exclude (iterable) – List of regular expression objects. Usernames matching any expression in this list are skipped. Defaults to None. club (str) – Club for which to create user accounts. If None, no filter by club is applied. Otherwise, users accounts are exclusively generated for the specified club. Defaults to None.
Returns:	Generator of tuples containing (pilot, user, password).

update(cls, assignment, filter=None)¶

Update value in table

Uses mysql UPDATE statement to update values of a table.

Parameters:	cls (class) – Class for which to update the respective table assignment (str) – Update information in format compatible with MySQL SET clause of UPDATE statement filter (str) – Optional filter string passed verbatim to WHERE statement.

Example

Assuming db is a connected db.Database instance, the following code replaces each occurence of the pilot_id 3 with a pilot_id of 5 in table Flights:

import pysk.db.model.Flight as Flight
[...]
db.update(Flight, "pilot_id=5", "pilot_id=3")

updateFlight(assignment, filter=None)¶

Update fields of selected flights

Parameters:	assignment (str) – Update information in format compatible with MySQL SET clause of UPDATE statement. filter (str) – Optional filter string passed verbatim to WHERE statement.

unique(cls, filter)¶

Get unique result of a query

Parameters:	cls (class) – Class to select filter (str) – Filter criteria
Returns:	Instance of cls matching query, if and only if the query returns exactly one result. Otherwise a KeyError is raised.

uniqueById(cls, id)¶

Get unique result of a query

Parameters:	cls (class) – Class to select id (int) – ID of item to select
Returns:	Instance of cls matching query, if and only if the query returns exactly one result. Otherwise a KeyError is raised.

pilot(id)¶

Get Pilot by id

Argument:

id (int): ID of item to select

Returns:	db.model.Pilot instance with ID id. Raises KeyError if no matching item is found.

plane(id)¶

Get Airplane by id

Argument:

id (int): ID of item to select

Returns:	db.model.Airplane instance with ID id. Raises KeyError if no matching item is found.

launchMethod(id)¶

Get launch method by id

Argument:

id (int): ID of item to select

Returns:	db.model.LaunchMethod instance with ID id. Raises KeyError if no matching item is found.

getPilotByName(firstName, lastName)¶

Get pilot id by name

Raises a KeyError if either no pilots or more than one pilot with this name exist.

Parameters:	firstName (str) – First name of pilot lastName (str) – Last name of pilot
Returns:	Matching db.model.Pilot instance

getPlaneByRegistration(registration)¶

Get aircraft id by registration

Raises a KeyError if either no plane or more than one plane with this registration exist.

Parameters:	registration (str) – Registration ID of aircraft
Returns:	Matching db.model.Airplane instance

getLaunchMethodByName(name, allowShortNames=True)¶

Get launch method id by name

Raises a KeyError if either no launch method or more than one launch method with the given name exist.

Parameters:	name (str) – Name of launch method to find allowShortNames (bool) – If True, name and short name of launch method are searched. Otherwise only the long name is matched.
Returns:	Matching db.model.LaunchMethod instance

getLaunchMethodByTowplane(registration)¶

Get launch method by registration of towplane

Raises a KeyError if either no launch method or more than one launch method with the given towplane registration exists.

Parameters:	registration (str) – Towplane registration ID
Returns:	Matching db.model.LaunchMethod instance

makeRecords(flights)¶

Convert flights into full records

Parameters:	flights (iterable) – Iterable of db.model.Flight objects
Returns:	Generator yielding a db.Record instance per flight in flights.

static copy(src, dest, ignoreID=True)¶

Copies all attributes of src to dest

Parameters:	src (object) – Source dataset dest (object) – Destination dataset ignoreID (bool) – If True, member id is ignored. Defaults to True.