Raw MySQL with SQLAlchemy using Pyramid framework

Question

I have recently made a decision to start using the Pyramid (python web framework) for my projects from now on.

I have also decided to use SQLalchemy, and I want to use raw MySQL (personal reasons) but still keep the ORM features.

The first part of the code in models.py reads:

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()

Now from here how do I exectue a query for CREATE TABLE using raw MySQL.

the traditional SQLalchemy way would be:

class Page(Base):
 __tablename__ = 'pages'
 id = Column(Integer, primary_key=True)
 name = Column(Text, unique=True)
 data = Column(Text)
def __init__(self, name, data):
 self.name = name
 self.data = data

Answer 1

DBSession.execute('CREATE TABLE ....')

Have a look at sqlalchemy.text() for parametrized queries.

• sidewinder: Thanks for your reply Marco. I have tried the following: DBSession.execute( """ CREATE TABLE ..... """ ) and recieve the following error: UnboundExecutionError: Could not locate a bind configured on SQL expression or this Session
• Marco Mariani: yes, you have to call DBSession.configure(bind=get_engine(...)) or something like that, but I'm not into pyramid yet and don't know how it is set up. I usually create the schema in pure sql then call reflect() on the connection...

Answer 2

My own biased suggestion would be to use http://pypi.python.org/pypi/khufu_sqlalchemy to setup the sqlalchemy engine.

Then inside a pyramid view you can do something like:

from khufu_sqlalchemy import dbsession
db = dbsession(request)
db.execute("select * from table where id=:id", {'id':7})

Answer 3

Inside the views.py if you are adding form elements, first create an object of the database.

In your snippet, do it as

pg = Page() 

and add it with

DBSession.add(pg) 

for all the form elements you want to add e.g name and data from your snippet.

the final code would be similar to:

 pg = Page()
 name = request.params['name']
 data = request.params['data']
 DBSession.add(pg)