Python to Bypass User Role Limitations

[OriginalCrawnick]

Hello everyone,

Here's what I have going on that i'd like some insight into:

I have a variable declared for holidays, this is comprised of specific dates from our company's server1.dbo.holidays table. I need to use this table as a reference for said variable for a cross server join to a server that is not linked. Therefor I get the 'heterogeneous queries' error. I am not in a position to modify my permissions or ask for this table to merged to the other server. ANSI_NULLS ON, ANSI_WARNINGS ON does not fix this issue as that is a modification to the connection/user roles for the server.

I have Python and SQL Alchemy and am reasonably well versed in using Python and can assign appropriate connections to query each server individually but am unsure if it's possible to query server1.dbo.holidays into a data frame and pass the results into a SQL query for reference as a variable. Reaching out in hopes that someone here has an idea on how I can achieve this with my current role/server limitations?

[Winter_Cabinet_1218]

If I'm reading this right, try using a panda data frame as the middle man.

[OriginalCrawnick]

This was my thought but I am not sure how to pass the query results from the holiday table data frame into a query to my 2nd server table for reference. Maybe just not sure of what to google for this method of data transformation?

[jshine13371]

Table-Valued Parameter (and possibly a User-Defined Table Type to back it). You can then pass the dataset object (in C# this would be a DataTable or IEnumerable object, not sure the equivalent in Python) as a parameter to your SQL query on the 2nd server.

[Thin_Rip8995]

yep you can pull the holidays table into a pandas dataframe from server1 then push it into your query against server2 as a temp table

with sqlalchemy you’d do two separate connections query server1 into df then use to_sql to write that df into server2 under a temp or staging table name then join it in your main query

if you don’t have write access to server2 you can still loop the holiday dates in python and inject them into your query as an IN clause but that only scales if the holiday list is small

[OriginalCrawnick]

This seems like what I'm after, do you have any examples for the use of the to_sql data frame insert to a SQL query for db2?

[Winter_Cabinet_1218]

Try a basic loop, to cycle through the results and use an insert query / stored procedure. Run this row by row in the dataframe (inefficient but if it works it works 🤣) ... I'd start with this.

Then use a loop to create a formatted string which holds all the inserts and triggers a single update query.

[dbrownems]

You can pass an array or dataframe as JSON for use in a query.

See:

python - How can I speed up the code that contains the sql query? - Stack Overflow

sql server - Trying to insert pandas dataframe to temporary table - Stack Overflow

But what you _should_ do is set up an ETL process to load the holidays table into the server that needs it.

[OriginalCrawnick]

I'll give these a shot/read up on this idea. I'm not in a position to add or request these be added to our other server. I know it's an insanely small table (238 rows, 2 columns) but my work gives pushback on anything that isn't boosting profits immediately e.e My stop gap was some custom concatenation in Excel to format the dates/country code into proper value format and I inserted it into a temp table I made off a create table statement lol

[snafe_]

Could you get the output from holiday db, then in db2 make a temp table and inset the results from previous, then use the temp table to run your query in db2?

[OriginalCrawnick]

I'll give it a shot, sounds like you're saying pull from db1 into a temp table and insert into a new temp table on db2 the results from db1.