diff options
Diffstat (limited to 'app/static/doc/flask-docs/_sources/patterns/sqlalchemy.txt')
| -rw-r--r-- | app/static/doc/flask-docs/_sources/patterns/sqlalchemy.txt | 214 |
1 files changed, 0 insertions, 214 deletions
diff --git a/app/static/doc/flask-docs/_sources/patterns/sqlalchemy.txt b/app/static/doc/flask-docs/_sources/patterns/sqlalchemy.txt deleted file mode 100644 index 5a33d1f..0000000 --- a/app/static/doc/flask-docs/_sources/patterns/sqlalchemy.txt +++ /dev/null @@ -1,214 +0,0 @@ -.. _sqlalchemy-pattern: - -SQLAlchemy in Flask -=================== - -Many people prefer `SQLAlchemy`_ for database access. In this case it's -encouraged to use a package instead of a module for your flask application -and drop the models into a separate module (:ref:`larger-applications`). -While that is not necessary, it makes a lot of sense. - -There are four very common ways to use SQLAlchemy. I will outline each -of them here: - -Flask-SQLAlchemy Extension --------------------------- - -Because SQLAlchemy is a common database abstraction layer and object -relational mapper that requires a little bit of configuration effort, -there is a Flask extension that handles that for you. This is recommended -if you want to get started quickly. - -You can download `Flask-SQLAlchemy`_ from `PyPI -<http://pypi.python.org/pypi/Flask-SQLAlchemy>`_. - -.. _Flask-SQLAlchemy: http://packages.python.org/Flask-SQLAlchemy/ - - -Declarative ------------ - -The declarative extension in SQLAlchemy is the most recent method of using -SQLAlchemy. It allows you to define tables and models in one go, similar -to how Django works. In addition to the following text I recommend the -official documentation on the `declarative`_ extension. - -Here the example `database.py` module for your application:: - - from sqlalchemy import create_engine - from sqlalchemy.orm import scoped_session, sessionmaker - from sqlalchemy.ext.declarative import declarative_base - - engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True) - db_session = scoped_session(sessionmaker(autocommit=False, - autoflush=False, - bind=engine)) - Base = declarative_base() - Base.query = db_session.query_property() - - def init_db(): - # import all modules here that might define models so that - # they will be registered properly on the metadata. Otherwise - # you will have to import them first before calling init_db() - import yourapplication.models - Base.metadata.create_all(bind=engine) - -To define your models, just subclass the `Base` class that was created by -the code above. If you are wondering why we don't have to care about -threads here (like we did in the SQLite3 example above with the -:data:`~flask.g` object): that's because SQLAlchemy does that for us -already with the :class:`~sqlalchemy.orm.scoped_session`. - -To use SQLAlchemy in a declarative way with your application, you just -have to put the following code into your application module. Flask will -automatically remove database sessions at the end of the request for you:: - - from yourapplication.database import db_session - - @app.teardown_request - def shutdown_session(exception=None): - db_session.remove() - -Here is an example model (put this into `models.py`, e.g.):: - - from sqlalchemy import Column, Integer, String - from yourapplication.database import Base - - class User(Base): - __tablename__ = 'users' - id = Column(Integer, primary_key=True) - name = Column(String(50), unique=True) - email = Column(String(120), unique=True) - - def __init__(self, name=None, email=None): - self.name = name - self.email = email - - def __repr__(self): - return '<User %r>' % (self.name) - -To create the database you can use the `init_db` function: - ->>> from yourapplication.database import init_db ->>> init_db() - -You can insert entries into the database like this: - ->>> from yourapplication.database import db_session ->>> from yourapplication.models import User ->>> u = User('admin', 'admin@localhost') ->>> db_session.add(u) ->>> db_session.commit() - -Querying is simple as well: - ->>> User.query.all() -[<User u'admin'>] ->>> User.query.filter(User.name == 'admin').first() -<User u'admin'> - -.. _SQLAlchemy: http://www.sqlalchemy.org/ -.. _declarative: - http://www.sqlalchemy.org/docs/orm/extensions/declarative.html - -Manual Object Relational Mapping --------------------------------- - -Manual object relational mapping has a few upsides and a few downsides -versus the declarative approach from above. The main difference is that -you define tables and classes separately and map them together. It's more -flexible but a little more to type. In general it works like the -declarative approach, so make sure to also split up your application into -multiple modules in a package. - -Here is an example `database.py` module for your application:: - - from sqlalchemy import create_engine, MetaData - from sqlalchemy.orm import scoped_session, sessionmaker - - engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True) - metadata = MetaData() - db_session = scoped_session(sessionmaker(autocommit=False, - autoflush=False, - bind=engine)) - def init_db(): - metadata.create_all(bind=engine) - -As for the declarative approach you need to close the session after -each request. Put this into your application module:: - - from yourapplication.database import db_session - - @app.teardown_request - def shutdown_session(exception=None): - db_session.remove() - -Here is an example table and model (put this into `models.py`):: - - from sqlalchemy import Table, Column, Integer, String - from sqlalchemy.orm import mapper - from yourapplication.database import metadata, db_session - - class User(object): - query = db_session.query_property() - - def __init__(self, name=None, email=None): - self.name = name - self.email = email - - def __repr__(self): - return '<User %r>' % (self.name, self.email) - - users = Table('users', metadata, - Column('id', Integer, primary_key=True), - Column('name', String(50), unique=True), - Column('email', String(120), unique=True) - ) - mapper(User, users) - -Querying and inserting works exactly the same as in the example above. - - -SQL Abstraction Layer ---------------------- - -If you just want to use the database system (and SQL) abstraction layer -you basically only need the engine:: - - from sqlalchemy import create_engine, MetaData - - engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True) - metadata = MetaData(bind=engine) - -Then you can either declare the tables in your code like in the examples -above, or automatically load them:: - - users = Table('users', metadata, autoload=True) - -To insert data you can use the `insert` method. We have to get a -connection first so that we can use a transaction: - ->>> con = engine.connect() ->>> con.execute(users.insert(name='admin', email='admin@localhost')) - -SQLAlchemy will automatically commit for us. - -To query your database, you use the engine directly or use a connection: - ->>> users.select(users.c.id == 1).execute().first() -(1, u'admin', u'admin@localhost') - -These results are also dict-like tuples: - ->>> r = users.select(users.c.id == 1).execute().first() ->>> r['name'] -u'admin' - -You can also pass strings of SQL statements to the -:meth:`~sqlalchemy.engine.base.Connection.execute` method: - ->>> engine.execute('select * from users where id = :1', [1]).first() -(1, u'admin', u'admin@localhost') - -For more information about SQLAlchemy, head over to the -`website <http://sqlalchemy.org/>`_. |