的Python - SQLServer中更新XML列（类型错误： '类型' 对象有没有属性 '__getitem__'）

Question

我一直在的SQLServer数据库从蟒蛇节省这样的XML：

import pypyodbc as pyodbc 
import urllib 
from xml.dom.minidom import Document 

doc = Document() 
base = doc.createElement('PERSONA') 
doc.appendChild(base) 
entry = doc.createElement('INFORMACION') 
base.appendChild(entry) 
nombre = doc.createElement('NOMBRE') 
contenidoNombre = doc.createTextNode('Jack') 
nombre.appendChild(contenidoNombre) 
entry.appendChild(nombre) 
apellido = doc.createElement('APELLIDO') 
contenidoApellido = doc.createTextNode('Black') 
apellido.appendChild(contenidoApellido) 
entry.appendChild(apellido) 

print doc.toxml() 

cnxn = pyodbc.connect(
    'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=localhost;DATABASE=HOLA;UID=sa;[email protected]' 
) 

idPerson = 2 

cursor = cnxn.cursor() 
cursor.execute("INSERT INTO prueba VALUES (?, ?)", (idPerson, doc.toxml())) 
cnxn.commit() 
print("XML Guardado"); 

这是数据库的脚本：

USE [HOLA] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[prueba](
    [id] [int] NOT NULL, 
    [xml] [xml] NULL, 
CONSTRAINT [PK_prueba] PRIMARY KEY CLUSTERED 
(
    [id] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

GO 

现在，我想更新XML场从的Python，我一直想这样的：当程序运行时更新查询

import pypyodbc as pyodbc 
import urllib 
from xml.dom import minidom 
from xml.dom.minidom import parse, parseString 

# Replace text from nodes of the xml 
def replaceText(node, newText): 
    if node.firstChild.nodeType != node.TEXT_NODE: 
     raise Exception("node does not contain text") 
    node.firstChild.replaceWholeText(newText) 

# Connection string 
cnxn = pyodbc.connect(
    'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=localhost;DATABASE=TEST;UID=sa;PWD=123456' 
) 

idPersona = 3 
# Load the selected row 
cursor = cnxn.cursor() 
cursor.execute('SELECT id, xml FROM prueba WHERE id=?', (idPersona,)) 

for row in cursor.fetchall(): 
    print row[1] 

# Create a xml from the result string 
xml = row[1] 
dom = parseString(xml) 

# Update the nodes in the XML 
node = dom.getElementsByTagName('NOMBRE')[0] 
replaceText(node, "Modificado") 
nombre = node.firstChild.nodeValue 

node = dom.getElementsByTagName('APELLIDO')[0] 
replaceText(node, "Modificado") 
apellido = node.firstChild.nodeValue 

# Show the result... 
print ("Nombre: " + nombre) 
print ("Apellido: " + apellido) 

# The problem is here... 
cursor = cnxn.cursor() 
cursor.execute("UPDATE prueba SET xml=? WHERE id=?", (dom, idPersona)) 
cnxn.commit() 
print 'Actualizacion Realizada con Exito' 

，它给了我一个错误：

Traceback (most recent call last): 
    File "C:\Users\cnavarro\Downloads\test.py", line 44, in <module> 
    cursor.execute("UPDATE prueba SET xml=? WHERE id=?", (dom, idPersona)) 
    File "build\bdist.win32\egg\pypyodbc.py", line 1470, in execute 
    self._BindParams(param_types) 
    File "build\bdist.win32\egg\pypyodbc.py", line 1275, in _BindParams 
    if param_types[col_num][0] == 'u': 
TypeError: 'type' object has no attribute '__getitem__' 

我在做什么错了.. 。？

Answer 1

在忘记将XML对象插入表之前，您已经忘记将XML对象转换回字符串。

您的插入脚本在传递到数据库之前会将XML文档转换为字符串，并且您的更新脚本会将XML从列中解析出来，因此将更新后的XML转换为字符串似乎也是一致的。因此

解决方法是更换线路

cursor.execute("UPDATE prueba SET xml=? WHERE id=?", (dom, idPersona)) 

与

cursor.execute("UPDATE prueba SET xml=? WHERE id=?", (dom.toxml(), idPersona))