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 <javascript:>.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/7cd88b87-6f1c-4e67-b0ba-a2d4319945b9%40googlegroups.com.
