Hi Mike!
I guess that I am looking for advice on a 'reasonably good way' to do each
of the different parts of the solution. What I have at the moment is all
hand-rolled SQL and I guess I am looking at some way of moving some of this
to sqlalchemy so that the technique is more composable.
1. Is there any SQL Server dialect support for CROSS APPLY OPENJSON? any
tie-ins with json schema? How do you suggest making a CTE given a
table-like json schema?
2. What is the best way to emulate multi-statement batches? Make
individual statements in SA and then compile them to text and reassemble
them with sql.text? This is the equivalent to what is referred to as
'inner_sql' in my example program.
3. Is there a SQL Alchemy block for EXEC sproc? how might I rewrite the
equivalent of what is assigned to outer_sql in myexample program?
4. is dropping down to dbapi/pyodbc still the best way to handle
multiple result-sets?
Hope you are doing well and looking forward to getting back up to speed on
what sqlalchemy and seeing what has been incorporated since 0.8!
pjjH
On Monday, November 30, 2020 at 1:08:14 PM UTC-5 Mike Bayer wrote:
> hey there -
>
> took a quick look and we don't support anything with variable
> declarations, multiple statements in a single string, or multiple result
> sets. All of that is outside of SQLAlchemy expression languages scope.
>
> Since what you're doing is extremely specific to a certain database, if
> your approach works what is the problem you are seeking to solve ?
>
>
>
> On Mon, Nov 30, 2020, at 10:03 AM, Paul Harrington wrote:
>
> Hello Community!
> I have not posted here for several years as I have been getting along just
> fine with the excellent SQLAlchemy toolkit and the excellent documentation.
> However, I am trying to do something a bit fancy since all the SQL Server
> dataservers I use are running versions that support OPENJSON. A number of
> our applications have used staging techniques to copy tables of data to the
> dataserver so that that can be used in JOINs (e.g. send up 10,000
> identifiers into the table rather than doing RBAR or batching into IN
> clauses). This technique is IO intensive and has quite a bit of overhead.
> An alternative approach is to serialize the data into a big blob of JSON,
> send it over as a single scalar and then expand it out to a table via a CTE
> on the dataserver. We have found the performance of this to be excellent
> from scripting language, presumably because we are avoiding so many API
> calls. However, I have not found a way to do this cleanly from SQLAlchemy
> and end up using sql.text a lot. Also, because it does not seem possible to
> re-use the same placeholder multiple times in a statement, if we want to
> expand out the JSON multiple times (e.g. if the JSON is nested and has
> multiple tables in it), we work around that using [sys].[sp_executesql].
> Please see snippet below and a complete script (with create_engine
> redacted) attached.
>
> What I am looking for is some guidance on how to use SQLALchemy to produce
> CTEs that reference the same variable (@blob_o_json) multiple times without
> having to pass in the same value multiple times.
>
> thanks in advance!
> pjjH
>
>
>
> *# **TODO*
> *: formulate this as a SQLAlchemy expression (is there an exec() element?)*
> outer_sql = r'''
> EXEC [sys].[sp_executesql] @statement = :sql,
>
> @params = N'@bind_value_blob_o_json varchar(max)',
> @bind_value_blob_o_json = :json_blob
> '''
>
> *# we prepare the *outer* statement that has a single command in it*
> statement = sql.text(outer_sql)
>
> *# now execute that passing in unicode value of the inner SQL.*
>
> logging.basicConfig(format='%(asctime)s %(message)s')
> logging.getLogger().setLevel(logging.DEBUG)
> nrows = [1000,10000,100000, 200000, 500000]
> for n in nrows:
>
> *# we cons up a list of dicts to represent a single 'inline' table with lots
> of rows*
> logging.debug("passing %d items" % (n))
>
> blob_o_json =json.dumps([dict(rn=x,foo=x*2,bar=x-1,fruit='banana',
> flavor='yummy') for x in
> *range*(0,n)])
>
> *# Note: we named arguments in this next line correspond to the :sql and
> :json_blob placeholders in outer_sql*
>
> result = engine.execute(statement,sql=six.text_type(inner_sql),
> json_blob=blob_o_json)
> rs = result.fetchall()
> *#logging.debug((len(rs), rs[]))*
>
> *# drop down to DBAPI to process each result-set. The application should know
> how many result-sets will be present*
>
> *# I think it is likely that the vast majority of queries will just have a
> single result-set*
>
>
> *# I did not know how to get to anything other than the first result-set ...
> I thought there *
>
> *# was probably some pyodbc magic to make it work with SQLAlchemy and --
> sure enough! -- there*
> *# was a ticket with a workaround:
> https://github.com/sqlalchemy/sqlalchemy/issues/1635#issuecomment-441907416
> <https://github.com/sqlalchemy/sqlalchemy/issues/1635#issuecomment-441907416>*
>
> *#cursor = result.cursor*
> *#rs1 = cursor.fetchall()*
> *#cursor.nextset()*
> *#rs2 = cursor.fetchall()*
>
> *#logging.debug((len(rs1), rs1[-4:], rs2[-4:]))*
> logging.debug("finished %d" % (n))
>
> logging.debug("done")
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/345f67ce-a4b9-4575-87bc-81653868908en%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/345f67ce-a4b9-4575-87bc-81653868908en%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
> *Attachments:*
>
> - sqlalchemy_execute_sql_demo.py
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/7b609b03-e672-4890-8b4c-74d4f584bf80n%40googlegroups.com.
