[sqlalchemy] Re: Secialists question: how to do implement stock-management
+1 for the books of silverston, this is a must read when implementing such system btw : the default persons-organization model of Camelot is based on this book On Oct 29, 2:40 pm, Mark Erbaugh m...@microenh.com wrote: 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 athttp://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] Simple relationship 1:1 fails with Foreign key assocated with column ------- could not find table
Thank you again, Connor. I'll give it a try on Monday and I'll let you know the results. I kind of suspected that having the classes defined in two different places was making the whole thing go nuts, but that's the way I found it (and I was trying to keep it consistent with what was there) but I don't think that under any concept re-writing code is a good idea so I'll try to change it. Thank you for the very well explained and detailed reply. 2010/10/29 Conor conor.edward.da...@gmail.com: 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
[sqlalchemy] NamedTuple error in multiple join?
I'm using SQLAlchemy 0.6.4 on top of OpenBSD utilitizing PostgreSQL 8.4.4.
As a first project, I am gathering statistics on the availability of another
Open Source project. The schema is normalized, the following SQL query
(which works at the console) to find the latest snapshot is giving me fits
when translating to Python:
SELECT s.id
FROM snapshots s
WHERE s.cron_id = (
SELECT ce.id FROM cron_events ce
WHERE ce.timestamp = (
SELECT MAX(ce.timestamp)
FROM snapshots s
JOIN cron_events ce ON ce.id = s.cron_id
JOIN platforms p ON p.id = s.platform_id
WHERE p.name = 'amd64'))
Aside from the nested subqueries, I'm stuck at implementing the innermost
SELECT which finds the latest recorded snaphot:
SELECT MAX(ce.timestamp)
FROM snapshots s
JOIN cron_events ce ON ce.id = s.cron_id
JOIN platforms p ON p.id = s.platform_id
WHERE p.name = 'amd64'
The class structure is as follows:
class Cron(Base):
collect various timed values here
__tablename__ = CRON_TABLENAME
id = Column(Integer, Sequence(CRON_TABLENAME + '_id_seq'),
primary_key=True)
timestamp = Column(DateTime, nullable=False, unique=True)
ftp_time = Column(Interval, nullable=False)
db_time = Column(Interval, nullable=True)
platforms = relationship('Platform', order_by='Platform.id',
backref='cron')
snapshots = relationship('Snapshot', order_by='Snapshot.id',
backref='cron')
def __init__(self, timestamp, ftp_time):
self.timestamp = timestamp
self.ftp_time = ftp_time
def __repr__(self):
return Cron'%s','%s','%s','%s' % (self.id, self.timestamp,
self.ftp_time, self.db_time)
class Platform(Base):
abstraction of platform name first occurrence
__tablename__ = PLATFORM_TABLENAME
id = Column(Integer, Sequence(PLATFORM_TABLENAME + '_id_seq'),
primary_key=True)
cron_id = Column(Integer, ForeignKey(CRON_TABLENAME + '.id'),
nullable=False)
name = Column(String(32), nullable=False, unique=True)
def __init__(self, cron_id, name):
self.cron_id = cron_id
self.name = name
def __repr__(self):
return Platform'%s','%s','%s','%s' % (self.id, self.cron_id,
self.cron.timestamp, self.name)
class Snapshot(Base):
abstraction of individual platform snapshot
__tablename__ = SNAPSHOT_TABLENAME
id = Column(Integer, Sequence(SNAPSHOT_TABLENAME + '_id_seq'),
primary_key=True)
cron_id = Column(Integer, ForeignKey(CRON_TABLENAME + '.id'),
nullable=False)
platform_id = Column(Integer, ForeignKey(PLATFORM_TABLENAME + '.id'),
nullable=False)
def __init__(self, cron_id, platform_id):
self.cron_id = cron_id
self.platform_id = platform_id
def __repr__(self):
return Snapshot'%s','%s','%s','%s' % (self.id, self.cron_id,
self.cron.timestamp, self.platform_id\
)
The following Python code:
for t in session.query(func.max(Cron.timestamp)).\
join((Snapshot, Cron.id == Snapshot.cron_id), (Platform,
Platform.id == Snapshot.platform_id)).\
filter(Platform.id == platform_id):
print t
...or variations such as:
t = session.query(func.max(Cron.timestamp)).\
join((Snapshot, Cron.id == Snapshot.cron_id), (Platform,
Platform.id == Snapshot.platform_id)).\
filter(Platform.id == platform_id).one()
...all are giving me the following error. I suspect I am missing something
obvious. Any insight shared would certainly be appreciated.
Thanks.
2010-10-30 14:47:43,783 INFO sqlalchemy.engine.base.Engine.0x...dccL SELECT
max(cron_events.timestamp) AS max_1
FROM cron_events JOIN snapshots ON cron_events.id = snapshots.cron_id JOIN
platforms ON platforms.id = snapshots.platform_id
WHERE platforms.id = %(id_1)s
2010-10-30 14:47:43,790 INFO sqlalchemy.engine.base.Engine.0x...dccL
{'id_1': (1,)}
Traceback (most recent call last):
File ./snapshots.py, line 138, in module
snapshot_id = get_latest_snapshot(cron_id, platform_id, name)
File ./snapshots.py, line 110, in get_latest_snapshot
filter(Platform.id == platform_id):
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
line 1451, in __iter__
return self._execute_and_instances(context)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
line 1456, in _execute_and_instances
mapper=self._mapper_zero_or_none())
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/session.py,
line 737, in execute
clause, params or {})
File /usr/local/lib/python2.6/site-packages/sqlalchemy/engine/base.py,
line 1109, in execute
return Connection.executors[c](self, object, multiparams, params)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/engine/base.py,
line 1186, in _execute_clauseelement
return self.__execute_context(context)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/engine/base.py,
line 1215, in __execute_context
context.parameters[0], context=context)
File
