Python Sqlite3: INSERT INTO table VALUE(字典放在这里)

Python Sqlite3: INSERT INTO table VALUE(dictionary goes here)

提问by Crispy

I would like to use a dictionary to insert values into a table, how would I do this?

我想使用字典将值插入表中，我该怎么做？

import sqlite3

db = sqlite3.connect('local.db')
cur = db.cursor()

cur.execute('DROP TABLE IF EXISTS Media')

cur.execute('''CREATE TABLE IF NOT EXISTS Media(
                id INTEGER PRIMARY KEY, title TEXT, 
                type TEXT,  genre TEXT,
                onchapter INTEGER,  chapters INTEGER,
                status TEXT
                )''')


values = {'title':'Hyman', 'type':None, 'genre':'Action', 'onchapter':None,'chapters':6,'status':'Ongoing'}

#What would I Replace x with to allow a 
#dictionary to connect to the values? 
cur.execute('INSERT INTO Media VALUES (NULL, x)'), values)
cur.execute('SELECT * FROM Media')

meida = cur.fetchone()

print meida

采纳答案by abarnert

If you're trying to use a dictto specify both the column names and the values, you can't do that, at least not directly.

如果您尝试使用 adict来指定列名和值，则不能这样做，至少不能直接这样做。

That's really inherent in SQL. If you don't specify the list of column names, you have to specify them in CREATE TABLEorder—which you can't do with a dict, because a dicthas no order. If you really wanted to, of course, you could use a collections.OrderedDict, make sure it's in the right order, and then just pass values.values(). But at that point, why not just have a list(or tuple) in the first place? If you're absolutely sure you've got all the values, in the right order, and you want to refer to them by order rather than by name, what you have is a list, not a dict.

这确实是 SQL 固有的。如果您不指定列名列表，则必须按CREATE TABLE顺序指定它们——这不能用 a 来做dict，因为 adict没有顺序。当然，如果你真的想要，你可以使用 a collections.OrderedDict，确保它的顺序正确，然后传递values.values()。但在这一点上，为什么不首先有一个list（或tuple）？如果您绝对确定您已按正确顺序获得所有值，并且您想按顺序而不是按名称引用它们，那么您拥有的是 a list，而不是 a dict。

And there's no way to bind column names (or table names, etc.) in SQL, just values.

并且没有办法在 SQL 中绑定列名（或表名等），只能绑定值。

You can, of course, generate the SQL statement dynamically. For example:

当然，您可以动态生成 SQL 语句。例如：

columns = ', '.join(values.keys())
placeholders = ', '.join('?' * len(values))
sql = 'INSERT INTO Media ({}) VALUES ({})'.format(columns, placeholders)
cur.execute(sql, values.values())

However, this is almost always a bad idea. This really isn't much better than generating and execing dynamic Python code. And you've just lost all of the benefits of using placeholders in the first place—primarily protection from SQL injection attacks, but also less important things like faster compilation, better caching, etc. within the DB engine.

然而，这几乎总是一个坏主意。这确实并不比生成和exec编写动态 Python 代码好多少。而且您刚刚失去了首先使用占位符的所有好处——主要是防止 SQL 注入攻击，但也失去了一些不太重要的东西，比如在数据库引擎中更快的编译、更好的缓存等。

It's probably better to step back and look at this problem from a higher level. For example, maybe you didn't really want a static list of properties, but rather a name-value MediaPropertiestable? Or, alternatively, maybe you want some kind of document-based storage (whether that's a high-powered nosql system, or just a bunch of JSON or YAML objects stored in a shelve)?

最好退后一步，从更高的层次来看这个问题。例如，也许您并不真正想要一个静态的属性列表，而是一个名称-值MediaProperties表？或者，也许您想要某种基于文档的存储（无论是高性能的 nosql 系统，还是只是存储在 a 中的一堆 JSON 或 YAML 对象shelve）？

---

---

An alternative using named placeholders:

使用命名占位符的替代方法：

columns = ', '.join(my_dict.keys())
placeholders = ':'+', :'.join(my_dict.keys())
query = 'INSERT INTO my_table (%s) VALUES (%s)' % (columns, placeholders)
print query
cur.execute(query, my_dict)
con.commit()

回答by tacaswell

key_lst = ('status', 'title', 'chapters', 'onchapter', 'genre', 'type')
cur.execute('INSERT INTO Media (status,title,chapters,onchapter,genre,type) VALUES ' + 
            '(?,?,?,?,?,?);)',tuple(values[k] for k in key_lst))

Do your escaping right.

做你的逃避权利。

You probably also need a commitcall in there someplace.

您可能还需要commit在某个地方打电话。

回答by MrGumble

There is a solution for using dictionaries. First, the sql-statement

有一个使用字典的解决方案。一、sql-语句

INSERT INTO Media VALUES (NULL, 'x');

would not work, as it assumes you are referring to all columns, in the order they are defined in the CREATE TABLEstatement, as abarnert stated. (See SQLite INSERT.)

将不起作用，因为它假定您指的是所有列，按照它们在CREATE TABLE语句中定义的顺序，如 abarnert 所述。（请参阅SQLite 插入。）

Once you have fixed it by specifying the columns, you can use named placeholders to insert data. The advantage of this is that is safely escapes key-characters, so you do not have to worry. From the Python sqlite-documentation:

通过指定列修复它后，您可以使用命名占位符插入数据。这样做的好处是可以安全地转义关键字符，因此您不必担心。从Python sqlite-documentation：

values = {'title':'Hyman', 'type':None, 'genre':'Action', 'onchapter':None,'chapters':6,'status':'Ongoing'}
cur.execute('INSERT INTO Media (id, title, type, onchapter, chapters, status) VALUES (:id, :title, :type, :onchapter, :chapters, :status);'), values)

回答by eichin

You could use named parameters:

您可以使用命名参数：

cur.execute('INSERT INTO Media VALUES (NULL, :title, :type, :genre, :onchapter, :chapters, :status)', values)

This still depends on the column order in the INSERTstatement (those :are only used as keys in the valuesdict) but it at least gets away from having to order the values on the python side, plus you can have other things in valuesthat are ignored here; if you're pulling what's in the dict apart to store it in multiple tables, that can be useful.

这仍然取决于INSERT语句中的列顺序（那些:仅用作valuesdict 中的键），但它至少不必在 python 端对值进行排序，此外，您可以在values此处忽略其他内容；如果您将 dict 中的内容分开以将其存储在多个表中，这可能很有用。

If you still want to avoid duplicating the names, you could extract them from an sqlite3.Rowresult object, or from cur.description, after doing a dummy query; it may be saner to keep them around in python form near wherever you do your CREATE TABLE.

如果您仍想避免重复名称，则可以在执行虚拟查询后从sqlite3.Row结果对象或从 中提取它们cur.description；将它们以 python 形式保存在您执行CREATE TABLE.

回答by yash jain

I was having the similar problem so I created a string first and then passed that string to execute command. It does take longer time to execute but mapping was perfect for me. Just a work around:

我遇到了类似的问题，所以我先创建了一个字符串，然后将该字符串传递给执行命令。执行确实需要更长的时间，但映射对我来说是完美的。只是一个解决方法：

create_string = "INSERT INTO datapath_rtg( Sr_no"
for key in record_tab:
    create_string = create_string+ " ," + str(key)
create_string = create_string+ ") VALUES("+ str(Sr_no) 
for key in record_tab:
    create_string = create_string+ " ," + str(record_tab[key])
create_string = create_string + ")"
cursor.execute(create_string)

By doing above thing I ensured that if my dict (record_tab) doesn't contain a particular field then the script wont throw out error and proper mapping can be done which is why I used dictionary at the first place.

通过做上述事情，我确保如果我的 dict (record_tab) 不包含特定字段，那么脚本不会抛出错误并且可以完成正确的映射，这就是我首先使用字典的原因。

回答by Subgenius

I was having a similar problem and ended up with something not entirely unlike the following (Note - this is the OP's code with bits changed so that it works in the way they requested)-

我遇到了类似的问题，最终得到的结果与以下内容并不完全不同（注意 - 这是 OP 的代码，更改了位，以便按他们要求的方式工作）-

import sqlite3
db = sqlite3.connect('local.db')
cur = db.cursor()

cur.execute('DROP TABLE IF EXISTS Media')

cur.execute('''CREATE TABLE IF NOT EXISTS Media(
                id INTEGER PRIMARY KEY, title TEXT, 
                type TEXT,  genre TEXT,
                onchapter INTEGER,  chapters INTEGER,
                status TEXT
                )''')


values = {'title':'Hyman', 'type':None, 'genre':'Action',     'onchapter':None,'chapters':6,'status':'Ongoing'}

#What would I Replace x with to allow a 
#dictionary to connect to the values? 
#cur.execute('INSERT INTO Media VALUES (NULL, x)'), values)
# Added code.
cur.execute('SELECT * FROM Media')
colnames = cur.description
list = [row[0] for row in cur.description]
new_list = [values[i] for i in list if i in values.keys()]
sql = "INSERT INTO Media VALUES ( NULL, "
qmarks = ', '.join('?' * len(values))
sql += qmarks + ")"
cur.execute(sql, new_list)
#db.commit() #<-Might be important.
cur.execute('SELECT * FROM Media')
media = cur.fetchone()
print (media)

回答by keithpjolley

Here's a more generic way with the benefit of escaping:

这是一种更通用的方法，具有转义的好处：

# One way. If keys can be corrupted don't use.
sql = 'INSERT INTO demo ({}) VALUES ({})'.format(
            ','.join(my_dict.keys()),
            ','.join(['?']*len(my_dict)))

# Another, better way. Hardcoded w/ your keys.
sql = 'INSERT INTO demo ({}) VALUES ({})'.format(
            ','.join(my_keys),
            ','.join(['?']*len(my_dict)))

cur.execute(sql, tuple(my_dict.values()))

回答by a7xcarter

Super late to this, but figured I would add my own answer. Not an expert, but something I found that works.

太晚了，但我想我会添加我自己的答案。不是专家，但我发现有用的东西。

There are issues with preserving order when using a dictionary, which other users have stated, but you could do the following:

使用字典时保留顺序存在问题，其他用户已经说过，但您可以执行以下操作：

# We're going to use a list of dictionaries, since that's what I'm having to use in my problem
input_list = [{'a' : 1 , 'b' : 2 , 'c' : 3} , {'a' : 14 , 'b' : '' , 'c' : 43}]
for i in input_list:
    # I recommend putting this inside a function, this way if this 
    # Evaluates to None at the end of the loop, you can exit without doing an insert
    if i :
        input_dict = i 
    else:
        input_dict = None
        continue
# I am noting here that in my case, I know all columns will exist.
# If you're not sure, you'll have to get all possible columns first.

keylist = list(input_dict.keys())
vallist = list(input_dict.values())

query = 'INSERT INTO example (' +','.join( ['[' + i + ']' for i in keylist]) + ') VALUES (' + ','.join(['?' for i in vallist]) + ')'

items_to_insert = list(tuple(x.get(i , '') for i in keylist) for x in input_list)
# Making sure to preserve insert order. 

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.executemany(query , items_to_insert)
conn.commit()

回答by Nik

dictionary = {'id':123, 'name': 'Abc', 'address':'xyz'}    
query = "insert into table_name " + str(tuple(dictionary.keys())) + " values" + str(tuple(dictionary.values())) + ";"
cursor.execute(query)

query becomes

查询变成

insert into table_name ('id', 'name', 'address') values(123, 'Abc', 'xyz');