On Tue, 2012-01-24 at 14:49 +0100, Piotr Oh wrote:

I'm not a programmer, so please be patient I just need some scripting done and my choice is python+lxml. The problem to solve is. 1. xml file with some data is exported from our ERP system using third party tools. It has xml schema. 2. It is intended to import to another system (sql/firebird) 3. Between the export/import process I'd like to validate the xml (using xml schema) 4. Than I need to import it to another system: 4a. check the values of the corresponding data in xml and SQL database, compare them, do some action, write to log etc 4b. put them to SQL database, update (update, insert new) From this point of view IMHO what is the right way: use lxml.objectify or etree? I don't care about efficiency. Instead it should be as simple as possible (to modify, read etc) Could you give me some hints here? Some programming idioms, patterns or just basic pseudocode to follow?

What you are describing is a classic use case of a workflow engine; numerous of which exist. Perhaps you should investigate using such a tool. Of course they are off-topic for an LXML list. Most allow you to select data from an RDBMS or import from some type of file [flat, delimited, etc...] to XML. Then you can use XSLT, XPath, what-not, and export the data to a file or insert/upsert/update the data into an RDBMS. For example, using the engine I hack on, I'd perform an sqlSelectAction, pass the output of that to a transformAction, and then pass the output of that to either an sqlInsertAction or an sqlUpsertAction. I just edit the routes [workflow definitions] in BPML [which is itself XML]. -- System & Network Administrator [ LPI & NCLA ] http://www.whitemiceconsulting.com OpenGroupware Developer http://www.opengroupware.us Adam Tauno Williams

On Tue, 2012-01-24 at 21:03 +0100, Piotr Oh wrote:

2012/1/24 Adam Tauno Williams What you are describing is a classic use case of a workflow engine; numerous of which exist. Perhaps you should investigate using such a tool. Of course they are off-topic for an LXML list. Ok, I am aware that there are many ways to do this, but why not with lxml and some python code?

You can certainly do that; it just seems like a lot of plumbing code for something that is already 'done'. And you end up writing the code over and over again.

Dealing with another high level technology like BPML and whatever else it requires is just too much for me. Most allow you to select data from an RDBMS or import from some type of file [flat, delimited, etc...] to XML. Then you can use XSLT, XPath, what-not, and export the data to a file or insert/upsert/update the data into an RDBMS.

And what you are talking about here is free, opensource etc?

Numerous of them. I used to use BIE, which is Java, but have ported most of my routes over the OpenGrouwpare Coils [which contains a workflow engine; and I'm the primary developer of this Python based project]. https://sourceforge.net/projects/coils/ It depends on what you want to do. If you just need a script Python is your answer. I like the ability to submit data for processing, getting error reports, being able to go back and look at how something was processed, how long it took, etc... -- System & Network Administrator [ LPI & NCLA ] http://www.whitemiceconsulting.com OpenGroupware Developer http://www.opengroupware.us Adam Tauno Williams

On Wed, 2012-01-25 at 11:41 +0100, Piotr Oh wrote:

2012/1/24 Adam Tauno Williams

...

You can certainly do that; it just seems like a lot of plumbing code for something that is already 'done'. And you end up writing the code over and over again.

To be honest I even don't know how it could help in my little task. Ok. My post was only a suggestion of something worth investigating. [little tasks if they succeed don't stay little]. And this debate is off-topic for the list. But feel free to raid the OpenGroupware Coils source code [license is MIT/X11]. It can select data into XML and supports loading data into a Python DBAPI connection from XML [is uses a schema called 'StandardXML' to represent columnar type data].

...

Most allow you to select data from an RDBMS or import from some type of file [flat, delimited, etc...] to XML. Then you can use XSLT, XPath, what-not, and export the data to a file orinsert/upsert/update the data into an RDBMS

Nice, but the learning curve is IMHO much higher than lxml+ (custom python code to do the job done with no extra features)?

The "no extra features" comment is absurd.

Numerous of them. I used to use BIE, which is Java, but have ported mostof my routes over the OpenGrouwpare Coils [which contains a workflow engine; and I'm the primary developer of this Python based project]. <https://sourceforge.net/projects/coils/

Opengroupware still alive? that page looks dead http://www.opengroupware.org/ and I wondered what happened to this project.

That isn't the link I sent you.

Previously was it not php based?

Nope, never used PHP. OGo was/is built in Objective-C. -- Adam Tauno Williams http://www.whitemiceconsulting.com System Administrator, OpenGroupware Developer, LPI / CNA Fingerprint 8C08 209A FBE3 C41A DD2F A270 2D17 8FA4 D95E D383

I don't know how Firebird handles this, but you should try if

a) it has a direct way to import XML data in some way

b) you can get away with generating "INSERT OR UPDATE" statements.

In general, a large amount of database roundtrips will make your program much slower than a direct import of a database dump, sometimes by orders of magnitude. Generating a SQL dump file and letting the DB load that directly is bound to be much faster.

Assuming the usage of the kinterbasdb firebrid driver, the most efficient way would be to send a list of tuples as parameters for such a Update/Insert statement. The statement would have to be only prepared (pseudo-compiled, whatever) _once_ at the server side. And the number of network roundtrips will also be minimal. --dirk

Am 25.01.2012, 14:31 Uhr, schrieb Stefan Behnel :

Dirk Rothe, 25.01.2012 14:16:

...

...

I don't know how Firebird handles this, but you should try if

a) it has a direct way to import XML data in some way

b) you can get away with generating "INSERT OR UPDATE" statements.

In general, a large amount of database roundtrips will make your program much slower than a direct import of a database dump, sometimes by orders of magnitude. Generating a SQL dump file and letting the DB load that directly is bound to be much faster.

Assuming the usage of the kinterbasdb firebrid driver, the most efficient way would be to send a list of tuples as parameters for such a Update/Insert statement. The statement would have to be only prepared (pseudo-compiled, whatever) _once_ at the server side. And the number of network roundtrips will also be minimal.

Right, that makes sense for bulk updates.

However, I was referring to the intention to do a diff and incremental updates between the DB content and the XML data, in which case there'd likely be a lot of requests sending data back and forth between the database and the program while parsing the XML file and comparing the corresponding DB entries. You can't really do any bulk access here.

In my experience that leads normally to some form of caching and "incremental" bulky updates. But I guess thats not really lxml related anymore ;)

Assuming the usage of the kinterbasdb firebrid driver, the most efficient way would be to send a list of tuples as parameters for such a Update/Insert statement. The statement would have to be only prepared (pseudo-compiled, whatever) _once_ at the server side. And the number of network roundtrips will also be minimal.

And what about odbc driver? which one of them would be more straightforward? simpler etc? P.

Piotr Oh, 25.01.2012 15:25:

...

That sounds like the easy part. What amount of data are you talking about? Most importantly: does it fit into memory or not?

Amount of data is not a problem.

...

I don't know how Firebird handles this, but you should try if

a) it has a direct way to import XML data in some way

But here I loose the in between process of data manipulation

...

b) you can get away with generating "INSERT OR UPDATE" statements.

Hm, that sound like I should avoid my first naive approach.

Yes, databases can usually process dumps quite quickly and "INSERT OR UPDATE" is a good way to take advantage of that.

...

However, if the diff is a real requirement, you may still have to find a way to compare the data manually. What I did in a project once was to dump the database content in SQL format, then line diff that with a dump I had provided myself, after running both through Unix sort.

So basically you compared the values outside of the database? I wonder why it was faster, than using the database engine itself to search, compare etc. Are not databases supposed to do this in optimal way?

I would expect a line diff to be much faster because you avoid having to query the database for each entry. There's a mostly constant little overhead for each database query which can quickly add up. However, it's also likely harder to process because if you want more than a diff, you have to parse the data back from a SQL dump diff text file. Anyway, you mentioned that performance isn't crucial. If your processing only requires a couple of queries per entry, you may not notice the difference and it may turn out to be simpler to run the queries than to trigger a database dump and process that. Regarding database connectivity in Python, you should post your questions on comp.lang.python rather than here. Many people use OR-mapping tools for database interaction these days, e.g. SQLAlchemy or Django's OR mapper. Depending on the complexity of your queries, you may want to take a look at those, especially since you mentioned that code readability is of high value to you. Stefan