[sqlalchemy] Re: how to do manualy incremented counter
In mysql you can declare a integer column to be auto_increment and it will handle for you giving each row a different number. Other databases have similar mechanisms. It sounds like the invoice number is the primary key for the invoice table? If you have a integer primary key in sqlalchemy, it assumes that you want to make it auto increment so this should all happen automatically. On Tue, Jun 24, 2008 at 10:13 AM, [EMAIL PROTECTED] wrote: hi lets have, say, invoice-numbers. a completely new invoice has new invoice-number. same invoice may have many versions - the invoice-number does not change. how to safely generate a new number for each new document (lets say it is simple number-incrementing)? one safe and simple way i've just invented is to have a separate table of one column - the invoice-numbers - and have a foreign key to it. First version of new invoice will create a row in the table and link to it, other versions of the invoice keep the link. Besides the simplicity this allows for any numbering scheme - whatever one puts there; even the table may have just primary id if just rowids are enough. More space-savvy way is to have just one table with a row per numbering-type (one for invoice-numbers, one for account-numbers, employee-numbers etc), and for a new document increment the respective row and get its value. If there are sequences, this seems alright, the whole operation is atomic. But if there are no sequences, i cannot make this multi-access safe. atomic updates are alright for avoiding races, but reading the value after that - no guarantee it is right one, another atomic update may have sneaked in meanwhile. Any help here? or should i use locking? im trying to avoid it... ciao svilen --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to do manualy incremented counter
Bobby Impollonia wrote: In mysql you can declare a integer column to be auto_increment and it will handle for you giving each row a different number. Other databases have similar mechanisms. It sounds like the invoice number is the primary key for the invoice table? If you have a integer primary key in sqlalchemy, it assumes that you want to make it auto increment so this should all happen automatically. I don't think this would work for invoices. What happens if a transaction is rolled back in MySQL an an auto-increment column is used? In PostgreSQL, using sequences, the number would be lost forever. Invoices usually require a continuous numbering and should have no holes in the sequence. What is the best solution is to have a table where you'll store the numbers, access it in a serializable way and then create and remove locks. This will grant that you don't have holes and that you don't have repeated numbers allocated to different invoices. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Models split across files
In my various models, I recently noticed that I have been reusing my
customer table as it relates to many other models. I've been making
CustomerPart objects in each of the model files and retyping the table
schema and object (only including the pieces I need for that
particular case)
Now I'd like to have one customer model and let the other models
access it -- rather than continually retyping everything.
But I get this error when I break it out (this is when I try to use it
in a controller in pylons):
raise exceptions.InvalidRequestError(Could not find table '%s' with
which to generate a foreign key % tname)
InvalidRequestError: Could not find table 'customer' with which to
generate a foreign key
I've included a before and after (hooray for GIT) of the models:
Thanks!
Matt
saq.py before (this one works)
from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
func
from sqlalchemy.orm import mapper, relation
from datetime import datetime
from formencode import validators
from smmodels import fe_obj, NoHTML, SuperDateValidator, fe_setter
metadata = MetaData()
...
customer_table_part = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)
questionnaire_table = Table('saq_questionnaire', metadata,
...
Column('customer_id', types.Integer, ForeignKey('customer.id'),
index=True),
...
)
questions_table = Table('saq_questions_new', metadata,
...
)
class Questionnaire(fe_setter):
def __str__(self):
return 'id: %s customer_id: %s' % (self.id, self.customer_id)
def __repr__(self):
return Questionnaire(%s, customer_id:%s) % (self.id,
self.customer_id)
class Question(fe_setter):
pass
class CustomerPart(fe_setter):
def __init__(self):
pass
mapper(Question, questions_table)
mapper(CustomerPart, customer_table_part)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer' :relation(CustomerPart, backref='questionnaires')
})
saq.py after (all the same except removed references to CustomerPart
...
from smmodels.customer import customer_table, Customer
...
mapper(Question, questions_table)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer' :relation(Customer, backref='questionnaires')
})
customer.py after (newly created)
from sqlalchemy import Column, MetaData, Table, types, ForeignKey
from sqlalchemy.orm import mapper, relation
from formencode import validators
from smmodels import fe_obj, NoHTML, fe_setter
from datetime import date
metadata = MetaData()
customer_table = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)
class Customer(object):
def __init__(self):
pass
mapper(Customer, customer_table)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to do manualy incremented counter
On Tuesday 24 June 2008 19:32:19 Jorge Godoy wrote: Bobby Impollonia wrote: In mysql you can declare a integer column to be auto_increment and it will handle for you giving each row a different number. Other databases have similar mechanisms. It sounds like the invoice number is the primary key for the invoice table? If you have a integer primary key in sqlalchemy, it assumes that you want to make it auto increment so this should all happen automatically. I don't think this would work for invoices. What happens if a transaction is rolled back in MySQL an an auto-increment column is used? In PostgreSQL, using sequences, the number would be lost forever. Invoices usually require a continuous numbering and should have no holes in the sequence. What is the best solution is to have a table where you'll store the numbers, access it in a serializable way and then create and remove locks. This will grant that you don't have holes and that you don't have repeated numbers allocated to different invoices. mmm yes, there is this things also. Some numbering schemes allow/force holes, others not. There can be also hierarchical numbering schemes, e.g. employee is 1-31-52 maybe a table is really the most flexible way of doing it... --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Models split across files
are metadatas same?
On Tuesday 24 June 2008 19:45:46 Matt Haggard wrote:
In my various models, I recently noticed that I have been reusing
my customer table as it relates to many other models. I've been
making CustomerPart objects in each of the model files and retyping
the table schema and object (only including the pieces I need for
that particular case)
Now I'd like to have one customer model and let the other models
access it -- rather than continually retyping everything.
But I get this error when I break it out (this is when I try to use
it in a controller in pylons):
raise exceptions.InvalidRequestError(Could not find table '%s'
with which to generate a foreign key % tname)
InvalidRequestError: Could not find table 'customer' with which to
generate a foreign key
I've included a before and after (hooray for GIT) of the models:
Thanks!
Matt
saq.py before (this one works)
from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
func
from sqlalchemy.orm import mapper, relation
from datetime import datetime
from formencode import validators
from smmodels import fe_obj, NoHTML, SuperDateValidator, fe_setter
metadata = MetaData()
...
customer_table_part = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)
questionnaire_table = Table('saq_questionnaire', metadata,
...
Column('customer_id', types.Integer, ForeignKey('customer.id'),
index=True),
...
)
questions_table = Table('saq_questions_new', metadata,
...
)
class Questionnaire(fe_setter):
def __str__(self):
return 'id: %s customer_id: %s' % (self.id,
self.customer_id)
def __repr__(self):
return Questionnaire(%s, customer_id:%s) % (self.id,
self.customer_id)
class Question(fe_setter):
pass
class CustomerPart(fe_setter):
def __init__(self):
pass
mapper(Question, questions_table)
mapper(CustomerPart, customer_table_part)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer' :relation(CustomerPart, backref='questionnaires')
})
saq.py after (all the same except removed references to
CustomerPart
...
from smmodels.customer import customer_table, Customer
...
mapper(Question, questions_table)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer' :relation(Customer, backref='questionnaires')
})
customer.py after (newly created)
from sqlalchemy import Column, MetaData, Table, types, ForeignKey
from sqlalchemy.orm import mapper, relation
from formencode import validators
from smmodels import fe_obj, NoHTML, fe_setter
from datetime import date
metadata = MetaData()
customer_table = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)
class Customer(object):
def __init__(self):
pass
mapper(Customer, customer_table)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Models split across files
using the declarative layer tends to simplify the imports for this
kind of thing. Just a thought.
On Jun 24, 2008, at 12:45 PM, Matt Haggard wrote:
In my various models, I recently noticed that I have been reusing my
customer table as it relates to many other models. I've been making
CustomerPart objects in each of the model files and retyping the table
schema and object (only including the pieces I need for that
particular case)
Now I'd like to have one customer model and let the other models
access it -- rather than continually retyping everything.
But I get this error when I break it out (this is when I try to use it
in a controller in pylons):
raise exceptions.InvalidRequestError(Could not find table '%s' with
which to generate a foreign key % tname)
InvalidRequestError: Could not find table 'customer' with which to
generate a foreign key
I've included a before and after (hooray for GIT) of the models:
Thanks!
Matt
saq.py before (this one works)
from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
func
from sqlalchemy.orm import mapper, relation
from datetime import datetime
from formencode import validators
from smmodels import fe_obj, NoHTML, SuperDateValidator, fe_setter
metadata = MetaData()
...
customer_table_part = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)
questionnaire_table = Table('saq_questionnaire', metadata,
...
Column('customer_id', types.Integer, ForeignKey('customer.id'),
index=True),
...
)
questions_table = Table('saq_questions_new', metadata,
...
)
class Questionnaire(fe_setter):
def __str__(self):
return 'id: %s customer_id: %s' % (self.id, self.customer_id)
def __repr__(self):
return Questionnaire(%s, customer_id:%s) % (self.id,
self.customer_id)
class Question(fe_setter):
pass
class CustomerPart(fe_setter):
def __init__(self):
pass
mapper(Question, questions_table)
mapper(CustomerPart, customer_table_part)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer' :relation(CustomerPart, backref='questionnaires')
})
saq.py after (all the same except removed references to CustomerPart
...
from smmodels.customer import customer_table, Customer
...
mapper(Question, questions_table)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer' :relation(Customer, backref='questionnaires')
})
customer.py after (newly created)
from sqlalchemy import Column, MetaData, Table, types, ForeignKey
from sqlalchemy.orm import mapper, relation
from formencode import validators
from smmodels import fe_obj, NoHTML, fe_setter
from datetime import date
metadata = MetaData()
customer_table = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)
class Customer(object):
def __init__(self):
pass
mapper(Customer, customer_table)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Moving On
Hi, I've had fun over the last 18 months doing odd bits of work on SQLAlchemy. It works pretty damn well on MSSQL now, although I never did quite get all the unit tests nailed. It's been great seeing the library continue to evolve, and particularly satisfying to see things I've started (e.g. AutoCode) being taken forward. Just of late, I've been reassessing priorities in my life, and open source development isn't going to be a big one going forward. In fact, I may even be giving up the computer completely for a year or two and going travelling. I'll be unsubscribing from the mailing list in a couple of days, although I'm happy to receive SA related emails at my personal address, for the next couple of months at least. Thanks for the interesting times, Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Moving On
have fun svilen On Tuesday 24 June 2008 22:37:08 Paul Johnston wrote: Hi, I've had fun over the last 18 months doing odd bits of work on SQLAlchemy. It works pretty damn well on MSSQL now, although I never did quite get all the unit tests nailed. It's been great seeing the library continue to evolve, and particularly satisfying to see things I've started (e.g. AutoCode) being taken forward. Just of late, I've been reassessing priorities in my life, and open source development isn't going to be a big one going forward. In fact, I may even be giving up the computer completely for a year or two and going travelling. I'll be unsubscribing from the mailing list in a couple of days, although I'm happy to receive SA related emails at my personal address, for the next couple of months at least. Thanks for the interesting times, Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Moving On
Paul Johnston wrote: Hi, I've had fun over the last 18 months doing odd bits of work on SQLAlchemy. It works pretty damn well on MSSQL now, although I never did quite get all the unit tests nailed. It's been great seeing the library continue to evolve, and particularly satisfying to see things I've started (e.g. AutoCode) being taken forward. Just of late, I've been reassessing priorities in my life, and open source development isn't going to be a big one going forward. In fact, I may even be giving up the computer completely for a year or two and going travelling. I'll be unsubscribing from the mailing list in a couple of days, although I'm happy to receive SA related emails at my personal address, for the next couple of months at least. Thanks for the interesting times, Paul Hi Paul, Thanks for all of your great work on SA and best of luck with the new road ahead. All the best, Jason --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Moving On
hi Paul - Congrats on making some hard decisions. Thanks for all your help, and good luck ! Your commit access remains open. - mike On Jun 24, 2008, at 3:37 PM, Paul Johnston wrote: Hi, I've had fun over the last 18 months doing odd bits of work on SQLAlchemy. It works pretty damn well on MSSQL now, although I never did quite get all the unit tests nailed. It's been great seeing the library continue to evolve, and particularly satisfying to see things I've started (e.g. AutoCode) being taken forward. Just of late, I've been reassessing priorities in my life, and open source development isn't going to be a big one going forward. In fact, I may even be giving up the computer completely for a year or two and going travelling. I'll be unsubscribing from the mailing list in a couple of days, although I'm happy to receive SA related emails at my personal address, for the next couple of months at least. Thanks for the interesting times, Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Moving On
Hey I'll miss you Paul; thanks for all of your help with MSSQL and for being the pyodbc trailblazer. Good luck with whatever your new direction in life brings -- turning off the computer and a taking bit of travel time sounds pretty appealing! On Tue, Jun 24, 2008 at 3:37 PM, Paul Johnston [EMAIL PROTECTED] wrote: Hi, I've had fun over the last 18 months doing odd bits of work on SQLAlchemy. It works pretty damn well on MSSQL now, although I never did quite get all the unit tests nailed. It's been great seeing the library continue to evolve, and particularly satisfying to see things I've started (e.g. AutoCode) being taken forward. Just of late, I've been reassessing priorities in my life, and open source development isn't going to be a big one going forward. In fact, I may even be giving up the computer completely for a year or two and going travelling. I'll be unsubscribing from the mailing list in a couple of days, although I'm happy to receive SA related emails at my personal address, for the next couple of months at least. Thanks for the interesting times, Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] overzealous check breaks doctesting
Hi there, I'm writing a doctest in which I include a MappedCollection subclass. In my doctest, I create such a subclass:: class Foo(MappedCollection): ...pass Unfortunately later on, sqlalchemy.orm.collections.py has a check to determine whether Foo is really a builtin, and if so, it fails to instrument, here:: def _instrument_class(cls): ... # In the normal call flow, a request for any of the 3 basic collection # types is transformed into one of our trivial subclasses # (e.g. InstrumentedList). Catch anything else that sneaks in here... if cls.__module__ == '__builtin__': raise sa_exc.ArgumentError( Can not instrument a built-in type. Use a subclass, even a trivial one.) Unfortunately, when Foo is 'cls', it will have __module__ set to '__builtin__' even while Foo is not a builtin. I can work around this issue in the doctest by something something evil like:: Foo.__module__ = 'foo' Things then seem to work. Could the check somehow be modified to still find true builtins but not those defined in a doctest? I can also see this as being a doctest problem; perhaps the __module__ should really be set to '__main__' in them, but it might be easier to get it fixed here... Regards, Martijn --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: overzealous check breaks doctesting
Martijn Faassen wrote: Hi there, I'm writing a doctest in which I include a MappedCollection subclass. In my doctest, I create such a subclass:: class Foo(MappedCollection): ...pass Unfortunately later on, sqlalchemy.orm.collections.py has a check to determine whether Foo is really a builtin, and if so, it fails to instrument, here:: def _instrument_class(cls): ... # In the normal call flow, a request for any of the 3 basic collection # types is transformed into one of our trivial subclasses # (e.g. InstrumentedList). Catch anything else that sneaks in here... if cls.__module__ == '__builtin__': raise sa_exc.ArgumentError( Can not instrument a built-in type. Use a subclass, even a trivial one.) Unfortunately, when Foo is 'cls', it will have __module__ set to '__builtin__' even while Foo is not a builtin. I can work around this issue in the doctest by something something evil like:: Foo.__module__ = 'foo' Things then seem to work. Could the check somehow be modified to still find true builtins but not those defined in a doctest? Sure. Any suggestions for an alternate check? I can also see this as being a doctest problem; perhaps the __module__ should really be set to '__main__' in them, but it might be easier to get it fixed here... --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Beginner: query.join not cooperating
The Query.join() documentations says: def join(self, prop, id=None, aliased=False, from_joinpoint=False) 'prop' may be one of: * a class-mapped attribute, i.e. Houses.rooms What exactly counts as class-mapped? I've set up a ForeignKey in my Files table as well as a backref relation in my Projects table, but none of these work: print session.query(File).join(Project).filter_by(project='mario', tapeout='sa11').one() print session.query(File).join(Project.files).filter_by(project='mario', tapeout='sa11').one() print session.query(File).join(Project.id).filter_by(project='mario', tapeout='sa11').one() print session.query(File).join(File.project).filter_by(project='mario', tapeout='sa11').one() print session.query(File).join(File.project_id).filter_by(project='mario', tapeout='sa11').one() They all fail with: sqlalchemy.exceptions.InvalidRequestError: Mapper 'Mapper|File|files' has no property 'sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x2a9a283650' It seems like .join(Project) should 'just work' after the information I put in the sytem, but I don't know much... If there's some documentation that makes this clear, feel free to just link it. If you want more code, let me know what to copy/paste. Thanks! --Buck --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Beginner: printing out queries
Is there a way to print out the query as it would execute on the server? I'd like to copy/paste it into the server to get the 'explain' output, and the '%s' variables are very unhelpful here. I'd also like to turn off the 'alias and backtick-escape every column' default behavior if I can. Thanks! --Buck Hopefully I'll be able to *answer* questions someday soon... --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
