On Nov 22, 2010, at 5:38 AM, A. S. wrote: > Hi! > > at the company I work for I'd like to propose introducing Python to > replace the hopelessly outdated SAS scripts. SQLAlchemy seems like a > good option for SQL handling and I can imagine modules specifically > tailored to our needs. > However, the other guys are slightly conservative and might object > they are fine using simple SQL and SAS. Actually they are not, because > even simple tasks take hours of work. And our project work requires > solving many ad hoc tasks which our system isn't really able to > handle. > Now I'd like to point out advantages of the SQLAlchemy approach, but I > only vaguely know them. > > Could you pin point the advantages of using (python classes)/(the > sqlalchemy system) for database toolboxes? > Something simple, maybe with example ideas that I could present? > [I know there is a small paragraph in the documentation, but it only > says an ORM couldn't be written otherwise. Unfortunately I don't know > well enough why to use an ORM. Actually I was thinking about the > expression language.]
I don't have any example code, but I have written several Python applications (and one C++ app) using plain SQL and have started work on a new app using SQLAlchemy, so I'll share my experience. Let me add that I spent several years maintaining a moderately sized SQL database and wrote lots of pure SQL, though not necessarily using Python. As my programs accessed SQL, I found myself writing a lot of SQL code to access the data. A lot of this code while not identical, was very similar and seemed redundant. For example, take a simple single table lookup. If you want to do simple CRUD (create, update & delete), you have to write at least three separate SQL statements for each table. While the skeleton of these SQL statements are similar, the specific column names and the table name are different. I ended up writing some Python routines that would build the SQL statements if I supplied a list of columns and the table name. But this is what SQLAlchemy does (and much more) so why reinvent the wheel? In the case of my C++ app (I hadn't found a suitable ORM), I ended up writing a Python script to generate SQL statements and C++ code to access the tables. Another advantage is the relative ease with which you can handle changes to the database structure. As I was developing my SA app, I realized that I needed a new column in (at least) one of my tables. I'm using SA's declarative approach and I only had to add the column to my declarative. I didn't have to change any SQL or Python code. One "problem" I have had with SQLAlchemy is unlearning the way I did things with pure SQL. At it's simplest level, SQLAlchemy can generate the SQL and Python code to access single tables and you might be tempted to write Python code to merge this kind of single table access into larger data graphs, but the true power (IMHO) of SQLAlchemy is that it can handle complex data graphs automatically. Mark -- 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.