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

Marco Mariani · Accepted Answer · 2011-02-19 09:28:00Z

4

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

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

answered Feb 19, 2011 at 9:28

Marco Mariani

13.8k7 gold badges42 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sidewinder Over a year ago

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 Over a year ago

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...

Rocky Burt · Accepted Answer · 2011-03-06 17:26:25Z

3

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

answered Mar 6, 2011 at 17:26

Rocky Burt

4013 silver badges7 bronze badges

Comments

martin · Accepted Answer · 2014-08-15 14:18:37Z

0

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)

edited Aug 15, 2014 at 14:18

martin

3,2591 gold badge29 silver badges37 bronze badges

answered Aug 15, 2014 at 13:46

Mwesigwa Gilbert

511 silver badge8 bronze badges

Collectives™ on Stack Overflow

Raw MySQL with SQLAlchemy using Pyramid framework

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related