SQL-Basics: Relations

As the Junior Data Scientist of Knight Industries we created a table to keep track of all our operatives: SQL-Basics: Create, Read, Update, Delete

Devon asks us to keep track of our operations aka missions as well. For the first implementation let us assume that a mission has one operative and an operative can participate in multiple missions. that’s what we call an 1 to many relationship.

  +------------+                 +----------+
  |            |1   active in   n|          |
  | operative  +-----------------+ missions |
  |            |                 |          |
  +------------+                 +----------+

Missions have an id, a code name like “Phantom Liberty”, an operative id and a total cost.

To say that the operative id is equivalent to the id in the operative column we use the FOREIGN KEY constraint.

So let’s create the new table in our database:

CREATE TABLE missions 
(id INTEGER, code_name TEXT, operative_id INTEGER, total_cost DECIMAL(15,2), PRIMARY KEY(id), FOREIGN KEY (operative_id) REFERENCES operatives(id));
INSERT INTO missions VALUES (1, "Knight of the Phoenix", 1, 6246382.43);
INSERT INTO missions VALUES (2, "Deadly Maneuvers", 1, 4568893.53);

Our first join

SELECT missions.code_name, operatives.name FROM missions LEFT JOIN operatives ON missions.operative_id = operatives.id;

More on joins https://dsin.files.wordpress.com/2013/03/sqljoins_cheatsheet.png

SQL-Functions

In SQL-Basics

we learned the standard SQL statements to create a table, insert data into it, retrieving data from tables and altering data. Now we want to do even

Devon is a data driven man and to evaluate our mission, he likes to know from us:

    • how many missions we finished
    • the total cost of all missions
    • the mission with the lowest cost
    • the mission with the highest cost
    • the average mission cost
    • the five most expensive missions

 

SELECT COUNT(*) FROM missions;
SELECT SUM(total_cost) FROM missions;
SELECT MIN(total_cost) FROM missions;
SELECT MAX(total_cost) FROM missions;
SELECT AVG(total_cost) FROM missions;
SELECT * FROM missions ORDER BY total_cost DESC LIMIT 5;

Distributing your own package on PyPi – Part 2

In Distributing your own package on PyPi I wrote about my first package on PyPI. Here are some refinements aka lessons learned:

Project Description on PyPI

I wondered why the project description on PyPi was empty. Solution: You need a long_description. If You already have a README.md, you can read it into a string and use this as the description.

But you have to add long_description_content_type=’text/markdown’ as well.

from setuptools import setup

# read the contents of your README file
from os import path
this_directory = path.abspath(path.dirname(__file__))
with open(path.join(this_directory, 'README.md'), encoding='utf-8') as f:
    long_description = f.read()

setup(
    name='flask_url_mapping',
    version='0.6',
    packages=['flask_url_mapping'],
    url='https://github.com/jboegeholz/flaskurls',
    download_url='https://github.com/jboegeholz/flaskurls/archive/0.2.tar.gz',
    license='MIT',
    author='Joern Boegeholz',
    author_email='boegeholz.joern@gmail.com',
    description='Django-style URL handling for Flask',
    long_description=long_description,
    long_description_content_type='text/markdown',
    install_requires=["Flask", "Flask-Login"]
)

 

Dependencies of your Package

If your package relies on the usage of other python packages you should add them to your setup.py as well via install_requires.

setup(
    name='flask_url_mapping',
    version='0.6',
    packages=['flask_url_mapping'],
    url='https://github.com/jboegeholz/flaskurls',
    download_url='https://github.com/jboegeholz/flaskurls/archive/0.2.tar.gz',
    license='MIT',
    author='Joern Boegeholz',
    author_email='boegeholz.joern@gmail.com',
    description='Django-style url handling for Flask',
    install_requires=["Flask", "Flask-Login"]
)