插入到数据库的varchar字段时引用字符串的问题。 [英] problem with quoted strings while inserting into varchar field ofdatabase.
问题描述
你好，
我终于得到了一些代码来将一个腌制列表推送到数据库表中。
但是现在问题在技术上很复杂，尽管可以解决。 />
问题是我可以在dumps（）的帮助下很好地腌制和存储列表在一个blob
字段中用于剔除成字符串然后
将字符串传递给blob。
我也能够安全地返回字符串并执行load（）以获取对象的内容。
但这只适用于列表中包含数字的情况。
如果有一个列表，例如
lst = [" a"，" b"，" c"]然后在一个转储后我得到一个腌制对象到一个
字符串但是当我尝试将其插入blob字段时它拒绝
进入表中。
有一个sql语法错误。
我进一步发现包含
pickle对象的字符串变量包含很多单个quots ; '' "这就是
可能阻止了sql insert的继承。可以有一个
建议如何解决这个问题？
问候，
Krishnakant。
hello,
I finally got some code to push a pickled list into a database table.
but now the problem is technically complex although possible to solve.
the problem is that I can nicely pickle and store lists in a blob
field with the help of dumps() for picklling into a string and then
passing the string to the blob.
I am also able to get back the string safely and do a loads() to
unpickle the object.
but this only works when list contains numbers.
if there is a list such als
lst = ["a","b","c"] then after a dumpls I get a pickled object into a
string but when I try to insert this into the blob field it refuses to
get into the table.
there is an sql syntax error.
I further discovered that the string variable that contains the
pickled object contains a lot of single quots "''" and this is what is
probably preventing the sql insert from succedding. can some one
suggest how to work around this problem?
regards,
Krishnakant.
推荐答案
我进一步发现包含
I further discovered that the string variable that contains the
pickle对象的字符串变量包含大量单个quots"这就是
可能阻止了sql insert的继承。可以有一个
建议如何解决这个问题？
pickled object contains a lot of single quots "''" and this is what is
probably preventing the sql insert from succedding. can some one
suggest how to work around this problem?
每个严肃的数据库驱动程序都有一个完整而可靠的SQL转义
机制。这种机制通常涉及将占位符放在
中的SQL字符串中，并将python数据传递到单独的元组或
字典中。有点
cur.execute（" INSERT INTO datatable（data）VALUES（％s）;"，
（pickled_data，））
而不是：
cur.execute（" INSERT INTO datatable（data）VALUES（''％s''）;"％
（pickled_data，））
驱动程序有责任序列化数据（通常
涉及添加封闭引号并转义奇怪的字符如
引用自己。）
您使用的数据库/驱动程序是什么？ PostgreSQL + psycopg2或任何其他
错误的一个？ ;）在任何一种情况下，请阅读驱动程序文档和
DBAPI文档（ http://www.python.org/dev/peps/pep-0249/ ）
更多细节。
- Daniele
Every serious database driver has a complete and solid SQL escaping
mechanism. This mechanism tipically involves putting placeholders in
your SQL strings and passing python data in a separate tuple or
dictionary. Kinda
cur.execute("INSERT INTO datatable (data) VALUES (%s);",
(pickled_data,))
instead of:
cur.execute("INSERT INTO datatable (data) VALUES (''%s'');" %
(pickled_data,))
It is the driver responsibility to serialize the data (which usually
involves adding enclosing quotes and escape odd charaters such as
quotes themselves).
What database/driver are you using? PostgreSQL+psycopg2 or any other
wrong one? ;) In eiither case, read the driver documentation and the
DBAPI documentation (http://www.python.org/dev/peps/pep-0249/) for
further details.
-- Daniele
2007年5月6日11:22:52 -0700，Daniele Varrazzo< da ****** ********@gmail.com
On 6 May 2007 11:22:52 -0700, Daniele Varrazzo <da**************@gmail.com
每个严肃的数据库驱动程序都有一个完整而可靠的SQL转义
机制。这种机制通常涉及将占位符放在
中的SQL字符串中，并将python数据传递到单独的元组或
字典中。有点
cur.execute（" INSERT INTO datatable（data）VALUES（％s）;"，
（pickled_data，））
Every serious database driver has a complete and solid SQL escaping
mechanism. This mechanism tipically involves putting placeholders in
your SQL strings and passing python data in a separate tuple or
dictionary. Kinda
cur.execute("INSERT INTO datatable (data) VALUES (%s);",
(pickled_data,))
一旦我回到实验室，我会尝试这样做。
意思是我忘了在我之前的电子邮件中提到我使用MySQLdb
用于python-mysql连接。
我没有找到任何这样的参考来在API中存储pickled对象。
任何想法什么可以用我正在使用的mysql python模块完成吗？
问候，
Krishnakant。
I will try doing that once I get back to the lab.
mean while I forgot to mention in my previous email that I use MySQLdb
for python-mysql connection.
I did not find any such reference to storing pickled objects in the API.
any Idea what could be done with the mysql python module I am using?
regards,
Krishnakant.
On 7 Mag ，08：55，krishnakant Mane， < researchb ... @ gmail.comwrote：
On 7 Mag, 08:55, "krishnakant Mane" <researchb...@gmail.comwrote:
2007年5月6日11:22:52 -0700，Daniele Varrazzo< daniele.varra ... @ gmail.com每个严重的数据库驱动程序都有一个完整而可靠的SQL转义
On 6 May 2007 11:22:52 -0700, Daniele Varrazzo <daniele.varra...@gmail.comEvery serious database driver has a complete and solid SQL escaping
机制。这种机制通常涉及将占位符放在
中的SQL字符串中，并将python数据传递到单独的元组或
字典中。 Kinda
mechanism. This mechanism tipically involves putting placeholders in
your SQL strings and passing python data in a separate tuple or
dictionary. Kinda
cur.execute（" INSERT INTO datatable（data）VALUES（％s）;"，
（pickled_data，））
cur.execute("INSERT INTO datatable (data) VALUES (%s);",
(pickled_data,))
一旦我回到实验室，我会尝试这样做。
意思是我忘了提到我的我使用MySQLdb的上一封电子邮件
用于python-mysql连接。
I will try doing that once I get back to the lab.
mean while I forgot to mention in my previous email that I use MySQLdb
for python-mysql connection.
OK：MySQLdb实现了我所描述的转义机制。如果你更难找到它，你可以找到文件。
OK: MySQLdb implements the escaping mechanism i described. You can
find the documentation if you look for it harder.
我没有找到任何这样的参考来在API中存储pickled对象。
I did not find any such reference to storing pickled objects in the API.
存储腌制对象与存储BLOB中的任何其他内容没有区别。如果你不得不写下来，你会遇到同样的问题。
O''Reilly在VARCHAR字段中。
- Daniele
Storing pickled object is not different from storing anything else
into BLOB. You would have faced the same problem if you had to write
"O''Reilly" in a VARCHAR field.
-- Daniele
这篇关于插入到数据库的varchar字段时引用字符串的问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
