Write dataframe support

[akommera]

How to write ploars dataframe using mssql-python ?

polars.DataFrame.write_database

[github-actions]

Hi ak (@akommera), thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[gordthompson]

If you are working from the polars documentation here

https://docs.pola.rs/user-guide/io/database/#sqlalchemy

be aware that support for mssql-python is included in SQLAlchemy 2.1, which is currently in beta. So, your command to install the additional dependencies should be

$ pip install SQLAlchemy==2.1.0b3 mssql-python pandas pyarrow

and your example code should look like this

uri = "mssql+mssqlpython://username:password@server:port/database"
df = pl.DataFrame({"foo": [1, 2, 3]})

df.write_database(table_name="records",  connection=uri)

[arkdvd]

What should be connection string for win auth?

[gordthompson]

According to

https://docs.sqlalchemy.org/en/21/dialects/mssql.html#connection-strings

that would be

uri = "mssql+mssqlpython://hostname/database?authentication=ActiveDirectoryIntegrated"

[arkdvd]

Error:
ConnectionStringParseError: Connection string parsing failed:
Incomplete specification: keyword '{' has no value (missing '=')
Unknown keyword 'authentication}' is not recognized

uri = "mssql+mssqlpython://hostname\Instancename:portnum/database?authentication=ActiveDirectoryIntegrated"
uri = "mssql+mssqlpython://hostname\Instancename/database?authentication=ActiveDirectoryIntegrated"

Ran with all these libs :
Name: SQLAlchemy
Version: 2.1.0b3

Name: pandas
Version: 3.0.1

Name: pyarrow
Version: 25.0.0

Name: mssql-python
Version: 1.12.0

[subrata-ms]

Hi ak (@akommera), arkdvd , I was unable to reproduce the issue on my end. The two connection strings shared earlier are being parsed correctly when using SQLAlchemy 2.1.0b3 together with the mssql-python driver, and I do not observe any parsing-related problems.
The specific two-line error message you're seeing is typically generated only when the connection string that ultimately reaches mssql_python.connect() contains an unbalanced {...} segment that spans across a ; delimiter and closes immediately before a = pair. For example:
Server=host\Inst;{ ;authentication}=ActiveDirectoryIntegrated;Database=db
^ ^
stray '{' closing '}' ends up on the 'authentication' side
The parser processes the connection string by splitting it into tokens separated by ;. In the example above, it encounters:

1. { alone → "Incomplete specification: keyword '{' has no value (missing '=')"
2. authentication}=... → key parsed as literal authentication} → "Unknown keyword 'authentication}' is not recognized"

Next Steps
My current hypothesis is that the parsing error is not being caused by the URI itself. Instead, it appears that something in the environment may be modifying the connection string and injecting additional braces around a key=value segment before it reaches the driver.
Could you please run the following script in the environment where the issue occurs and share the output from the print() statement?

Script:
import sqlalchemy as sa

uri = r"mssql+mssqlpython://hostname\Instancename:portnum/database?authentication=ActiveDirectoryIntegrated"

url = sa.engine.make_url(uri)
engine = sa.create_engine(uri)
print(engine.dialect.create_connect_args(url))

For a correctly parsed connection string, we would expect output similar to:
(('Server=hostname\Instancename,1433;Database=database;Authentication=ActiveDirectoryIntegrated',), {})

This output will help us determine whether the connection string is being altered before it reaches the mssql-python driver and narrow down the source of the issue.

[gordthompson]

FWIW, I prefer to use URL.create() wherever possible. It avoids string parsing, handles "funny" characters in attributes (e.g., passwords), etc..

import polars as pl
import sqlalchemy as sa

engine = sa.create_engine(
    sa.engine.URL.create(
        drivername="mssql+mssqlpython",
        username="scott",
        password="tiger^5HHH",
        host=r"192.168.0.182\SQLEXPRESS01",
        database="test",
        query={"TrustServerCertificate": "yes"},
    )
)

df = pl.DataFrame({"foo": [1, 2, 3]})

df.write_database(table_name="records", connection=engine)

[subrata-ms]

Hi ak (@akommera) , arkdvd
I’m closing this bug for now as we need some additional information to continue the investigation. Please feel free to reopen the bug once the requested data is available, and we’ll be happy to resume the analysis.

Thanks
Subrata