Python/pypyODBC:使用字符串和NULL的行插入 [英] Python / pypyODBC: Row Insert Using String and NULLs

问题描述

我对Python还是很陌生，直到这个当前项目之前，它还没有真正与SQL Server和XML解析进行交互(甚至可能不是最好的方法).简而言之，我的IT团队积压了很多工作，我需要将数据放入沙箱表(由XML解析)中.

I am pretty new to Python and have not really interacted with SQL Server and XML Parsing until this current project (might not even the best approach to take). Long story short, my IT team is very back-logged and I need to get data into a sandbox table (parsed from an XML).

XML的一部分包含[xx] 001至[xx] 025属性.我要解析的每个XML都没有包括所有这些属性.因此，我遍历了所有可能的属性的循环并将结果附加到列表中.由于并非所有XML中都包含所有属性，因此我可以通过尝试表示每个属性来产生noneTypes.在创建需要将它们转换为NULL的SQL插入语句之前，这不是问题.解决这个问题的方法可能很简单，但是我的新手身份妨碍了我的进步.相关代码片段如下...

There is a segment of the XML that contains attributes [xx]001 to [xx]025. Not all of these attributes are included for each XML I will be parsing. Therefore, I iterate through a loop of all the possible attributes and append the results to a list. Since not all attributes are in each XML, I can incur noneTypes by trying to represent each one. Not an issue until a get to creating my SQL insert statement where I need them converted to NULLs. There is probably some very simple way to handle this, but my newbie status is impeding my progress. Relevant pieces of the code follow...

也许总体上有更好的方法吗?我担心sqlList可能只能在达到极限之前变大.

Maybe there is a better way to do it in general? I am concerned that sqlList might only be able to get so large before I hit a limit as well.

#this list contains Nones
sqlList = (", ".join(map(repr,appendedList)))

#the Nones are an issue when I get to here
curs.execute("USE Sandbox INSERT INTO myTable VALUES (%s)" % (sqlList))
curs.commit()

以下是sqlList的示例:

Here is an example of what sqlList looks like:

'20_2014', '20_2014_3/25/2015 2:01 PM', 'FBR', 'A', '0', '0', '3', '1', '134', None, None, '0', None, '0', '0', '0', '0', '0', None, None, '2', None, None, None, None

我收到以下错误消息:

pypyodbc.ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'None'.")

推荐答案

一般来说，您仍然想知道这是否是执行此操作的正确"方法.

In general would still like to know if this is the "right" way to do this.

您的解决方案仍然是动态SQL ，始终受

Your solution is still Dynamic SQL, which always suffers from

• SQL注入问题(例如，如果字符串之一包含单引号怎么办?)
• 玩杂耍(例如，无->如您所问的那样为NULL)
• 必须为您的文字值(字符串，日期等)使用正确的定界符

如果您使用参数化查询，所有这些问题都会消失.为了清楚起见，本示例仅使用2个属性(而不是问题中的25个)，但是原理是完全相同的.请注意，我不必执行任何特殊操作即可处理None值，以便将它们作为NULL插入.

All of those issues go away if you use a parameterized query. This example uses only 2 attributes for clarity (instead of the 25 in your question), but the principle is exactly the same. Notice that I don't have to do anything special to handle None values in order for them to be inserted as NULLs.

import pypyodbc
myData = [
    (1, 'foo'),
    (2, None),
    (3, 'bar'),
    ]
connStr = """
DSN=myDb_SQLEXPRESS;
"""
cnxn = pypyodbc.connect(connStr)
crsr = cnxn.cursor()
sql = """
INSERT INTO myTable VALUES (?, ?)
"""
for dataRow in myData:
    print(dataRow)
    crsr.execute(sql, dataRow)
cnxn.commit()
crsr.close()
cnxn.close()

控制台输出为...

(1, 'foo')
(2, None)
(3, 'bar')

...，并将三行正确插入到表中，第二行包括NULL(无).

... and the three rows are inserted into the table correctly, including the NULL (None) in the second row.

这篇关于Python/pypyODBC:使用字符串和NULL的行插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！