queryClientError: Error: syntax error at or near ":"

Question

I don't know what exactly the problem is but counting the rows in a csv table in Vospace results in an error for me. I've broken it down to the following proof of concept:

from dl import queryClient as qc

nrows = qc.query(sql='select count(*) from vos://catwxvhs/catwxvhs_p02_00.csv').split()[1]
print (nrows)

The error returned is:

queryClientError: Error: syntax error at or near ":"
LINE 1: copy (select count(*) from vos://catwxvhs/catwxvhs_p02_00.cs...
                                      ^

Have I done something wrong?

Answer 1

Thanks for reaching out. It appears that you are attempting to run an SQL query against a CSV file. SQL queries can only be run against database tables (either in our main DB, or in your MyDB). If you want to get the content of a CSV file in your VOSpace, please use the sc.get() method. (sc is here shorthand for the storeClient, which I usually import as

from dl import storeClient as sc

If you just want to count the number of lines in your CSV file, you can also simply open a terminal in the Jupyter interface, and then there type something like:

youraccount@datalab>wc -l vospace/catwxvhs/catwxvhs_p02_00.csv

Best,
Robert

Comments

Thanks for clarifying. :) I was saving these tables to vospace during a crossmatch run in a jupyter notebook. So I falsely assumed the queryClient methods would work on them the same ways as mydb tables.

The rows counting was there for logging output purposes. I came up with a quick and dirty solution by using sc.get() and counting linebreaks.

nrows = sc.get(tname).count('\n')