How to set up global connect to datebase in pylons(python), sqlalchemy.

Question

Hi!

I just start lern python, pylons.

i have problem with setting up datebase connection.

i won't to set connection, where i can see this connection in all my controllers.

Now i use: some thing like this in my controller:

 45 '   db = create_engine('mysql://root:password@localhost/python')
 46 '   metadata = MetaData(db)
 47 
 48 '   email_list = Table('email',metadata,autoload=True)

in development.ini i have: 44 sqlalchemy.url = mysql://root@password@localhost/python 45 sqlalchemy.pool_recycle = 3600

and now, pleas help me to set __init__.py

Answer 1

What you want to do is modify the Globals class in your app_globals.py file to include a .engine (or whatever) attribute. Then, in your controllers, you use from pylons import app_globals and app_globals.engine to access the engine (or metadata, session, scoped_session, etc...).

Answer 2

I hope you got pylons working; for anyone else that may later read question I'll present some pointers in the right direction.

First of all, you are only creating a engine and a metadata object. While you can use the engine to create connections directly you would almost always use a Session to manage querying and updating your database.

Pylons automatically setups this for you by creating a engine from your configuration file, then passing it to yourproject.model.__init__.py:init_model() which binds it to a scoped_session object.

This scoped_session object is available from yourproject.model.meta and is the object you would use to query your database. For example:

record = meta.Session.query(model.MyTable).filter(id=42)

Because it is a scoped_session it automatically creates a Session object and associates it with the current thread if it doesn't already exists. Scoped_session passes all action (.query(), .add(), .delete()) down into the real Session object and thus allows you a simple way to interact the database with having to manage the non-thread-safe Session object explicitly.

The scoped_session, Session, object from yourproject.model.meta is automatically associated with a metadata object created as either yourproject.model.meta:metadata (in pylons 0.9.7 and below) or yourproject.model.meta:Base.metadata (in pylons 1.0). Use this metadata object to define your tables. As you can see in newer versions of pylons a metadata is associated with a declarative_base() object named Base, which allows you to use SqlAlchemy's declarative style.

Using this from the controller

from yourproject import model
from yourproject.model import Session

class MyController(..):

    def resource(self):
        result = Session.query(model.email_list).\
            filter(model.email_list.c.id=42).one()
        return str(result)

Use real connections

If you really want to get a connection object simply use

from yourproject.model import Session
connection = Session.connection()
result = connection.execute("select 3+4;")
// more connection executions
Session.commit()

However this is all good, but what you should be doing is...

This leaves out that you are not really using SqlAlchemy much. The power of SqlAlchemy really shines when you start mapping your database tables to python classes. So anyone looking into using pylons with a database should take a serious look at what you can do with SqlAlchemy. If SqlAlchemy starts out intimidating simply start out with using its declarative approach, which should be enough for almost all pylons apps.

In your model instead of defining Table constructs, do this:

from sqlalchemy import Column, Integer, Unicode, ForeignKey
from sqlalchemy.orm import relation
from yourproject.model.meta import Base

class User(Base):
    __tablename__ = 'users'

    # primary_key implies nullable=False
    id = Column(Integer, primary_key=True, index=True) 
    # nullable defaults to True
    name = Column(Unicode, nullable=False)

    notes = relation("UserNote", backref="user")

    query = Session.query_property()


class UserNote(Base):
    __tablename__ = 'usernotess'

    # primary_key implies nullable=False
    id = Column(Integer, primary_key=True, index=True) 
    userid = Column(Integer, index=True, ForeignKey("User.id"))
    # nullable defaults to True
    text = Column(Unicode, nullable=False)

    query = Session.query_property()

Note the query objects. These are smart object that live on the class and associates your classes with the scoped_session(), Session. This allows you to event more easily extract data from your database.

from sqlalchemy.orm import eagerload

def resource(self):
    user = User.query.filter(User.id==42).options(eagerload("notes")).one()
    return "\n".join([ x.text for x in user.notes ])

Answer 3

1.0 version of Pylons use declarative syntax. More about this, you can see here .

In mode/init.py you can write somthing like this:

from your_programm.model.meta import Session, Base
from sqlalchemy import *
from sqlalchemy.types import *

def init_model(engine):
     Session.configure(bind=engine)

class Foo(Base) :
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    ...