On Feb 12, 2013, at 9:21 AM, Oltmans <rolf.oltm...@gmail.com> wrote:
> Greetings, I trust everyone is doing well. > > Our code base uses SQLAlchemy and some of the old code uses expression > language style code e.g. > > > appts = Table("Appointment", META, autoload=True, autoload_with=DB) > statement = select([appts.c.appointmentId], and_( > appts.c.appointmentId == 212 > > > )) > results = select_all(statement) > > > where as some of our code uses declarative style classes e.g. > > class Appointment(Alchemy_Base, QueryMixin): > > __tablename__ = "Appointment" > appointmentId = Column(Integer, primary_key=True) > > @classmethod > def get_by_id(cls, appointment_id): > query = cls.query.filter_by(appointmentId=appointment_id) > return query.one() > > > Some of our scripts are going to use both of these files (both expression > style and declarative style) so my question is, is it (i.e. mixing two styles > of code) going to cause any sort of problems or we're going to be okay? > > I am asking because some people in our company are suggesting that we convert > all code into one format (devs want to convert expression style old code into > declarative style code). > > Kindly let me know your suggestions. BTW, we're using MySQL as database and > Python 2.6.4. Thanks in advance. OK well this answer got kind of long. This is the TLDR: "make sure your select_all() function is making use of the same Session that your Appointment.get_by_id() method is using.". If that make sense, then you're good to go. Ultimately your code would probably be more succinct and cleaner if you moved to an all ORM style, but as far as actual execution problems just sharing the Session as a point of execution is enough. With the TLDR given, here is the detailed explanation: There's one aspect to this style that is worth taking a look at, which is the scope of connection checkouts and transactions. It's not so critical that you're producing SQL queries using two different systems, what can cause problems in some situations is that those two sets of SQL queries are invoked using different connection contexts, and once the application wants to use both styles in an interactive way, but due to the use of many different connections simultaneously, the work of the two systems carries on in separate transactions which are isolated from each other and can produce conflicts. So in the abstract, what we are talking about is if you wrote a program like this (this is not SQLAlchemy code): connection = database.connect() connection.execute("statement 1") connection.execute("statement 2") connection.execute("statement 3") connection.commit() connection.close() Above, that's pretty simple, three statements on a connection, within a transaction. When we invoke statement 2, the work of statement 1 will be available to us, and invoking statement 3, we'll be able to see the work of statement 2 and 1 as well. Other parts of the program or other applications that are using the same database, will in all likelihood not see much of the effects of these three statements until we commit the transaction, which is due to a behavior known as transaction isolation. The one case you actually won't have any isolation is if your MySQL database is using MyISAM tables. If you're on MyISAM, most of what I'm writing about here probably doesn't matter because there's no real transactions present. But assuming you're on InnoDB, the above style is the best way to go. This is in contrast to this: conn1 = database.connect() conn1.execute("statement 1") conn2 = database.connect() conn2.execute("statement 2") conn2.commit() conn3 = database.connect() conn3.execute("statement 3") conn1.commit() conn3.commit() So above, we illustrate using three separate connections to emit the three statements, using three separate transactions. Just to make it even more disastrous I've interleaved the first and third transactions. If you're using InnoDB, the above system will be very prone to issues, assuming there's some degree of relationship between those three statements. The effects of statement1 won't be visible to that of statement2, nor will statement3 have any context. The risk of conflicts and locks is greatly increased as well. We're mixing up transactions and additionally putting a lot more burden on the database with three separate connections in use as well as three individual commits. So when using SQLAlchemy we want to make sure the way a series of steps proceeds is closer to the first version. When mixing the Core and ORM as you're doing, this is doable, but you need to invoke the Core and ORM queries using the same context. Normally, if you invoke Core statement like this: stmt = table.select() result = engine.execute(stmt) or even like this: result = stmt.execute() what we're using above is a feature called "implicit execution", where you can see we didn't check out any connection anywhere, or commit anything. The Engine actually did that for us, behind the scenes, and if our statement was an INSERT, UPDATE, or DELETE, it also "auto committed" the transaction; otherwise, the transaction is rolled back. So if we invoke a bunch of statements using engine-level execution, the effect is a little bit like style #2, where we have a lot of ad-hoc connections being checked out, each with their own transaction going on. Even the "interleaving" effect is possible with implicit execution if we're dealing with result objects, as the connection isn't released until we finish reading all rows. The popular "implicit execution" feature of Core is pretty different from how the ORM session works these days. When we have a Session like below: session = Session(bind=engine) data = session.query(SomeClass).all() session.flush() result = session.execute("some statement") otherresult = session.execute("some other statement") session.commit() the Session is keeping available a single connection on a single transaction all the way from when we first emitted SQL until we said "commit". That is, it is making it much more like style #1, rather than style #2. So when we mix up Core and ORM, it's best if we make sure that everyone coordinate on the Session object to execute statements, so that the mixture of SQL from those different sources all feeds into a single connection/transaction at a time on style #1: session = Session(bind=engine) appt = session.query(Appointment).filter_by(id=1).one() statement = select([appts.c.appt_id]).where(…) result = session.execute(statement) session.commit() session.close() Above, you'll see that I passed the Core select() statement to session.execute(), rather than passing it to the Engine directly or to another Connection. So the short answer to your original code example, is to make sure your select_all() function is making use of the same Session that your Appointment.get_by_id() method is using. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.