CODEWITHSUNDEEP
pythonsqlpymssql
Bas Hofstra
Bas Hofstra

Reputation: 47

python - Fetching rows in between a range of rownumbers with python and pymssql

I want to fetch a specific range of row numbers from a database with the python package pymssql. I'm using the following code that doesn't seem to work

# connection details to SQL
con = pymssql.connect("some connection credentials")

df = pd.read_sql("""SELECT
            [col1], 
            [col2],
            WHERE rownum 
            BETWEEN 0 AND 1000""", 
            con = con)

It shows the following error:

(207, "Invalid column name 'rownum'.DB-Lib error message 20018, severity
16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib
error message 20018, severity 16:\nGeneral SQL Server error: Check 
messages from the SQL Server\n")

What am I doing wrong? It is not a connection issue, If I'm selecting the top 1000 rows it works flawlessly.

Upvotes: 1

Views: 549

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

It looks like you want to use the ROW_NUMBER() windowing function, which you could do with a query similar to this:

sql = """\
SELECT [id], [txtcol] 
FROM 
    (
        SELECT [id], [txtcol], ROW_NUMBER() OVER (ORDER BY id) AS [rownum]
        FROM test10k
    ) AS subquery
WHERE [rownum] BETWEEN 2 AND 4
"""
crsr.execute(sql)

Upvotes: 1

Related Questions