CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyapache-arrow
MMCM_
MMCM_

Reputation: 639

SQLAlchemy select with arrow ADBC driver

I am trying to use SQLAlchemy queries with the ADBC driver.

The main issue is that I use a lot of sqlalchemy queries, i.e. query = select(*cols)... and I can't simply mix the two like in the following way, as query needs to be a str type with the ADBC engine.

from adbc_driver_postgresql import dbapi
from sqlalchemy.sql import select

query = select(*cols) ...

with dbapi.connect('postgres:///db_name') as conn:

    data = pd.read_sql(query), con=conn)

The question is how to combine the two? Simply str(query) won't work. Or is there a way to use create_engine() with the ADBC driver

Upvotes: 0

Views: 376

Answers (1)

Mahrez BenHamad
Mahrez BenHamad

Reputation: 2096

I think, calling str(query) won't work because it doesn't necessarily produce valid SQL (depending on the database dialect). So, you have to convert SQLAlchemy Query to a String then use the Compiled SQL with ADBC.

Exp:

from sqlalchemy import create_engine, select
from sqlalchemy.dialects import postgresql
from adbc_driver_postgresql import dbapi
import pandas as pd
   
# Build your SQLAlchemy query
query = select(*cols)

# Compile the query to a PostgreSQL-compatible SQL string
compiled_query = query.compile(dialect=postgresql.dialect())

# Convert the compiled query to a string
query_str = str(compiled_query)

# Using ADBC to execute the query and read the data into a DataFrame
with dbapi.connect('postgres:///db_name') as conn:
    data = pd.read_sql(query_str, con=conn)

Update:

About the tmp table which arises because these tables are session-bound, meaning they only exist for the duration of the session or connection.

For that, you can try:

from sqlalchemy import create_engine, MetaData, Table, select
import pandas as pd

with engine.connect() as conn:
    conn.execute("CREATE TEMPORARY TABLE temp_table AS SELECT * FROM original_table WHERE condition")

    metadata = MetaData()
    temp_table = Table('temp_table', metadata, autoload_with=conn)
    query = select(temp_table.c.column1, temp_table.c.column2)

    data = pd.read_sql(query, con=conn)

Upvotes: 1

Related Questions