How to read SQL database with Polars

Question

I am trying to read a SQL database that contains a username and a password. I did:

import polars as pl

server = serveur # confidential 
user = user # confidential 
password = password # confidential 
database= database # confidential 

conn_str =f"mssql://{user}:{password}@{server}/{database}?encrypt=true"

query = "SELECT * FROM [dbo].[my_table]"
df = pl.read_database(query=query, connection_uri=conn_str)

I get an error message which is:

TypeError: read_database() got an unexpected keyword argument 'connection_uri'

How is it then possible to read a SQL database from Polars?

Answer 1

There're 2 (currently, polars v1.7.0) different ways to connect to MS SQL DB from polars:

1. Using ODBC connection string / SqlAlchemy connection.

Method is called read_database() and it has connection parameter:

An instantiated connection (or cursor/client object) that the query can be executed against. Can also pass a valid ODBC connection string, identified as such if it contains the string “Driver={…}”, in which case the arrow-odbc package will be used to establish the connection and return Arrow-native data to Polars. Async driver connections are also supported, though this is currently considered unstable.

Simple example of ODBC connection usage might look like:

conn = """
    Driver={SQL Server Native Client 11.0};
    Server=server;
    Database=db;
    Uid=username;
    Pwd=password;
    Trusted_Connection=yes;
"""

df = pl.read_database("select * from table", conn)

You can see possible parameters for your connection string here - sql server connection string.

2. Using connectorX or adbc engine.

Method is called read_database_uri. It accepts uri parameter:

A connectorx or ADBC connection URI string that starts with the backend’s driver name, for example:

• “postgresql://user:pass@server:port/database”
• “snowflake://user:pass@account/database/schema?warehouse=warehouse&role=role”

The caller is responsible for escaping any special characters in the string, which will be passed “as-is” to the underlying engine (this is most often required when coming across special characters in the password).

In theory this should work, but I couldn't make it work on my machine so far (I am not the only one - see github.com):

conn = "mssql://user:pwd@server:port/db"

pl.read_database_uri("select * from table", conn)

See some information about connectorx here - MsSQL Connection.