4

How can I directly connect MS SQL Server to polars?

The documentation does not list any supported connections but recommends the use of pandas.

Update:

SQL Server Authentication works per answer, but Windows domain authentication is not working. see issue

Isaacnfairplay
  • 217
  • 2
  • 18

3 Answers3

4

Here you can connect to MS SQL Server with Polar (connectorx under the hood). Just use a connection string:

import polars as pl

# usually don't store sensitive info in plain text
username = 'my_username'
password = '1234'
server = 'SERVER1'
database = 'db1'
trusted_conn = 'no' # or yes

conn = f'mssql://{username}:{password}@{server}/{database}?driver=SQL+Server&trusted_connection={trusted_conn}'

query = "SELECT * FROM table1"

df = pl.read_sql(query, conn)
Sura-da
  • 301
  • 3
  • 12
3

Ahh, actually MsSQL is supported for loading directly into polars (via the underlying library that does the work, which is connectorx); the documentation is just slightly out of date - I'll take a look and refresh it accordingly.

alexander-beedie
  • 427
  • 2
  • 8
0

The class below allows for easy connection to connectorx.

import keyring
from typing import Optional


class KeyringSQL:
    def __init__(self,  username: str, server: str, database: str, server_type:  Optional[str]= 'mssql' ,driver: Optional[str] = 'SQL+Server', trusted_connection: Optional[bool] = False):
        self.server_type = server_type
        self.username = username
        self.server = server
        self.database = database
        self.driver = driver
        self.trusted_connection = trusted_connection

    def safe_to_expose_dict(self):
        return {'server_type': self.server_type, 'server': self.server, 'database': self.database, 'driver': self.driver}
    def full_table_path(self, tablename: str):
        if self.server_type == 'mssql':
            return f"[{self.database}].[dbo].[{tablename}]"
        return None
    def get_connection_uri(self) -> str:
        password = self._get_password() if not self.trusted_connection else ''
        append = '&trusted_connection=true' if self.trusted_connection else ''
        return f"{self.server_type}://{self.username}:{password}@{self.server}/{self.database}?driver={self.driver}{append}"

    def _get_password(self) -> str:
        password = keyring.get_password(self.server, self.username)
        if password is None:
            password = self._ask_password()
            keyring.set_password(self.server, self.username, password)
        return password

    def _ask_password(self) -> str:
        while True:
            password1 = input(f"Please set the password for {self.server}")
            password2 = input(f"Please confirm the password for {self.server}")
            if password1 == password2:
                return password1
            print("Passwords did not match. Please try again.")
Isaacnfairplay
  • 217
  • 2
  • 18