Deepnote SQL under-the-hood

SQL

Saved to variable

df_1

SELECT * FROM users

ValueError: invalid literal for int() with base 10: 'a'

%history


query = """SELECT * FROM users
"""
df_1 = _deepnote_execute_sql(query, 'SQL_812A9158_D6D5_41E0_9F96_563BF3BD34EE')
df_1
%history

import inspect print(inspect.getsource(_deepnote_execute_sql))

def _deepnote_execute_sql(query, sql_alchemy_json_env_var):
  if query.isspace():
    return

  class ExecuteSqlError(Exception):
    pass

  import pandas as pd
  import os
  import json
  from sqlalchemy.engine import create_engine
  from sqlalchemy.exc import ResourceClosedError

  if not sql_alchemy_json_env_var:
    raise ExecuteSqlError('This SQL cell is not linked with an connected integration')

  sql_alchemy_json = os.environ.get(sql_alchemy_json_env_var)
  if not sql_alchemy_json:
    raise ExecuteSqlError('This SQL cell is not linked with an connected integration')

  sql_alchemy_dict = json.loads(sql_alchemy_json)
  engine = create_engine(sql_alchemy_dict['url'], **sql_alchemy_dict['params'])

  # we have to replace % by %% in the SQL query due to how SQL alchemy interprets %
  # https://stackoverflow.com/q/8657508/2761695
  fixed_query = query.replace('%', '%%')

  try:
    return pd.io.sql.read_sql_query(fixed_query, engine)
  except ResourceClosedError:
    # this happens if the query is e.g. UPDATE and pandas tries to create a dataframe from its result
    return None