Advice for Method to consistently import XML into SQLAlchemy

[Sayth Renshaw]

Hi

Looking for some guidance and advice on using xml as an update source for my to be data web project. If I am consistently going to be updating data into the database from XML files what is a good method?


Should I be creating a Sax parser as in this example http://iamtgc.com/importing-xml-into-a-database-with-python-and-sqlalchemy/ .
To be clear the XML I am importing from is not basic, using from that example my xml would like more like. So i would need to filter the values out that I want which I have acheived using xmltodict.

<!-- books.xml -->

<catalog>

  <book isbn="1-880985-26-8" binding="paperback" ebook="mobi" altformat="pdf" etc="ManyMore">

    <title>The Consumer</title>

    <author>M. Gira</author>

  </book>

  <book isbn="0-679775-43-9" binding="paperback" ebook="mobi" altformat="pdf" etc="ManyMore">

    <title>The Wind-Up Bird Chronicle</title>

    <author>Haruki Murakami</author>

  </book>

  <!-- imagine more entries here... -->

</catalog>

Copies are available here http://old.racingnsw.com.au/Site/_content/racebooks/20140515GOSF0.xml













Or should I be attempting to filter and convert the XML to json format and import into SQLAlchemy.

Or other, searching around I cannot, strangely find one and only one way to do it. It seems to be a mish mosh of good luck, well from perspective of someone looking to implement this for the first time. Probably painfully obvious to those who have done it before.

Anyway thank you for your time.

Sayth

[Sayth Renshaw]

I can manipulate the xml now with xmltodict and lxml.objectify but I am not sure about what I am transforming it to, the what will SqlAlchemy require of me.

[AM]

On 05/14/2014 04:23 AM, Sayth Renshaw wrote:
> Hi
>
> Looking for some guidance and advice on using xml as an update source
> for my to be data web project. If I am consistently going to be
> updating data into the database from XML files what is a good method?
>
>
> Should I be creating a Sax parser as in this example
> http://iamtgc.com/importing-xml-into-a-database-with-python-and-sqlalchemy/
> .
> To be clear the XML I am importing from is not basic, using from that
> example my xml would like more like. So i would need to filter the
> values out that I want which I have acheived using xmltodict.
> |<!-- books.xml -->|
> |<||catalog||>|

> |||<||book| |isbn||=||"1-880985-26-8"||binding="paperback"

> ebook="mobi" altformat="pdf" etc="ManyMore">|
> |||<||title||>The Consumer</||title||>|
> |||<||author||>M. Gira</||author||>|
> |||</||book||>|

> |||<||book| |isbn||=||"0-679775-43-9"|||binding="paperback"

> ebook="mobi" altformat="pdf" etc="ManyMore"|>|
> |||<||title||>The Wind-Up Bird Chronicle</||title||>|
> |||<||author||>Haruki Murakami</||author||>|
> |||</||book||>|
> |||<!-- imagine more entries here... -->|
> |</||catalog||>|
> Copies are available here
> http://old.racingnsw.com.au/Site/_content/racebooks/20140515GOSF0.xml
>
>
>
>
>
>
>
>
>
>
>
>
>
> Or should I be attempting to filter and convert the XML to json format
> and import into SQLAlchemy.
>
> Or other, searching around I cannot, strangely find one and only one
> way to do it. It seems to be a mish mosh of good luck, well from
> perspective of someone looking to implement this for the first time.
> Probably painfully obvious to those who have done it before.
>
> Anyway thank you for your time.
>
> Sayth

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

The last time I had to domething like this, I converted all xml
attributes to tags using an XSLT sheet and then used lxml to populate a
sqla object.

You can also do it manually as in the link but use lxml etree which
might make the code a bit easier to read. IMO sax is a bit more
performant than etree but can get a bit confusing.

HTH
AM

[Sayth Renshaw]

Is there an advantage to using xslt compared to converting it to something like json?

Sayth

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com <mailto:sqlalchemy+unsub...@googlegroups.com>.
To post to this group, send email to sqlal...@googlegroups.com <mailto:sqlalchemy@googlegroups.com>.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

The last time I had to domething like this, I converted all xml attributes to tags using an XSLT sheet and then used lxml to populate a sqla object.

You can also do it manually as in the link but use lxml etree which might make the code a bit easier to read. IMO sax is a bit more performant than etree but can get a bit confusing.

HTH
AM

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/zpjw1nOkRWg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[Michael Bayer]

not really reading this thread since it isn’t too SQLA specific but if you’re parsing XML, unless you have unusual memory/performance requirements you just use lxml with etree to set up a DOM, and that’s it.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

[Sayth Renshaw]

The part I am not understanding though is what does SqlAlchemy want my data to look like to be an acceptable format.

I know how to create my models and db in SqlAlchemy and though I am a beginner at etree can use it but can't find examples to see what my end goal should look like.

Sayth

To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Jonathan Rogers]

Sayth Renshaw wrote:
> The part I am not understanding though is what does SqlAlchemy want my
> data to look like to be an acceptable format.
>
> I know how to create my models and db in SqlAlchemy and though I am a
> beginner at etree can use it but can't find examples to see what my end
> goal should look like.

SQLAlchemy doesn't define a data format. It's a tool that allows you to
define your own interfaces and formats. You probably need to read one of
the excellent tutorials. Most likely you're trying to use classes mapped
using SQLAlchemy's ORM which allows great flexibility in defining an
interface to your data.
<URL:http://docs.sqlalchemy.org/en/latest/orm/tutorial.html>

Alternatively, you may be trying to use SQLAlchemy's expression language
to generate INSERT or UPDATE statements more directly, in which case read:
<URL:http://docs.sqlalchemy.org/en/latest/core/tutorial.html>
--
Jonathan Rogers