%matplotlib inline
import matplotlib.pyplot as plt
plt.style.use('ggplot')

"""
Explanation: SQL Magic Commands with SQLite in a Notebook
SQL from a notebooks, using magic commands to query a sqllite3 database.
End of explanation
"""


import pyensae
import pyensae.datasource
%load_ext pyensae
pyensae.datasource.download_data("velib_vanves.zip", website = "xd")

import os
if os.path.exists("notebook_example.db3"):
    os.remove("notebook_example.db3")

"""
Explanation: This notebook introduced some of the magic commands used to easily query a SQLite database with sqlite3. SQLite databases can be easily manipulated with open source tools such as SQLite Manager (add-on for firefox) or SQLiteSpy (only on Windows). However, it is very convenient to put the results of a SQL query into a DataFrame. That what's this notebook is about.
Let's start by importing some data.
End of explanation
"""


%SQL_connect notebook_example.db3

"""
Explanation: We connect to the database:
End of explanation
"""


%SQL_tables

"""
Explanation: The database is empty so the table list should be empty:
End of explanation
"""


%SQL_import_tsv -t velib velib_vanves.txt

"""
Explanation: So we import a flat file (TSV format only) into the database.
End of explanation
"""


%SQL_tables

"""
Explanation: We check there is one more table:
End of explanation
"""


%SQL_schema velib

"""
Explanation: We get the schema of the database:
End of explanation
"""


%%SQL 
SELECT MAX(available_bike_stands +available_bikes) FROM velib

"""
Explanation: And we execute the first query:
End of explanation
"""


%SQL -q "SELECT COUNT(*) FROM velib"

"""
Explanation: Or another in one row:
End of explanation
"""


import random
def arandomfunction():
    return random.randint(1,100)
%SQL_add_function arandomfunction

"""
Explanation: We want to draw a random sample out of this table. We ask the database to recognize the following python function (its name cannot contain '_'):
End of explanation
"""


%%SQL --df=df
SELECT * FROM (
    SELECT *,arandomfunction() AS rnd FROM velib)
    WHERE rnd==1

"""
Explanation: We then execute the following query. Because the first line contains a identifier, the query will only display the shape of the results dataframe and not its content which will be stored in df:
End of explanation
"""


df.head()

"""
Explanation: We look at the head:
End of explanation
"""


%SQL_import_df -t sample df

"""
Explanation: If you into column rnd, you can see that it does not contain the value if was filtered on. It is probably because the python function is evaluated twice. We add this new dataframe to the database:
End of explanation
"""


%SQL_tables

"""
Explanation: We check it is part the list of tables:
End of explanation
"""


%%SQL

SELECT insample, COUNT(*) AS nb FROM (
    SELECT velib.*, sample.idr AS insample 
    FROM velib OUTER JOIN sample
    ON velib.idr == sample.idr
)

"""
Explanation: We do some more queries:
End of explanation
"""


%%SQL

SELECT insample, COUNT(*) AS nb FROM (
    SELECT velib.idr, sample.idr is not null insample
    FROM velib LEFT OUTER JOIN sample
    ON velib.idr == sample.idr
) GROUP BY insample

"""
Explanation: We fix it:
End of explanation
"""


%%SQL --df=dfo
SELECT DB.CC.velib.available_bike_stands, COUNT(*) 
FROM DB.CC.velib 
GROUP BY DB.CC.velib.available_bike_stands

dfo.shape

"""
Explanation: Autocompletion also works when the prefix is DB.CC.:
End of explanation
"""


from pyquickhelper.helpgen import NbImage
NbImage("dbcc.png")

"""
Explanation: The autocompletion looks like:
End of explanation
"""


dfo.plot(x="available_bike_stands", y="COUNT(*)", kind="bar", figsize=(16,4))

"""
Explanation: We finally draw a graph:
End of explanation
"""


%SQL_close

"""
Explanation: And we finally close the connection:
End of explanation
"""


