Getting error "near "(": syntax error sqlite error"

Question

I am using sqlite version 3.21.0.

I am getting error in executing the below query. pd is pandas library. It seems that command is correct.

This code fetches Gender and their respective count in the Person table using over(partition by Gender) clause.

pd.read_sql_query(""" select Gender, Count(Gender) Over(Partition By Gender) CategoryCount from Person """,conn)

OperationalError                          Traceback (most recent call last)
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   1430             else:
-> 1431                 cur.execute(*args)
   1432             return cur

OperationalError: near "(": syntax error

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-42-e842fb9f3a5b> in <module>()
      1 pd.read_sql_query(""" select Gender, Count(Gender) Over(Partition By Gender) CategoryCount
----> 2                       from Person """,conn)

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize)
    312     return pandas_sql.read_query(
    313         sql, index_col=index_col, params=params, coerce_float=coerce_float,
--> 314         parse_dates=parse_dates, chunksize=chunksize)
    315 
    316 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1466 
   1467         args = _convert_params(sql, params)
-> 1468         cursor = self.execute(*args)
   1469         columns = [col_desc[0] for col_desc in cursor.description]
   1470 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   1443                 "Execution failed on sql '{sql}': {exc}".format(
   1444                     sql=args[0], exc=exc))
-> 1445             raise_with_traceback(ex)
   1446 
   1447     @staticmethod

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\compat\__init__.py in raise_with_traceback(exc, traceback)
    418         if traceback == Ellipsis:
    419             _, _, traceback = sys.exc_info()
--> 420         raise exc.with_traceback(traceback)
    421 else:
    422     # this version of raise is a syntax error in Python 3

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   1429                 cur.execute(*args, **kwargs)
   1430             else:
-> 1431                 cur.execute(*args)
   1432             return cur
   1433         except Exception as exc:

DatabaseError: Execution failed on sql ' select Gender, Count(Gender) Over(Partition By Gender) CategoryCount
                      from Person ': near "(": syntax error

Gordon Linoff · Accepted Answer · 2020-02-22 12:28:29Z

1

You would get this problem if you are using an old version of SQLite that does not support window functions.

In any case, a window function does not seem appropriate here. I would recommend simple aggregation:

select Gender, Count(*) as CategoryCount
from Person
group by Gender;

This will return one row per value in Gender along with the count.

Using window functions, you would get all the rows in Person with only the Gender and count -- presumably a lot more rows and duplicated data.

answered Feb 22, 2020 at 12:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting error "near "(": syntax error sqlite error"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related