Setting up Pyramid to use MySQL raw instead of SQLAlchemy

Question

We're trying to set up a Pyramid project that will use MySQL instead of SQLAlchemy.

My experience with Pyramid/Python is limited, so I was hoping to find a guide online. Unfortunately, I haven't been able to find anything to push us in the right direction. Most search results were for people trying to use raw SQL/MySQL commands with SQLAlchemy (many were re-posted links).

Anyone have a useful tutorial on this?

• Mark Hildreth: I'm assuming that "Raw" SQL means at least using the DB-API compliant library, which should handle proper parameter substitution, AFAIK.
• webjunkie: Mark is right you can still use raw sql with execute and SQLAlchemy will handle parameter substitution correctly and preventing sql injection. i.e. data = con.execute("select id from table where name=%(client_name)s",client_name=somevalue)
• Mark Hildreth: Actually, what I was saying is that you should be able to get correctly quoted parameter substitution to work without the need for SQLAlchemy by just using the DB-API compliant MySQLdb library. I have not used this library, but I assume it does what other DB-API libraries do, which include proper parameter substitution.

Answer 1

Pyramid at its base does not assume that you will use any one specific library to help you with your persistence. In order to make things easier, then, for people who DO wish to use libraries such as SQLALchemy, the Pyramid library contains Scaffolding, which is essentially some auto-generated code for a basic site, with some additions to set up items like SQLAlchemy or a specific routing strategy. The pyramid documentation should be able to lead you through creating a new project using the "pyramid_starter" scaffolding, which sets up the basic site without SQLAlchemy.

This will give you the basics you need to set up your views, but next you will need to add code to allow you to connect to a database. Luckily, since your site is just python code, learning how to use MySQL in Pyramid is simply learning how to use MySQL in Python, and then doing the exact same steps within your Pyramid project.

Keep in mind that even if you'd rather use raw SQL queries, you might still find some usefulness in SQLAlchemy. At it's base level, SQLAlchemy simply wraps around the DBAPI calls and adds in useful features like connection pooling. The ORM functionality is actually a large addition to the tight lower-level SQLAlchemy toolset.

Answer 2

sqlalchemy does not make any assumption that you will be using it's orm. If you wish to use plain sql, you can do so, with nothing more than what sqlalchemy already provides. For instance, if you followed the recipe in the cookbook, you would have access to the sqlalchemy session object as request.db, your handler would look something like this:

def someHandler(request):
 rows = request.db.execute("SELECT * FROM foo").fetchall()

Answer 3

The Quick Tutorial shows a Pyramid application that uses SQL but not SQLAlchemy. It uses SQLite, but should be reasonably easy to adapt for MySQL.