Working with Databases
1. Working with Databases
So far, the team has been working with data in files. But some data lives in a database instead. Today, we help them pull data from the Buildings Department's Postgres database.2. Querying a database from Polars
We first explain the high-level flow of querying a database from Polars. First, the team sends a SQL query to the database, along with connection details called a URI.3. Querying a database from Polars
The database sends the results back, and Polars turns them into a DataFrame. The connection to the database is handled by a process known as an engine. The team aren't familiar with URIs or engines, so we'll introduce them as we go along.4. Databases Polars can query
Before we write any code, let's quickly orient ourselves. Polars can work with a range of databases, including Postgres, MySQL, or SQL Server. It can also work with local databases such as SQLite and DuckDB. DuckDB is an embedded analytical database, which means it runs locally rather than on a separate database server like Postgres. This makes DuckDB a good fit for team members who want database features without managing a full server.5. Building permits table
The team runs a short SQL query which shows us that the table contains details of each application including the location and the nature of the work. They want to know how many permits of each type have been issued in 2026.6. Connection URI
The first thing they need to connect to the database is a uniform resource identifier, known as a URI. The URI sets out all of the information needed to make a query against the database. Here, the protocol is PostgreSQL, followed by the username and password, then the host and port, and finally the database name. The team get this URI from a database administrator. Now they can write their query.7. Querying a database
The team want to select the rows they want from the database and then do the rest of the pipeline in Polars. First, they define the SQL query that selects what they need.8. Querying a database
Then they pass that query and the URI to read_database_uri. Polars executes the query against the database and loads the result into a DataFrame.9. Querying a database
Once the selected rows are in Polars, the rest of the query looks familiar. Now the team groups by permit type, counts rows, and sorts the result.10. A grouped permit count
And we get the output as a DataFrame. It was the engine that sent the query to the database and then returned the data to Polars.11. Choose your engine
The read_database_uri function can use two different engines to connect to the database. We choose it with the engine argument. The default engine is ConnectorX, which supports a broad range of databases. The alternative engine is ADBC. This only works with a few databases, but includes popular databases such as Postgres and SQLite. But ADBC is often faster when it's available, and so we recommend it to the team for their Postgres queries. This link shows supported ADBC databases.12. Writing back to the database
After finishing the summary, the buildings team wants the pipeline result written back to the Postgres database. The team can do that with the write_database method on a DataFrame.13. Writing back to the database
We first pass the target table name and URI.14. Writing back to the database
We handle clashes with existing tables using the if_table_exists argument. Here we specify that we want to replace any existing table. But we can also append to an existing table or raise an exception if the table already exists. Some team members want to read from a DuckDB database in a pipeline. Do they use pl.read_database_uri for that?15. DuckDB integration
No, because DuckDB has a built-in method for outputting to Polars. First, we import duckdb and connect to a database that already has the permit summary table.16. DuckDB integration
We query the full table followed by .pl to get the result back as a Polars DataFrame.17. Let's practice!
Now it's time to practice reading from and writing to databases with Polars.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.