python 是否可以将值列表保存到 SQLite 列中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1517771/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to save a list of values into a SQLite column?
提问by john2x
I want 3 columns to have 9 different values, like a list in Python. Is it possible? If not in SQLite, then on another database engine?
我希望 3 列具有 9 个不同的值，就像 Python 中的列表一样。是否可以？如果不在 SQLite 中，那么在另一个数据库引擎上？
回答by Alex Martelli
You must serialize the list (or other Python object) into a string of bytes, aka "BLOB";-), through your favorite means (marshalis good for lists of elementary values such as numbers or strings &c, cPickleif you want a very general solution, etc), and deserialize it when you fetch it back. Of course, that basically carries the list (or other Python object) as a passive "payload" -- can't meaningfully use it in WHEREclauses, ORDER BY, etc.
您必须通过您喜欢的方式将列表（或其他 Python 对象）序列化为一串字节，又名“BLOB”；-) marshal，cPickle如果您想要一个非常通用的解决方案等），并在取回时反序列化它。当然，这基本上将列表（或其他 Python 对象）作为被动的“有效负载”——不能在WHERE子句中有意义地使用它ORDER BY，等等。
Relational databases just don't deal all that well with non-atomic values and would prefer other, normalized alternatives (store the list's items in a different table which includes a "listID" column, put the "listID" in your main table, etc). NON-relational databases, while they typically have limitations wrt relational ones (e.g., no joins), may offer more direct support for your requirement.
关系数据库不能很好地处理非原子值，并且更喜欢其他规范化的替代方案（将列表的项目存储在包含“listID”列的不同表中，将“listID”放在主表中，等等）。非关系数据库，虽然它们通常有关系数据库的限制（例如，没有连接），但可以为您的需求提供更直接的支持。
Some relational DBs do have non-relational extensions. For example, PostGreSQL supports an arraydata type (not quite as general as Python's lists -- PgSQL's arrays are intrinsically homogeneous).
一些关系数据库确实具有非关系扩展。例如，PostGreSQL 支持数组数据类型（不像 Python 的列表那么通用——PgSQL 的数组本质上是同构的）。
回答by Ned Batchelder
Generally, you do this by stringifying the list (with repr()), and then saving the string. On reading the string from the database, use eval() to re-create the list. Be careful, though that you are certain no user-generated data can get into the column, or the eval() is a security risk.
通常，您可以通过对列表进行字符串化（使用 repr()）来完成此操作，然后保存该字符串。从数据库中读取字符串时，使用 eval() 重新创建列表。请注意，尽管您确定没有用户生成的数据可以进入该列，或者 eval() 存在安全风险。
回答by steveha
Your question is difficult to understand. Here it is again:
你的问题很难理解。又来了：
I want 3 columns to have 9 different values, like a list in Python. Is it possible? If not in SQLite, then on another database engine?
我希望 3 列具有 9 个不同的值，就像 Python 中的列表一样。是否可以？如果不在 SQLite 中，那么在另一个数据库引擎上？
Here is what I believe you are asking: is it possible to take a Python list of 9 different values, and save the values under a particular column in a database?
这就是我相信您要问的问题：是否可以采用包含 9 个不同值的 Python 列表，并将这些值保存在数据库中的特定列下？
The answer to this question is "yes". I suggest using a Python ORM library instead of trying to write the SQL code yourself. This example code uses Autumn:
这个问题的答案是“是”。我建议使用 Python ORM 库，而不是尝试自己编写 SQL 代码。此示例代码使用Autumn：
import autumn
import autumn.util
from autumn.util import create_table
# get a database connection object
my_test_db = autumn.util.AutoConn("my_test.db")
# code to create the database table
_create_sql = """\
DROP TABLE IF EXISTS mytest;
CREATE TABLE mytest (
id INTEGER PRIMARY KEY AUTOINCREMENT,
value INTEGER NOT NULL,
UNIQUE(value)
);"""
# create the table, dropping any previous table of same name
create_table(my_test_db, _create_sql)
# create ORM class; Autumn introspects the database to find out columns
class MyTest(autumn.model.Model):
db = my_test_db
lst = [3, 6, 9, 2, 4, 8, 1, 5, 7] # list of 9 unique values
for n in lst:
row = MyTest(value=n) # create MyTest() row instance with value initialized
row.save() # write the data to the database
Run this code, then exit Python and run sqlite3 my_test.db. Then run this SQL command inside SQLite: select * from mytest;Here is the result:
运行此代码，然后退出 Python 并运行sqlite3 my_test.db. 然后在 SQLite 中运行此 SQL 命令：select * from mytest;结果如下：
1|3
2|6
3|9
4|2
5|4
6|8
7|1
8|5
9|7
This example pulls values from one list, and uses the values to populate one column from the database. It could be trivially extended to add additional columns and populate them as well.
此示例从一个列表中提取值，并使用这些值填充数据库中的一列。它可以简单地扩展为添加额外的列并填充它们。
If this is not the answer you are looking for, please rephrase your request to clarify.
如果这不是您正在寻找的答案，请重新表述您的要求以进行澄清。
P.S. This example uses autumn.util. The setup.pyincluded with the current release of Autumn does not install util.pyin the correct place; you will need to finish the setup of Autumn by hand.
PS 此示例使用autumn.util. 在setup.py与秋天的当前版本不包含安装util.py在正确的位置; 您需要手动完成 Autumn 的设置。
You could use a more mature ORM such as SQLAlchemy or the ORM from Django. However, I really do like Autumn, especially for SQLite.
您可以使用更成熟的 ORM，例如 SQLAlchemy 或来自 Django 的 ORM。但是，我真的很喜欢 Autumn，尤其是对于 SQLite。
