使用 Python 将列表插入我的数据库
我想在我的数据库中插入一个列表,但我不能。
这是我需要的一个例子:
variable_1 = "HELLO"
variable_2 = "ADIOS"
list = [variable_1,variable_2]
INSERT INTO table VALUES ('%s') % list
可以做这样的事情吗?我可以插入列表作为值吗? 当我尝试时,出现错误,指出这是因为 MySQL 语法错误
I want to insert a list in my database but I can't.
Here is an example of what I need:
variable_1 = "HELLO"
variable_2 = "ADIOS"
list = [variable_1,variable_2]
INSERT INTO table VALUES ('%s') % list
Can something like this be done? Can I insert a list as a value?
When I try it, an error says that is because of an error in MySQL syntax
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您原来问题的答案是:不,您不能插入这样的列表。
然而,通过一些调整,您可以通过使用
%r
并传入一个元组来使该代码工作:不幸的是,这种变量插入方式使您的代码容易受到 SQL注入攻击。
相反,我们建议使用 Python 的 DB API 并为数据构建带有多个问号的自定义查询字符串要插入的:
SQLite3 文档 开头的示例展示了如何使用问号并解释了为什么它们是必要的(本质上,它确保正确引用变量)。
The answer to your original question is: No, you can't insert a list like that.
However, with some tweaking, you could make that code work by using
%r
and passing in a tuple:Unfortunately, that style of variable insertion leaves your code vulnerable to SQL injection attacks.
Instead, we recommend using Python's DB API and building a customized query string with multiple question marks for the data to be inserted:
The example at the beginning of the SQLite3 docs shows how to pass arguments using the question marks and it explains why they are necessary (essentially, it assures correct quoting of your variables).
你的问题不清楚。
您想要将列表作为逗号分隔的文本字符串插入到数据库中的单个列中吗?或者您想将每个元素插入到单独的列中?两者皆有可能,但技术不同。
将逗号分隔的列表插入到一列中:
插入到单独的列中:
两者都假设您已经建立了一个名为 conn 的连接。
其他一些建议:
尽量避免使用不列出要插入的列的名称和顺序的 INSERT 语句。这种声明会导致代码非常脆弱;如果您在表中添加、删除或移动列,它就会中断。
如果您将逗号分隔的列表插入到单个字段中,这通常会违反数据库设计的原则,您应该使用单独的表,每条记录只有一个值。
如果您要插入到单独的字段中,并且它们的名称类似于
Word1
和Word2
,那么这同样表明您应该使用单独的表。< /p>切勿使用直接字符串替换来创建 SQL 语句。如果其中一个值是,例如
o'clock
,它将中断。它还会让您容易受到使用 SQL 注入技术的人的攻击。</p>Your question is not clear.
Do you want to insert the list as a comma-delimited text string into a single column in the database? Or do you want to insert each element into a separate column? Either is possible, but the technique is different.
Insert comma-delimited list into one column:
Insert into separate columns:
both assuming you have established a connection name conn.
A few other suggestions:
Try to avoid INSERT statements that do not list the names and order of the columns you're inserting into. That kind of statement leads to very fragile code; it breaks if you add, delete, or move columns around in your table.
If you're inserting a comma-separted list into a single-field, that generally violates principals of database design and you should use a separate table with one value per record.
If you're inserting into separate fields and they have names like
Word1
andWord2
, that is likewise an indication that you should be using a separate table instead.Never use direct string substitution to create SQL statements. It will break if one of the values is, for example
o'clock
. It also opens you to attacks by people using SQL injection techniques.您可以使用 json.dumps 将列表转换为 json 并将 json 写入数据库。
例如:
You can use
json.dumps
to convert a list to json and write the json to db.For example: