On Mon, Nov 30, 2020, at 1:39 PM, Paul Harrington wrote: > 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?
I don't know anything about OPENJSON so I don't have any insight on how that might work. > 1. What is the best way to emulate multi-statement batches? it depends on what you are using them for. in your case it looks like you need them to support SQL-side variables which we have no support for, this is imperative programming and SQLAlchemy's expression langauge draws the line at where declarative becomes imperative. > 1. 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. yeah I saw that you were doing that, more robustly you'd use a @compiles recipe, see <https://docs.sqlalchemy.org/en/13/core/compiler.html>https://docs.sqlalchemy.org/en/13/core/compiler.html . > 1. 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? the Python DBAPI only guarantees that EXEC is available via the callproc() interface: <https://www.python.org/dev/peps/pep-0249/#callproc>https://www.python.org/dev/peps/pep-0249/#callproc which SQLAlchemy does not include in its facade, as there's not much point. Our guidelines for using callproc are at <https://docs.sqlalchemy.org/en/13/core/connections.html#calling-stored-procedures> <https://docs.sqlalchemy.org/en/13/core/connections.html#calling-stored-procedures>https://docs.sqlalchemy.org/en/13/core/connections.html#calling-stored-procedures . to the degree that your Python driver (pyodbc?) supports EXEC passed into cursor.execute(), you could send it to regular connection.execute() as well. > 1. is dropping down to dbapi/pyodbc still the best way to handle multiple > result-sets? yes, there was a proposal for this long ago but multple result sets are really not a thing that are commonly used outside of SQL server for very special cases. it's not worth the extra complexity and luckily I was able to not have to deal with it when I just did the 1.4 refactoring of the result object. > 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! oh quite a lot! just none of what you're working on above :( > > 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* >>> >>> *#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 > > <https://groups.google.com/d/msgid/sqlalchemy/7b609b03-e672-4890-8b4c-74d4f584bf80n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/c4c27b0d-006a-449b-a55c-9b939fe045da%40www.fastmail.com.
