0

I am writing a Python script in Jupyter notebook to run 20+ long SQL queries. I have defined the SQL query strings in a separate file queryStrings.ipynb and the main body of code is in file analytics2020.ipynb.

This old StackOverflow post describes a nice clean way to define lists of constants ion a sepertate file (see the last answer ... the one from Ned Batchelder)

python-best-cleanest-way-to-define-constant-lists-or-dictionarys

However this does not appear to work in Jupyter Notebook. I have created the two seperate files

  1. queryStrings.ipynb

    q_CurrWeekiOSDailySessionCountDuration = '''
with session_boundaries as (
SELECT
    e.cust_id_attr_value
   ,e.event_timestamp
   ,DATEDIFF(minutes, LAG(e.event_timestamp) OVER(PARTITION BY e.cust_id_attr_value ORDER BY e.event_timestamp), e.event_timestamp) AS inactivity_time
   ,LAG(e.event_timestamp) OVER(PARTITION BY e.cust_id_attr_value ORDER BY e.event_timestamp) as prior_event_timestamp
FROM
   APPLICATIONDB e
WHERE
   event_data:"c-platform-m-os" = 'iOS' AND 
   event_timestamp BETWEEN \'{:s}\' AND \'{:s}\'
)
select 
    session_date,
    sum(num_sessions) as total_sessions,

 etc. etc. 
 '''

  2. analytics2020.ipynb

    import pandas as pd



import numpy as np

from queryStrings import q_CurrWeekiOSDailySessionCountDuration

print('===== q_CurrWeekiOSDailySessionCountDuration ====')

print(q_CurrWeekiOSDailySessionCountDuration)

However, when I try running this I get an error: 

26 from queryStrings import q_CurrWeekiOSDailySessionCountDuration
     27 print('===== q_CurrWeekiOSDailySessionCountDuration ====')
     28 print(q_CurrWeekiOSDailySessionCountDuration)

ModuleNotFoundError: No module named 'queryStrings'

The previous post I quoted however tells me this ought to work. Perhaps it's I have a hunch that this is because these files are Jupyter Notebook .ipynb files rather than plain vanilla .py files.

Would appreciate any help resolving this! Thanks so much.

eyllanesc
  • 235,170
  • 19
  • 170
  • 241
CoolDocMan
  • 637
  • 7
  • 29
  • 1
    To get this to work, you need to either rename queryStrings.ipynb as .py or use the import-ipynb module (see [ipynb import another ipynb file](https://stackoverflow.com/questions/20186344/ipynb-import-another-ipynb-file) for more details). – cco Jan 08 '20 at 19:39
  • Is using Jupyter Notebooks a necessity? – AMC Jan 09 '20 at 00:56

1 Answers1

1

I did some research found a way to do this in Jupyter using the %store class.

So in queryStrings.ipynb I added the line:

%store q_CurrWeekiOSDailySessionCountDuration

Then in analytics2020.ipynb I added then line

%store -r q_CurrWeekiOSDailySessionCountDuration

And then presto! It worked.

CoolDocMan
  • 637
  • 7
  • 29