Import Initial Data
Gustavo Andrés Angulo
Hi, I'm creating a Module for tryton, but I need to import initial
data, are approximately 60.000 records and I have a csv with this data,
I created script that converts from csv to xml , but I want to know if
this is correct form or exists some other way to import csv without xml.
Thanks.
Cédric Krier
There is a lot of way to import datas. I think the choice depends of the use of
those datas.
Here is some possibilities:
- xml file inside module: it is good if you don't have to change those datas
later (like countries in country module).
It is also possible to add attribute noupdate to have a kind of first shot
for the data importation (like for location in stock module).
- cvs file from the client: you can use the import function from the client.
You must be on the form of the model you want to import and define the
matching between csv columns and model fields. It is good for a one shot but
with a lot of data it can be very slow and it can be difficult to fix it if
there is errors in the cvs file.
- write a script that use the import_data of the model. The script must
connect to the database, instanciate a Pool etc. If you choose this way, I
think vengfulsquirrel has already done a script like that.
- write a script thet use NetRPC or XML-RPC to create records.
http://code.google.com/p/tryton/wiki/CodeSnippets
--
Cédric Krier
B2CK SPRL
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59
Email: cedric...@b2ck.com
Jabber: cedric...@b2ck.com
Website: http://www.b2ck.com/
Mathias Behrle
> There is a lot of way to import datas. I think the choice depends of the use
> of those datas.
> Here is some possibilities:
>
> - xml file inside module: it is good if you don't have to change those datas
> later (like countries in country module).
> It is also possible to add attribute noupdate to have a kind of first shot
> for the data importation (like for location in stock module).
>
> - cvs file from the client: you can use the import function from the client.
> You must be on the form of the model you want to import and define the
> matching between csv columns and model fields. It is good for a one shot but
> with a lot of data it can be very slow and it can be difficult to fix it if
> there is errors in the cvs file.
>
> - write a script that use the import_data of the model. The script must
> connect to the database, instanciate a Pool etc. If you choose this way, I
> think vengfulsquirrel has already done a script like that.
>
> - write a script thet use NetRPC or XML-RPC to create records.
> http://code.google.com/p/tryton/wiki/CodeSnippets
I created a module to import german bank data (german bank institutes with
bankcodes and coordinates). These data are kind of "semi-static", since they
are subject to modification in the way, that new banks can be added, old banks
deleted, bankcodes changed. Nevertheless those data shouldn't be changed by the
user, so I went for xml import.
Now I got stuck with a problem:
- Installing first module party_bank_de and module account in a second step
works fine: Account default properties were set also for banks.
- Installing first module account and module party_bank_de in a second step,
doesn't work: I get the error
'The value of the field "Account Payable" on "Party" is not valid according to
its domain.'
Server log:
INFO:modules:party_bank_de:loading bank.xml
ERROR:convert:Error while parsing xml file:
In tag record: model bank.bank with id bk_10000000.
So my question is:
- Which is the preferred way to add those "semi_static" data?
- How to work around the import problem via xml, if there exist default
properties for an underlying model?
I have put a stripped down version of party_bank_de on intuxication, since the
final xml file is about 8 MB;). You find the modules in question at
http://mercurial.intuxication.org/hg/party_bank
http://mercurial.intuxication.org/hg/party_bank_de_sample
Thx for any input!
--
Mathias Behrle
MBSolutions
Gilgenmatten 10 A
D-79114 Freiburg
Tel: +49(761)471023
Fax: +49(761)4770816
http://mbsolutions.selfip.biz
UStIdNr: DE 142009020
PGP/GnuPG key availabable from any keyserver, ID: 0x89BCA161
Cédric Krier
> I created a module to import german bank data (german bank institutes with
> bankcodes and coordinates). These data are kind of "semi-static", since they
> are subject to modification in the way, that new banks can be added, old banks
> deleted, bankcodes changed. Nevertheless those data shouldn't be changed by the
> user, so I went for xml import.
>
> Now I got stuck with a problem:
>
> - Installing first module party_bank_de and module account in a second step
> works fine: Account default properties were set also for banks.
>
> - Installing first module account and module party_bank_de in a second step,
> doesn't work: I get the error
>
> 'The value of the field "Account Payable" on "Party" is not valid according to
> its domain.'
>
> Server log:
> INFO:modules:party_bank_de:loading bank.xml
> ERROR:convert:Error while parsing xml file:
> In tag record: model bank.bank with id bk_10000000.
>
> So my question is:
> - Which is the preferred way to add those "semi_static" data?
Definitly xml, because I don't see user keeping uptodate all those data.
> - How to work around the import problem via xml, if there exist default
> properties for an underlying model?
I tried your senario and it was working.
The "Account Payable" is only required when the user has a company or the
import from xml is done by the root user which must not have any company.
Mathias Behrle
Thx a lot!
> > - How to work around the import problem via xml, if there exist default
> > properties for an underlying model?
>
> I tried your senario and it was working.
> The "Account Payable" is only required when the user has a company or the
> import from xml is done by the root user which must not have any company.
Perhaps I am missing something in your explanation, I cannot get it
working so far, but I probably should have been a little more explicit.
I am installing from the client (as admin user). As soon as I have configured
the wizards (for company and account), it is no more possible to install
party_bank_de.
Did I miss something?
Cédric Krier
> * Betr.: " [tryton] Re: Import Initial Data" (Mon, 31 Aug 2009 09:54:16 +0200):
>
> Thx a lot!
>
> > > - How to work around the import problem via xml, if there exist default
> > > properties for an underlying model?
> >
> > I tried your senario and it was working.
> > The "Account Payable" is only required when the user has a company or the
> > import from xml is done by the root user which must not have any company.
>
> Perhaps I am missing something in your explanation, I cannot get it
> working so far, but I probably should have been a little more explicit.
>
> I am installing from the client (as admin user). As soon as I have configured
> the wizards (for company and account), it is no more possible to install
> party_bank_de.
>
> Did I miss something?
>
It is what I did.
Hartmut Goebel
> It is what I did.
I really love brief answers. But sometime a bit more verbosity would
really be helpful.
--
Schönen Gruß - Regards
Hartmut Goebel
Dipl.-Informatiker (univ.), CISSP, CSSLP
Goebel Consult
Spezialist für IT-Sicherheit in komplexen Umgebungen
http://www.goebel-consult.de
Monatliche Kolumne: http://www.cissp-gefluester.de/
Goebel Consult mit Mitglied bei http://www.7-it.de
Cédric Krier
> On 31/08/09 22:02 +0200, Mathias Behrle wrote:
> > * Betr.: " [tryton] Re: Import Initial Data" (Mon, 31 Aug 2009 09:54:16 +0200):
> >
> > Thx a lot!
> >
> > > > - How to work around the import problem via xml, if there exist default
> > > > properties for an underlying model?
> > >
> > > I tried your senario and it was working.
> > > The "Account Payable" is only required when the user has a company or the
> > > import from xml is done by the root user which must not have any company.
> >
> > Perhaps I am missing something in your explanation, I cannot get it
> > working so far, but I probably should have been a little more explicit.
> >
> > I am installing from the client (as admin user). As soon as I have configured
> > the wizards (for company and account), it is no more possible to install
> > party_bank_de.
> >
> > Did I miss something?
> >
>
> It is what I did.
>
>
Ok, I find it. I didn't set a default account payable nor receivable.
This [1] fix it.
[1] http://hg.tryton.org/modules/company/rev/ec83757de74a
Mathias Behrle
> On 31/08/09 23:50 +0200, Cédric Krier wrote:
> > On 31/08/09 22:02 +0200, Mathias Behrle wrote:
> > > * Betr.: " [tryton] Re: Import Initial Data" (Mon, 31 Aug 2009 09:54:16
> > > +0200):
> > >
> > > Thx a lot!
> > >
> > > > > - How to work around the import problem via xml, if there exist
> > > > > default properties for an underlying model?
> > > >
> > > > I tried your senario and it was working.
> > > > The "Account Payable" is only required when the user has a company or
> > > > the import from xml is done by the root user which must not have any
> > > > company.
> > >
> > > Perhaps I am missing something in your explanation, I cannot get it
> > > working so far, but I probably should have been a little more explicit.
> > >
> > > I am installing from the client (as admin user). As soon as I have
> > > configured the wizards (for company and account), it is no more possible
> > > to install party_bank_de.
> > >
> > > Did I miss something?
> > >
> >
> > It is what I did.
> >
> >
>
> Ok, I find it. I didn't set a default account payable nor receivable.
>
> This [1] fix it.
>
> [1] http://hg.tryton.org/modules/company/rev/ec83757de74a
>
Thanks, works.
Mathias Behrle
Hi Cédric,
import is now working, but there is an issue similar to
https://bugs.tryton.org/roundup/issue1025, when installing the final module with
the 8 GB xml file.
Installation with the client failed due to timeout.
From the command line with only party_bank preinstalled and doing
-i party_bank_de it took about 80 min. to finish.
With account and party_bank preinstalled the xml import took even 140 min.
I am just running an update (there was just a release of new bank codes) and it
is not yet finished after 120 min. trytond uses > 850 MB of RAM with 80% CPU
load.
I am suspecting another memory leak, could you please check?
You find the final module at
http://mercurial.intuxication.org/hg/party_bank_de
Cédric Krier
> * Betr.: " [tryton] Re: Import Initial Data" (Tue, 1 Sep 2009 11:29:10 +0200):
>
> Hi Cédric,
>
> import is now working, but there is an issue similar to
> https://bugs.tryton.org/roundup/issue1025, when installing the final module with
> the 8 GB xml file.
>
> Installation with the client failed due to timeout.
>
> From the command line with only party_bank preinstalled and doing
> -i party_bank_de it took about 80 min. to finish.
>
> With account and party_bank preinstalled the xml import took even 140 min.
Nothing strange.
> I am just running an update (there was just a release of new bank codes) and it
> is not yet finished after 120 min. trytond uses > 850 MB of RAM with 80% CPU
> load.
>
> I am suspecting another memory leak, could you please check?
>
There is no memory leak. On an update Tryton load every record linked to the
module to check if it changes. So your 8GB of xml record are loaded.
Cédric Krier
> On 02/09/09 23:30 +0200, Mathias Behrle wrote:
> > * Betr.: " [tryton] Re: Import Initial Data" (Tue, 1 Sep 2009 11:29:10 +0200):
> >
> > Hi Cédric,
> >
> > import is now working, but there is an issue similar to
> > https://bugs.tryton.org/roundup/issue1025, when installing the final module with
> > the 8 GB xml file.
> >
> > Installation with the client failed due to timeout.
> >
> > From the command line with only party_bank preinstalled and doing
> > -i party_bank_de it took about 80 min. to finish.
> >
> > With account and party_bank preinstalled the xml import took even 140 min.
>
> Nothing strange.
>
> > I am just running an update (there was just a release of new bank codes) and it
> > is not yet finished after 120 min. trytond uses > 850 MB of RAM with 80% CPU
> > load.
> >
> > I am suspecting another memory leak, could you please check?
> >
>
> There is no memory leak. On an update Tryton load every record linked to the
> module to check if it changes. So your 8GB of xml record are loaded.
>
Could you try with this patch? It clean the cache of BrowseRecord after
seeing it in xml. I think it is good to suppose that once we have seeing a
record we will not see any more for major case (like 99%).
Cédric Krier
> On 03/09/09 01:04 +0200, Cédric Krier wrote:
> > On 02/09/09 23:30 +0200, Mathias Behrle wrote:
> > > * Betr.: " [tryton] Re: Import Initial Data" (Tue, 1 Sep 2009 11:29:10 +0200):
> > >
> > > Hi Cédric,
> > >
> > > import is now working, but there is an issue similar to
> > > https://bugs.tryton.org/roundup/issue1025, when installing the final module with
> > > the 8 GB xml file.
> > >
> > > Installation with the client failed due to timeout.
> > >
> > > From the command line with only party_bank preinstalled and doing
> > > -i party_bank_de it took about 80 min. to finish.
> > >
> > > With account and party_bank preinstalled the xml import took even 140 min.
> >
> > Nothing strange.
> >
> > > I am just running an update (there was just a release of new bank codes) and it
> > > is not yet finished after 120 min. trytond uses > 850 MB of RAM with 80% CPU
> > > load.
> > >
> > > I am suspecting another memory leak, could you please check?
> > >
> >
> > There is no memory leak. On an update Tryton load every record linked to the
> > module to check if it changes. So your 8GB of xml record are loaded.
> >
>
> Could you try with this patch? It clean the cache of BrowseRecord after
> seeing it in xml. I think it is good to suppose that once we have seeing a
> record we will not see any more for major case (like 99%).
>
Could you try instead with this one?
Mathias Behrle
Results for patch convert_browserecord_cache.patch:
> > > From the command line with only party_bank preinstalled and doing
> > > -i party_bank_de it took about 80 min. to finish.
Now takes 15 min instead of 80 min..
> > > With account and party_bank preinstalled the xml import took even 140 min.
> >
> > Nothing strange.
Not yet tested.
> > > I am just running an update (there was just a release of new bank codes)
> > > and it is not yet finished after 120 min. trytond uses > 850 MB of RAM
> > > with 80% CPU load.
Wow. Also 15 min, instead of 120 - 140 min.
Mathias Behrle
Results for patch convert_cache.patch:
> > > From the command line with only party_bank preinstalled and doing
> > > -i party_bank_de it took about 80 min. to finish.
> > >
> > > With account and party_bank preinstalled the xml import took even 140 min.
> >
> > Nothing strange.
Nothing changed.
> > > I am just running an update (there was just a release of new bank codes)
> > > and it is not yet finished after 120 min. trytond uses > 850 MB of RAM
> > > with 80% CPU load.
Not tested.
Mathias Behrle
> On 04/09/09 02:54 +0200, Mathias Behrle wrote:
> > * Betr.: " [tryton] Re: Import Initial Data" (Thu, 3 Sep 2009 10:00:14
> > +0200):
> >
> > Results for patch convert_browserecord_cache.patch:
> >
> > > > > From the command line with only party_bank preinstalled and doing
> > > > > -i party_bank_de it took about 80 min. to finish.
> >
> > Now takes 15 min instead of 80 min..
> >
> > > > > With account and party_bank preinstalled the xml import took even 140
> > > > > min.
> > > >
> > > > Nothing strange.
> >
> > Not yet tested.
> >
> > > > > I am just running an update (there was just a release of new bank
> > > > > codes) and it is not yet finished after 120 min. trytond uses > 850
> > > > > MB of RAM with 80% CPU load.
> >
> > Wow. Also 15 min, instead of 120 - 140 min.
>
> I think it is good now.
> Did you check the memory usage?
JFTR: size of xml file is 8 MB, not 8 GB as erroneously reported before
Testing on tip = 1988:29f3eabb811 (-u party_bank_de):
Command being timed: "/usr/local/tryton/trytond/bin/trytond
--config /usr/local/tryton/trytond/etc/trytond.conf.own -u party_bank_de -d
test_pbd12"
User time (seconds): 289.83 System time (seconds): 5.29
Percent of CPU this job got: 51%
Elapsed (wall clock) time (h:mm:ss or m:ss): 9:38.19
mem usage: ~400 MB
with psyco enabled:
Command being timed: "/usr/local/tryton/trytond/bin/trytond
--config /usr/local/tryton/trytond/etc/trytond.conf.own -u party_bank_de -d
test_pbd12"
User time (seconds): 289.90 System time (seconds): 5.34
Percent of CPU this job got: 52%
Elapsed (wall clock) time (h:mm:ss or m:ss): 9:24.45