import pyensae
from jyquickhelper import add_notebook_menu
add_notebook_menu()

"""
Explanation: Import a flat file into a SQLite database
Importing a flatfile can be done with pandas. pyensae proposes a function which does so by guessing the schema over the first lines.
End of explanation
"""


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

"""
Explanation: Mix SQLite and DataFrame
When a dataset is huge (~3Gb), it takes some time to load it into a DataFrame. It is difficult to look at it in any tool (Python, Excel, ...) One option I usually do is to load it a SQL server if you have one. If you do not, then SQLite is the best option. Let's see how it works with a custom datasets.
End of explanation
"""


import pandas
df = pandas.read_csv("velib_vanves.txt",sep="\t")
df.head(n=2)

"""
Explanation: As this file is small (just an example), let's see how it looks like with a DataFrame.
End of explanation
"""


from pyensae.sql import import_flatfile_into_database
import_flatfile_into_database("velib_vanves.db3", "velib_vanves.txt", add_key="key")

"""
Explanation: Then we import it into a SQLite3 database. The following function automatically guesses the table schema.
End of explanation
"""


import os
os.listdir(".")

"""
Explanation: We check  the database exists:
End of explanation
"""


try:
    from pymyinstall.installcustom import install_sqlitespy
    exe = install_sqlitespy()
except:
    # we skip an exception
    # the website can be down...
    exe = None
exe

"""
Explanation: On Windows, you can use SQLiteSpy to visualize the created table. We use pymysintall to download it.
End of explanation
"""


if exe:
    from pyquickhelper import run_cmd
    run_cmd("SQLiteSpy.exe velib_vanves.db3")

"""
Explanation: We just need to run it (see run_cmd).
End of explanation
"""


from pyquickhelper.helpgen import NbImage
NbImage('img_nb_sqlitespy.png')

"""
Explanation: You should be able to see something like (on Windows):
End of explanation
"""


sql = """SELECT * FROM velib_vanves WHERE key IN ({0})"""

import random
from pyquickhelper.loghelper import noLOG
from pyensae.sql import Database
db = Database("velib_vanves.db3", LOG = noLOG)
db.connect()
mx = db.execute_view("SELECT MAX(key) FROM velib_vanves")[0][0]
rnd_ids = [ random.randint(1,mx) for i in range(0,100) ]  # liste de 100 id aléatoires
strids  = ",".join( str(_) for _ in rnd_ids )
res = db.execute_view(sql.format (strids))
df = db.to_df(sql.format (strids))
db.close()
df.head()[["key","last_update","available_bike_stands","available_bikes"]]

"""
Explanation: It is easier to use that tool to extract a sample of the data. Once it is ready, you can execute the SQL query in Python and converts the results into a DataFrame. The following code extracts a random sample from the original sets.
End of explanation
"""


with open("temp_big_file.txt","w") as f :
    f.write("c1\tc2\tc3\n")
    for i in range(0,10000000):
        x = [ i, random.random(), random.random() ]
        s = [ str(_) for _ in x ]
        f.write( "\t".join(s) + "\n" )
os.stat("temp_big_file.txt").st_size        

import pandas,time
t = time.perf_counter()
df = pandas.read_csv("temp_big_file.txt",sep="\t")
print("duration (s)",time.perf_counter()-t)

"""
Explanation: <h3 id="mem">Memory Dump</h3>

Once you have a big dataset available in text format, it takes some time to load into memory and you need to do that every time you need it again after you closed your python instance.
End of explanation
"""


t = time.perf_counter()
df.to_pickle("temp_big_file.bin")
print("duration (s)",time.perf_counter()-t)

"""
Explanation: It is slow considering that many datasets contain many more features. But we can speed it up by doing a kind of memory dump with to_pickle.
End of explanation
"""


t = time.perf_counter()
df = pandas.read_pickle("temp_big_file.bin")
print("duration (s)",time.perf_counter()-t)

"""
Explanation: And we reload it with read_pickle:
End of explanation
"""


