Querying data with sql statements instead of dataframe API?

[nikhilmakan02]

Hi all is it possible to actually execute a given SQL statement against the backend of choice instead of using the Dataframe API? I am using MSSQL.

I know that may defeat the purpose of using Ibis in the first place. However, I am a building a Web API and there are features of Ibis I want to use with the Dataframe API but at the same time I would like to have an end point on my API that takes in a SQL string and executes that.

Thanks!

[cpcloud]

Hi @nikhilmakan02 👋🏻! Welcome to ibis, and thanks for opening up a discussion about this.

is it possible to actually execute a given SQL statement against the backend of choice instead of using the Dataframe API?

Indeed it is possible.

You can do this with the Backend.sql method, or with the Table.sql method.

Backend.sql

In [7]: from ibis.interactive import *

In [8]: con = ibis.mssql.connect(user="sa", password="1bis_Testing!", database="ibis_testing")

In [9]: con.list_tables()
Out[9]: ['awards_players', 'batting', 'diamonds', 'functional_alltypes', 'win']

In [10]: con.sql("SELECT TOP 5 * FROM batting ORDER BY yearID")
Out[10]:
┏━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━┓
┃ playerID  ┃ yearID ┃ stint ┃ teamID ┃ lgID   ┃ G     ┃ AB    ┃ R     ┃ H     ┃ X2B   ┃ X3B   ┃ HR    ┃ RBI   ┃ … ┃
┡━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━┩
│ string    │ int64  │ int64 │ string │ string │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ … │
├───────────┼────────┼───────┼────────┼────────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───┤
│ armstbo01 │   1871 │     1 │ FW1    │ NA     │    12 │    49 │     9 │    11 │     2 │     1 │     0 │     5 │ … │
│ ansonca01 │   1871 │     1 │ RC1    │ NA     │    25 │   120 │    29 │    39 │    11 │     3 │     0 │    16 │ … │
│ allisdo01 │   1871 │     1 │ WS3    │ NA     │    27 │   133 │    28 │    44 │    10 │     2 │     2 │    27 │ … │
│ allisar01 │   1871 │     1 │ CL1    │ NA     │    29 │   137 │    28 │    40 │     4 │     5 │     0 │    19 │ … │
│ addybo01  │   1871 │     1 │ RC1    │ NA     │    25 │   118 │    30 │    32 │     6 │     0 │     0 │    13 │ … │
└───────────┴────────┴───────┴────────┴────────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───┘

Table.sql

In [1]: from ibis.interactive import *

In [2]: con = ibis.mssql.connect(user="sa", password="1bis_Testing!", database="ibis_testing")

In [3]: con.list_tables()
Out[3]: ['awards_players', 'batting', 'diamonds', 'functional_alltypes', 'win']

In [4]: batting = con.tables.batting

In [5]: joined = batting.alias("ob").sql("SELECT l.playerID AS lp, r.playerID rp, l.G - r.G AS delta FROM batting AS l
   ...: JOIN ob AS r ON l.yearID = r.yearID AND l.teamID = r.teamID AND l.lgID = r.lgID AND l.playerID <> r.playerID")

In [6]: joined
Out[6]:
┏━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━┓
┃ lp        ┃ rp        ┃ delta ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━┩
│ string    │ string    │ int64 │
├───────────┼───────────┼───────┤
│ abercda01 │ beaveed01 │    -2 │
│ abercda01 │ bellast01 │   -28 │
│ abercda01 │ connone01 │    -6 │
│ abercda01 │ cravebi01 │   -26 │
│ abercda01 │ flowedi01 │   -20 │
│ abercda01 │ flynncl01 │   -28 │
│ abercda01 │ kingst01  │   -28 │
│ abercda01 │ mcgeami01 │   -28 │
│ abercda01 │ mcmuljo01 │   -28 │
│ abercda01 │ pikeli01  │   -27 │
│ …         │ …         │     … │
└───────────┴───────────┴───────┘

[nikhilmakan02]

@cpcloud I thought I understood the above but I have a few follow up questions after trying to use it.

1. Where is this method defined in the API reference as I can't seem to locate it.

2. I tried to use the above method to submit a SQL insert statement however I realised through the logging that the query statement you provide gets wrapped in a select statement by Ibis which I guess doesn't make a whole lot of sense for a SQL insert statement and subsequently throws an error.

pymssql._mssql.MSSQLDatabaseException: (156, b"Incorrect syntax near the keyword 'FROM'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

3. I am came across the raw_sql method which I would be keen to understand how to use this and what the cursor object returned is. Is this a pymssql cursor in the case of using the MSSQL backend with the pymssql driver. I believe I tried calling .fetchall() and .execute() on the return cursor object however this did not work.

The above questions are a follow on to the discussion 6402 but make more sense under this topic. @gforsyth for reference to my comment in the other discussion.

[gforsyth]

API docs for .sql are here: https://ibis-project.org/api/backends/sql/#ibis.backends.base.sql.BaseSQLBackend.sql (that's a hard method name to search for in ibis...)

You'll get back an expression (.sql is still lazy) but you can either run .cache() or execute() or to_pyarrow() to make it run.

raw_sql returns a cursor -- it will be a pymssql cursor with the MSSQL backend