Uploaded image for project: 'Science Database'
  1. Science Database
  2. SCIDB-124

SciServer/Compute sql.pandas_read_sql not working

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Done (View Workflow)
    • Priority: Normal
    • Resolution: Done
    • Component/s: None
    • Labels:
      None

      Description

       I got a kind of timeout error when simply trying this:

      # import the hscdata module to talk to the database
      from hscdata import sql# SQL script
      # for details of each database column, please refer to the schema browser linked from the previous cell
      script = '''
      SELECT
          object_id
          ,spec.ra
          ,spec.dec
          ,spec.z0
          ,spec.reliability0
          ,phot.g_cmodel_mag
          ,phot.r_cmodel_mag
          ,phot.i_cmodel_mag
          ,phot.patch_s
          ,phot.tract
      FROM
          scidb_25.gal_specline AS spec JOIN
          pdr2_dud.forced AS phot USING (object_id)
      WHERE
          phot.isprimary AND
          pdr2_dud.search_cosmos(object_id) AND
          phot.i_cmodel_mag < 22
          --- feel free to include more constraints here
      '''# query the database.  you will get the result in pandas dataframe
      data = sql.pandas_read_sql(script)

      -->

      /python-extra/lib/python3.9/site-packages/hscdata/config.py:35: UserWarning: Waiting for the db connection information... warnings.warn('Waiting for the db connection information...') --------------------------------------------------------------------------- RuntimeError Traceback (most recent call last) <ipython-input-2-36c45ed638a2> in <module> 27 28 # query the database. you will get the result in pandas dataframe ---> 29 data = sql.pandas_read_sql(script)/python-extra/lib/python3.9/site-packages/hscdata/sql.py in pandas_read_sql(sql, index_col, coerce_float, params, parse_dates, columns, chunksize) 29 raise ValueError('Use pandas_read_sql_chunked instead.') 30 ---> 31 with connect2db() as conn: 32 return pandas.read_sql( 33 sql=sql, con=conn, index_col=index_col,/usr/local/lib/python3.9/contextlib.py in __enter__(self) 115 del self.args, self.kwds, self.func 116 try: --> 117 return next(self.gen) 118 except StopIteration: 119 raise RuntimeError("generator didn't yield") from None/python-extra/lib/python3.9/site-packages/hscdata/sql.py in connect2db() 55 @contextlib.contextmanager 56 def connect2db(): ---> 57 conn = psycopg2.connect(config.sql_dsn) 58 try: 59 yield conn/python-extra/lib/python3.9/site-packages/hscdata/config.py in __getattr__(self, key) 23 24 def __getattr__(self, key): ---> 25 self._wait_until_config_ready() 26 return getattr(self._config, key) 27 /python-extra/lib/python3.9/site-packages/hscdata/config.py in _wait_until_config_ready(self) 39 time.sleep(1) 40 else: ---> 41 raise RuntimeError('Failed to get db connection information') 42 43 RuntimeError: Failed to get db connection information
      

        Attachments

          Activity

            People

            • Assignee:
              michitaro michitaro
              Reporter:
              kiyoto.yabe Kiyoto Yabe
              Reviewers:
              Kiyoto Yabe
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: