Build update statement in python using results from previous query

Question

I have a task in which I must update a database on another server. As my options are limited I'm using python to do the update.

However I have this error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW)")

My code is this:

first I create a select and then use it in the update

> query_dwt = "SELECT     [cdcliente]\
>                                 ,[nmcontato]\
>                                 ,[cddepartamento]\
>                                 ,[nmcargo] \
>                                 ,[dsemail]\
>                                 ,[cdlingua]\
>                                 ,[nrcpfcnpj]\
>                                 ,[cdcargo]\
>                                 ,[cdcontatosuperior]\
>                                 ,[idativo]\
>                                 ,[cdcidade]\
>                                 ,[dsendereco]\
>                                 ,[dscomplemento]\
>                                 ,[nmbairro]\
>                                 ,[nrcep]\
>                                 ,[nrcelular]\
>                                 ,[dtnascimento]\
>                                 ,[idbloqueado]\
>                                 ,[cdlocalidade]\
>                                 ,[nrmatricula]\
>                                 ,[nmskin]\
> FROM [dw].[d_Qualitor_ad_contato_RH] WITH (NOLOCK)\
> WHERE   cdcliente = 9402\
> AND (cdcontato = 38584 OR cdcontato = 22320 OR cdcontato = 37284);"

Second I use the select created to bring the information from the table to update the desired table

> query_qltr = """UPDATE ad\
> SET\
> ad.nmcontato = PR.nmcontato\
>                         ,ad.cddepartamento = PR.cddepartamento\
>                         ,ad.nmcargo = PR.nmcargo\
>                         ,ad.dsemail = PR.dsemail\
>                         ,ad.cdlingua = PR.cdlingua\
>                         ,ad.nrcpfcnpj = PR.nrcpfcnpj\
>                         ,ad.cdcargo = PR.cdcargo\
>                         ,ad.cdcontatosuperior = PR.cdcontatosuperior\
>                         ,ad.idativo = PR.idativo\
>                         ,ad.cdcidade = PR.cdcidade\
>                         ,ad.dsendereco = PR.dsendereco\
>                         ,ad.dscomplemento = PR.dscomplemento\
>                         ,ad.nmbairro = PR.nmbairro\
>                         ,ad.nrcep = PR.nrcep\
>                         ,ad.nrcelular = PR.nrcelular\
>                         ,ad.dtnascimento = PR.dtnascimento\
>                         ,ad.idbloqueado = PR.idbloqueado\
>                         ,ad.cdlocalidade = PR.cdlocalidade\
>                         ,ad.nrmatricula = PR.nrmatricula\
>                         ,ad.nmskin = PR.nmskin\
> FROM dbo.ad_contato ad\
> INNER JOIN ({}) PR\
> ON ad.cdcontato = PR.cdcontato\
> AND ad.cdcliente LIKE '9402';""".format(OpenSqlDatabaseConnection.execute_query(query_dwt,'target-db-conn-str'))
> 
> OpenSqlDatabaseConnection.execute_query(query_qltr,'rdn-db-clt-sql-06a-inssql01-qualitor-prd-jdbc-conn-string-01')

I'm sure it's something simple but I can't figure it out.

INNER JOIN is to bring the table that comes from the first step, in the select — Matheus Sauthier
– Matheus Sauthier, Commented Dec 29, 2022 at 19:26
I'm not familiar with that syntax, how sure are you its working? Because thats where the bracket is which its complaining about. And does it replace the {} with the actual select query? Or with the results of the select query? — Dale K
– Dale K, Commented Dec 29, 2022 at 19:28
Please add the full solution as an answer - it might help someone else. — Dale K
– Dale K, Commented Jan 3, 2023 at 20:41

Matheus Sauthier · Accepted Answer · 2023-01-04 18:43:40Z

Solution:

1-Extract the data in the first database and insert it into a dataframe.

def select_dw_qualitor_ad_contato():
    query_dwt = "SELECT     [cdcliente]\
                            ,[cdcontato]\
                            ,[nmcontato]\
                            ,[cddepartamento]\
                            ,[nmcargo] \
                            ,[dsemail]\
                            ,[cdlingua]\
                            ,[nrcpfcnpj]\
                            ,[cdcargo]\
                            ,[cdcontatosuperior]\
                            ,[idativo]\
                            ,[cdcidade]\
                            ,[dsendereco]\
                            ,[dscomplemento]\
                            ,[nmbairro]\
                            ,[nrcep]\
                            ,[nrcelular]\
                            ,[dtnascimento]\
                            ,[idbloqueado]\
                            ,[cdlocalidade]\
                            ,[nrmatricula]\
                            ,[nmskin]\
            FROM [dw].[d_Qualitor_ad_contato_RH] WITH (NOLOCK)\
                            WHERE   cdcliente = 9402\
                                    AND (cdcontato = 38584\
                                        OR cdcontato = 22320\
                                        OR cdcontato = 37284\
                                        OR cdcontato = 36139\
                                        OR cdcontato = 41035\
                                        OR cdcontato = 38819);"

    return pd.read_sql(query_dwt,OpenSqlDatabaseConnection.connection('target-db-conn-str'),
                        parse_dates={"date_column": {"errors": "ignore"}})

2- Update second query row by row with dataframe

    def update_qualitor_table():

    dfdw = QueriesLists.select_dw_qualitor_ad_contato()

    end = len(dfdw)
    for i,line in enumerate(dfdw):
        if i < end:
            df = dfdw.iloc[i]
            QueriesLists.update_database(df)
        else:
            break

3- Query SQL using update command using dataframe

def update_database(df):

    query_qltr = "UPDATE [dbo].[ad_contato]\
                    SET [nmcontato] = CAST('{0}' AS VARCHAR(200))\
                        ,[cddepartamento] = CAST('{1}' AS INT)\
                        ,[nmcargo] = CAST('{2}' AS VARCHAR (50))\
                        ,[dsemail] = CAST('{3}' AS VARCHAR(200))\
                        ,[cdlingua] = CAST('{4}' AS INT)\
                        ,[nrcpfcnpj] = CAST('{5}' AS VARCHAR(20))\
                        ,[cdcargo] = CAST('{6}' AS INT)\
                        ,[cdcontatosuperior] = CAST('{7}' AS INT)\
                        ,[idativo] = CAST('{8}' AS VARCHAR(1))\
                        ,[dsendereco] = CAST('{9}' AS VARCHAR(200))\
                        ,[dscomplemento] = CAST('{10}' AS VARCHAR(200))\
                        ,[nmbairro] = CAST('{11}' AS VARCHAR(40))\
                        ,[nrcep] = CAST('{12}' AS VARCHAR(9))\
                        ,[dtnascimento] = CAST('{13}' AS DATETIME) \
                        ,[idbloqueado] = CAST('{14}' AS VARCHAR(1))\
                        ,[cdlocalidade] = CAST('{15}' AS INT)\
                        ,[nrmatricula] = CAST('{16}' AS VARCHAR(20))\
                WHERE   [cdcontato] = CAST('{17}' AS INT)\
                        AND [cdcliente] = 9402;\
                ".format(str(df[2])                 #nmcontato
                        ,int(df[3])                 #cddepartamento
                        ,str(df[4])                 #nmcargo
                        ,str(df[5])                 #dsemai
                        ,int(df[6])                 #cdlingua
                        ,str(df[7])                 #nrcpfcnpj
                        ,int(df[8])                 #cdcargo
                        ,int(df[9])                 #cdcontasuperior
                        ,str(df[10])                #idativo
                        ,str(df[12])                #dsendereco
                        ,str(df[13])                #dscomplemento
                        ,str(df[14])                #nmbairro
                        ,str(df[15])                #nrcep
                        ,pd.to_datetime(df[17])     #datetime
                        ,str(df[18])                #idbloqueado
                        ,int(df[19])                #cdlocalidade
                        ,str(df[20])                #nrmatricula
                        ,int(df[1])                            
                )
    OpenSqlDatabaseConnection.execute_query(query_qltr,'rdn-db-clt-sql-06a-inssql01-qualitor-prd-jdbc-conn-string-01')

Collectives™ on Stack Overflow

Build update statement in python using results from previous query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related