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*
>
> *#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 sqlalchemy+unsubscr...@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a94c8509-4019-47ac-98b9-893b8ba9974a%40www.fastmail.com.
