Hey all I just got started with prefect and like the workflo

Question

Hey all I just got started with prefect and like the workflow My question does prefect sqlalchmy support inserto into select queries I am trying to execute an insert into select columns from table query which works when I interact with the db directly but not through the SqlAlchemyConnector execute query string method Wasn t sure if it wasn t supported or I was doing something wrong

Sean Williams · Answer

Does it work if you use SQLAlchemy without Prefect It sounds like this might be a SQLAlchemy problem but it s tough to say without more info

Nate · Answer

can you share a minimal example of how you re trying to use `SqlAlchemyConnector execute query string ` with your `query string`

Albert Wong · Answer

sure one sec

Albert Wong · Answer

```with cte as select l load time at time zone america los angeles load time pst l tableau servername row number over partition by event obj gt gt traceUuid order by l load time desc rnk event obj event obj from log activity log l left outer join lateral jsonb array elements l event data as event obj on true insert into etl activity f select loading info load time pst tableau servername event event obj gt gt traceUuid trace uuid cast event obj gt event gt gt eventTime as timestamp with time zone at time zone america los angeles event time pst cast event obj gt event gt gt isError as boolean is error event obj gt event gt metadata gt gt eventCategory event category event obj gt event gt metadata gt gt eventType event type permissions event obj gt event gt gt granteeType grantee type cast event obj gt event gt gt granteeId as integer grantee id event obj gt event gt gt granteeValue grantee value event obj gt event gt gt capabilityValue capability value user cast event obj gt event gt gt actorUserId as integer actor user id cast event obj gt event gt gt initiatingUserId as integer initiating user id content event obj gt event gt gt authorizableType content type cast event obj gt event gt gt contentId as integer content id site event obj gt event gt gt siteLuid site luid from cte where rnk=1 and event obj gt gt traceUuid not in select trace uuid from etl activity f and date cast event obj gt event gt gt eventTime as timestamp with time zone at time zone america los angeles between start date and end date between 2023 03 01 and 2023 03 02 ```

Albert Wong · Answer

^^^ query string in sql etl activity f sql

Albert Wong · Answer

``` with open sql etl activity f sql as f sql query = f read format start date start date end date end date doesn t seem to support insert into select statements with SqlAlchemyConnector load destination block as connector connector execute sql query ```

Albert Wong · Answer

^^^ code snippet

Albert Wong · Answer

I haven t used sqlalchemy much so was going to try the regular library next

Albert Wong · Answer

that didn t work looks like it may be a sqlalchemy thing When I use a regular postgres library psycopg2 I m able to execute the raw sql through the methods below fine ``` cursor = connection cursor cursor execute sql query connection commit connection close ``` appreciate you hearing me out

Nate · Answer

I wonder if it would work if you used the <https github com PrefectHQ prefect sqlalchemy blob main prefect sqlalchemy credentials py L64|psycopg2 driver> when you make your `DatabaseCredentials` something like ```from prefect sqlalchemy import DatabaseCredentials SyncDriver db creds = DatabaseCredentials driver=SyncDriver POSTGRESQL PSYCOPG2 ```

Albert Wong · Answer

I was using the psycopg2 driver in the DatabaseCredentails and it supported singleton inserts just not the insert into select statement that gets me all my data in one query

Nate · Answer

ahh i see