[sqlalchemy] order_by(datetime)
Hi All. I have a datetime column in my model. If I do an .order_by I get year-month-day but how do I do an order_by to get month-day-year? or even a day-month-year Thanks, Frans. -- 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] order_by(datetime)
you'd need to use SQL functions that break the datetime into its component parts, and order by them. such as: order_by(func.datepart(MONTH, my_date_col), func.datepart(DAY, my_date_col), func.datepart(YEAR, my_date_col)) datepart routines vary by database backend with very little cross compatibility - you'd have to consult the documentation for your database on the recommended way to break dates up into components. On Jan 10, 2011, at 5:35 AM, F.A.Pinkse wrote: Hi All. I have a datetime column in my model. If I do an .order_by I get year-month-day but how do I do an order_by to get month-day-year? or even a day-month-year Thanks, Frans. -- 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] Re: partial rollback in transaction ?
One important change here is to change the engine type to InnoDB, otherwise
transactions are entirely meaningless with MySQL.
If I use InnoDB, the end result of used is 0 in all cases. If I don't and
use MyISAM, the end result of used is 1 in all cases, regardless of whether
InviteCode is loaded before, or after, the begin().
It seems likely that the issue is simply that you forgot to use InnoDB.
On Jan 10, 2011, at 1:43 AM, Romy wrote:
Sorry Michael, 'self-contained' wasn't a proper term for that test
given that it required an initial DB state containing a single row.
I've modified your version to try and reproduce the bug.
Since yours didn't use elixir at all and I'm not familiar with
elixir's internals, I was able to reproduce only what I believe to be
the equivalent in sqlalchemy. Please note you'll need to create a
mysql database and fill in the connection string, as the test does not
fail with sqlite!
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
e = create_engine('mysql://user:p...@localhost/test', echo=True)
Base = declarative_base()
class InviteCode(Base):
__tablename__ = 'test_invite_codes'
id = Column(Integer, primary_key=True)
used = Column(Integer, default=0)
users = relationship(User, backref=invite_code)
class User(Base):
__tablename__ = 'test_users'
id = Column(Integer, primary_key=True)
invite_code_id = Column(Integer,
ForeignKey('test_invite_codes.id'))
email = Column(String(128), unique=True)
Base.metadata.create_all(e)
session = Session(e, autocommit=True)
session.query(User).delete()
session.query(InviteCode).delete()
invite_code = InviteCode()
session.add(invite_code)
session.flush()
assert invite_code.used == 0
session.close()
session.begin()
user_row = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row)
session.commit()
invite_code = session.query(InviteCode).first()
assert invite_code.used == 0
session.begin()
invite_code.used = invite_code.used + 1
session.add(invite_code)
session.flush()
user_row_2 = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row_2)
rolled_back = False
try:
session.commit()
except:
rolled_back = True
session.rollback()
assert rolled_back
assert invite_code.used == 0
--
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] Re: Gracefully recovering from MySQL failures in a connection pool
On 1/7/2011 7:52 AM, Michael Bayer wrote: the disconnect detection has to be implemented individually for each DBAPI for each backend. pymssql is not very frequently used so this would appear to be a missing message. We currently intercept: Error 10054, Not connected to any MS SQL server, Connection is closed not sure why you're getting something different - those values were added in March 2010, ultimately from an end-user, to support the new rewrite of pymssql. FYI (Having nothing to do specifically with pymssql or SA), I ran into a similar problem with a server containing a connection pool. While the server was idle, the DBA restarted the SQL Server. The next access through one of the pooled connections got an error message that was very much nonspecific. After some digging, I was unable to identify any way to distinguish this case from other errors. I wound up gving up on the pool, and creating a new connection for each request. -- Don Dwiggins Advanced Publishing Technology -- 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] SQLAlchemy 0.6.6 Released
On 09/01/2011 16:25, Michael Bayer wrote: The limitations of distutils are also troubling here. In my own work app, we use pip in conjunction with a Makefile and for the SQLAlchemy install the flag is on in the Makefile. In that regard the flag being off by default doesn't feel like that big a deal to me personally. Anyone know how to set this flag in a buildout setup? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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 (Foreign key) inside composite column
Hello, I'm trying to do the following: 'home': orm.composite( Address, user.c.HomeAddress_Street, # The following column is a UUID but is a foreign key to a mapped # table in SQLAlchemy, ideally would be to say relationship(City) # and specify the column user.c.HomeAddress_City, user.c.HomeAddress_ZipCode ) I've read in this newsgroups that in the past somebody tried to do the samething, and he was referred to establish a listener to the set event. I already tried that by defining an AttributeExtension class and checking that if is an UUID a query must be done to get the mapped object. However I noticed that when a SQLAlchemy session does a commit() it tries to initialize the same object in sqlalchemy.orm.strategies:CompositeColumnLoader inside the copy function of the init_class_attribute method, without calling my event listener. As such I tried a simple if isinstance(city, uuid.UUID): city = City.load(city), where this method is just: @classmethod def load(cls, id): session = Session() obj = session.query(cls).get(id) session.close() return obj However during session.commit() it appears that my Session class (made with scoped_session(sessionmaker()) ) goes to None (and crashes). Is this an expected behavior? Is there any simpler way to have a mapped class in a composite column with a foreign key? Thanks -- 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] Relationships : how to properly filter children ?
Dear all,
I'm developing a website aimed at handling tournaments' results and
subscriptions.
One subscription is bound to one user and one tournament.
subscriptions_table = Table('SUBSCRIPTIONS', metadata,
...
Column('tournament_id', Integer,
ForeignKey('TOURNAMENTS.id')),
Column('user_id', Integer, ForeignKey('USERS.id')),
Here are the mappers (I'm not sure I actually need the backref) :
mapper(Tournament, tournaments_table, properties={
subscriptions: relationship(Subscription, backref=tournament)
})
mapper(Subscription, subscriptions_table, properties={
user: relationship(User)
})
When the user clicks Subscribe on the page, the model is checking if the
user already subscribed to the current tournament or not. If yes,
SUBSCRIPTIONS.UPDATE should be issued, otherwise SUBSCRIPTIONS.INSERT
As you can see, it's a very classical scenario.
Here's how I implemented the subscribe method... but I don't like it at
all :
class Tournament(Base):
def subscribe(self, user, status):
# Works, but dirty ! Why should I manually query RESULTS since I
have access to self.subscriptions ?
# Should I manually look the user in [subscription .user for
subscription in self.subscriptions] ?
current_subscription = orm.query(Result).filter(Result.tournament ==
self).filter(Result.user == user).first()
if current_subscription :
current_subscription.status = status
else :
self.subscriptions.append(Subscription(user, status))
# Commit / rollback logic
What do you think ? Since self.subscriptions is already bound, how should I
properly filter it by user ?
Thanks very much for your help !
Franck
--
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 (Foreign key) inside composite column
On Jan 10, 2011, at 1:10 PM, Arturo Sevilla wrote: Hello, I'm trying to do the following: 'home': orm.composite( Address, user.c.HomeAddress_Street, # The following column is a UUID but is a foreign key to a mapped # table in SQLAlchemy, ideally would be to say relationship(City) # and specify the column user.c.HomeAddress_City, user.c.HomeAddress_ZipCode ) I don't understand what this means, i think you're missing some context here that would make this easier for others to understand, are there *other* columns on user that also reference city ? I don't understand what relationship() and specify the column means. relationship() accepts a primaryjoin expression for this purpose, its in the docs. If you have HomeAddress and WorkAddress, you'd make two relationships. I also have an intuition, since it seems like you want a variant on relationship(), that composite is definitely not what you want to be using, it pretty much never is, but a picture of all the relevant columns here would be helpful. You definitely cannot use a column that's part of a relationship() in a composite() as well and manipulating foreign keys through composites is not the intended usage. As such I tried a simple if isinstance(city, uuid.UUID): city = City.load(city), where this method is just: @classmethod def load(cls, id): session = Session() obj = session.query(cls).get(id) session.close() return obj However during session.commit() it appears that my Session class (made with scoped_session(sessionmaker()) ) goes to None (and crashes). Is this an expected behavior? no, the scoped_session object always returns either an existing or a new Session and is never None. -- 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: Relationship (Foreign key) inside composite column
Hello,
Thanks again for the quick reply!
I tried to isolate all the mapper columns to try to make it less
confusing, now I know that was not a good idea.
orm.mapper(User, user, properties={
'id': user.c.ID,
'_first_name': user.c.FirstName,
'_middle_name': user.c.MiddleName,
'_last_name': user.c.LastName,
'_salutation': user.c.Salutation,
'_username': user.c.Username,
'_password': user.c.Password,
'_personal_email': user.c.PersonalEmail,
'_bussiness_email': user.c.BussinessEmail,
'_birth_date': user.c.BirthDate,
'_profession': user.c.Profession,
'_contact': orm.composite(ContactInformation, user.c.HomePage,
user.c.Telephone, user.c.Fax,
user.c.Nextel),
'_home_address': orm.composite(
Address,
user.c.HomeAddress_Street,
user.c.HomeAddress_City,
user.c.HomeAddress_ZipCode
),
'_billing_address': orm.composite(
Address,
user.c.BillingAddress_Street,
user.c.BillingAddress_City,
user.c.BillingAddress_ZipCode
),
'active': user.c.Active
})
I made the Address class because in some places it makes it easy to
encapsulate this information. I don't have any relationship() as you
can see in the mapping, that's what I would want but within the
composite object. There is no address table in the schema as my
intention was to avoid a join (city in this case would have been lazy
loaded).
In order to get this behavior, do I must then create an address table
and join it through a primaryjoin in relationship()?
Thanks!
On Jan 10, 10:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jan 10, 2011, at 1:10 PM, Arturo Sevilla wrote:
Hello,
I'm trying to do the following:
'home': orm.composite(
Address,
user.c.HomeAddress_Street,
# The following column is a UUID but is a foreign key to a
mapped
# table in SQLAlchemy, ideally would be to say
relationship(City)
# and specify the column
user.c.HomeAddress_City,
user.c.HomeAddress_ZipCode
)
I don't understand what this means, i think you're missing some context here
that would make this easier for others to understand, are there *other*
columns on user that also reference city ? I don't understand what
relationship() and specify the column means. relationship() accepts a
primaryjoin expression for this purpose, its in the docs. If you have
HomeAddress and WorkAddress, you'd make two relationships.
I also have an intuition, since it seems like you want a variant on
relationship(), that composite is definitely not what you want to be using,
it pretty much never is, but a picture of all the relevant columns here would
be helpful. You definitely cannot use a column that's part of a
relationship() in a composite() as well and manipulating foreign keys through
composites is not the intended usage.
As such I tried a simple if isinstance(city, uuid.UUID): city =
City.load(city), where this method is just:
@classmethod
def load(cls, id):
session = Session()
obj = session.query(cls).get(id)
session.close()
return obj
However during session.commit() it appears that my Session class (made
with scoped_session(sessionmaker()) ) goes to None (and crashes).
Is this an expected behavior?
no, the scoped_session object always returns either an existing or a new
Session and is never None.
--
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] Re: Relationship (Foreign key) inside composite column
On Jan 10, 2011, at 2:21 PM, Arturo Sevilla wrote:
Hello,
Thanks again for the quick reply!
I tried to isolate all the mapper columns to try to make it less
confusing, now I know that was not a good idea.
orm.mapper(User, user, properties={
'id': user.c.ID,
'_first_name': user.c.FirstName,
'_middle_name': user.c.MiddleName,
'_last_name': user.c.LastName,
'_salutation': user.c.Salutation,
'_username': user.c.Username,
'_password': user.c.Password,
'_personal_email': user.c.PersonalEmail,
'_bussiness_email': user.c.BussinessEmail,
'_birth_date': user.c.BirthDate,
'_profession': user.c.Profession,
'_contact': orm.composite(ContactInformation, user.c.HomePage,
user.c.Telephone, user.c.Fax,
user.c.Nextel),
'_home_address': orm.composite(
Address,
user.c.HomeAddress_Street,
user.c.HomeAddress_City,
user.c.HomeAddress_ZipCode
),
'_billing_address': orm.composite(
Address,
user.c.BillingAddress_Street,
user.c.BillingAddress_City,
user.c.BillingAddress_ZipCode
),
'active': user.c.Active
})
I made the Address class because in some places it makes it easy to
encapsulate this information. I don't have any relationship() as you
can see in the mapping, that's what I would want but within the
composite object. There is no address table in the schema as my
intention was to avoid a join (city in this case would have been lazy
loaded).
In order to get this behavior, do I must then create an address table
and join it through a primaryjoin in relationship()?
having a separate address table would certainly be preferable, sure.
the composite approach would be viewonly:
@property
def home_address(self):
return Address(self.home_street, self.home_city, self.home_zip)
in 0.7 it works pretty much in this way.
Thanks!
On Jan 10, 10:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jan 10, 2011, at 1:10 PM, Arturo Sevilla wrote:
Hello,
I'm trying to do the following:
'home': orm.composite(
Address,
user.c.HomeAddress_Street,
# The following column is a UUID but is a foreign key to a
mapped
# table in SQLAlchemy, ideally would be to say
relationship(City)
# and specify the column
user.c.HomeAddress_City,
user.c.HomeAddress_ZipCode
)
I don't understand what this means, i think you're missing some context here
that would make this easier for others to understand, are there *other*
columns on user that also reference city ? I don't understand what
relationship() and specify the column means. relationship() accepts a
primaryjoin expression for this purpose, its in the docs. If you have
HomeAddress and WorkAddress, you'd make two relationships.
I also have an intuition, since it seems like you want a variant on
relationship(), that composite is definitely not what you want to be using,
it pretty much never is, but a picture of all the relevant columns here
would be helpful. You definitely cannot use a column that's part of a
relationship() in a composite() as well and manipulating foreign keys
through composites is not the intended usage.
As such I tried a simple if isinstance(city, uuid.UUID): city =
City.load(city), where this method is just:
@classmethod
def load(cls, id):
session = Session()
obj = session.query(cls).get(id)
session.close()
return obj
However during session.commit() it appears that my Session class (made
with scoped_session(sessionmaker()) ) goes to None (and crashes).
Is this an expected behavior?
no, the scoped_session object always returns either an existing or a new
Session and is never None.
--
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] combining join with alias
On Sat, Jan 8, 2011 at 7:59 PM, Petra Clementson petraclement...@gmail.comwrote: On Sat, Jan 8, 2011 at 10:38 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 8, 2011, at 3:23 PM, Petra Clementson wrote: I want to do a self join on combined but it wont let me. Essensially, I want to join two different tables, and create aliases so I can compare one column and make sure that each item in the column is uniqe. If there are duplicates, I want my piece of code to show the duplicates. Combining join with alias seems like the best way to do this because when using other methods, if I change the duplicates to a unique name, the name that used to be a duplicate still prints. Note that the Header table has a foreign key reference to DiskFile and the sql version used was 0.6.5. This is the portion of code where I am getting an error: combined = join(DiskFile, Header) combined_alias = aliased(combined) ERROR: Traceback (most recent call last): File duplicatedl.py, line 32, in module combined_1 = aliased(combined) File /opt/sqlalchemy/lib/python2.5/site-packages/sqlalchemy/orm/ util.py, line 304, in __init__ self.__target = self.__mapper.class_ AttributeError: 'SQLCompiler' object has no attribute 'class_' This could perhaps be improved in the interface; aliased() applies to mapped classes and mappers. join() is a Selectable expression. To alias a selectable, use its alias() method to generate an alias object: join(A, B).alias() I'll think about having orm.aliased() detect a selectable and return selectable.alias() in 0.7. Thanks for your prompt reply! This looks like something I haven't tried but would probably work. I'll have to try it on Monday and let you know how it goes. -- Okay, so I implemented join(A, B).alias(), but I can't seem to access the columns of the aliased table. Here is my code: session = sessionfactory() combined_1 = join(DiskFile, Header).alias() combined_2 = join(DiskFile, Header).alias() query = session.query(header).select_from(combined_1, combined_2).filter(DiskFile.canonical == True).filter(Header.datalab != 'none').filter(combined_1.datalab == combined_2.datalab) And I get: AttributeError: 'Alias' object has no attribute 'datalab' Any ideas? -- 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: partial rollback in transaction ?
Face palm.. missed the forest for the trees.
Does this mean both tables would need to be InnoDB ?
On Jan 10, 7:10 am, Michael Bayer mike...@zzzcomputing.com wrote:
One important change here is to change the engine type to InnoDB, otherwise
transactions are entirely meaningless with MySQL.
If I use InnoDB, the end result of used is 0 in all cases. If I don't and
use MyISAM, the end result of used is 1 in all cases, regardless of whether
InviteCode is loaded before, or after, the begin().
It seems likely that the issue is simply that you forgot to use InnoDB.
On Jan 10, 2011, at 1:43 AM, Romy wrote:
Sorry Michael, 'self-contained' wasn't a proper term for that test
given that it required an initial DB state containing a single row.
I've modified your version to try and reproduce the bug.
Since yours didn't use elixir at all and I'm not familiar with
elixir's internals, I was able to reproduce only what I believe to be
the equivalent in sqlalchemy. Please note you'll need to create a
mysql database and fill in the connection string, as the test does not
fail with sqlite!
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
e = create_engine('mysql://user:p...@localhost/test', echo=True)
Base = declarative_base()
class InviteCode(Base):
__tablename__ = 'test_invite_codes'
id = Column(Integer, primary_key=True)
used = Column(Integer, default=0)
users = relationship(User, backref=invite_code)
class User(Base):
__tablename__ = 'test_users'
id = Column(Integer, primary_key=True)
invite_code_id = Column(Integer,
ForeignKey('test_invite_codes.id'))
email = Column(String(128), unique=True)
Base.metadata.create_all(e)
session = Session(e, autocommit=True)
session.query(User).delete()
session.query(InviteCode).delete()
invite_code = InviteCode()
session.add(invite_code)
session.flush()
assert invite_code.used == 0
session.close()
session.begin()
user_row = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row)
session.commit()
invite_code = session.query(InviteCode).first()
assert invite_code.used == 0
session.begin()
invite_code.used = invite_code.used + 1
session.add(invite_code)
session.flush()
user_row_2 = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row_2)
rolled_back = False
try:
session.commit()
except:
rolled_back = True
session.rollback()
assert rolled_back
assert invite_code.used == 0
--
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
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 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] combining join with alias
On Jan 10, 2011, at 3:47 PM, Petra Clementson wrote: Okay, so I implemented join(A, B).alias(), but I can't seem to access the columns of the aliased table. Here is my code: session = sessionfactory() combined_1 = join(DiskFile, Header).alias() combined_2 = join(DiskFile, Header).alias() query = session.query(header).select_from(combined_1, combined_2).filter(DiskFile.canonical == True).filter(Header.datalab != 'none').filter(combined_1.datalab == combined_2.datalab) And I get: AttributeError: 'Alias' object has no attribute 'datalab' When you work with join() and alias() objects, you start using the SQL expression language. The objects are known as selectables and their namespace of column attributes is available via the .c. attribute. It's advisable to get a feel for SQL expression constructs via the tutorial: http://www.sqlalchemy.org/docs/core/tutorial.html . You can probably skim the first half of it then start looking at the sections on Aliases and Joins more closely. -- 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] Re: partial rollback in transaction ?
yah MySQL doesn't really operate with a mixture.
On Jan 10, 2011, at 4:13 PM, Romy wrote:
Face palm.. missed the forest for the trees.
Does this mean both tables would need to be InnoDB ?
On Jan 10, 7:10 am, Michael Bayer mike...@zzzcomputing.com wrote:
One important change here is to change the engine type to InnoDB, otherwise
transactions are entirely meaningless with MySQL.
If I use InnoDB, the end result of used is 0 in all cases. If I don't and
use MyISAM, the end result of used is 1 in all cases, regardless of
whether InviteCode is loaded before, or after, the begin().
It seems likely that the issue is simply that you forgot to use InnoDB.
On Jan 10, 2011, at 1:43 AM, Romy wrote:
Sorry Michael, 'self-contained' wasn't a proper term for that test
given that it required an initial DB state containing a single row.
I've modified your version to try and reproduce the bug.
Since yours didn't use elixir at all and I'm not familiar with
elixir's internals, I was able to reproduce only what I believe to be
the equivalent in sqlalchemy. Please note you'll need to create a
mysql database and fill in the connection string, as the test does not
fail with sqlite!
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
e = create_engine('mysql://user:p...@localhost/test', echo=True)
Base = declarative_base()
class InviteCode(Base):
__tablename__ = 'test_invite_codes'
id = Column(Integer, primary_key=True)
used = Column(Integer, default=0)
users = relationship(User, backref=invite_code)
class User(Base):
__tablename__ = 'test_users'
id = Column(Integer, primary_key=True)
invite_code_id = Column(Integer,
ForeignKey('test_invite_codes.id'))
email = Column(String(128), unique=True)
Base.metadata.create_all(e)
session = Session(e, autocommit=True)
session.query(User).delete()
session.query(InviteCode).delete()
invite_code = InviteCode()
session.add(invite_code)
session.flush()
assert invite_code.used == 0
session.close()
session.begin()
user_row = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row)
session.commit()
invite_code = session.query(InviteCode).first()
assert invite_code.used == 0
session.begin()
invite_code.used = invite_code.used + 1
session.add(invite_code)
session.flush()
user_row_2 = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row_2)
rolled_back = False
try:
session.commit()
except:
rolled_back = True
session.rollback()
assert rolled_back
assert invite_code.used == 0
--
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
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 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] Re: order_by(datetime)
Hi Michael, Thanks for your quick reply. I understand. I am using SQLite as the engine. I have not found the functions needed in the doc. I will leave this sort option open and stick with SQlite for a while. Frans Op 1/10/2011 4:03 PM, Michael Bayer schreef: you'd need to use SQL functions that break the datetime into its component parts, and order by them. such as: order_by(func.datepart(MONTH, my_date_col), func.datepart(DAY, my_date_col), func.datepart(YEAR, my_date_col)) datepart routines vary by database backend with very little cross compatibility - you'd have to consult the documentation for your database on the recommended way to break dates up into components. On Jan 10, 2011, at 5:35 AM, F.A.Pinkse wrote: Hi All. I have a datetime column in my model. If I do an .order_by I get year-month-day but how do I do an order_by to get month-day-year? or even a day-month-year Thanks, Frans. -- 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] Re: partial rollback in transaction ?
Not sure what you mean, as I've seen hybrid setups before.
In any case, thanks for helping me narrow this down. I need to decide
whether I'll need real transactions here. Despite being an oversight
on my part, do you think perhaps the docs for rollback / commit should
mention DB support ? I did look at them before writing to the list,
and it still didn't dawn on me.
On Jan 10, 1:19 pm, Michael Bayer mike...@zzzcomputing.com wrote:
yah MySQL doesn't really operate with a mixture.
On Jan 10, 2011, at 4:13 PM, Romy wrote:
Face palm.. missed the forest for the trees.
Does this mean both tables would need to be InnoDB ?
On Jan 10, 7:10 am, Michael Bayer mike...@zzzcomputing.com wrote:
One important change here is to change the engine type to InnoDB,
otherwise transactions are entirely meaningless with MySQL.
If I use InnoDB, the end result of used is 0 in all cases. If I don't
and use MyISAM, the end result of used is 1 in all cases, regardless of
whether InviteCode is loaded before, or after, the begin().
It seems likely that the issue is simply that you forgot to use InnoDB.
On Jan 10, 2011, at 1:43 AM, Romy wrote:
Sorry Michael, 'self-contained' wasn't a proper term for that test
given that it required an initial DB state containing a single row.
I've modified your version to try and reproduce the bug.
Since yours didn't use elixir at all and I'm not familiar with
elixir's internals, I was able to reproduce only what I believe to be
the equivalent in sqlalchemy. Please note you'll need to create a
mysql database and fill in the connection string, as the test does not
fail with sqlite!
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
e = create_engine('mysql://user:p...@localhost/test', echo=True)
Base = declarative_base()
class InviteCode(Base):
__tablename__ = 'test_invite_codes'
id = Column(Integer, primary_key=True)
used = Column(Integer, default=0)
users = relationship(User, backref=invite_code)
class User(Base):
__tablename__ = 'test_users'
id = Column(Integer, primary_key=True)
invite_code_id = Column(Integer,
ForeignKey('test_invite_codes.id'))
email = Column(String(128), unique=True)
Base.metadata.create_all(e)
session = Session(e, autocommit=True)
session.query(User).delete()
session.query(InviteCode).delete()
invite_code = InviteCode()
session.add(invite_code)
session.flush()
assert invite_code.used == 0
session.close()
session.begin()
user_row = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row)
session.commit()
invite_code = session.query(InviteCode).first()
assert invite_code.used == 0
session.begin()
invite_code.used = invite_code.used + 1
session.add(invite_code)
session.flush()
user_row_2 = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row_2)
rolled_back = False
try:
session.commit()
except:
rolled_back = True
session.rollback()
assert rolled_back
assert invite_code.used == 0
--
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
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 sqlalch...@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 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.
