MSSQL

ibis.mssql.connect

con = ibis.mssql.connect(
    user="username",
    password="password",
    host="hostname",
    driver="SQL Server",
)

con = ibis.mssql.connect(
    user="username",
    password="password",
    host="hostname",
    driver="FreeTDS",  # if you are using unixODBC
)

Connection Parameters

>>> import os
>>> import ibis
>>> host = os.environ.get("IBIS_TEST_MSSQL_HOST", "localhost")
>>> user = os.environ.get("IBIS_TEST_MSSQL_USER", "sa")
>>> password = os.environ.get("IBIS_TEST_MSSQL_PASSWORD", "1bis_Testing!")
>>> database = os.environ.get("IBIS_TEST_MSSQL_DATABASE", "ibis-testing")
>>> driver = os.environ.get("IBIS_TEST_MSSQL_PYODBC_DRIVER", "FreeTDS")
>>> con = ibis.mssql.connect(
...     database=database,
...     host=host,
...     user=user,
...     password=password,
...     driver=driver,
... )
>>> con.list_tables()
[...]
>>> t = con.table("functional_alltypes")
>>> t
DatabaseTable: functional_alltypes
  id              int32
  bool_col        boolean
  tinyint_col     int16
  smallint_col    int16
  int_col         int32
  bigint_col      int64
  float_col       float32
  double_col      float64
  date_string_col string
  string_col      string
  timestamp_col   timestamp(7)
  year            int32
  month           int32

ibis.connect URL format

In addition to ibis.mssql.connect, you can also connect to MSSQL by passing a properly-formatted MSSQL connection URL to ibis.connect:

con = ibis.connect(f"mssql://{user}:{password}@{host}:{port}")

Using Integrated Authentication

Integrated Authentication allows users to log in via NTLM or Kerberos. To make use of integrated authentication, use ibis.mssql.connect as documented above, but do not specify values for user or password. The PyODBC driver will then default to Integrated Authentication.

Some environments may require you to set additional keywords to connect. Additional information is available at the following pages:

• https://learn.microsoft.com/en-us/aspnet/web-api/overview/security/integrated-windows-authentication
• https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication

begin

begin(self)

compile

compile(self, expr, /, *, limit=None, params=None, pretty=False)

Compile an expression to a SQL string.

connect

connect(self, *args, **kwargs)

Connect to the database.

create_catalog

create_catalog(self, name, /, *, force=False)

Create a new catalog.

create_database

create_database(self, name, /, *, catalog=None, force=False)

Create a database named name in catalog.

create_table

create_table(self, name, /, obj=None, *, schema=None, database=None, temp=None, overwrite=False)

Create a new table.

create_view

create_view(self, name, /, obj, *, database=None, overwrite=False)

Create a view from an Ibis expression.

disconnect

disconnect(self)

Disconnect from the backend.

drop_catalog

drop_catalog(self, name, /, *, force=False)

Drop a catalog with name name.

drop_database

drop_database(self, name, /, *, catalog=None, force=False)

Drop the database with name in catalog.

drop_table

drop_table(self, name, /, *, database=None, force=False)

Drop a table from the backend.

drop_view

drop_view(self, name, /, *, database=None, force=False)

Drop a view from the backend.

execute

execute(self, expr, /, *, params=None, limit=None, **kwargs)

Execute an Ibis expression and return a pandas DataFrame, Series, or scalar.

from_connection

from_connection(cls, con, /)

Create an Ibis client from an existing connection to a MSSQL database.

get_schema

get_schema(self, name, *, catalog=None, database=None)

has_operation

has_operation(cls, operation, /)

Return whether the backend supports the given operation.

insert

insert(self, name, /, obj, *, database=None, overwrite=False)

Insert data into a table.

list_catalogs

list_catalogs(self, *, like=None)

List existing catalogs in the current connection.

list_databases

list_databases(self, *, like=None, catalog=None)

List existing databases in the current connection.

list_tables

list_tables(self, *, like=None, database=None)

The table names that match like in the given database.

For some backends, the tables may be files in a directory, or other equivalent entities in a SQL database.

>>> import ibis
>>> con = ibis.duckdb.connect()
>>> foo = con.create_table("foo", schema=ibis.schema(dict(a="int")))
>>> con.list_tables()
['foo']
>>> bar = con.create_view("bar", foo)
>>> con.list_tables()
['bar', 'foo']
>>> con.create_database("my_database")
>>> con.list_tables(database="my_database")
[]
>>> con.raw_sql("CREATE TABLE my_database.baz (a INTEGER)")
<duckdb.duckdb.DuckDBPyConnection object at 0x...>
>>> con.list_tables(database="my_database")
['baz']

raw_sql

raw_sql(self, query, **kwargs)

read_csv

read_csv(self, path, /, *, table_name=None, **kwargs)

Register a CSV file as a table in the current backend.

read_delta

read_delta(self, path, /, *, table_name=None, **kwargs)

Register a Delta Lake table in the current database.

read_json

read_json(self, path, /, *, table_name=None, **kwargs)

Register a JSON file as a table in the current backend.

read_parquet

read_parquet(self, path, /, *, table_name=None, **kwargs)

Register a parquet file as a table in the current backend.

reconnect

reconnect(self)

Reconnect to the database already configured with connect.

register_options

register_options(cls)

Register custom backend options.

rename_table

rename_table(self, old_name, new_name)

Rename an existing table.

sql

sql(self, query, /, *, schema=None, dialect=None)

Create an Ibis table expression from a SQL query.

table

table(self, name, /, *, database=None)

Construct a table expression from the corresponding table in the backend.

>>> import ibis
>>> backend = ibis.duckdb.connect()

>>> backend.table("foo")

>>> backend.table("foo", database="bar")

>>> backend.table("foo", database=("baz", "bar"))

to_csv

to_csv(self, expr, /, path, *, params=None, **kwargs)

Write the results of executing the given expression to a CSV file.

This method is eager and will execute the associated expression immediately.

to_delta

to_delta(self, expr, /, path, *, params=None, **kwargs)

Write the results of executing the given expression to a Delta Lake table.

This method is eager and will execute the associated expression immediately.

to_json

to_json(self, expr, /, path, **kwargs)

Write the results of expr to a json file of [{column -> value}, …] objects.

This method is eager and will execute the associated expression immediately.

to_pandas

to_pandas(self, expr, /, *, params=None, limit=None, **kwargs)

Execute an Ibis expression and return a pandas DataFrame, Series, or scalar.

to_pandas_batches

to_pandas_batches(self, expr, /, *, params=None, limit=None, chunk_size=1000000, **kwargs)

Execute an Ibis expression and return an iterator of pandas DataFrames.

to_parquet

to_parquet(self, expr, /, path, *, params=None, **kwargs)

Write the results of executing the given expression to a parquet file.

This method is eager and will execute the associated expression immediately.

to_parquet_dir

to_parquet_dir(self, expr, /, directory, *, params=None, **kwargs)

Write the results of executing the given expression to a parquet file in a directory.

This method is eager and will execute the associated expression immediately.

to_polars

to_polars(self, expr, /, *, params=None, limit=None, **kwargs)

Execute expression and return results in as a polars DataFrame.

This method is eager and will execute the associated expression immediately.

to_pyarrow

to_pyarrow(self, expr, /, *, params=None, limit=None, **kwargs)

Execute expression to a pyarrow object.

This method is eager and will execute the associated expression immediately.

to_pyarrow_batches

to_pyarrow_batches(self, expr, /, *, params=None, limit=None, chunk_size=1000000, **_)

Execute expression and return an iterator of PyArrow record batches.

This method is eager and will execute the associated expression immediately.

to_torch

to_torch(self, expr, /, *, params=None, limit=None, **kwargs)

Execute an expression and return results as a dictionary of torch tensors.

truncate_table

truncate_table(self, name, /, *, database=None)

Delete all rows from a table.