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 sqlalchemy+unsubscr...@googlegroups.com.
> 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/b7fd25b4-8267-4216-a6ec-fdcfcb2ad8a3%40www.fastmail.com.
