python 使用python将数据从xml文件填充到sqlite数据库

populating data from xml file to a sqlite database using python

提问by fx.

I have a question related to some guidances to solve a problem. I have with me an xml file, I have to populate it into a database system (whatever, it might be sqlite, mysql) using scripting language: Python.

我有一个与解决问题的一些指导相关的问题。我有一个 xml 文件，我必须使用脚本语言 Python 将它填充到数据库系统中（无论如何，它可能是 sqlite、mysql）。

Does anyone have any idea on how to proceed?

有没有人知道如何进行？

• Which technologies I need to read further?
• Which environments I have to install?
• Any tutorials on the same topic?

• 我需要进一步阅读哪些技术？
• 我必须安装哪些环境？
• 有关于同一主题的教程吗？

I already tried to parse xml using both by tree-based and sax method in other language, but to start with Python, I don't know where to start. I already know how to design the database I need.

我已经尝试在其他语言中使用基于树的方法和 sax 方法来解析 xml，但是要从 Python 开始，我不知道从哪里开始。我已经知道如何设计我需要的数据库。

Another question, is Python alone possible of executing database ddl queries?

另一个问题，是否可以单独使用 Python 执行数据库 ddl 查询？

回答by Alex Martelli

I recommend you study on ElementTreefor parsing your XML file into memory (parse it all, then emit it all to a SQL DB, is probably easier, but element-tree also allows incremental operation if your file is huge) -- it's part of the standard Python library as module xml.etree.

我建议您研究ElementTree以将您的 XML 文件解析到内存中（全部解析，然后将其全部发送到 SQL DB，可能更容易，但如果您的文件很大，元素树也允许增量操作）——它是作为模块xml.etree的标准 Python 库。

I recommend sqlite3(also in the standard Python library) as the relational DB of choice (if you have a choice), again because it's handy and easy -- the underlying SQLite embedded relational DB is also well documented at its own site. If you need a general tutorial on how Python likes to interface to relational DBs (the "DB-API"), there's a nice one here.

我推荐sqlite3（也在标准 Python 库中）作为首选的关系数据库（如果你有选择的话），同样是因为它方便易用——底层的 SQLite 嵌入式关系数据库也在它自己的站点上有很好的记录。如果您需要Python的喜欢接口到关系的DB（以下简称“DB-API”）一般的教程，有一个很好的一个位置。

Once you fully understand etree and sqlite3 -- and you don't necessarily need to install anything for either (which is part of their charms;-) -- you're basically all set. (Of course an installation of SQLite itself (commandline and/or GUI tools) so you can look at your DB files and tweak them may be nice, as may graphical XML editors such as oXygenor XMLmindto look at and/or tweak your XML, but neither kind of tool is at all needed, not at all related to using Python rather than other languages for the XML parsing and SQLite writing;-).

一旦您完全理解了 etree 和 sqlite3——并且您不一定需要为它们安装任何东西（这是它们魅力的一部分；-）——您基本上都准备好了。（当然，安装 SQLite 本身（命令行和/或 GUI 工具）以便您可以查看您的 DB 文件并对其进行调整可能很好，图形 XML 编辑器（例如oXygen或XMLmind）也可以查看和/或调整您的 XML ，但根本不需要任何一种工具，与使用 Python 而不是其他语言进行 XML 解析和 SQLite 编写完全没有关系；-)。

And yes, you can perfectly well do CREATE TABLEand other DDL queries from sqlite3(and any other DB-API compliant Python module, if you choose to use other relational DBs;-).

是的，您完全可以CREATE TABLE从sqlite3（以及任何其他符合 DB-API 的 Python 模块中执行其他 DDL 查询，如果您选择使用其他关系数据库；-）。

回答by kev

You can do it all by hand with sqlite3and xmlstarlet.

您可以使用sqlite3和手动完成所有操作xmlstarlet。

• convert xml to csv
• import csv to db.

• 将 xml 转换为 csv
• 将 csv 导入数据库。

(Maybe it's not your want. but it's handy)

（也许这不是你想要的。但它很方便）

# content of xml file
$ cat artist.xml
<results><artist><id>Ae2300d8b0232c06c</id><name>莎拉 布莱曼(Sarah Brightman)</name><hotSongs><!--freemusic/song/result/Sa3f6b810d7f98646--><song><id>Sa3f6b810d7f98646</id><name>Gloomy Sunday</name><artist>莎拉 布莱曼(Sarah Brightman)</artist><artistId>Ae2300d8b0232c06c</artistId><album>月光女神</album><duration>227.0</duration><canBeDownloaded>true</canBeDownloaded><hasFullLyrics>true</hasFullLyrics><canBeStreamed>true</canBeStreamed><albumId>Bfef909d41922984a</albumId><hasSimilarSongs>true</hasSimilarSongs><hasRecommendation>false</hasRecommendation></song><!--freemusic/song/result/Sbb94fa21258a8b51--><song><id>Sbb94fa21258a8b51</id><name>我和你(2008年奥运会主题曲)(You And Me)</name><artist>刘欢</artist><artist>莎拉 布莱曼(Sarah Brightman)</artist><artistId>A6682d46bd4bdfc84</artistId><artistId>Ae2300d8b0232c06c</artistId><album>我和你 2008北京奥运会主题歌</album><duration>256.0</duration><canBeDownloaded>true</canBeDownloaded><hasFullLyrics>true</hasFullLyrics><canBeStreamed>true</canBeStreamed><albumId>Babf3ab3b7ef0e3ed</albumId><hasSimilarSongs>true</hasSimilarSongs><hasRecommendation>false</hasRecommendation></song><!--freemusic/song/result/S6a47788bef57ff87--><song><id>S6a47788bef57ff87</id><name>Scarborough Fair</name><artist>莎拉 布莱曼(Sarah Brightman)</artist><artistId>Ae2300d8b0232c06c</artistId><album>月光女神</album><duration>251.0</duration><canBeDownloaded>true</canBeDownloaded><hasFullLyrics>true</hasFullLyrics><canBeStreamed>true</canBeStreamed><albumId>Bfef909d41922984a</albumId><hasSimilarSongs>true</hasSimilarSongs><hasRecommendation>false</hasRecommendation></song><!--freemusic/song/result/Sde1aa68da126ddfe--><song><id>Sde1aa68da126ddfe</id><name>Time To Say Goodbye</name><artist>莎拉 布莱曼(Sarah Brightman)</artist><artistId>Ae2300d8b0232c06c</artistId><album>Symphony: Live In Vienna</album><duration>275.0</duration><canBeDownloaded>true</canBeDownloaded><hasFullLyrics>true</hasFullLyrics><canBeStreamed>true</canBeStreamed><albumId>B0187937a07b940f7</albumId><hasSimilarSongs>true</hasSimilarSongs><hasRecommendation>false</hasRecommendation></song><!--freemusic/song/result/S7483cc8c44e06bbb--><song><id>S7483cc8c44e06bbb</id><name>Amazing Grace</name><artist>莎拉 布莱曼(Sarah Brightman)</artist><artistId>Ae2300d8b0232c06c</artistId><album>真爱永恒 - 冬之歌</album><duration>186.0</duration><canBeDownloaded>true</canBeDownloaded><hasFullLyrics>true</hasFullLyrics><canBeStreamed>true</canBeStreamed><albumId>B38e85a3056c0381c</albumId><hasSimilarSongs>true</hasSimilarSongs><hasRecommendation>false</hasRecommendation></song><!--freemusic/song/result/S2d9683c7230e18de--><song><id>S2d9683c7230e18de</id><name>This Love</name><artist>莎拉 布莱曼(Sarah Brightman)</artist><artistId>Ae2300d8b0232c06c</artistId><album>月光女神</album><duration>371.0</duration><canBeDownloaded>true</canBeDownloaded><hasFullLyrics>true</hasFullLyrics><canBeStreamed>true</canBeStreamed><albumId>Bfef909d41922984a</albumId><hasSimilarSongs>true</hasSimilarSongs><hasRecommendation>false</hasRecommendation></song><!--freemusic/song/result/Scfb4989195b44e57--><song><id>Scfb4989195b44e57</id><name>Here With Me</name><artist>莎拉 布莱曼(Sarah Brightman)</artist><artistId>Ae2300d8b0232c06c</artistId><album>月光女神</album><duration>324.0</duration><canBeDownloaded>true</canBeDownloaded><hasFullLyrics>true</hasFullLyrics><canBeStreamed>true</canBeStreamed><albumId>Bfef909d41922984a</albumId><hasSimilarSongs>true</hasSimilarSongs><hasRecommendation>false</hasRecommendation></song><!--freemusic/song/result/S7bafc588178b932c--><song><id>S7bafc588178b932c</id><name>La Lune</name><artist>莎拉 布莱曼(Sarah Brightman)</artist><artistId>Ae2300d8b0232c06c</artistId><album>月光女神</album><duration>173.0</duration><canBeDownloaded>true</canBeDownloaded><hasFullLyrics>false</hasFullLyrics><canBeStreamed>true</canBeStreamed><albumId>Bfef909d41922984a</albumId><hasSimilarSongs>true</hasSimilarSongs><hasRecommendation>false</hasRecommendation></song></hotSongs></artist></results>

# convert xml to csv file using xmlstarlet
$ xml sel -t -m '//song' -v 'concat(id, ",", name, ",", duration)' -n artist.xml | sed '$d' >artist.csv

# content of csv file
$ cat artist.csv
Sa3f6b810d7f98646,Gloomy Sunday,227.0
Sbb94fa21258a8b51,我和你(2008年奥运会主题曲)(You And Me),256.0
S6a47788bef57ff87,Scarborough Fair,251.0
Sde1aa68da126ddfe,Time To Say Goodbye,275.0
S7483cc8c44e06bbb,Amazing Grace,186.0
S2d9683c7230e18de,This Love,371.0
Scfb4989195b44e57,Here With Me,324.0
S7bafc588178b932c,La Lune,173.0

# create sqlite database
$ sqlite3 song.db
sqlite> CREATE TABLE song(id, name, duration);

# enter csv mode to import csv file
sqlite> .mode csv
sqlite> .import artist.csv song

# check everything is ok
sqlite> .mode column
sqlite> .header on
sqlite> select * from song;
id                 name           duration
-----------------  -------------  ----------
Sa3f6b810d7f98646  Gloomy Sunday  227.0
Sbb94fa21258a8b51  我和你(200  256.0
S6a47788bef57ff87  Scarborough F  251.0
Sde1aa68da126ddfe  Time To Say G  275.0
S7483cc8c44e06bbb  Amazing Grace  186.0
S2d9683c7230e18de  This Love      371.0
Scfb4989195b44e57  Here With Me   324.0
S7bafc588178b932c  La Lune        173.0

回答by Krab

If you are accustomed to DOM (tree) access to xml from other language, you may find useful these standard library modules (and their respective docs):

如果您习惯于 DOM（树）从其他语言访问 xml，您可能会发现这些标准库模块（及其各自的文档）很有用：

• xml.dom
• xml.dom.minidom

• xml文件
• xml.dom.minidom

To save tha data to DB, you can use standard module sqlite3 or look for binding to mysql. Or you may wish to use something more abstract, like SQLAlchemy or Django's ORM.

要将数据保存到数据库，您可以使用标准模块 sqlite3 或查找与 mysql 的绑定。或者您可能希望使用更抽象的东西，例如 SQLAlchemy 或 Django 的 ORM。