Excuse me,
After your valuable advice, i've modified my code and i've removed the
"manual" setting of the foreign key (which was completely wrong). But now
i've another problem, maybe due to another misunderstanding.
(I've moved all my ORM classes within the same module now).
I am parsing an xml where i can found something like:
<definition id=1>
..
<platform> Windows XP</platform>
..
</definition>
<definition id=2>
..
<platform> Linux</platform>
..
</definition>
<definition id=3>
..
<platform> Windows 7</platform>
<platform> Windows XP</platform>
<platform> Windows Vista</platform>
..
</definition>
When a single platform is associated to a definition (1:1), i expect the
following table "layout":
table_platform:
id | platform | definitionId_fk
----------------------------------------------------
1 Windows XP 1
2 Linux 2
When N platforms are associated to the same definition (N:1), i expect the
following table "layout":
id | platform | definitionId_fk
----------------------------------------------------
3 Windows 7 3
4 Windows XP 3
5 Windows Vista 3
5 Solaris 4
For the first case, everything works fine and i got exactly what i am
expecting but, for the second case i got:
id | platform | definitionId_fk
----------------------------------------------------
3 Windows 7 None
4 Windows XP None
5 Windows Vista 3
Maybe it's a stupid problem but i can't figure it out at the moment :/
Code:
...
for definitions in ovalXML._childrenMap['definitions']:
for definition in definitions.getchildren():
defInst = ORM_Classes.DefinitionClass(definition)
...
if subElem1.tag == mainNS + "platform":
platf = ORM_Classes.PlatformClass()
platf.setPlatform(str(subElem1))
defInst.PlatformRel = [platf]
session.add(defInst)
session.add(platf)
#i perform a commit every 1000 definitions as you suggested :)
DefinitionClass:
class DefinitionClass(Base):
__tablename__ = 'definitions'
defId = Column(Integer, primary_key=True)
...
version = Column(String)
PlatformRel = relation(PlatformClass, backref="definitions")
def __init__(self, node):
self.version = node.get("version")
...
PlatformClass:
class PlatformClass(Base):
__tablename__ = 'platform'
platformId = Column(Integer, primary_key=True)
platform = Column(String)
platformId_fk = Column('definitionId_fk', Integer,
ForeignKey('definitions.defId'))
def setPlatform(self, node):
self.platform = node
What can i do || correct to get the expected result?
Thanks for your patience.
---
Masetto
On Thu, Mar 25, 2010 at 4:56 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> the relationship between two tables requires both the ForeignKey to be
> present as well as the relationship() (relation() in 0.5) function to be
> present in the mapping.
>
>
> masetto wrote:
> > From 30 mins to 2mins... shame :P
> >
> > Thanks Micheal !
> >
> > Forgive me, what about the other question about foreign keys?
> >
> >
> > On Thu, Mar 25, 2010 at 3:43 PM, Michael Bayer
> > <mike...@zzzcomputing.com>wrote:
> >
> >> masetto wrote:
> >> > Hi all,
> >> >
> >> > i am writing a python script which parse an xml file (python lxml) and
> >> > import it into a sqlite db, and it works.
> >> > The xml file size is about 30Mb and the import operation takes about
> >> 15
> >> > minutes (do you think is too much? is there something i can do to
> >> speed
> >> up
> >> > the process?)
> >> >
> >> > This is a piece of the import function:
> >> >
> >> > ...
> >> > for definition in definitions.getchildren(): #iterate for every xml
> >> > children
> >> > node
> >> > defInst = SQLTableBuilder_Definition.DefinitionClass(definition)
> >> #read
> >> > and write on db some attribute of the node
> >> > ...
> >> > if subbaElem1.tag == mainNS + "platform": #another loop iterate
> >> for
> >> > every sub-node of the definition node
> >> > platf = SQLTableBuilder_Platform.PlatformClass()
> >> > platf.setPlatform(str(subbaElem1))
> >> > platf.platformId_fk = defInst.defId
> >> >
> >> > session.add(platf)
> >> > session.commit()
> >> > ...
> >> > session.add(defInst)
> >> > session.commit()
> >>
> >>
> >> don't commit on every node and on every sub-node. Just commit once
> >> every
> >> 1000 new objects or so. will save a ton of processing.
> >>
> >>
> >>
> >> >
> >> > where DefinitionClass contains the attributes declaration
> >> (primary_key,
> >> > column(string), etc.) and a Foreign Key.
> >> > There is a relation between the definition table and the platform
> >> table
> >> > (one
> >> > or more platforms - Operating System - can be associated to a single
> >> > definition) so,
> >> > in the platform table, i've added the following: platformId_fk =
> >> > Column('definitionId_fk', Integer, ForeignKey('definitions.defId'))
> >> >
> >> > All my ORM-Classes are declared within n different classes within n
> >> > different python modules so, i've included the needed imports
> >> everytime i
> >> > needed it.
> >> > And i suppose this is a problem, at least for me, sometime, because
> >> when
> >> i
> >> > try to add: PlatformRel =
> >> > relation(SQLTableBuilder_Definition.DefinitionClass,
> >> backref="platform")
> >> > within my platformClass, i got: 'list' object has no attribute
> >> > '_sa_instance_state' :/
> >> >
> >> > So, i've tried to "manually" set the foreign key, as you can see
> >> above.
> >> In
> >> > the documentation (http://www.sqlalchemy.org/docs/ormtutorial.html) i
> >> > read:
> >> > " SQLAlchemy is automatically aware of many-to-one/one-to-many based
> >> on
> >> > foreign keys." Does this mean that what i've done is correct or i'm a
> >> > little
> >> > confused? If i "manually" set a foreign key value, does sqlalchemy
> >> > understand that a relation between two tables exists?
> >> >
> >> > Thanks for your attention.
> >> > ---
> >> > Masetto
> >> >
> >> > --
> >> > You received this message because you are subscribed to the Google
> >> Groups
> >> > "sqlalchemy" group.
> >> > To post to this group, send email to sqlalch...@googlegroups.com.
> >> > To unsubscribe from this group, send email to
> >> > sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> <sqlalchemy%2bunsubscr...@googlegroups.com<sqlalchemy%252bunsubscr...@googlegroups.com>
> >
> >> .
> >> > For more options, visit this group at
> >> > http://groups.google.com/group/sqlalchemy?hl=en.
> >> >
> >> >
> >>
> >> --
> >> You received this message because you are subscribed to the Google
> >> Groups
> >> "sqlalchemy" group.
> >> To post to this group, send email to sqlalch...@googlegroups.com.
> >> To unsubscribe from this group, send email to
> >> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> <sqlalchemy%2bunsubscr...@googlegroups.com<sqlalchemy%252bunsubscr...@googlegroups.com>
> >
> >> .
> >> For more options, visit this group at
> >> http://groups.google.com/group/sqlalchemy?hl=en.
> >>
> >>
> >
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> .
> > For more options, visit this group at
> > http://groups.google.com/group/sqlalchemy?hl=en.
> >
> >
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
