使用Pyodbc访问数据库UPSERT [英] Access database UPSERT with pyodbc

问题描述

我正在使用pyodbc更新Access数据库.

I am using pyodbc to update an Access database.

我需要UPSERT的功能.

I need the functionality of an UPSERT.

ON DUPLICATE KEY UPDATE在Access SQL中不存在，并且REPLACE不是一个选项，因为我想保留其他字段.

ON DUPLICATE KEY UPDATE doesn't exist in Access SQL, and REPLACE is not an option since I want to keep other fields.

有很多建议可以解决这个问题，所以这是 我提出的解决方案:

There are a lot of suggestions out there how to solve that, so this is the solution which I put together:

for table_name in data_source:
    table = data_source[table_name]

    for y in table:
        if table_name == "whatever":
            SQL_UPDATE = "UPDATE {} set [Part Name] = '{}', [value] = {}, [code] = {}, [tolerance] = {} WHERE [Unique Part Number]='{}'".\
                format(table_name,y['PartName'],y['Value'],y['keycode'],y['Tolerance'], y['PartNum'])
            SQL_INSERT = "INSERT INTO {} ([Part Name],[Unique Part Number], [value], [code], [tolerance]) VALUES ('{}','{}','{}',{},{},{});".\
                format(table_name,y['PartName'],y['PartNum'],y['Value'],y['keycode'],y['Tolerance'])
        elsif ....
                9 more tables....

       res = cursor.execute(SQL_UPDATE)
       if res.rowcount == 0:
         cursor.execute(SQL_INSERT)

我不得不说，我不是Python专家，而且我也没有理解基本概念或SQL的魔力， 所以我可以在这里把Google的东西放在一起.

Well I have to say, I am not a Python expert, and I didn't manage to understand the fundamental concept nor the Magic of SQL, so I can just Google things together here.

我不喜欢上面的解决方案，因为它很难阅读且难以维护(我不得不为此〜10个不同的表).另一点是，我必须使用2个查询，因为我无法理解和运行我发现的任何其他UPSERT方法.

I don't like my above solution because it is very hard to read and difficult to maintain (I have to to this for ~10 different tables). The other point is that I have to use 2 queries because I didn't manage to understand and run any other UPSERT approach I found.

有人建议我如何以更聪明，更易维护的方式进行操作吗?

Does anyone have a recommendation for me how to do this in a smarter, better maintainable way?

推荐答案

考虑使用来自使用?占位符的预准备语句的参数化查询.对于表和字段名称之类的标识符，仍需要str.format.然后使用zip(*dict.items())解压缩字典项，以将其作为参数传递给光标的execute调用:cursor.execute(query, params).

Consider using parameterized queries from prepared statements that uses ? placeholders. The str.format is still needed for identifiers such as table and field names. Then unpack dictionary items with zip(*dict.items()) to pass as parameters in the cursor's execute call: cursor.execute(query, params).

for table_name in data_source:
    table = data_source[table_name]

    for y in table:
        keys, values = zip(*y.items())    # UNPACK DICTIONARY INTO TWO TUPLES

        if table_name == "whatever":
            SQL_UPDATE = "UPDATE {} set [Part Name] = ?, [value] = ?, [code] = ?," + \
                       " [tolerance] = ? WHERE [Unique Part Number]= ?".format(table_name)

            SQL_INSERT = "INSERT INTO {} ([Part Name], [Unique Part Number], [value]," + \
                     " [code], [tolerance]) VALUES (?, ?, ?, ?, ?);".format(table_name)

            res = cursor.execute(SQL_UPDATE, values)
            if res.rowcount == 0:
                cursor.execute(SQL_INSERT, values)
       ...

这篇关于使用Pyodbc访问数据库UPSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！