Using Sqlalchemy to Execute the Query

Oct 13, 2025 by pinkandtrash.com 3 views

Describe the Bug

When running a query on a Postgres database that contains a POSIX operator using daft.read_sql, a query parsing error is thrown. The error indicates that the POSIX operator ~ is incorrect, even though the query is correct and can be executed successfully using psql. This issue arises due to the way daft uses sqlglot to parse the queries.

The Problematic Code

Below is a snippet of the code that throws the error:

from sqlalchemy import create_engine
import daft

engine = create_engine('postgresql://user:password@localhost:5432/test_db')
query = """
SELECT count(*)
FROM addresses a
WHERE state ~ 'test_state\.*'
"""

df = daft.read_sql(query, engine.connect)
print(df.show())

The Error Message

The error message thrown by daft is:

File "/Users/onkarravgan/Desktop/projects/application-sdk/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 1570, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/Users/onkarravgan/Desktop/projects/application-sdk/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 1611, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 4, Col: 13.
  
SELECT count(*)
FROM addresses a
WHERE state ~ 'test_state\.*'

The Root Cause

Upon further investigation, it was found that daft uses sqlglot to parse the queries. However, when the query is parsed, the dialect for that query is not passed into the method, resulting in the error.

Question 1: Should This Be Fixed by Passing `dialect=target_dialect`?

Yes, this should be fixed by passing dialect=target_dialect. The correct fix is to pass the dialect to the sqlglot parser, which will allow it to correctly parse the query.

Question 2: Can the Query Parsing Via Sqlglot Be Somehow Skipped Altogether?

Yes, the query parsing via sqlglot can be skipped altogether. Since we use sqlalchemy, an option to skip this query parsing would be ideal. This can be achieved by using the sqlalchemy engine to execute the query directly, without relying on daft and sqlglot.

To Reproduce

To reproduce this issue, execute the following snippet on a Postgres database:

from sqlalchemy import create_engine
import daft

engine = create_engine('postgresql://user:password@localhost:5432/test_db')
query = """
SELECT count(*)
FROM addresses a
WHERE state ~ 'test_state\.*'
"""

df = daft.read_sql(query, engine.connect)
print(df.show())

Expected Behavior

The expected behavior is that the query should be parsed correctly, and the result should be returned as a DataFrame.

Component(s)

The component affected by this issue is the SQL parser.

Additional Context

No additional context is required to reproduce this issue.

Fixing the Issue

To fix this issue, we can modify the daft code to pass the dialect to the sqlglot parser. Alternatively, we can use the sqlalchemy engine to execute the query directly, without relying on daft and sqlglot.

Using Sqlalchemy to Execute the Query

Here is an example of how we can use sqlalchemy to execute the query directly:

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine('postgresql://user:password@localhost:5432/test_db')
query = """
SELECT count(*)
FROM addresses a
WHERE state ~ 'test_state\.*'
"""

result = engine.execute(query)
df = pd.DataFrame(result.fetchall())
print(df)

This code uses the sqlalchemy engine to execute the query directly, without relying on daft and sqlglot. The result is returned as a DataFrame, which can be printed or further processed as needed.

Conclusion

Q1: What is the root cause of the issue?

A1: The root cause of the issue is that daft uses sqlglot to parse the queries, but when the query is parsed, the dialect for that query is not passed into the method, resulting in the error.

Q2: How can we fix this issue?

A2: We can fix this issue by passing the dialect to the sqlglot parser, or by using the sqlalchemy engine to execute the query directly.

Q3: What is the benefit of using `sqlalchemy` to execute the query?

A3: The benefit of using sqlalchemy to execute the query is that we can avoid the issue altogether and return the result as a DataFrame.

Q4: How can we modify the `daft` code to pass the dialect to the `sqlglot` parser?

A4: We can modify the daft code to pass the dialect to the sqlglot parser by adding the dialect parameter to the sqlglot parser.

Q5: What is the syntax for passing the dialect to the `sqlglot` parser?

A5: The syntax for passing the dialect to the sqlglot parser is dialect=target_dialect.

Q6: Can we skip the query parsing via `sqlglot` altogether?

A6: Yes, we can skip the query parsing via sqlglot altogether by using the sqlalchemy engine to execute the query directly.

Q7: What is the benefit of skipping the query parsing via `sqlglot`?

A7: The benefit of skipping the query parsing via sqlglot is that we can avoid the issue altogether and return the result as a DataFrame.

Q8: How can we use the `sqlalchemy` engine to execute the query directly?

A8: We can use the sqlalchemy engine to execute the query directly by creating a create_engine object and executing the query using the execute method.

Q9: What is the syntax for using the `sqlalchemy` engine to execute the query directly?

A9: The syntax for using the sqlalchemy engine to execute the query directly is engine.execute(query).

Q10: Can we use the `sqlalchemy` engine to execute the query directly with a DataFrame?

A10: Yes, we can use the sqlalchemy engine to execute the query directly with a DataFrame by using the pd.DataFrame function to convert the result to a DataFrame.

Q11: What is the benefit of using the `sqlalchemy` engine to execute the query directly with a DataFrame?

A11: The benefit of using the sqlalchemy engine to execute the query directly with a DataFrame is that we can return the result as a DataFrame, which can be easily processed and analyzed.

Q12: How can we modify the `daft` code to use the `sqlalchemy` engine to execute the query directly?

A12: We can modify the daft code to use the sqlalchemy engine to execute the query directly by replacing the daft.read_sql function with the sqlalchemy engine.

Q13: What is the benefit of modifying the `daft` code to use the `sqlalchemy` engine to execute the query directly?

A13: The benefit of modifying the daft code to use the sqlalchemy engine to execute the query directly is that we can avoid the issue altogether and return the result as a DataFrame.

Q14: Can we use the `sqlalchemy` engine to execute the query directly with a custom dialect?

A14: Yes, we can use the sqlalchemy engine to execute the query directly with a custom dialect by passing the custom dialect to the create_engine function.

Q15: What is the benefit of using the `sqlalchemy` engine to execute the query directly with a custom dialect?

A15: The benefit of using the sqlalchemy engine to execute the query directly with a custom dialect is that we can return the result as a DataFrame, which can be easily processed and analyzed.

Conclusion

In conclusion, the issue of running a Postgres query with a POSIX operator using daft.read_sql is caused by the way daft uses sqlglot to parse the queries. To fix this issue, we can modify the daft code to pass the dialect to the sqlglot parser, or we can use the sqlalchemy engine to execute the query directly. By using sqlalchemy to execute the query, we can avoid the issue altogether and return the result as a DataFrame.