title: Fast loading and querying data in Python
body: <p>I am doing some data analysis in Python. I have ~15k financial products identified by ISIN code and ~15 columns of daily data for each of them. I would like to easily and quickly access the data given an ISIN code. </p>
  
  <p>The data is in a MySQL DB. On the Python side so far I have been working with Pandas DataFrame.</p>
  
  <p>First thing I did was to use pd.read_sql to load the DF directly from the database. However, this is relatively slow. Then I tried loading the full database in a single DF and serializing it to a pickle file. The loading of the pickle file is fast, a few seconds. However, when querying for an individual product, the perfomance is the same as if I am querying the SQL DB. Here is some code:</p>
  
  <pre class="lang-py prettyprint-override"><code>import pandas as pd
  from sqlalchemy import create_engine, engine
  from src.Database import Database
  import time
  import src.bonds.database.BondDynamicDataETL as BondsETL
  
  database_instance = Database(Database.get_db_instance_risk_analytics_prod())
  
  engine = create_engine(
      "mysql+pymysql://"
      + database_instance.get_db_user()
      + ":"
      + database_instance.get_db_pass()
      + "@"
      + database_instance.get_db_host()
      + "/"
      + database_instance.get_db_name()
  )
  con = engine.connect()
  
  
  class DataBase:
      def __init__(self):
          print("made a DatBase instance")
  
      def get_individual_bond_dynamic_data(self, isin):
          return self.get_individual_bond_dynamic_data_from_db(isin, con)
  
      @staticmethod
      def get_individual_bond_dynamic_data_from_db(isin, connection):
          df = pd.read_sql(
              "SELECT * FROM BondDynamicDataClean WHERE isin = '"
              + isin
              + "' ORDER BY date ASC",
              con=connection,
          )
          df.set_index("date", inplace=True)
          return df
  
  
  class PickleFile:
      def __init__(self):
          print("made a PickleFile instance")
          df = pd.read_pickle("bonds_pickle.pickle")
          # df.set_index(['isin', 'date'], inplace=True)
          self.data = df
          print("loaded file")
  
      def get_individual_bond_dynamic_data(self, isin):
          result = self.data.query("isin == '@isin'")
          return result
  
  
  fromPickle = PickleFile()
  fromDB = DataBase()
  
  isins = BondsETL.get_all_isins_with_dynamic_data_from_db(
      connection=con,
      table_name=database_instance.get_bonds_dynamic_data_clean_table_name(),
  )
  
  isins = isins[0:50]
  
  start_pickle = time.time()
  
  for i, isin in enumerate(isins):
      x = fromPickle.get_individual_bond_dynamic_data(isin)
      print("pickle: " + str(i))
  
  stop_pickle = time.time()
  
  for i, isin in enumerate(isins):
      x = fromDB.get_individual_bond_dynamic_data(isin)
      print("db: " + str(i))
  
  stop_db = time.time()
  
  pickle_t = stop_pickle - start_pickle
  db_t = stop_db - stop_pickle
  print("pickle: " + str(pickle_t))
  print("db: " + str(db_t))
  print("ratio: " + str(pickle_t / db_t))
  </code></pre>
  
  <p>This results in:
      pickle: 7.636280059814453
      db: 6.167926073074341
      ratio: 1.23806283819615</p>
  
  <p>Also, curiously enough setting the index on the DF (uncommenting the line in the constructor) slows down everything! </p>
  
  <p>I thought of trying <a href="https://www.pytables.org/index.html" rel="nofollow noreferrer">https://www.pytables.org/index.html</a> as an alternative to Pandas. Any other ideas or comments?</p>
  
  <p>Greetings,
  Georgi</p>