时间戳

首页 > 解决方案 > 出现错误“靠近”（“：语法错误 sqlite 错误”

sql - 出现错误“靠近”（“：语法错误 sqlite 错误”

问题描述

我正在使用 sqlite 版本 3.21.0。

我在执行以下查询时遇到错误。pd 是熊猫库。看来命令是正确的。

此代码使用 over(partition by Gender) 子句在 Person 表中获取 Gender 及其各自的计数。

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

标签： sqlpandassqlite

解决方案

如果您使用不支持窗口函数的旧版本 SQLite，您会遇到此问题。

无论如何，窗口函数在这里似乎不合适。我会推荐简单的聚合：

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

Gender这将与计数一起返回每个值一行。

使用窗口函数，您将Person只使用Gender和计数来获取所有行——大概更多的行和重复的数据。

推荐阅读