python sqlite插入命名参数或null [英] python sqlite insert named parameters or null

问题描述

我正在尝试使用命名参数将字典中的数据插入到数据库中.我有一个简单的 SQL 语句，例如

I'm trying to insert data from a dictionary into a database using named parameters. I have this working with a simple SQL statement e.g.

SQL = "INSERT INTO status (location, arrival, departure) VALUES (:location, :arrival,:departure)"
dict = {'location': 'somewhere', 'arrival': '1000', 'departure': '1001'}
c.execute(SQL,dict)

将某处插入位置，1000 插入到达列，1001 插入离开列.

Inserts somewhere into location, 1000 into the arrival column, and 1001 into departure column.

我实际拥有的数据将包含位置，但可能包含到达或离开，但可能不会同时包含两者(在这种情况下，表中可能没有任何内容或 NULL).在这种情况下，我得到 sqlite3.ProgrammingError: You did not provide a value for binding 2.

The data that I will actually have will contain location but may contain either arrival, or departure but might not have both (in which case either nothing or NULL can go into the table). In this case, I get sqlite3.ProgrammingError: You did not supply a value for binding 2.

我可以使用 defaultdict 解决这个问题:

I can fix this by using defaultdict:

c.execute(SQL,defaultdict(str,dict))

为了让事情稍微复杂一点，我实际上会有一个字典列表，其中包含多个到达或离开的位置.

To make things slightly more complicated, I will actually have a list of dictionaries containing multiple locations with either an arrival or departure.

    ({'location': 'place1', 'departure': '1000'},
    {'location': 'palce2', 'arrival': '1010'},
    {'location': 'place2', 'departure': '1001'})

并且我希望能够使用 c.executemany 运行它，但是我现在不能使用 defaultdict.

and I want to be able to run this with c.executemany however I now can't use defaultdict.

我可以遍历列表中的每个字典并运行许多 c.execute 语句，但 executemany 似乎是一种更整洁的方法.

I could loop through each dictionary in the list and run many c.execute statements, but executemany seems a tidier way to do it.

为了方便起见，我简化了这个例子，实际数据在字典中有更多的条目，我从一个 JSON 文本文件构建它.

I've simplified this example for convenience, the actual data has many more entries in the dictionary, and I build it from a JSON text file.

有人对我如何做到这一点有任何建议吗?

Anyone have any suggestions for how I could do this?

推荐答案

使用None插入一个NULL:

dict = {'location': 'somewhere', 'arrival': '1000', 'departure': None}

您可以使用默认字典和生成器将其与 executemany() 一起使用:

You can use a default dictionary and a generator to use this with executemany():

defaults = {'location': '', 'arrival': None, 'departure': None}

c.executemany(SQL, ({k: d.get(k, defaults[k]) for k in defaults} for d in your_list_of_dictionaries)

这篇关于python sqlite插入命名参数或null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！