[sqlalchemy] Secialists question: how to do implement stock-management
Hi, i have a question to database/design specialist. How can (should!) i implement a stock management system in sql-alchemy and python? I get the following data from another system via files - movements: bills from a scanner at a cash deck - movements: from goods-receipt Of course i have also master data on an per article basis. What i want do is keep charge of the stock available in our warehouse. Should i built a warehouse table with [ article / amount of article available ] and update the articles with an update statement like UPDATE warehouse_table SET amount = amount - (bill amount) where article = bill_article ? Would that be a good solution? Is there any literature or reference implementations around? Any hint is much apreciated. Although i have written a couple of database applications, i never had the probleme to change a field (amount field per article) so often. I guess there is a lot of data comming in ... Thanks in advance, Dan -- 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] Secialists question: how to do implement stock-management
On Oct 29, 2010, at 6:12 AM, Dan @ Austria wrote: Hi, i have a question to database/design specialist. How can (should!) i implement a stock management system in sql-alchemy and python? I get the following data from another system via files - movements: bills from a scanner at a cash deck - movements: from goods-receipt Of course i have also master data on an per article basis. What i want do is keep charge of the stock available in our warehouse. Should i built a warehouse table with [ article / amount of article available ] and update the articles with an update statement like UPDATE warehouse_table SET amount = amount - (bill amount) where article = bill_article ? Would that be a good solution? Is there any literature or reference implementations around? Any hint is much apreciated. Although i have written a couple of database applications, i never had the probleme to change a field (amount field per article) so often. I guess there is a lot of data comming in ... I have a book that I have that gives data models, including those for inventory management is The Data Model Resource Book, Revised Edition, Volume 1 by Len Silverston. The models presented are quite complex though and intended to be refined by the designer by removing complexity that isn't needed for a given application. One beef I have with the book is that even though the book is priced higher than many computer books (about $60 US) at Amazon and comes with a CD-ROM, the schema for the data which is printed in the book is only available from the CD once you purchase a license (about $200, if I recall) to unlock it. The Revised Edition is copyright 2001. More information is at http://silverston.wiley.com/ While you can use SQL statements directly with SQLAlchemy, you should consider using SA methods. For an example of what you suggest with your SQL look for the Correlated Updates section in the SQL Expression Language Tutorial in the SA docs. Mark -- 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] Create_all() - Create_JustThese(engine, [Table1,Table2,....])?
Simon (and the others), thank you! the someClass.__table__works... Kind regards, Martijn On Oct 27, 2010, at 5:02 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 27 October 2010 15:47 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Create_all() - Create_JustThese(engine, [Table1,Table2,])? Mike, I have checked the docs but somehow overlooked it multiple times. I now looked at the index and found it as an MetaData method and found it. Using it gives me all sorts of errors now, I need to get some reference to the Table object hidden in the Internals somewhere when using declarative. I am an extremely experienced (Technical low level) programmer but I have always been able to let others do the database stuff (Since I have allways hated it sooo much, Personally I think Databases are pure torture), now that I am in the need of databases I am suddenly facing my inexperience with the technical language. The SQLAlchemy docs look like chinese to me and often I just do not know how you DBA's call things and therefore searching the documentation is a problem if you do not know what to search for. Again I have searched the Docs, can anyone tell me how to get a reference to a Table object when it is defined the declarative way? Martijn The Synopsis for the declarative extension says the following: -- The resulting table and mapper are accessible via __table__ and __mapper__ attributes on the SomeClass class: # access the mapped Table SomeClass.__table__ # access the Mapper SomeClass.__mapper__ -- http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#synopsis Hope that helps, Simon -- 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. -- 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] Working with mapper objects without saving them
I did this in one of my previous projects. what I did was to make a python module, with the right SQLAlchemy includes and all the mapper objects. what I did not do is connecting to the database (it makes the class module database independent) What it does is it implements the iCal standard, it can read iCal files and export iCal files. in one occasion it is being used by a CalDav server (completely written in python) and of course it needs to be interacting with the database. in the other occasion it is being used as a man-in-the-middle between an CRM application (with its own calendar) and Microsoft Exchange, in that case the class module is being used as a conversion utility and the data is actually never ever put into a database. It has both relationships and backrefs and works fine in both occasions. Let me know if you have any trouble, I use SQLalchemy a lot for handling data without saving it to a database. SQLAlchemy works fine when no database connection is present Martijn On Oct 27, 2010, at 5:41 PM, Michael Bayer wrote: On Oct 27, 2010, at 11:31 AM, Michael Elsdörfer wrote: I have a mapper-based data model that uses relationships() extensively. In one particular instance, to implement a sort of preview feature, I'd like to work with a set of instances of my model class without saving them to the database, i.e. without adding them to the session. So I'm not calling session.add() for the objects I newly create, but if such a preview object as a relationship with another, existing object (i.e. with a session state of Persistent), then when the new and the existing objects are connected through that relationship, the latter is marked as dirty, causing both to be saved. Solutions that I've come up with so far: * Manually call expunge() on the preview objects * Set the proper cascade-settings for the relationships. Both I'm not entirely fond of; in particular, the latter prevents me from using the cascades I really want in all other cases. Is there a better way to deal with this? there's a new flag on relationship() called cascade_backrefs. It prevents save-update cascade from occurring for backrefs - in other words, the save-update cascade moves only left to right. So if you were to say my_transient_object.some_related = some_related, if some_related were in the Session, it would not cascade my_transient_object in. We are possibly going to make this the default in 0.7. An example of the flag is at http://www.sqlalchemy.org/docs/orm/session.html#cascades . -- 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. -- 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. -- 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] Relationship between a class with two different instances of other class (Newbie)
Hello list... I wrote a couple of days ago about how to model an structure of three classes (http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#). I thing I almost have it, but I am still getting problems mapping an structure like this. class Child(rdb.Model): def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): def __init__(self): self.child1 = Child() self.child2 = Child() The “Parent” class has two different instances of a Child() class. I am not even sure about how to treat this (two different 1:1 relationships or a 1:2 relationship). My last try is this mapping: (don't let the rdb.Model thing fool you, is just something that automatically maps the class to the table specified in rdb.tablename). class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, primary_key=True) field1 = Column(“field1”, String(64)) #Irrelevant def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parent_table”) id = Column(id, Integer, primary_key=True) child1_id = Column(child_1_id, Integer, ForeignKey(children_table.id)) child2_id = Column(child_2_id, Integer, ForeignKey(children_table.id)) child1 = relationship(Child, primaryjoin = (child1_id==children_table.id) ) child2 = relationship(Child, primaryjoin = (child2_id==children_table.id) ) I have tried (almost) everything. I say “almost” because obviously I haven't tried the right thing. I keep getting errors that sqlalchemy can't determine the relationship between the two tables. It looks very similar to: http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child but I also understand that maybe I can't get this working that way, because I am actually putting two ForeignKeys from the same table (“children_table”) in the parent table. I am not sure how that will work out, or is correct or what... :-( Thank you! -- 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] LONGVARCHAR
Hi, firefox uses sqlite to store bookmark info. The file is called places.sqlite and the schema has type LONGVARCHAR for some fields and LONG for another. Autoloading that with sqlalchemy maps that columns to NullType rather that to a String / Integer one. Same for LONG type I don't really know if LONGVARCHAR/LONG are correct types for sqlite but they are accepted and used: is it possible to make sqlalchemy autoload correctly? Or should we ask firefox guys to use different types? sandro *:-) -- 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] Relationship between a class with two different instances of other class (Newbie)
On 10/29/2010 09:43 AM, Hector Blanco wrote: Hello list... I wrote a couple of days ago about how to model an structure of three classes (http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#). I thing I almost have it, but I am still getting problems mapping an structure like this. class Child(rdb.Model): def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): def __init__(self): self.child1 = Child() self.child2 = Child() The “Parent” class has two different instances of a Child() class. I am not even sure about how to treat this (two different 1:1 relationships or a 1:2 relationship). My last try is this mapping: (don't let the rdb.Model thing fool you, is just something that automatically maps the class to the table specified in rdb.tablename). class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, primary_key=True) field1 = Column(“field1”, String(64)) #Irrelevant def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parent_table”) id = Column(id, Integer, primary_key=True) child1_id = Column(child_1_id, Integer, ForeignKey(children_table.id)) child2_id = Column(child_2_id, Integer, ForeignKey(children_table.id)) child1 = relationship(Child, primaryjoin = (child1_id==children_table.id) ) child2 = relationship(Child, primaryjoin = (child2_id==children_table.id) ) I have tried (almost) everything. I say “almost” because obviously I haven't tried the right thing. I keep getting errors that sqlalchemy can't determine the relationship between the two tables. It looks very similar to: http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child but I also understand that maybe I can't get this working that way, because I am actually putting two ForeignKeys from the same table (“children_table”) in the parent table. I am not sure how that will work out, or is correct or what... :-( Thank you! Usually we consider the table with the foreign key as the child table, but that's just being picky. The problem is that SQLAlchemy is treating children_table.id as a literal instead of a clause, so your join would be like (parent JOIN child ON parent.child1_id = 'children_table.id'). Obviously that is not what you want. There are several ways to formulate primaryjoin/secondaryjoin. Pass the whole thing in as a string: child1 = relationship(Child, primaryjoin=Parent.child1_id == Child.id) Use the column objects directly (this requires that Child be defined before Parent): child1 = relationship(Child, primaryjoin=child1_id==Child.id) Use a callable (my favorite): child1 = relationship(Child, primaryjoin=lambda: Parent.child1_id == Child.id) -Conor -- 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] LONGVARCHAR
Am 29.10.2010 17:08, schrieb sandro dentella: Hi, firefox uses sqlite to store bookmark info. The file is called places.sqlite and the schema has type LONGVARCHAR for some fields and LONG for another. Autoloading that with sqlalchemy maps that columns to NullType rather that to a String / Integer one. Same for LONG type I don't really know if LONGVARCHAR/LONG are correct types for sqlite but they are accepted and used: is it possible to make sqlalchemy autoload correctly? Or should we ask firefox guys to use different types? LONGVARCHAR/LONG are not really supported types for SQLite for sure and will be handled via affinity rules. According to the affinity rules used by SQLite the LONGVARCHAR columns should have affinity TEXT, so maybe SQLalchemy or the SQLite wrapper in use could have done something about it, LONG should have affinity NUMERIC. I would doubt this is an SQLAlchemy problem, sounds more like a problem with the underlying python dbapi interface that is too limited or not properly configured to return the right types or affinities. dbapi specifies that the cursor.description() method MUST return a type_code, but last time i looked sqlite3 from the python stdlib ignores the part about type_code being required to compare with the available DBAPI type_codes. If Firefox used normal SQLite type names like TEXT/BLOB and INTEGER for those fields it would just work with all SQLite tools, even if they get affinities wrong. So one should at least question the design decision. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 -- 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] SQLite / Decimal
How do people handle Decimal data with SA / SQLite? Newer versions of SA give the following warning: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage. I assume that the gist of the message is similar to the reason that decimal.Decimal objects in Python can't be initialized with a float, just int's and string's. I'm working on an accounting application where I would like to use Decimal fields to store monetary amounts. If I understand the warning, I should convert the Python Decimal data and from ints to store in the database. I think I can safely get away with ints if I design things so that every data is stored with the same number of decimal digits. I could also use strings in the database, but that would eliminate the ability to do math operations, such as SUM in the SQL code. Also, is there an single place in the code to to the int / Decimal conversion. Ideally, I would like the application to take advantage of Decimal database objects in databases where they are supported, so it would be nice to have one place in the code that changes depending on which database is in use. Thanks, Mark -- 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] Relationship between a class with two different instances of other class (Newbie)
Thanks Conor! The callable works like a charm! It's great news! (I've been trying to figure out this for 3 days... yeah... I guess I'm not that smart) Now that I have it working, a “design” question pops up. Nothing technical, really. As Connor mentioned in his reply: “Usually we consider the table with the foreign key as the child table, but that's just being picky” That's very true, and now I don't know how to design it... I can do it the way I asked or... class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, primary_key=True) parent_id = Column(id, Integer, ForeignKey(“parent_table.id”)) # New! type = Column(type, ShortInteger)# New! field1 = Column(“field1”, String(64)) #Irrelevant def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parent_table”) id = Column(id, Integer, primary_key=True) child1 = relationship( # Well... this I still don't know how to write it down, # but it would be something like: # Give me all the children whose “parent_id” is my “id” # AND type == 1 # I'll deal with the joins and that depending on your answer, guys ) child2 = relationship( # Would be same as above # AND type == 2 ) This may be good for adding new children to the parent class... If I add a “Parent.child3”, I just need to create a new relationship very similar to the already existing ones. The way I asked in my former question would imply creating a new relationship AND adding a new foreign key to the parent. I'd like to know what people that know much more about databases think :) 2010/10/29 Conor conor.edward.da...@gmail.com: On 10/29/2010 09:43 AM, Hector Blanco wrote: Hello list... I wrote a couple of days ago about how to model an structure of three classes (http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#). I thing I almost have it, but I am still getting problems mapping an structure like this. class Child(rdb.Model): def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): def __init__(self): self.child1 = Child() self.child2 = Child() The “Parent” class has two different instances of a Child() class. I am not even sure about how to treat this (two different 1:1 relationships or a 1:2 relationship). My last try is this mapping: (don't let the rdb.Model thing fool you, is just something that automatically maps the class to the table specified in rdb.tablename). class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, primary_key=True) field1 = Column(“field1”, String(64)) #Irrelevant def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parent_table”) id = Column(id, Integer, primary_key=True) child1_id = Column(child_1_id, Integer, ForeignKey(children_table.id)) child2_id = Column(child_2_id, Integer, ForeignKey(children_table.id)) child1 = relationship(Child, primaryjoin = (child1_id==children_table.id) ) child2 = relationship(Child, primaryjoin = (child2_id==children_table.id) ) I have tried (almost) everything. I say “almost” because obviously I haven't tried the right thing. I keep getting errors that sqlalchemy can't determine the relationship between the two tables. It looks very similar to: http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child but I also understand that maybe I can't get this working that way, because I am actually putting two ForeignKeys from the same table (“children_table”) in the parent table. I am not sure how that will work out, or is correct or what... :-( Thank you! Usually we consider the table with the foreign key as the child table, but that's just being picky. The problem is that SQLAlchemy is treating children_table.id as a literal instead of a clause, so your join would be like (parent JOIN child ON parent.child1_id = 'children_table.id'). Obviously that is not what you want. There are several ways to formulate primaryjoin/secondaryjoin. Pass the whole thing in as a string: child1 = relationship(Child, primaryjoin=Parent.child1_id == Child.id) Use the column objects directly (this requires that Child be defined before Parent): child1 = relationship(Child, primaryjoin=child1_id==Child.id) Use a callable (my favorite): child1 = relationship(Child, primaryjoin=lambda: Parent.child1_id == Child.id) -Conor -- You received
[sqlalchemy] Many to One vs session
I have a table (T) that has a many-to-one relationship (via foreign key inclusion) to a category table (C). Let's name the relationship category. When I retrieve an existing T record, SA populates the category field with an instance of C. I can change the category to a different value by storing a different instance of C into category. My question is does it matter if the various instances of C are associated with the same session as the T instance? Can the C instances come from a different session, or can the be expunge'd from the session that retrieved them? Can I store an entirely new C instance, and if so, will SA do an insert into the C table? Thanks, Mark -- 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] SQLite / Decimal
On Oct 29, 2010, at 12:37 PM, Mark Erbaugh wrote: How do people handle Decimal data with SA / SQLite? Newer versions of SA give the following warning: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage. I assume that the gist of the message is similar to the reason that decimal.Decimal objects in Python can't be initialized with a float, just int's and string's. I'm working on an accounting application where I would like to use Decimal fields to store monetary amounts. If I understand the warning, I should convert the Python Decimal data and from ints to store in the database. I think I can safely get away with ints if I design things so that every data is stored with the same number of decimal digits. I could also use strings in the database, but that would eliminate the ability to do math operations, such as SUM in the SQL code. Also, is there an single place in the code to to the int / Decimal conversion. Ideally, I would like the application to take advantage of Decimal database objects in databases where they are supported, so it would be nice to have one place in the code that changes depending on which database is in use. Decimals can actually be initialized with floats in Python 2.7. I looked at their code and i didn't immediately grok it - suffice to say it is significantly more elaborate than just '%f % num'. But anyway, a loss of precision is a given when dealing with native floating points, hence the warning. Storing monetary values as ints using a known exponent on the app side is the standard way to work around the issue. You'd use a TypeDecorator for this: http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlalchemy.types.TypeDecorator . Thanks, Mark -- 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. -- 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] Simple relationship 1:1 fails with Foreign key assocated with column ------- could not find table
Hello, group! I am still dealing with the relationship I asked before (http://groups.google.com/group/sqlalchemy/browse_thread/thread/c1d46daf35116999). To tell the truth, I'm not even sure if this is a question I should ask in the SqlAlchemy forum because I'm also dealing with Megrok.rdb (http://pypi.python.org/pypi/megrok.rdb) and I don't know if that's what is causing the problem, but I'm pretty lost... As I explained in my other question, I think the rdb.Model thing that appears in the classes is just a tool to create the mapper class -- table in a slightly more transparent way for the programmer. That's why I thought I may get some help here. In this message, I have simplified the code (compared to my former question) to make it clearer, but well... The fact is that now I'm getting a problem with a simple 1:1 relationship (if I can fix it, I will be able to move to the more complicated stuff as I detailed in the former question) I am getting this error: Foreign key assocated with column 'children_table.id' could not find table 'parents_table' with which to generate a foreign key to target column 'id' I have a file, called Tables.py where all the classes and auxiliary (or intermediate) tables that I'm going to use in my application are defined: Tables.py class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parents_table) id = Column(id, Integer, primary_key=True) _whateverField= Column(whatever_field, String(16)) #Irrelevant child1 = relationship(Child, uselist=False) class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, ForeignKey(Parent.id), primary_key = True) type = Column(type, String(2)) #Irrelevant (for this example) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - And then I have two different Python .py files (Parent.py and Child.py) where the methods that manage said classes are implemented. In those files, the static area of each class is copied from Tables.py with some changes in the quotes (where I can use the object itself, I use it): Parent.py from child import Child metadata = rdb.MetaData() class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parents_table) id = Column(id, Integer, primary_key=True) _whateverField= Column(whatever_field, String(16)) #Irrelevant child1 = relationship(Child, uselist=False) #No quotation marks on this Child - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - And Child.py metadata = rdb.MetaData() class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, ForeignKey(parent_table.id), primary_key = True) type = Column(type, String(2)) #Irrelevant (for this example) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - When I try to use these classes, I get: Foreign key assocated with column 'children_table.id' could not find table 'parents_table' with which to generate a foreign key to target column 'id' But if I take a look to the tables with a MySQL Query Browser, the table parents_table is there, happily and properly created. In some other places, I have had similar problems, but I've been able to fix them by delaying the imports. I had been able to (kind of) import the Parent type in the Child file so I can use the Parent object directly. It would be a little bit as if in this case I was able to do: from parent import Parent [ . . . ] class Child(rdb.Model): [ . . . ] id = Column(id, Integer, ForeignKey(Parent.id), primary_key = True) and that usually fixed the problem but in this specific case, I can't really do that: In the Parent file I need to import the Child and that gives a very, very nasty circular dependency problem. Is there a way to tell the Child.py file something like Hey, dude... Here's the parent_table that you need! ? (Well... In a more Pythonic way, of course... I don't think 'dude'is a reserved keywork in Python, or an SqlAlchemy type). I don't know, something like: from whatever.repository.of.tables import parent_table so I can, without quotes, use: id = Column(id, Integer, ForeignKey(parent_table.id), primary_key = True) (I guess that may work) Thank you all. -- 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] Simple relationship 1:1 fails with Foreign key assocated with column ------- could not find table
On 10/29/2010 05:31 PM, Hector Blanco wrote: Hello, group! I am still dealing with the relationship I asked before (http://groups.google.com/group/sqlalchemy/browse_thread/thread/c1d46daf35116999). To tell the truth, I'm not even sure if this is a question I should ask in the SqlAlchemy forum because I'm also dealing with Megrok.rdb (http://pypi.python.org/pypi/megrok.rdb) and I don't know if that's what is causing the problem, but I'm pretty lost... As I explained in my other question, I think the rdb.Model thing that appears in the classes is just a tool to create the mapper class -- table in a slightly more transparent way for the programmer. That's why I thought I may get some help here. In this message, I have simplified the code (compared to my former question) to make it clearer, but well... The fact is that now I'm getting a problem with a simple 1:1 relationship (if I can fix it, I will be able to move to the more complicated stuff as I detailed in the former question) I am getting this error: Foreign key assocated with column 'children_table.id' could not find table 'parents_table' with which to generate a foreign key to target column 'id' I have a file, called Tables.py where all the classes and auxiliary (or intermediate) tables that I'm going to use in my application are defined: Tables.py class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parents_table) id = Column(id, Integer, primary_key=True) _whateverField= Column(whatever_field, String(16)) #Irrelevant child1 = relationship(Child, uselist=False) class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, ForeignKey(Parent.id), primary_key = True) type = Column(type, String(2)) #Irrelevant (for this example) The target of a ForeignKey should be a string, e.g.: ForeignKey(parents_table.id) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - And then I have two different Python .py files (Parent.py and Child.py) where the methods that manage said classes are implemented. In those files, the static area of each class is copied from Tables.py with some changes in the quotes (where I can use the object itself, I use it): Parent.py from child import Child metadata = rdb.MetaData() class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parents_table) id = Column(id, Integer, primary_key=True) _whateverField= Column(whatever_field, String(16)) #Irrelevant child1 = relationship(Child, uselist=False) #No quotation marks on this Child - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - And Child.py metadata = rdb.MetaData() class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, ForeignKey(parent_table.id), primary_key = True) type = Column(type, String(2)) #Irrelevant (for this example) These class definitions should be merged with those in Tables.py. You should only have one class Parent statement and one class Child statement. You may be confusing this with the non-declarative class setup, where you define the table first, class 2nd, and mapper 3rd. It looks like rdb uses the declarative approach, where the table and mapper are defined as part of the class in one step. Also, it is a good idea to make the first argument to relationship() a string, as it lets you avoid worrying about which order classes are defined. Example: # This works even if Child hasn't been defined yet. child1 = relationship(Child, uselist=False) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - When I try to use these classes, I get: Foreign key assocated with column 'children_table.id' could not find table 'parents_table' with which to generate a foreign key to target column 'id' This is probably due to the foreign key issue above. But if I take a look to the tables with a MySQL Query Browser, the table parents_table is there, happily and properly created. In some other places, I have had similar problems, but I've been able to fix them by delaying the imports. I had been able to (kind of) import the Parent type in the Child file so I can use the Parent object directly. It would be a little bit as if in this case I was able to do: from parent import Parent [ . . . ] class Child(rdb.Model): [ . . . ] id = Column(id, Integer, ForeignKey(Parent.id), primary_key = True) and that usually fixed the problem but in this specific case, I can't really do that: In the Parent file I need to import the Child and that gives a very, very nasty circular dependency problem. Is there a way to tell the Child.py file something like Hey, dude... Here's the parent_table that you need! ? (Well... In a more Pythonic way, of course... I don't think 'dude'is a reserved keywork in
[sqlalchemy] Secialists question: how to do implement stock-management
Hi Dan This is a little off topic for this group, however, I consider myself a specialist in the area you question, so I'm delighted to be on the helping end for a change :-) We can continue this one-on-one outside the group. Send your questions to me. I prefer Skype for this type of thing - see my Skype id below. Cheers Warwick Warwick Prince CEO mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com On 29/10/2010, at 8:12 PM, Dan @ Austria wrote: Hi, i have a question to database/design specialist. How can (should!) i implement a stock management system in sql-alchemy and python? I get the following data from another system via files - movements: bills from a scanner at a cash deck - movements: from goods-receipt Of course i have also master data on an per article basis. What i want do is keep charge of the stock available in our warehouse. Should i built a warehouse table with [ article / amount of article available ] and update the articles with an update statement like UPDATE warehouse_table SET amount = amount - (bill amount) where article = bill_article ? Would that be a good solution? Is there any literature or reference implementations around? Any hint is much apreciated. Although i have written a couple of database applications, i never had the probleme to change a field (amount field per article) so often. I guess there is a lot of data comming in ... Thanks in advance, Dan -- 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. -- 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] SQLite / Decimal
On Oct 29, 2010, at 6:18 PM, Michael Bayer wrote: How do people handle Decimal data with SA / SQLite? Newer versions of SA give the following warning: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage. I assume that the gist of the message is similar to the reason that decimal.Decimal objects in Python can't be initialized with a float, just int's and string's. I'm working on an accounting application where I would like to use Decimal fields to store monetary amounts. If I understand the warning, I should convert the Python Decimal data and from ints to store in the database. I think I can safely get away with ints if I design things so that every data is stored with the same number of decimal digits. I could also use strings in the database, but that would eliminate the ability to do math operations, such as SUM in the SQL code. Also, is there an single place in the code to to the int / Decimal conversion. Ideally, I would like the application to take advantage of Decimal database objects in databases where they are supported, so it would be nice to have one place in the code that changes depending on which database is in use. Decimals can actually be initialized with floats in Python 2.7. I looked at their code and i didn't immediately grok it - suffice to say it is significantly more elaborate than just '%f % num'. But anyway, a loss of precision is a given when dealing with native floating points, hence the warning. Storing monetary values as ints using a known exponent on the app side is the standard way to work around the issue. You'd use a TypeDecorator for this: http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlalchemy.types.TypeDecorator . Thank you for the pointer on the type decorator. Mark -- 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] Relationship between a class with two different instances of other class (Newbie)
On 10/29/2010 11:51 AM, Hector Blanco wrote: Thanks Conor! The callable works like a charm! It's great news! (I've been trying to figure out this for 3 days... yeah... I guess I'm not that smart) Now that I have it working, a “design” question pops up. Nothing technical, really. As Connor mentioned in his reply: “Usually we consider the table with the foreign key as the child table, but that's just being picky” That's very true, and now I don't know how to design it... It would be easier to design it if we had more concrete names instead of Parent and Child. What is the actual use case? Is this a tree hierarchy? Does each parent have exactly two children? I can do it the way I asked or... class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename(children_table) id = Column(id, Integer, primary_key=True) parent_id = Column(id, Integer, ForeignKey(“parent_table.id”)) # New! type = Column(type, ShortInteger)# New! field1 = Column(“field1”, String(64)) #Irrelevant def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename(parent_table”) id = Column(id, Integer, primary_key=True) child1 = relationship( # Well... this I still don't know how to write it down, # but it would be something like: # Give me all the children whose “parent_id” is my “id” # AND type == 1 # I'll deal with the joins and that depending on your answer, guys ) child2 = relationship( # Would be same as above # AND type == 2 ) This may be good for adding new children to the parent class... If I add a “Parent.child3”, I just need to create a new relationship very similar to the already existing ones. The way I asked in my former question would imply creating a new relationship AND adding a new foreign key to the parent. I'd like to know what people that know much more about databases think :) I'm confused as to why you would want separate child1, child2, etc. relationships instead of a single children relationship. Is Child.type really something you want for distinguishing children, or is it something you added to try and make the relationships work? Assuming you really do want to keep separate child1 and child2 relationships, and they are both one-to-one relationships, they would look like this: # omit uselist=False if this is a one-to-many relationship child1 = relationship( Child, primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 1), uselist=False) child2 = relationship( Child, primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 2), uselist=False) -Conor -- 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] Many to One vs session
On 10/29/2010 01:23 PM, Mark Erbaugh wrote: I have a table (T) that has a many-to-one relationship (via foreign key inclusion) to a category table (C). Let's name the relationship category. When I retrieve an existing T record, SA populates the category field with an instance of C. I can change the category to a different value by storing a different instance of C into category. Technically, SQLAlchemy by default does not populate the category relation until you access it the first time (i.e. it is a lazy load). My question is does it matter if the various instances of C are associated with the same session as the T instance? I'm not sure what you mean here. There is no problem having multiple C instances in the same session as the T instance. SQLAlchemy will complain (hopefully; I haven't tried it) if multiple C instances in the same session share a primary key. Can the C instances come from a different session, or can the be expunge'd from the session that retrieved them? The only restriction is, if the T instance is part of session S, then the C instance that you assign to T.category must be part of S or not be part of any session. It is allowed if you first expunge the C instance from another session and merge it into S before assigning it to T.category. Note that there are only a few use cases for moving instances from one session to another that I am aware of: * Caching query results. You expunge the instances from the original session, keep them around in memory (or serialized to disk, memcache, etc.). Then you can merge them back into subsequent sessions to avoid repeated DB queries. * Passing objects between threads. Sessions are not thread-safe, so if you want to pass objects from thread A to thread B, you have to merge session A's objects into session B before thread B can use them. Can I store an entirely new C instance, and if so, will SA do an insert into the C table? Yes, this is part of the save-update cascade[1] which is enabled by default on a relationship. Generally you only worry about cascade for one-to-many or many-to-many relationships. At least that's my experience. -Conor [1] http://www.sqlalchemy.org/docs/orm/relationships.html#the-relationship-api -- 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] Many to One vs session
On Oct 29, 2010, at 9:39 PM, Conor wrote: I have a table (T) that has a many-to-one relationship (via foreign key inclusion) to a category table (C). Let's name the relationship category. When I retrieve an existing T record, SA populates the category field with an instance of C. I can change the category to a different value by storing a different instance of C into category. Technically, SQLAlchemy by default does not populate the category relation until you access it the first time (i.e. it is a lazy load). My question is does it matter if the various instances of C are associated with the same session as the T instance? I'm not sure what you mean here. There is no problem having multiple C instances in the same session as the T instance. SQLAlchemy will complain (hopefully; I haven't tried it) if multiple C instances in the same session share a primary key. Can the C instances come from a different session, or can the be expunge'd from the session that retrieved them? The only restriction is, if the T instance is part of session S, then the C instance that you assign to T.category must be part of S or not be part of any session. It is allowed if you first expunge the C instance from another session and merge it into S before assigning it to T.category. Thanks, that was what I was asking. I think I've tested it with C instances belonging to the same session and to C instances that have been expunged, but I didn't test C instances that were still an active part of a different session. Note that there are only a few use cases for moving instances from one session to another that I am aware of: Caching query results. You expunge the instances from the original session, keep them around in memory (or serialized to disk, memcache, etc.). Then you can merge them back into subsequent sessions to avoid repeated DB queries. Passing objects between threads. Sessions are not thread-safe, so if you want to pass objects from thread A to thread B, you have to merge session A's objects into session B before thread B can use them. Can I store an entirely new C instance, and if so, will SA do an insert into the C table? Yes, this is part of the save-update cascade[1] which is enabled by default on a relationship. Generally you only worry about cascade for one-to-many or many-to-many relationships. At least that's my experience. Mark -- 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] Re: Best way to get data from database
I understand your question if you are getting different data from the server in the two database accesses. But if you are loading the exact same data twice for a page load, you should try to eliminate that redundancy instead of finding a plan to perform the redundancy in the best way. If it's the identical data twice, then why not render it into the page when you are rendering the HTML... you can render hidden fields, CDATA sections, regions of javascript containing any data structure you need, etc. It's a confusing question because if it's two different DB requests then you wouldn't be inquiring about caching for this purpose, but if it's two identical DB requests I suspect you already would have realized that the data could easily be encoded in the original page render. On Oct 28, 4:22 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hey guys, I have a doubt. I need to get the data from the sever twice every time when I load a page, one to render the HTML and another one to get the data for client side (javascript). So I don't know exactly what it's the best way and fastest. I was trying to implement a session object and store the data once using joinedload loading technique. When the data is in the client side, to kill the session object. Another one it's to call the database twice. I don't know which one is faster and better because I don't know if the database or server stores the first call in memory. If so it's not like to call the database twice, right? And if the second choice is better which loading technique (joinedload, eagerload or subqueryload) is better to use. Every call could be a bunch of data. Any help could be really useful. Thanks in advance! -- 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.
