window Function - Row_Number not working

[saketdabi1385]

Hi I am truing to use window functions but it seems they are not supported ? on checking i found out that sqllite recently enabled them. Do you think you can include them as well.

[stonebig]

in theory nothing is impossible

[saketdabi1385]

still running into Execution failed on sql 'select row_number() over (partition by asin)': near "(": syntax error while doing this

import pandas as pd from pandasql import sqldf from baresql import baresql df = pd.read_csv(r'C:\Automation\Simulator\file.csv', index_col=0) bsql = baresql.baresql(keep_log=True) bsqldf = lambda q: bsql.df(q, dict(globals(),**locals()))

print('data read') query = "select row_number() over (partition by date)"

query_result = bsqldf(query)

print(query_result.head())

Regards Saket Dabi

On Thu, Aug 22, 2019 at 11:20 AM stonebig notifications@github.com wrote:

in theory nothing is impossible

[image: image] https://user-images.githubusercontent.com/4312421/63539380-3c1f0c00-c51a-11e9-93b4-348e866de54e.png

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yhat/pandasql/issues/77?email_source=notifications&email_token=AIYKYXOII5JWWM6I23FAF73QF3KHZA5CNFSM4IOXXTHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD456U5Q#issuecomment-524020342, or mute the thread https://github.com/notifications/unsubscribe-auth/AIYKYXMEUMT3RFFCUQXZHRDQF3KHZANCNFSM4IOXXTHA .

[saketdabi1385]

query = """select row_number() over (partition by date) from df;""" same error

[stonebig]

query = "select row_number() over (partition by date) from df$$" or query = "select row_number() over (order by date) from df$$"

may be better ? never tried with a date format though

[stonebig]

[saketdabi1385]

even this simple aint working .. its shocking

query = """select row_number() over () as rn from dff$$;"""

query_result = bsqldf(query)

q = bsqldf(query)

Regards Saket Dabi

On Thu, Aug 22, 2019 at 1:23 PM stonebig notifications@github.com wrote:

[image: image] https://user-images.githubusercontent.com/4312421/63547023-7e9d1480-c52b-11e9-944a-84108616c930.png

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yhat/pandasql/issues/77?email_source=notifications&email_token=AIYKYXPWWVFXPPPOC4WYAADQF3YVHA5CNFSM4IOXXTHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD46JCUQ#issuecomment-524063058, or mute the thread https://github.com/notifications/unsubscribe-auth/AIYKYXLTP6NLEVEIUI7JPBTQF3YVHANCNFSM4IOXXTHA .

[stonebig]

maybe try bsqldf("select sqlite_version()". only Python-3.7.4 has a fresh enough sqlite motor

[saketdabi1385]

its 3.21.0

On Thu, Aug 22, 2019 at 1:46 PM stonebig notifications@github.com wrote:

maybe try bsqldf("select sqlite_version()"

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yhat/pandasql/issues/77?email_source=notifications&email_token=AIYKYXMWEHAKCQ2433AM6DTQF33MDA5CNFSM4IOXXTHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD46K7LI#issuecomment-524070829, or mute the thread https://github.com/notifications/unsubscribe-auth/AIYKYXPRLLJQ3BCF3E4CNQ3QF33MDANCNFSM4IOXXTHA .

[stonebig]

so now, you know the problem

[saketdabi1385]

what did i miss ? version ?which version ?

On Thu, Aug 22, 2019 at 1:50 PM stonebig notifications@github.com wrote:

so now, you know the problem

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yhat/pandasql/issues/77?email_source=notifications&email_token=AIYKYXM4LYQKSRKTGEF6G2LQF332FA5CNFSM4IOXXTHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD46LJZY#issuecomment-524072167, or mute the thread https://github.com/notifications/unsubscribe-auth/AIYKYXLCS7CEYEO2IJEABK3QF332FANCNFSM4IOXXTHA .

[stonebig]

https://www.sqlite.org/releaselog/3_25_0.html

[stonebig]

https://bugs.python.org/issue35360

[saketdabi1385]

i did download the 3.25 version but how can i upgrade the package itself for it to use 3.25 instead of 3.21

Regards Saket Dabi

On Thu, Aug 22, 2019 at 1:56 PM stonebig notifications@github.com wrote:

https://bugs.python.org/issue35360

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yhat/pandasql/issues/77?email_source=notifications&email_token=AIYKYXNGEHLNI35NDHVWCF3QF34N7A5CNFSM4IOXXTHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD46LXHI#issuecomment-524073885, or mute the thread https://github.com/notifications/unsubscribe-auth/AIYKYXJLJ5EZD3IINNBTEBTQF34N7ANCNFSM4IOXXTHA .

[stonebig]

Replace the sqlite3.dll. see https://www.google.com/amp/s/dfir.blog/upgrading-pythons-sqlite/amp/

[saketdabi1385]

done - works ..much thanks

On Thu, Aug 22, 2019 at 2:32 PM stonebig notifications@github.com wrote:

Replace the sqlite3.dll. see https://www.google.com/amp/s/dfir.blog/upgrading-pythons-sqlite/amp/

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/yhat/pandasql/issues/77?email_source=notifications&email_token=AIYKYXL5WE4HLBATEIQNX3DQF4AYRA5CNFSM4IOXXTHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD46OXJQ#issuecomment-524086182, or mute the thread https://github.com/notifications/unsubscribe-auth/AIYKYXN5U2A3OMTWJEOCBU3QF4AYRANCNFSM4IOXXTHA .

[stonebig]

You may close the issue, if you are satisfied.

[saketdabi1385]

resolved