Problem Using Cursor to Execute a Database Query

Question

I'm trying to do a query similar to this:

ctx = snowflake.connector.connect(
          host=host,
          user=user,
          password=password,
          account=account,
          warehouse=warehouse,
          database=database,
          schema=schema,
          protocol='https',
          port=port)

## Create a cursor object.
cur = ctx.cursor()

## Execute a statement that will generate a result set.
sql = "select * from t"
cur.execute(sql)

My query looks something like this:

select NUMBER, ID, DATE, CODE from DATABASE.SCHEMA.TABLE
where (cast(CODE as string) like 'ABC12%'
and (DATE < '2021-12-31' and DATE >= '2021-10-01')

I'm getting this error:

ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 3 at position 52 unexpected '<EOF>'.

I've looked up this error code and tried removing any special characters that might be there, but no luck.

Thanks in advance

Answer 1

If we re-format the SQL:

select 
    NUMBER, 
    ID, 
    DATE, 
    CODE 
from DATABASE.SCHEMA.TABLE
where (
    cast(CODE as string) like 'ABC12%'
    and (
        DATE < '2021-12-31' and DATE >= '2021-10-01'
        )

we can see you are missing the close bracket/paren to the WHERE clause.

But you don't actually need any brackets on this:

select 
    NUMBER, 
    ID, 
    DATE, 
    CODE 
from DATABASE.SCHEMA.TABLE
where CODE::text like 'ABC12%'
    and DATE < '2021-12-31' and DATE >= '2021-10-01'