Polars throw OperationalError when trying to write dataframe to a sqlite3 database

Question

import polars as pl
import sqlite3

conn = sqlite3.connect("test.db")
df = pl.DataFrame({"col1": [1, 2, 3]})

According to the documentation of pl.write_database, I need to pass a connection URI string e.g. "sqlite:////path/to/database.db" for SQLite database:

df.write_database("test_table", f"sqlite:////test.db", if_table_exists="replace")

However, I got the following error:

OperationalError: (sqlite3.OperationalError) unable to open database file

EDIT: Based on the answer, install SQLAlchemy with the pip install polars[sqlalchemy] command.

Answer 1

Here is an example for writing / reading sqlite tables using polars.

Write table to database (sqlalchemy needs to be installed).

import polars as pl

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

df.write_database(
    "my_table",
    connection="sqlite:///database.db",
    if_table_exists="replace"
)

Read table from database (connectorx needs to be installed).

pl.read_database_uri(query="SELECT * FROM my_table", uri="sqlite://database.db")

Note the format of the connection URI.

• sqlite:///:memory: (or, sqlite://)
• sqlite:///relative/path/to/file.db
• sqlite:////absolute/path/to/file.db
• sqlite:///c:/absolute/path/on/windows/file.db (credit to @Thomas)