I see, thanks again for your reply and clarification, I'm quite new to the environment. It looks like for this kind of usage I should move to pyodbc directly instead of sqlalchemy. If I do so and find anything else, I'll let you know. Fortunately, for now, the code serves its purpose.
On Tuesday, 7 April 2020 16:28:52 UTC-3, Mike Bayer wrote: > > hi there - > > if you are sending that whole string to execute() with multiple statements > embedded, this again has nothing to do with SQLAlchemy and you want to deal > with whatever pyodbc and your underlying ODBC driver are doing with the SQL > commands being passed to them. > > > > On Tue, Apr 7, 2020, at 3:12 PM, Iván Gómez Villafañe wrote: > > PS. errata, change var1/var2 for col1/col2.. > > -- SQL START > > SET ANSI_WARNINGS OFF > > SELECT > id_col, > col1, > col2, > -col1 AS col1_neg, > -col2 AS col2_neg > INTO output_table_temp > FROM input_table; > > UPDATE output_table_temp SET col1 = NULL WHERE col1 <= 0 > UPDATE output_table_temp SET col2 = NULL WHERE col2 <= 0 > UPDATE output_table_temp SET col1_neg = NULL WHERE col1_neg <= 0 > UPDATE output_table_temp SET col2_neg = NULL WHERE col2_neg <= 0 > > DECLARE @avg_col1 AS float > DECLARE @std_col1 AS float > DECLARE @avg_col2 AS float > DECLARE @std_col2 AS float > DECLARE @avg_col1_neg AS float > DECLARE @std_col1_neg AS float > DECLARE @avg_col2_neg AS float > DECLARE @std_col2_neg AS float > > SET @avg_col1 = (SELECT avg(log(col1)) FROM output_table_temp) > SET @std_col1 = (SELECT stdev(log(col1)) FROM output_table_temp) > SET @avg_col2 = (SELECT avg(log(col2)) FROM output_table_temp) > SET @std_col2 = (SELECT stdev(log(col2)) FROM output_table_temp) > SET @avg_col1_neg = (SELECT avg(log(col1_neg)) FROM output_table_temp) > SET @std_col1_neg = (SELECT stdev(log(col1_neg)) FROM output_table_temp) > SET @avg_col2_neg = (SELECT avg(log(col2_neg)) FROM output_table_temp) > SET @std_col2_neg = (SELECT stdev(log(col2_neg)) FROM output_table_temp) > > SELECT > id_col, > (log(col1) - @avg_col1) / @std_col1 AS col1, > (log(col2) - @avg_col2) / @std_col2 AS col2, > (log(col1_neg) - @avg_col1_neg) / @std_col1_neg AS col1_neg, > (log(col2_neg) - @avg_col2_neg) / @std_col2_neg AS col2_neg > INTO output_table > FROM output_table_temp; > > DROP TABLE output_table_temp; > > > On Tuesday, 7 April 2020 16:09:10 UTC-3, Iván Gómez Villafañe wrote: > > Hi Mike, thanks for your reply. > > Regarding context, here's a sample sql with 2 cols: > > -- SQL START > > SET ANSI_WARNINGS OFF > > SELECT > id_col, > col1, > col2, > -var1 AS var1_neg, > -var2 AS var2_neg > INTO output_table_temp > FROM input_table; > > UPDATE output_table_temp SET col1 = NULL WHERE col1 <= 0 > UPDATE output_table_temp SET col2 = NULL WHERE col2 <= 0 > UPDATE output_table_temp SET var1_neg = NULL WHERE var1_neg <= 0 > UPDATE output_table_temp SET var2_neg = NULL WHERE var2_neg <= 0 > > DECLARE @avg_col1 AS float > DECLARE @std_col1 AS float > DECLARE @avg_col2 AS float > DECLARE @std_col2 AS float > DECLARE @avg_var1_neg AS float > DECLARE @std_var1_neg AS float > DECLARE @avg_var2_neg AS float > DECLARE @std_var2_neg AS float > > SET @avg_col1 = (SELECT avg(log(col1)) FROM output_table_temp) > SET @std_col1 = (SELECT stdev(log(col1)) FROM output_table_temp) > SET @avg_col2 = (SELECT avg(log(col2)) FROM output_table_temp) > SET @std_col2 = (SELECT stdev(log(col2)) FROM output_table_temp) > SET @avg_var1_neg = (SELECT avg(log(var1_neg)) FROM output_table_temp) > SET @std_var1_neg = (SELECT stdev(log(var1_neg)) FROM output_table_temp) > SET @avg_var2_neg = (SELECT avg(log(var2_neg)) FROM output_table_temp) > SET @std_var2_neg = (SELECT stdev(log(var2_neg)) FROM output_table_temp) > > SELECT > id_col, > (log(col1) - @avg_col1) / @std_col1 AS col1, > (log(col2) - @avg_col2) / @std_col2 AS col2, > (log(var1_neg) - @avg_var1_neg) / @std_var1_neg AS var1_neg, > (log(var2_neg) - @avg_var2_neg) / @std_var2_neg AS var2_neg > INTO output_table > FROM output_table_temp; > > DROP TABLE output_table_temp; > > Without SET ANSI_WARNINGS OFF, the final output_table is not created and > output_table_temp is not dropped. So the warnings probably correspond to the > previous SET batch. Keep in mind, 2 columns is fine, the problem arises when > executing around 20 columns. > > > On Tuesday, 7 April 2020 15:49:47 UTC-3, Mike Bayer wrote: > > this warning is not related to SQLAlchemy it has to do with the structure > of SQL you are passing to connection.execute(). some background I found > via googling here: > > > https://stackoverflow.com/questions/11384292/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation-in-aqua > > Assuming you are passing string SQL to SQLAlchemy's conn.execute(), > nothing special happens in SQLAlchemy and I would advise experimenting with > the pyodbc cursor directly in order to figure out why some parts of your > statements seem to be "ignored" - it's not clear what is telling you "the > last part of the SQL is never actually executed" since there's not really > any example or context provided here. > > > > On Tue, Apr 7, 2020, at 2:25 PM, Iván Gómez Villafañe wrote: > > I have my own tran() function, as the doc suggests: > > engine = sqlalchemy.create_engine( > 'mssql+pyodbc://@SHINKEN/supervielle?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Server' > , fast_executemany = True) > def tran(sql,engine): > with engine.begin() as conn: > conn.execute(sql) > > I execute with it custom sql strings, such as one obtained from another > custom complex sql-string generation function which obtains a new table > with log + z-transform of columns that have many 0's, therefore there's a > lot of NULL values going around. In the sql, 0's get turned into null so > that I can log, and when I then set variables and new columns as log(), > each column returns the warning: > > Warning: Null value is eliminated by an aggregate or other SET operation. > > When running the function on 4 columns, everything is fine. When running > it on 20, tran() executes as if it were fine, *but the last part of the > sql is never actually executed* and I receive no warning/error whatsoever > on the Python console. Please note this DOES run on MS SQL SMS, I tried > pasting the sql and executing there, and I do receive all the warnings, but > it completes fine. It appears that when running with sqlalchemy, after a > certain amount of warnings the execution is dropped. > > Anyway, I solved the issue by setting the following at the start of the > sql: > > SET ANSI_WARNINGS OFF > > Nevertheless, I suspect there's something not quite working as it should. > Maybe I'm doing something wrong, or maybe sqlalchemy could be updated to > deal with this kind of scenario, or maybe there's a setting I should look > into in MS SQL 2017. > > Any insights? Thanks. > > > -- > 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 sqlal...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/c80049c9-698d-4976-849a-abc422d15bf0%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c80049c9-698d-4976-849a-abc422d15bf0%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 sqlal...@googlegroups.com <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e3b64fd3-7855-4eb6-90f0-c58844a558a7%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/e3b64fd3-7855-4eb6-90f0-c58844a558a7%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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4ee9ba54-b941-441e-8cad-966b0427be1a%40googlegroups.com.