使用Python pyodbc
问题描述:
我有一个代码从SQL Server转换XML领域为XML文件:使用Python pyodbc
import pypyodbc as pyodbc
cnxn = pyodbc.connect(
'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=localhost;DATABASE=HOLA;UID=sa;PWD=HOLAMUNDO'
)
idPerson = 2
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM prueba WHERE id=?', (idPerson,))
for row in cursor.fetchall():
print(row)
这工作得很好,并选择XML领域有下格式:
<PERSON>
<INFO>
<NAME>Charlie</NAME>
<LASTNAME>Brown</LASTNAME>
</INFO>
</PERSON>
我怎样才能把这个领域并将其保存在一个新的XML文件中的目录...?
这是我的数据库脚本:
USE [HOLA]
GO
/****** Object: Table [dbo].[prueba] Script Date: 04/03/2016 8:29:30 ******/
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
答
解决,在未来的道路...
import pypyodbc as pyodbc
import urllib
from xml.dom.minidom import parse, parseString
xmlpath = "example.xml"
cnxn = pyodbc.connect(
'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=localhost;DATABASE=HOLA;UID=sa;PWD=HOLAMUNDO'
)
idPerson = 2
cursor = cnxn.cursor()
cursor.execute('SELECT id, xml FROM prueba WHERE id=?', (idPerson,))
for row in cursor.fetchall():
print row[1]
xml = row[1]
#Generate the xml file from the string
dom = parseString(xml)
# Write the new xml file
xml_str = dom.toprettyxml(indent=" ")
with open("example.xml", "w") as f:
f.write(xml_str)
只需保存'row'字符串文件。 – Parfait