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
