Sqlalchemy sqlの錬金術

SQLAlchemy SQLの錬金術
Plone Symposym Tokyo 2015
aodag

「Who Are You?」
Atsushi Odagiri
Use Python from 2001
Work at Be Proud, Inc.

What is SQLAlchemy?
Python Library for Data Accessing to RDBMS.
That has SQL Expression and OR Mapper.

Why I use SQLAlchemy
I Don’t Know Other OR Mapper with Python.
I Love SQL And Python!
SQLAlchemy has SQL Expression That makes
SQL Python Object.
SQLAlchemy doesn’t keep me away from SQL.

SQLAlchemy features
SQL Expression
Object-Relational Mapper
Unit of Work

SQL Expression(schema)
users = Table(“users”, metadata,
Column(‘id’, Integer, primary_key=True),
Column(‘first_name’, Unicode(255)),
Column(‘last_name’, Unicode(255)),
Column(‘company_id’, Integer,
ForeignKey(‘company.id’)))

SQL Expression (Insert)
users.insert().values(
first_name=u”Atsushi”,
last_name=u”Odagiri”,
)

SQL Expression(Select)
select([users]).where(
users.c.first_name==u’Atsushi’,
)

OR Mapper
class User(object):
def __init__(self, first_name, last_name):
self.first_name = first_name
self.last_name = last_name
@property
def full_name(self):
return self.first_name + self.last_name

OR Mapper
mapper(User, users)
user = Session.query(User).filter(
User.first_name == u’Atsushi’).first()
user.first_name = u’aodag’
Session.flush()

Unit Of Work
There are no save method.
Session object manages Object states.
That calls DML depending on the object state.

Unit Of Work
user = User(u’Atsushi’, u’Odagiri’)
Session.add(user)
user.company = Company(u’BeProud’)
Session.flush()
user.first_name = u’aodag’
Session.flush()

Getting Started
Install
Declare Models
Connect
Create Table
Query Model
Use Session

Install SQLAlchemy
pip install sqlalchemy
option:: Database Drivers
pip install psycopg2
pip install mysql-python
...

Declare Models (DataType)
from sqlalchemy import (
Colum,
Unicode,
Integer,
DateTime,
ForeignKey,
)

Declare Models (ORM)
from sqlalchemy.orm import (
relationship,
)
from sqlalchemy.ext.declarative import (
delcarative_base,
)

Declare Models (class)
Base = declarative_base()
class User(Base):
__tablename__ = ‘users’
...

Declare Models (Property)
id = Column(Integer, primary_key=True)
first_name = Column(Unicode(255))
last_name = Column(Unicode(255))

hybrid property
from sqlalchemy.ext.hybrid import (
hybrid_property,
)

@hybrid_property
def full_name(self):
return (self.first_name
+ “ “
+ self.last_name)

Connect
engine = create_engine(‘sqlite:///’)
connection url
postgresql://user:password@localhost/db
mysql+pymysql://user:
password@localhost/db

Create Table
Base.meatadata.create_all(bind=engine)

Query Model
from sqlalchemy.orm import (
scoped_session,
sessionmaker,
)
Session = scoped_session(sessionmaker())
Session.configure(bind=engine)

Query Model
aodag = Session.query(User).filter(
User.full_name == u’Atsushi Odagiri’
).one()

Session And Transaction
Session.remove()
Session.add(user)
Session.flush()
Session.commit()

With Web Application
Transaction Manager With zope.sqlalchemy
● repoze.tm2
● pyramid_tm
● Zope2 transaction

With Web Application
Transaction WSGI Middleware
@wsgify.middleware
def transaction_middleware(app, req, session):
try:
return req.get_response(app)
except:
session.rollback()
else:
session.commit()

With asyncio/aiopg
@async.coroutine
def get():
with (yeild from engine) as con:
res = yeild from con.execute(
select([users]).where(
users.c.first_name == u’Atsushi’)

Conclusion
SQLAlchemy has many features.
SQLAlchemy supports many kind of SQL
Statements.
That’s Great!

Bib.
● http://www.sqlalchemy.org/
● http://aiopg.readthedocs.org/
● http://repozetm2.readthedocs.org/
● https://pypi.python.org/pypi/zope.
sqlalchemy
● PEP 0249 -- Python Database API
Specification v2.0

Sqlalchemy sqlの錬金術

More Related Content

What's hot

Viewers also liked

Similar to Sqlalchemy sqlの錬金術

More from Atsushi Odagiri

Sqlalchemy sqlの錬金術