Re: [sqlalchemy] Clarification about performance and relation()
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 XP1
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 Vista3
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 Vista3
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.comwrote:
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.comwrote:
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
Re: [sqlalchemy] Clarification about performance and relation()
First of all, thanks for your answer :)
# defInst.PlatformRel = [platf]# change this to
platf.definitions = defInst
I don't have any definitions attribute within the PlatformClass, i suppose
you mean the foreign key, isnt'it?
That is
platf.platformId_fk = defInst
However, this results in another error:
sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 1 -
probably unsupported type. u'INSERT INTO platform (platform,
definitionId_fk) VALUES (?, ?)' ['Microsoft Windows 2000',
ORM_Classes.DefinitionClass object at 0x8f5278c]
I've played a little with it, then i've moved the relation() from
DefinitionClass to PlatformClass:
class PlatformClass(Base):
__tablename__ = 'platform'
platformId = Column(Integer, primary_key=True)
platform = Column(String)
platformId_fk = Column('definitionId_fk', Integer,
ForeignKey('definitions.defId'))
PlatformRel = relation(DefinitionClass, backref=platform)
and then:
platf.PlatformRel = defInst
Now i got the expected data! It WORKS :P Thanks Werner!
But, i need to understand.. why now it's working?
From the doc:
We are also free... to define the
relationship()http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm.relationshiponly
on one class and not the other. It is also possible to define two
separate
relationship()http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm.relationshipconstructs
for either direction, which is generally safe for many-to-one and
one-to-many relationships, but not for many-to-many relationships.
Maybe i don't have well understood the role of the relation()/relationship()
function but, shouldn't be the same thing to define the relation() within
the DefinitionClass? I've only changed the location of the relation() and
now it works.
Can you kindly better explain me the role of the relationship() function?
Mmm... please correct me if i'm wrong:
- The relationship between the User and Address classes is defined
separately using the
relationship()http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm.relationshipfunction
OK, and is the only way to define a relation between two tables.
- If i put relationship() in both classes i got a *bidirectional*relationship
- Because of the *placement* of the foreign key, from Address to User it is
*many to one*... !!! Oh, is this the point, right? If, in the same class,
i define a foreign key AND a relationship() i create a many to one relation
with the linked table
- ..., and from User to Address it is *one to many* - This is valid only
in the bidirectional case or it's automatic when i declare somewhere
foreign key + relationship() ?
- Initially i've defined the foreign key in the PlatformClass and the
relation() in the DefinitionClass. Which type of relation i've created in
that way?
Thanks again!
On Fri, Mar 26, 2010 at 4:50 PM, werner wbru...@free.fr wrote:
Hi Masetto,
On 26/03/2010 16:01, masetto wrote:
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)
session.add(defInst) # I think this line should be here,
you have it further down
...
if subElem1.tag == mainNS + platform:
platf = ORM_Classes.PlatformClass()
platf.setPlatform(str(subElem1))
#defInst.PlatformRel = [platf]# change this to
platf.definitions = defInst
Werner
--
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.comsqlalchemy%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.
[sqlalchemy] Clarification about performance and relation()
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()
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.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Clarification about performance and relation()
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.comwrote:
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.comsqlalchemy%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.comsqlalchemy%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.
[sqlalchemy] Problem with Foreign Key
Hi all,
i am new to SQLAlchemy (simply wonderful!), and i'm writing some
python scripts to do some experiment.
I've written a SINGLE python module with two classes which define two
different tables (declarative_base) with a simple relationship and a
single Foreign Key and everything WORKS fine as expected. Cool! :P
Then, i've moved that two classes in two different python modules to
better organize my code but now i got the following error:
sqlalchemy.exc.NoReferencedTableError: Could not find table 'metadata'
with which to generate a foreign key
I understood the error message but i can't found a way to resolve this
issue, can you help me?
This is my directory layout:
test.py - the main python module
SQLTableBuilder_Definition.py - class that define a table called
Definition
SQLTableBuilder_Metadata.py - class that define a table called
Metadata
test.py:
...
engine = create_engine('sqlite:///test.db3', echo=True,
encoding='utf-8' )
import SQLTableBuilder_Metadata
metadata_table = SQLTableBuilder_Metadata.MetadataClass.__table__
metadata = SQLTableBuilder_Metadata.Base.metadata
metadata.create_all(engine)
import SQLTableBuilder_Definition
definitions_table =
SQLTableBuilder_Definition.DefinitionClass.__table__
metadata = SQLTableBuilder_Definition.Base.metadata
metadata.create_all(engine) == My script explode here with the
following:
Traceback (most recent call last):
File test.py, line 82, in module
metadata.create_all(engine)
...
sqlalchemy.exc.NoReferencedTableError: Could not find table
'metadata' with which to generate a foreign key
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()
...
session.add(myObj)
...
SQLTableBuilder_Definition.py:
Base = declarative_base()
class DefinitionClass(Base):
__tablename__ = 'definitions'
defId = Column(Integer, primary_key=True)
Id = Column(String)
classType = Column(String)
version = Column(String)
metadataId = Column('metadataId', Integer,
ForeignKey('metadata.metadataId'))
def __init__(self, node):
self.Id = node.get(id)[len(IdName):]
self.version = node.get(version)
self.classType = node.get(class)
SQLTableBuilder_Metadata.py:
import SQLTableBuilder_Definition
Base = declarative_base()
class MetadataClass(Base):
__tablename__ = 'metadata'
metadataId = Column(Integer, primary_key=True)
title = Column(String)
defRef = relation(SQLTableBuilder_Definition.DefinitionClass,
backref=metadata)
def __init__(self, node):
self.title = node
If i remove relation and foreign key from the two classes, everything
works fine again.
I suppose that python can't find the metadata table (previously
created without error) from SQLTableBuilder_Definition.py, but how i
can point him in the right direction?
Thanks for your attention.
ps.
print sqlalchemy.__version__
0.6beta1
--
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.
Re: [sqlalchemy] Problem with Foreign Key
Damn, your're right! Mea culpa :P
Thanks! Now it's working again
On Thu, Mar 18, 2010 at 6:27 PM, Michael Bayer mike...@zzzcomputing.comwrote:
masetto wrote:
Hi all,
i am new to SQLAlchemy (simply wonderful!), and i'm writing some
python scripts to do some experiment.
I've written a SINGLE python module with two classes which define two
different tables (declarative_base) with a simple relationship and a
single Foreign Key and everything WORKS fine as expected. Cool! :P
Then, i've moved that two classes in two different python modules to
better organize my code but now i got the following error:
sqlalchemy.exc.NoReferencedTableError: Could not find table 'metadata'
with which to generate a foreign key
I understood the error message but i can't found a way to resolve this
issue, can you help me?
This is my directory layout:'
you need to share one declarative Base class for all of the classes that
are related to each other, or alternatively at least a single MetaData
for all tables that wish to reference foreign keys using strings.
Anytime you specify options using strings to find something else, the
relevant base has to be shared, i.e. declarative base if using strings
in relation(), and MetaData if using strings in ForeignKey().
test.py - the main python module
SQLTableBuilder_Definition.py - class that define a table called
Definition
SQLTableBuilder_Metadata.py - class that define a table called
Metadata
test.py:
...
engine = create_engine('sqlite:///test.db3', echo=True,
encoding='utf-8' )
import SQLTableBuilder_Metadata
metadata_table = SQLTableBuilder_Metadata.MetadataClass.__table__
metadata = SQLTableBuilder_Metadata.Base.metadata
metadata.create_all(engine)
import SQLTableBuilder_Definition
definitions_table =
SQLTableBuilder_Definition.DefinitionClass.__table__
metadata = SQLTableBuilder_Definition.Base.metadata
metadata.create_all(engine) == My script explode here with the
following:
Traceback (most recent call last):
File test.py, line 82, in module
metadata.create_all(engine)
...
sqlalchemy.exc.NoReferencedTableError: Could not find table
'metadata' with which to generate a foreign key
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()
...
session.add(myObj)
...
SQLTableBuilder_Definition.py:
Base = declarative_base()
class DefinitionClass(Base):
__tablename__ = 'definitions'
defId = Column(Integer, primary_key=True)
Id = Column(String)
classType = Column(String)
version = Column(String)
metadataId = Column('metadataId', Integer,
ForeignKey('metadata.metadataId'))
def __init__(self, node):
self.Id = node.get(id)[len(IdName):]
self.version = node.get(version)
self.classType = node.get(class)
SQLTableBuilder_Metadata.py:
import SQLTableBuilder_Definition
Base = declarative_base()
class MetadataClass(Base):
__tablename__ = 'metadata'
metadataId = Column(Integer, primary_key=True)
title = Column(String)
defRef = relation(SQLTableBuilder_Definition.DefinitionClass,
backref=metadata)
def __init__(self, node):
self.title = node
If i remove relation and foreign key from the two classes, everything
works fine again.
I suppose that python can't find the metadata table (previously
created without error) from SQLTableBuilder_Definition.py, but how i
can point him in the right direction?
Thanks for your attention.
ps.
print sqlalchemy.__version__
0.6beta1
--
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.comsqlalchemy%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.comsqlalchemy%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.
