[sqlalchemy] Re: Cascade Deletes
Stefano, Thank you again for your time. I am sorry for not posting my actual code - it is sensitive, therefore I am trying to reproduce the same issue with different code. In my __init__ function of my Rating I am setting 3 members that are objects like so: self.member = member These members are objects that have a relationship with Rating. When I remove these assignments, everything works fine. Does this ring any bells? On Aug 7, 1:15 pm, Stefano Fontanelli s.fontane...@asidev.com wrote: Il 07/08/11 20.08, Aviv Giladi ha scritto: I see. Where can I start looking for such an error? What could possibly be causing this? I'm sorry but I cannot help you without your code... not a similar code but THE code which produce that error. Regards, Stefano. -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web:www.asidev.com Skype: stefanofontanelli -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Cascade Deletes
I see. Where can I start looking for such an error? What could possibly be causing this? On Aug 7, 6:51 am, Stefano Fontanelli s.fontane...@asidev.com wrote: Il 07/08/11 00.35, Aviv Giladi ha scritto: Everything works great when I create and assign all 3 subratings to the rating object before I add it to the session. However, I need to be able to create a Rating that only has 1 or 2 subratings, and the other subratings absent. When I do that, SQLAlchemy tells me: InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'SELECT SubRating2.id AS subrating2_id \nFROM subratings2 \nWHERE subrating2.id = ?' (symbol 'NEVER_SET,) The above error is when I set Ratings's subrating1 and subrating3, but not subrating2. How do I avoid this error? I think the error is somewhere else in your application code. The model works as you can check in the attached test. Regards, Stefano. -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web:www.asidev.com Skype: stefanofontanelli test.py 7KViewDownload -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Cascade Deletes
Stefano,
Thanks! Your script helped me narrow down the problem.
My Rating object has multiple Subrating objects. So in my real code, I
have something like:
class SubRating1(Base):
__tablename__ = 'subratings1'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class SubRating2(Base):
__tablename__ = 'subratings2'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class SubRating3(Base):
__tablename__ = 'subratings3'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
And then my Rating looks like:
class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating1_id = Column(Integer, ForeignKey('subratings1.id'))
subrating1 = relationship(SubRating1, backref=backref(rating,
cascade=all, delete-orphan, uselist=False))
subrating2_id = Column(Integer, ForeignKey('subratings2.id'))
subrating2 = relationship(SubRating2, backref=backref(rating,
cascade=all, delete-orphan, uselist=False))
subrating3_id = Column(Integer, ForeignKey('subratings3.id'))
subrating3 = relationship(SubRating3, backref=backref(rating,
cascade=all, delete-orphan, uselist=False))
Everything works great when I create and assign all 3 subratings to
the rating object before I add it to the session.
However, I need to be able to create a Rating that only has 1 or 2
subratings, and the other subratings absent.
When I do that, SQLAlchemy tells me:
InterfaceError: (InterfaceError) Error binding parameter 0 - probably
unsupported type. u'SELECT SubRating2.id AS subrating2_id \nFROM
subratings2 \nWHERE subrating2.id = ?' (symbol 'NEVER_SET,)
The above error is when I set Ratings's subrating1 and subrating3, but
not subrating2.
How do I avoid this error?
On Aug 6, 6:16 am, Stefano Fontanelli s.fontane...@asidev.com wrote:
Il 06/08/11 00.32, Aviv Giladi ha scritto:
Hi Stefano,
I create and add a Rating and Subrating (both end up in the DB no
problem).
Then, I call session.delete(rating_obj) and commit it. I look at the
DB, and the Rating is gone, but the SubRating is still there.
The DB shows that the Rating has the correct Subrating's ID..
Hi Aviv,
I attached the code you sent me.
I move 'cascade' as I told you and everything works. See the log that I
pasted at the bottom of the script.
--
Ing. Stefano Fontanelli
Asidev S.r.l.
Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
E-mail: s.fontane...@asidev.com Web:www.asidev.com
Skype: stefanofontanelli
test.py
7KViewDownload
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] Re: Cascade Deletes
Hey Stefano, I tried that, but when I did, this is the error I got while inserting a new rating: InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'SELECT subratings.id AS subratings_id \nFROM subratings \nWHERE subratings.id = ?' (symbol 'NEVER_SET,) On Aug 5, 9:46 am, Stefano Fontanelli s.fontane...@asidev.com wrote: Il 04/08/11 21.27, Aviv Giladi ha scritto: Hey, Tried adding cascade to Rating's backref call like so: subrating = relationship(SubRating, backref=backref(rating, cascade=all, delete-orphan uselist=False)) This unfortunately doesn't work - when I delete a Rating, the according Subratings are NOT removed. What am I doing wrong? (Testing with SQLite) Are you sure about the position of 'cascade' keyword? I think the right way to do that could be: subrating = relationship(SubRating, cascade=all, delete-orphan, backref=backref(rating, uselist=False)) Regards, Stefano. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Cascade Deletes
Hi Stefano,
Thanks! The code is just like this:
subrating_subratingproperty_association =
Table('subrating_subratingproperty_association',
Base.metadata, Column('subrating_id', Integer,
ForeignKey('subratings.id')),
Column('subrating_property_id', Integer,
ForeignKey('subrating_properties.id')))
class SubRatingProperty(Base):
__tablename__ = 'subrating_properties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subratings = relationship(SubRating,
secondary=subrating_subratingproperty_association,
backref=subrating_properties)
class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating_id = Column(Integer, ForeignKey('subratings.id'))
subrating = relationship(SubRating, backref=backref(rating,
cascade=all, delete-orphan, uselist=False))
I create and add a Rating and Subrating (both end up in the DB no
problem).
Then, I call session.delete(rating_obj) and commit it. I look at the
DB, and the Rating is gone, but the SubRating is still there.
The DB shows that the Rating has the correct Subrating's ID..
On Aug 5, 11:45 am, Stefano Fontanelli s.fontane...@asidev.com
wrote:
Il 05/08/11 20.38, Aviv Giladi ha scritto:
Hey Stefano,
I tried that, but when I did, this is the error I got while inserting
a new rating:
InterfaceError: (InterfaceError) Error binding parameter 0 - probably
unsupported type. u'SELECT subratings.id AS subratings_id \nFROM
subratings \nWHERE subratings.id = ?' (symbol 'NEVER_SET,)
I need the whole code to help you :)
I think it is not related with cascade set.
Regards,
Stefano.
--
Ing. Stefano Fontanelli
Asidev S.r.l.
Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
E-mail: s.fontane...@asidev.com Web:www.asidev.com
Skype: stefanofontanelli
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] Re: Cascade Deletes
Hey, Tried adding cascade to Rating's backref call like so: subrating = relationship(SubRating, backref=backref(rating, cascade=all, delete-orphan uselist=False)) This unfortunately doesn't work - when I delete a Rating, the according Subratings are NOT removed. What am I doing wrong? (Testing with SQLite) On Jul 31, 9:01 am, Stefano Fontanelli s.fontane...@asidev.com wrote: Il 30/07/11 23.24, Aviv Giladi ha scritto: Sorry, but I am really confused. Are you guys saying that on SQLite for example, cascade deletes don't work at all? Or do they work, but are less efficient? ONUPDATE/ONDELETE cascade - SQL expression: on SQLite and MySQL MyISAM doesn't work at all. Michael told you that you can reach the same result using the SQLA's relationship option: cascade='all, delete-orphan' This approach is less efficient because delete actions are performed by mapper at the application layer instead of the database: relationship collection objects must be loaded into memory then deleted. Regards, Stefano. -- Ing. Stefano Fontanelli Asidev S.r.l. Via Osteria Bianca, 108/A 50053 Empoli (Firenze) Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978 E-mail: s.fontane...@asidev.com Web:www.asidev.com Skype: stefanofontanelli -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Cascade Deletes
Thank you for your response. In that case, how do you manage these kinds of situations in SQLite and other engines in MySQL? Do you manually delete the children as well? On Jul 28, 10:35 am, Stefano Fontanelli s.fontane...@asidev.com wrote: Il 28/07/11 01.15, Aviv Giladi ha scritto: Hi, I am actually using both MySQL and SQLite (one on the dev machine, one on the server). Does that make a difference? ONDELETE and ONUPDATE don't work on SQLite and MySQL MyISAM. You must change your database to test them. In MySQL you can create your database and tables as InnoDB. Regards, Stefano. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Cascade Deletes
Sorry, but I am really confused. Are you guys saying that on SQLite for example, cascade deletes don't work at all? Or do they work, but are less efficient? Thanks again! On Jul 30, 11:08 am, Michael Bayer mike...@zzzcomputing.com wrote: SQLAlchemy's cascade='all, delete-orphan' implements the same CASCADE functionality as ONDELETE does, in Python. It is just less efficient since collections need to be fully loaded into memory for them to be processed. On Jul 30, 2011, at 1:49 PM, Aviv Giladi wrote: Thank you for your response. In that case, how do you manage these kinds of situations in SQLite and other engines in MySQL? Do you manually delete the children as well? On Jul 28, 10:35 am, Stefano Fontanelli s.fontane...@asidev.com wrote: Il 28/07/11 01.15, Aviv Giladi ha scritto: Hi, I am actually using both MySQL and SQLite (one on the dev machine, one on the server). Does that make a difference? ONDELETE and ONUPDATE don't work on SQLite and MySQL MyISAM. You must change your database to test them. In MySQL you can create your database and tables as InnoDB. Regards, Stefano. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://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 sqlalchemy@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] Re: Cascade Deletes
Hi,
I am actually using both MySQL and SQLite (one on the dev machine, one
on the server).
Does that make a difference?
On Jul 27, 12:26 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 25, 2011, at 9:47 AM, Aviv Giladi wrote:
I can't seem to make cascade deletes work in sqlalchemy.
I have a parent class (called Rating), a sub class (Subrating) and a
third class called SubRatingProperty.
There is a one-to-one relationship between Rating and SubRating - each
Rating can only have one specific SubRating object. Next, the
SubRatingProperty refers to a row in a table with fixed values. There
are 3 SubRatingProperty entries - property1, property2 and property3.
The SubRating class can have one or more of either property1,
property2 and property3, therefore the relationship is many-to-many (a
SubRatingProperty can have more than one properties, and for example
property1 can be assigned to more than one SubRatingProperty's).
Here is the code that defines all of this:
subrating_subratingproperty_association =
Table('subrating_subratingproperty_association', Base.metadata,
Column('subrating_id', Integer,
ForeignKey('subratings.id')),
Column('subrating_property_id',
Integer, ForeignKey('subrating_properties.id')))
class SubRatingProperty(Base):
__tablename__ = 'subrating_properties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subratings = relationship(SubRating,
secondary=subrating_subratingproperty_association,
backref=subrating_properties)
class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating_id = Column(Integer, ForeignKey('subratings.id'))
subrating = relationship(SubRating, backref=backref(rating,
uselist=False))
Everything works fine, but I can't figure out how to do cascade
deletes. I am deleting Rating objects, and when I do, I would like the
according SubRating object to be deleted, as well as all the entries
in the association table. So deleting Rating1 would delete its
SubRating, as well as all the connection between the SubRating and
SubRatingProperty's.
I have tried adding cascade=all to the relationship call,
you have two relationships() here to build the full chain so you'd need
cascade='all, delete-orphan' on both Rating.subrating as well as
SubRating.subrating_properties (use the backref() function instead of a
string to establish the cascade rule on that end.
and I also
tried adding ondelete=cascade) to the ForeignKey call.
if all the involved FOREIGN KEYs are generated with ON DELETE CASCADE as this
would accomplish, as long as you are not on SQLIte or MySQL MyISAM the
deletes will be unconditional.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] Cascade Deletes
I can't seem to make cascade deletes work in sqlalchemy.
I have a parent class (called Rating), a sub class (Subrating) and a
third class called SubRatingProperty.
There is a one-to-one relationship between Rating and SubRating - each
Rating can only have one specific SubRating object. Next, the
SubRatingProperty refers to a row in a table with fixed values. There
are 3 SubRatingProperty entries - property1, property2 and property3.
The SubRating class can have one or more of either property1,
property2 and property3, therefore the relationship is many-to-many (a
SubRatingProperty can have more than one properties, and for example
property1 can be assigned to more than one SubRatingProperty's).
Here is the code that defines all of this:
subrating_subratingproperty_association =
Table('subrating_subratingproperty_association', Base.metadata,
Column('subrating_id', Integer,
ForeignKey('subratings.id')),
Column('subrating_property_id',
Integer, ForeignKey('subrating_properties.id')))
class SubRatingProperty(Base):
__tablename__ = 'subrating_properties'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subratings = relationship(SubRating,
secondary=subrating_subratingproperty_association,
backref=subrating_properties)
class SubRating(Base):
__tablename__ = 'subratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
class Rating(Base):
__tablename__ = 'ratings'
id = Column(Integer, primary_key=True)
name = Column(Unicode(32), unique=True)
subrating_id = Column(Integer, ForeignKey('subratings.id'))
subrating = relationship(SubRating, backref=backref(rating,
uselist=False))
Everything works fine, but I can't figure out how to do cascade
deletes. I am deleting Rating objects, and when I do, I would like the
according SubRating object to be deleted, as well as all the entries
in the association table. So deleting Rating1 would delete its
SubRating, as well as all the connection between the SubRating and
SubRatingProperty's.
I have tried adding cascade=all to the relationship call, and I also
tried adding ondelete=cascade) to the ForeignKey call. Nothing
seemed to have worked.
How do I set up this cascade deletes business?
Thanks!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] Best design for commits?
Hey guys, I have a Pylons back-end running on SQLAlchemy. I have a script that reads a tree of XML files from an HTTP server (it downloads an xml X, and then downloads that X's children, and then iterates the children, and so forth in recursion). Each xml file represents an SQLAlchemy model. The problem is that I have thousands of these xml's (sometimes 5000, sometimes 26000). I was able to optimize the download process with HTTP pooling, but I cannot seem to think of the best approach as to committing the models to the DB. Every time an xml file is downloaded, I create an orm object for it and add it to my session. Problem 1: some xml's will exists multiple times in the tree so I am checking that there is no duplicate insertion. Is the check in my code optimal or should I keep an indexed collection on the side and use it to check for duplicates? Problem 2: my autocommit is set to False because I don't want to commit on every add (not because its bad design, but because of performance). But I also don't want to iterate the entire tree of thousands of categories without committing at all. Therefor, I created a constant number upon which my code commits the data. Is this a good approach? What would be a good number for that? It might be important to mention that I do not know in advance how many xml's I am looking at. Here is what my pseudo-code looks like now (ignore syntax errors): count = 0 COMMIT_EVERY = 50 def recursion(parent): global count, COMMIT_EVERY pool = get_http_connection_pool(...) sub_xmls = get_sub_xmls(pool, parent) if sub_xmls == None: return for sub_xml in sub_xmls: orm_obj = MyObj(sub_xml) duplicate = Session.query(MyObj).filter(MyObj.id == orm_obj.id).first() if not duplicate: Session.add(orm_obj) count = count + 1 if count % COMMIT_EVERY == 0: Session.commit() recursion(orm_obj.id) recursion(0) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Best design for commits?
Dear Rick, Thank you for your reply. I understand, but is there not a better way than doing a lot of single commits in case of a commit exception? In other words, is there a way to tell SQLAlchemy to throw an exception on the Session.add if there's a duplicate as opposed to on the Session.commit? Or else, is it not better to keep an indexed collection on the side to check that an id was inserted before? Thanks! On Apr 20, 12:05 pm, Richard Harding rhard...@mitechie.com wrote: What I tend to do in cases like this is to break things into commit chunks. For instance, I've got an import script that goes through and processes 10 at a time and performs a commit every 10. This is tweakable via a config setting, but so far 10 works for my needs. As for the duplicates, If they're exact and you don't need to processes changes between one record and another of the same pk, just try: except and catch the pk error on the second insert. With my import script, if I get an exception, I then run through all 10 committing one at a time and finding the 'bad egg' so that I can log out that this one failed because of the exception. Sure, for that batch of 10 I'm doing a bunch of single commits, but more often than not I'm running in my batch mode. I'd just the db/exceptions tell me a record exists vs trying to query the server for each one to check first. Good ole case of 'better to ask for forgiveness than permission'. Rick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Best design for commits?
I agree, but the thing is that committing every 10 entries is a little low for me, I was thinking of around 50, at which case having 50 individual commits is quite costly.. In case I choose the implement your method, how would you go about it? How do you keep objects of the last 50 or whatever records from the last commits that have not been committed yet (taking into account my recursion)? Thanks again! On Apr 20, 12:17 pm, Richard Harding rhard...@mitechie.com wrote: Not that I'm aware of. When you do a Session.add() it's not touching the database yet. It's part of the performance tradeoff. There's not a good way for it to *know* there's a record in the db with that pk id until it does chat with the db on it. Sure, you can keep a list of ids on the side if you want, but it just seems that you're going to have a try: except block there anyway in case of other issues, db connection fails, bad values, etc, that you might as well just catch the exception for a row already existing as well. Rick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Best design for commits?
Thanks again Rick. The issue is that I have a LOT of duplicates (around 20-30%) - that's just how that tree is structured. Therefore, I think I am going to go with catching DB exceptions regardless, but also use an indexed collection to prevent duplicates. Cheers! On Apr 20, 12:43 pm, Richard Harding rhard...@mitechie.com wrote: I'm not sure, but I'd check the exception and see if you can get the info about which of your 50 were the dupe. I don't recall if it's in the traceback or exception error. If you can identify it then you could store it aside and remove it from the session and retry the other 49 again. Otherwise, it's the case of finding the mole. Maybe run some sort of binary split of the 50 so that you split the list in half, try to commit each half, one works, one fails. Split the fail side again, etc. In this way you should really only get down to what, 7 commits per 50? This is all assuming one dupe/bad record in the group of 50. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Best way to insert different string to Unicode columns?
Hey guys, I have a SQLAlchemy model with a Unicode column. I sometimes insert unicode values to it (u'Value'), but also sometimes insert ASCII strings. What is the best way to go about this? When I insert ASCII strings with special characters I get this warning: SAWarning: Unicode type received non-unicode bind param value ... How do I avoid this? What is the proper way to insert my different types of strings? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Best design for commits?
Thank you for your responses everyone. I have one more question - the really time heavy task here is retrieving the URLs over HTTP (it takes almost a second per URL). I am using urllib3 that has connection pooling, but other than that, is there any other way to speed this up? Perhaps multi-threading? On Apr 20, 3:26 pm, Michael Bayer mike...@zzzcomputing.com wrote: my practices with this kind of situation are: 1. theres just one commit() at the end. I'd like the whole operation in one transaction 2. There are flush() calls every 100-1000 or so. 10 is very low. 3. I frequently will disable autoflush, if there are many flushes occurring due to queries for related data as the bulk proceeds. 4. I dont use try/except to find duplicates - this invalidates the transaction (SQLAlchemy does this but many DBs force it anyway). I use a SELECT to get things ahead of time, preferably loading the entire database worth of keys into a set, or loading the keys that I know we're dealing with, so that individual per-key SELECTs are not needed. Or if the set of data I'm working with is the whole thing at once, I store the keys in a set as I get them, then I know which one's I've got as I go along. 5. if i really need to do try/except, use savepoints, i.e. begin_nested(). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Re: Error with Adjacency List Relationship Implementation
Figured it out, I was being retarded. Thanks!
On Apr 19, 7:33 pm, Aviv Giladi avivgil...@gmail.com wrote:
Hey guys,
I am playing around with SQLAlchemy (over Pylons) and encountering a
strange problem. I am using the adjacency list relationship concept to
represent nodes and their parents in a single table. I copied exactly
this code from the examples:
class Node(Base):
__tablename__ = 'nodes'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('nodes.id'))
data = Column(Unicode(50))
children = relationship('Node',
cascade=all,
backref=backref(parent, remote_side='Node.id'),
# tried with and without this:
collection_class=attribute_mapped_collection('data'),
)
My test is basically adding two nodes, like so:
n = Node()
n.id = 1
n.parent_id = 0
n.data = 'parent'
n2 = Node()
n2.id = 2
n2.parent_id = 1
n2.data = 'child'
Session.add(n)
Session.add(n2)
Session.commit()
When I run my test, I get the following exception raised from
connections.py:
sqlalchemy.exc.IntegrityError: (IntegrityError) (1452, 'Cannot add or
update a child row: a foreign key constraint fails (`db`.`nodes`,
CONSTRAINT `nodes_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `nodes`
(`id`))') 'INSERT INTO nodes (id, parent_id, data) VALUES (%s, %s,
%s)' (1, 0, 'parent')
Please, what am I missing here?
Thanks!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] Self-referencing Table Cannot Have 0 as Primary Index?
Hey guys,
I encountered a very strange problem with SQLAlchemy. I have a model
that is self-referencing (adjacency list relationship). I simply
copied the model (Node) from the SQLAlchemy tutorial. Here is the
model's code:
class Node(Base):
__tablename__ = 'nodes'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('nodes.id'))
parent = Column(Unicode(50))
children = relationship('Node',
cascade=all, #tried to remove this
backref=backref(parent, remote_side='Node.id'),
collection_class=attribute_mapped_collection('data'),
#tried to remove this as well
)
I reproduced the problem within my controllers, but I also ran this
test (after fully loading my environment of course):
parent = Node()
parent.id = 1
parent.parent_id = None
parent.name = 'parent'
Session.add(parent)
child = Node()
child.id = 20
child.parent_id = 1
child.name = 'child'
Session.add(child)
Session.commit()
The above code works just fine (the changes are successfully committed
and reflected in the DB).
The problem arises when I change the `parent` node's id to 0 (and the
`child`'s parent_id to 0 accordingly). Then, I get the following
exception:
..
File C:\Python26\Lib\site-packages\MySQLdb\connections.py, line
36, in defaulterrorhandler
raise errorclass, errorvalue
sqlalchemy.exc.IntegrityError: (IntegrityError) (1452, 'Cannot add
or update a child row: a
foreign key constraint fails (`db`.`nodes`, CONSTRAINT
`nodes_ibfk_1` FOREIGN KEY
(`parent_id`) REFERENCES `nodes` (`id`))') 'INSERT INTO nodes (id,
parent_id, name) VALUES
(%s, %s, %s)' (20, 0, 'child')
Surprisingly, changing this value (the `node`'s id and the `child`'s
parent_id) to _anything_ but 0 (-5, 1 and 150) makes the error go
away.
Am I missing something obvious? Is it not possible to assign 0 to a
self-referencing Integer id column?
Thanks!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
