Raw MySQL with SQLAlchemy using Pyramid framework

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 SQLal开发者_运维知识库chemy 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

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

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

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})

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)