Python Sqlite3:INSERT INTO table VALUE(字典在这里) [英] Python Sqlite3: INSERT INTO table VALUE(dictionary goes here)

问题描述

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

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':'jack', '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

推荐答案

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

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

这确实是 SQL 所固有的.如果您不指定列名列表，则必须以 CREATE TABLE 顺序指定它们 - 您不能使用 dict 执行此操作，因为 dict 没有顺序.如果你真的想要，当然，你可以使用 collections.OrderedDict，确保它的顺序正确，然后传递 values.values().但是到那时，为什么不首先有一个list(或tuple)呢?如果您绝对确定您已经以正确的顺序获取了所有值，并且您想按顺序而不是按名称引用它们，那么您拥有的是 list，而不是 <代码>字典.

That's really inherent in SQL. If you don't specify the list of column names, you have to specify them in CREATE TABLE order—which you can't do with a dict, because a dict has 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 中绑定列名(或表名等)，只能绑定值.

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:

columns = ', '.join(values.keys())
placeholders = ', '.join('?' * len(values))
sql = 'INSERT INTO Media ({}) VALUES ({})'.format(columns, placeholders)
values = [int(x) if isinstance(x, bool) else x for x in values.values()]
cur.execute(sql, values)

但是，这几乎总是一个坏主意.这实际上并不比生成和 execing 动态 Python 代码好多少.而且您一开始就失去了使用占位符的所有好处——主要是保护免受 SQL 注入攻击，但也失去了一些不太重要的东西，例如在数据库引擎中更快的编译、更好的缓存等.

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.

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

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 MediaProperties table? 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)?

另一种使用命名占位符:p>

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()

这篇关于Python Sqlite3:INSERT INTO table VALUE(字典在这里)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！