How to add custom column name in sqlalchemy update query?

[Mirek Morek]

Hello everyone,

I have a following question:

I use sqlalchemy within my python method as follows:

I want to be able to replace 'custom_column' with parameter passed in the method. The same way as it works fine for airport_sys_id, project_sys_id and value.

For example I want to pass in my method (1, 2, name_column, cat) and receive as follows:

query = AIRPORT.update(). \
            where(AIRPORT.c.airport_sys_id == 1). \
            where(AIRPORT.c.project_sys_id == 2). \
            values(name_column=cat)

I searched all I could but with no success. I would be extremely thankful for your support. Regards.

[Simon King]

Here are the docs for the Update.values() method:

https://docs.sqlalchemy.org/en/13/core/dml.html#sqlalchemy.sql.expression.Update.values

Note that it supports two main calling styles, either keyword arguments:

.values(name_column='cat')

...or passing a dictionary:

.values({'name_column': 'cat'})

Since you want to choose the column dynamically, you can use the
dictionary style:

updatevalues = {custom_column: value}
query = AIRPORT.update().values(updatevalues)

Hope that helps,

Simon

On Thu, Jul 11, 2019 at 3:00 PM Mirek Morek <teste...@gmail.com> wrote:
>
> Hello everyone,
> I have a following question:
>
> I use sqlalchemy within my python method as follows:
>
>

> I want to be able to replace 'custom_column' with parameter passed in the method. The same way as it works fine for airport_sys_id, project_sys_id and value.
>
> For example I want to pass in my method (1, 2, name_column, cat) and receive as follows:
>
> query = AIRPORT.update(). \
> where(AIRPORT.c.airport_sys_id == 1). \
> where(AIRPORT.c.project_sys_id == 2). \
> values(name_column=cat)
>
> I searched all I could but with no success. I would be extremely thankful for your support. Regards.
>
>

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/67448673-060d-449c-9dd9-19d5049c0c72%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Mirek Morek]

It works!

Thank you very much Simon, I really appreciate that. You saved me a lot of nerves. I would buy you a beer if I could.

So, in general we can say that only second calling style works in my example.

Regards.

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Simon King]

If SQLAlchemy didn't support the second calling style, you would still
be able to make this work using the Python "**" operator:

updatevalues = {custom_column: value}
query = AIRPORT.update().values(**updatevalues)

When calling a function, passing "**somedictionary" is equivalent to
passing each of the dictionary elements as keyword arguments to the
function.

>>> def f(a, b, c):
... print("a =", a)
... print("b =", b)
... print("c =", c)
...
>>> f(a=1, b=2, c=3)
a = 1
b = 2
c = 3
>>> args = {'a': 1, 'b': 2, 'c': 3}
>>> f(**args)
a = 1
b = 2
c = 3

Simon

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/11786218-4161-4d41-b4e1-6faa7a31611b%40googlegroups.com.

[Mirek Morek]

Hello Simon,

One extra question:

Do you know maybe how to assign NULL field value to numeric type field using sqlalchemy? I change field to some numeric value and then I want to clear this field.

I tried assign None as default for value parameter:

But I receive:

"Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed.  Use the CONVERT function to run this query."

[Simon King]

Passing None is the right way to set a column to NULL, so it sounds like you are doing something else wrong. How are you calling your update_airport_reference_temperature function?

Also, enabling debug level logging (by passing echo='debug' when you create your SQLAlchemy engine) will show exactly what values you are passing to the database.

Simon

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c35f40ae-17f6-4fb8-884f-32dd3157eae9%40googlegroups.com.

[Mirek Morek]

So far I tried to call my function by passing airport_sys_id, project_sys_id and column name, leaving value parameter empty as it is assigned to None by default.

I see in my logs that it tries to pass in sql query value=None, but it doesn't work for numeric type field. It works fine for string type, but for numeric one I receive as above: "Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed.  Use the CONVERT function to run this query.", so it seems that it treats passed None as string value.

[Simon King]

I don't have any other ideas, I'm afraid. Can you post the relevant section from the logs?

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/19fe84c0-1104-4298-922f-4e6b8fb8880e%40googlegroups.com.

[Mirek Morek]

Sure

[Simon King]

Hmm, the None looks fine, but now I'm suspicious of those airport_sys_id and project_sys_id values. Why are they being passed as floating point values, rather than integers?

Does the same query work if the reference_temperature is not None?

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7dfe5185-7b14-4fc9-85a9-35cda6556e10%40googlegroups.com.

[Mirek Morek]

Yes, when passing integer value as value everything is fine, so only value parameter is a concern here. This is Sybase database so maybe that is a case.

[Mirek Morek]

Ok, below there is a working solution. I had to import null from sqlachemy.sql.expression and then:

[Simon King]

I'm glad you found a way to make it work. I'd forgotten about sqlalchemy.null().

Simon

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1553e076-f7f4-409c-9b0b-3e085414af5f%40googlegroups.com.