DuckDB - What's the difference between .sql and .execute function?

Question

I am a newbie using DuckDb library in python and while going through docs I stumbled upon 2 functions to execute sql instructions, namely execute() and sql().

What's the difference between the 2? I am really scratching my head with this.

Answer 1

While sql and execute can be used to achieve the same results, they have slight differences which may impact which function you use.

The sql function runs the query as-is. It will return a DuckDBPyRelation which allows "constructing relationships".

duckdb.sql(query: str, alias: str = 'query_relation', connection: duckdb.DuckDBPyConnection = None) → duckdb.DuckDBPyRelation

Run a SQL query. If it is a SELECT statement, create a relation object from the given SQL query, otherwise run the query as-is.

The execute function will also run queries, but can handle prepared statements that accepts parameters and returns the connection DuckDBPyConnection instead of a relationship.

duckdb.execute(query: str, parameters: object = None, multiple_parameter_sets: bool = False, connection: duckdb.DuckDBPyConnection = None) → duckdb.DuckDBPyConnection

Execute the given SQL query, optionally using prepared statements with parameters set