SQLAlchemy tutorial

重要概念

• ORM：数据库对象 <--> class --> 类实例instance
• 定义数据库连接
• session：数据库事务通过session操作，flush()提交到内存但是未提交到数据库，commit()保存到数据库；

或者 What's the difference between flush() and commit()

定义映射

>>> from sqlalchemy import create_engine

>>> engine = create_engine('sqlite:///:memory:', echo=True)

>>> from sqlalchemy.ext.declarative import declarative_base

>>> Base = declarative_base()

>>> from sqlalchemy import Column, Integer, String

>>> class User(Base):

...     __tablename__ = 'users'

...     id = Column(Integer,  primary_key=True)

...     name = Column(String(50))

...     fullname = Column(String(50))

...     password = Column(String(12))

...     def __repr__(self):

...         return "<User(name='%s', fullname='%s', password='%s')>" % (self.nam

e, self.fullname, self.password)

...

class 定义

class User(Base):

    __tablename__ = 'users'

    id = Column(Integer,  primary_key=True)

    name = Column(String(50))

    fullname = Column(String(50))

    password = Column(String(12))

    def __repr__(self):

        return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password)

创建数据库

>>> Base.metadata.create_all(engine)

2015-12-29 15:56:59,029 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1

2015-12-29 15:56:59,030 INFO sqlalchemy.engine.base.Engine ()

2015-12-29 15:56:59,035 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1

2015-12-29 15:56:59,036 INFO sqlalchemy.engine.base.Engine ()

2015-12-29 15:56:59,039 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")

2015-12-29 15:56:59,039 INFO sqlalchemy.engine.base.Engine ()

2015-12-29 15:56:59,042 INFO sqlalchemy.engine.base.Engine

CREATE TABLE users (

        id INTEGER NOT NULL,

        name VARCHAR(50),

        fullname VARCHAR(50),

        password VARCHAR(12),

        PRIMARY KEY (id)

)

2015-12-29 15:56:59,043 INFO sqlalchemy.engine.base.Engine ()

2015-12-29 15:56:59,046 INFO sqlalchemy.engine.base.Engine COMMIT

添加数据

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')

>>> str(ed_user.id)

'None'

>>> from sqlalchemy.orm import sessionmaker

>>> Session = sessionmaker(bind=engine)

>>> session = Session()

>>> session.add(ed_user)

>>> our_user = session.query(User).filter_by(name='ed').first()

2015-12-29 15:57:48,361 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2015-12-29 15:57:48,364 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-29 15:57:48,365 INFO sqlalchemy.engine.base.Engine ('ed', 'Ed Jones', 'edspassword')

2015-12-29 15:57:48,369 INFO sqlalchemy.engine.base.Engine

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password

FROM users

WHERE users.name = ?

 LIMIT ? OFFSET ?

2015-12-29 15:57:48,371 INFO sqlalchemy.engine.base.Engine ('ed', 1, 0)

>>> our_user

<User(name='ed', fullname='Ed Jones', password='edspassword')>

>>> ed_user is our_user

True

>>> session.add_all([

...     User(name='wendy', fullname='Wendy Williams', password='foobar'),

...     User(name='mary', fullname='Mary Contrary', password='xxg527'),

...     User(name='fred', fullname='Fred Flinstone', password='blah')])

>>> ed_user.password = 'f8s7ccs'

>>> session.dirty

IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])

>>> session.new

IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>,

 <User(name='fred', fullname='Fred Flinstone', password='blah')>, <User(name='ma

ry', fullname='Mary Contrary', password='xxg527')>])

>>> session.commit()

2015-12-29 16:05:12,272 INFO sqlalchemy.engine.base.Engine UPDATE users SET password=? WHERE users.id = ?

2015-12-29 16:05:12,273 INFO sqlalchemy.engine.base.Engine ('f8s7ccs', 1)

2015-12-29 16:05:12,275 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-29 16:05:12,276 INFO sqlalchemy.engine.base.Engine ('wendy', 'Wendy Williams', 'foobar')

2015-12-29 16:05:12,278 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-29 16:05:12,279 INFO sqlalchemy.engine.base.Engine ('mary', 'Mary Contrary', 'xxg527')

2015-12-29 16:05:12,280 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-29 16:05:12,282 INFO sqlalchemy.engine.base.Engine ('fred', 'Fred Flinstone', 'blah')

2015-12-29 16:05:12,285 INFO sqlalchemy.engine.base.Engine COMMIT

>>> ed_user.id

2015-12-29 16:06:27,977 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2015-12-29 16:06:27,980 INFO sqlalchemy.engine.base.Engine SELECT users.id AS us

ers_id, users.name AS users_name, users.fullname AS users_fullname, users.passwo

rd AS users_password

FROM users

WHERE users.id = ?

2015-12-29 16:06:27,983 INFO sqlalchemy.engine.base.Engine (1,)

1

>>> ed_user.id

1

事务回滚 Rolling Back

>>> ed_user.name = 'Edwardo'

>>> fake_user = User(name='fakeuser', fullname='Invalid', password='12345')

>>> session.add(fake_user)

>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()

2015-12-29 16:10:52,517 INFO sqlalchemy.engine.base.Engine UPDATE users SET name=? WHERE users.id = ?

2015-12-29 16:10:52,519 INFO sqlalchemy.engine.base.Engine ('Edwardo', 1)

2015-12-29 16:10:52,520 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-29 16:10:52,522 INFO sqlalchemy.engine.base.Engine ('fakeuser', 'Invalid', '12345')

2015-12-29 16:10:52,528 INFO sqlalchemy.engine.base.Engine

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password

FROM users

WHERE users.name IN (?, ?)

2015-12-29 16:10:52,529 INFO sqlalchemy.engine.base.Engine ('Edwardo', 'fakeuser')

[<User(name='Edwardo', fullname='Ed Jones', password='f8s7ccs')>, <User(name='fa

keuser', fullname='Invalid', password='12345')>]

>>> session.rollback()

2015-12-29 16:11:14,276 INFO sqlalchemy.engine.base.Engine ROLLBACK

>>> ed_user.name

2015-12-29 16:11:20,378 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2015-12-29 16:11:20,381 INFO sqlalchemy.engine.base.Engine

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password

FROM users

WHERE users.id = ?

2015-12-29 16:11:20,384 INFO sqlalchemy.engine.base.Engine (1,)

u'ed'

>>> fake_user in session

False

>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()

2015-12-29 16:11:35,526 INFO sqlalchemy.engine.base.Engine

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password

FROM users

WHERE users.name IN (?, ?)

2015-12-29 16:11:35,529 INFO sqlalchemy.engine.base.Engine ('ed', 'fakeuser')

[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]