sqlite3.OperationalError: unrecognized token: ":"

[killuazoldyckreal]

Description

I want to get the position of the row, so I used this command but it didn't worked

cur = await self.bot.db.execute("SELECT x.position FROM (SELECT total.inviter_id, @rownum:=@rownum+1 AS position FROM totals JOIN (SELECT @rownum:=0) r WHERE guild_id = ? ORDER BY totals.total DESC) x WHERE x.inviter_id = ?",(ctx.guild.id,ctx.author.id,))

It caused an error

Traceback (most recent call last):
  File "D:\babyhawkdc\modules\invites\cog.py", line 218, in leaderboard
    cur = await self.bot.db.execute("SELECT x.position FROM (SELECT total.inviter_id, @rownum:=@rownum+1 AS position FROM totals JOIN (SELECT @rownum:=0) r WHERE guild_id = ? ORDER BY totals.total DESC) x WHERE x.inviter_id = ?",(ctx.guild.id,ctx.author.id,))
  File "C:\Users\Dell\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\Dell\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\core.py", line 129, in _execute
    return await future
  File "C:\Users\Dell\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\core.py", line 102, in run
    result = function()
sqlite3.OperationalError: unrecognized token: ":"

Details

• OS: Win 10
• Python version: 3.10
• aiosqlite version: 0.17.0
• Can you repro on 'main' branch?
• Can you repro in a clean virtualenv?

[amyreese]

I believe this error is coming from the core sqlite3 extension, not from aiosqlite. I'm not familiar with @rownum:=@rownum+1 as valid syntax. Is this a feature of newer sqlite versions? If so, perhaps you need a version of Python built against the newer sqlite?

[sjsadowski]

Was just poking through here. "@rownum:=@rownum+1" isn't valid in sqlite, it's mysql syntax.