On Feb 12, 2013, at 9:21 AM, Oltmans <[email protected]> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
