In this lab you will get to know the basics of Python and some of its most useful libraries. After completing this lab, you will be awarded the title of SQL Alchemist.

Ubuntu comes pre-installed with Python 2 and 3. It can also easily be installed using apt-get. However, these are system wide installations that are used by all Python programs. Since Python heavily relies on libraries, and not all libraries are compatible with all versions of Python, it is often necessary to use different versions. Thus, the use of Virtual Environments is highly encouraged. Every virtual environment has its own Python distributions and installed libraries. Thus, different projects can be cleanly separated. There are several tools to create and manage virtual environments. In this lab we will use Anaconda.

Follow the link to figure out how to create and activate a new virtual environment. Specify the Python version to be 3.6 and install the following libraries using pip:

• sqlalchemy
• sqlite3
• pandas
• numpy
• matplotlib

When programming with Python, one has the choice to use IDEs (Integrated Development Enviroments), text editors or an interactive python interpreter. In this lab, we will use a text editor to write our script and an interactive python shell for quick testing.

Let's verify that everything is working.

1. Open a terminal window and activate your virtual environment
2. List all installed packages and verify that sqlalchemy, sqlite3, pandas, numpy and matplotlib are instaleld

   conda list

3. Check that the Python version of the virtual environment is indeed 3.6

   python -V

4. Open the interactive Python interpreter

   ipython

5. Print something

   print("Something")

If everything is set up correctly, you should see the print statement's output in the terminal.

1. Open a text editor
2. Add a print statement

   print("Something Else")

3. Save the file as print.py
4. Inside the terminal, navigate to the location where you saved print.py and execute the script

   python print.py

If everything is set up correctly, you should see the print statement's output in the terminal.

Now that all is set up, you are ready for the next step.

Before you start solving the exercises, complete the following tutorial: https://www.stavros.io/tutorials/python/. This tutorial is not exhaustive and only scratches the surface, but it provides a good general idea of how Python works. Best of all, it only takes a few minutes to do.

We suggest you follow the tutorial by trying some of the code inside the interactive Python interpreter

ipython

Once you complete the tutorial, you can finally dive into the actual exercises.

For the rest of this lab you will be working with the files in the (TODO) folder in your home directory. Open a terminal and navigate to that directory. Inside you find following files:

1. lab.py : Python file where you need to add your own code
2. ti2db.sqlite : A database of movies containing several tables.
3. romances.csv : A list of the best romance movies of all time.

In addition to those files, we suggest you to open an interactive Python shell. It is often helpful for debugging, especially when one is unsure about the correct syntax. Furthermore, you will need to use Google to complete the tasks.

Open lab.py in a text editor and start filling in the missing code. For simplicity's sake, all the code is in one single file. Thus, the entire script will be executed every time you run it. You can comment out parts of the code you have already finished, e.g., some SQL queries, if you do not want to execute them every time. All the incomplete code is currently commented out, such that the script can be run without errors.

Before you start, create a copy of ti2db.sqlite, just in case it gets messed up.

cp ti2db.sqlite ti2db_backup.sqlite 

You can have a look at the ti2db.sqlite database in the terminal:

sqlite3 ti2db.sqlite

This opens a connection to the sqlite database and you can now enter standard SQLite commands.

Most answers to the exercises can be found at http://docs.sqlalchemy.org/en/latest/orm/tutorial.html.