populating data from xml file to a sqlite database using python

Question

Hi all,

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.

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.

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

Answer 1

Hi, 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):

• xml.dom
• 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.

Answer 2

I recommend you study on ElementTree for 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.

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.

Once you fully understand etree and sqlite3 -- and you don't necessarily need to install *any*thing 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 oXygen or 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;-).

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