Invalid column name 'esv_temperature'

[housw]

Hi Jesse,

I got the following error while trying the test input UCYN-A ASVs, it seems related to the database, any idea?

(opedia-env) shengwei@bounty-hunter: c-microbial-map$ ./scripts/blast2cmap.py -q example-input/UCYN-SAG.fasta -i 97 -o test -b blast/blast 
Running BLAST
Centroids query
    1: acf1bd3a8ae33d49e8a40be3ca58a5c2
Traceback (most recent call last):
  File "./scripts/blast2cmap.py", line 336, in <module>
    main()
  File "./scripts/blast2cmap.py", line 138, in main
    perc_identity=args.perc_identity)
  File "./scripts/blast2cmap.py", line 225, in cmap_query
    rows = cursor.execute(qry, (seq_id, )).fetchall()
pyodbc.ProgrammingError: ('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'esv_temperature'. (207) (SQLExecDirectW)")

Thanks,
Shengwei

[jcmcnch]

Hi Shengwei,

This app is currently not working because CMAP's way of accessing data has changed. The interface with CMAP's SQL database was previously done through the python library "opedia" which didn't require API access. Now we should be able to use "pycmap" but I haven't figured yet how to use it with an API key. Perhaps @kyclark can offer some advice here? Is this something easy to implement Ken? I know you're at a new job now, but if you would still be up to give me some pointers that would be really cool because I'm planning to add more data to CMAP in the near future and it would be cool to resurrect CMicrobeMAP!

Jesse

[kyclark]

Sorry that I forgot to respond to this. I did start a new job about a month ago, so I’m no longer have time to work on this. I would hope that Mohammed from Ginger’s lab would be able to help you figure out the access. Sorry I can’t be more helpful. Good luck! Ken

…

On Aug 29, 2020, at 1:03 PM, Jesse McNichol ***@***.***> wrote: Hi Shengwei, This app is currently not working because CMAP's way of accessing data has changed. The interface with CMAP's SQL database was previously done through the python library "opedia" which didn't require API access. Now we should be able to use "pycmap" but I haven't figured yet how to use it with an API key. Perhaps @kyclark can offer some advice here? Is this something easy to implement Ken? I know you're at a new job now, but if you would still be up to give me some pointers that would be really cool because I'm planning to add more data to CMAP in the near future and it would be cool to resurrect CMicrobeMAP! Jesse — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[jcmcnch]

Hi Ken, No worries, understand completely and I'll see if I can get it working with Mohammad's help. Once we fix the script, do you think I could/should talk to someone at Cyverse to re-implement it there? If so, do you know a good person to contact? Hope your new job is going well! Jesse

…

On Fri, Sep 11, 2020, at 11:10 AM, Ken Youens-Clark wrote: Sorry that I forgot to respond to this. I did start a new job about a month ago, so I’m no longer have time to work on this. I would hope that Mohammed from Ginger’s lab would be able to help you figure out the access. Sorry I can’t be more helpful. Good luck! Ken > On Aug 29, 2020, at 1:03 PM, Jesse McNichol ***@***.***> wrote: > > > Hi Shengwei, > > This app is currently not working because CMAP's way of accessing data has changed. The interface with CMAP's SQL database was previously done through the python library "opedia" which didn't require API access. Now we should be able to use "pycmap" but I haven't figured yet how to use it with an API key. Perhaps @kyclark can offer some advice here? Is this something easy to implement Ken? I know you're at a new job now, but if you would still be up to give me some pointers that would be really cool because I'm planning to add more data to CMAP in the near future and it would be cool to resurrect CMicrobeMAP! > > Jesse > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub, or unsubscribe. > — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#1 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABNOH7HA7DJ6WQZPOQQW74DSFJR2BANCNFSM4QOUOAVA>.

[kyclark]

On Sep 11, 2020, at 1:39 PM, Jesse McNichol ***@***.***> wrote: No worries, understand completely and I'll see if I can get it working with Mohammad's help. Once we fix the script, do you think I could/should talk to someone at Cyverse to re-implement it there? If so, do you know a good person to contact? Hope your new job is going well!

Yes, the app will need to be repackaged and updated on Stampede2, updated in Cyverse to work. None of that is easy, but if the Python code can be updated, then at least people could clone the repo and run it locally. It might be fairly easy to package it into a Docker container so people could run it w/o having to install Python and all that business. Ken

[jcmcnch]

Thanks for the advice Ken, Docker sounds like a good intermediate solution once we get the code running again.

Jesse

[mdashkezari]

Hi,
opedia python package is deprecated and pycmap should be used instead, see here.

however, opedia still has access to the database and should work.
please makes sure you are not using one of the very early versions of opedia which points to an old db server.
see the example below.

please consider upgrading to pycmap.

Python 3.8.3 (default, Jul  2 2020, 11:26:31) 
[Clang 10.0.0 ] :: Anaconda, Inc. on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import opedia
>>> from opedia import db
>>> opedia.__version__
'0.2.82'
>>> db.dbFetch("select top 10 esv_temperature from tblesv")
   esv_temperature
0           9.8878
1           9.8878
2           9.8878
3           9.8878
4           9.8878
5           9.8878
6           9.8878
7           9.8878
8           9.8878
9           9.8878

[jcmcnch]

Thanks for the advice Mohammad, I'd like to upgrade this app once we get the new BioGEOTRACES ASV data into CMAP so thanks for pointing us in the right direction with pycmap!

Jesse