sqlalchemy(一)基本操作

连接数据库

在sqlalchemy中，session用于创建程序与数据库之间的会话。所有对象的载入和保存都需要通过session对象。

<code class="language-python hljs"><span class="hljs-keyword">from sqlalchemy <span class="hljs-keyword">import create_engine
<span class="hljs-keyword">from sqlalchemy.orm <span class="hljs-keyword">import sessionmaker
engine = create_engine(<span class="hljs-string">‘mysql://user:passwd@ip:port/db‘, echo=<span class="hljs-keyword">True)
Session = sessionmaker(bind=engine)
session = Session()
session.execute(<span class="hljs-string">‘show databases‘)
</span></span></span></span></span></span></span></code>

其中，echo为True代表打开logging。

创建一个映射

一个映射对应着一个Python类，用来表示一个表的结构。下面创建一个person表，包括id和name两个字段。

<code class="language-python hljs"><span class="hljs-keyword">from sqlalchemy.ext.declarative <span class="hljs-keyword">import declarative_base
Base = declarative_base()
<span class="hljs-class"><span class="hljs-keyword">class <span class="hljs-title">Person<span class="hljs-params">(Base):
    __tablename__ = <span class="hljs-string">‘person‘
    id   = Column(Integer, primary_key=<span class="hljs-keyword">True)
    name = Column(String(<span class="hljs-number">32))
    <span class="hljs-function"><span class="hljs-keyword">def <span class="hljs-title">__repr__<span class="hljs-params">(self):
        <span class="hljs-keyword">return <span class="hljs-string">"<Person(name=‘%s‘)>" % self.name
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

添加数据

<code class="language-python hljs"><span class="hljs-comment">#创建一个person对象
person = Person(name=<span class="hljs-string">‘jack‘)
<span class="hljs-comment">#添加person对象，但是仍然没有commit到数据库
session.add(person)
<span class="hljs-comment">#commit操作
session.commit()
</span></span></span></span></code>

如何获取id的？

<code class="language-python hljs"><span class="hljs-meta">>>> person = Person(name=<span class="hljs-string">‘ilis‘)
<span class="hljs-meta">>>> person.id <span class="hljs-comment">#此时还没有commit到mysql,因此无id
<span class="hljs-meta">>>> session.add(person)
<span class="hljs-meta">>>> person.id <span class="hljs-comment">#同上
<span class="hljs-meta">>>> session.commit()
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">08:<span class="hljs-number">23,<span class="hljs-number">530 INFO sqlalchemy.engine.base.Engine INSERT INTO person (name) VALUES (%s)
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">08:<span class="hljs-number">23,<span class="hljs-number">531 INFO sqlalchemy.engine.base.Engine (<span class="hljs-string">‘ilis‘,)
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">08:<span class="hljs-number">23,<span class="hljs-number">532 INFO sqlalchemy.engine.base.Engine COMMIT
<span class="hljs-meta">>>> person.id <span class="hljs-comment">#commit后，可以获取该对象的id
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">08:<span class="hljs-number">27,<span class="hljs-number">556 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">08:<span class="hljs-number">27,<span class="hljs-number">557 INFO sqlalchemy.engine.base.Engine SELECT person.id AS person_id, person.name AS person_name
FROM person
WHERE person.id = %s
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">08:<span class="hljs-number">27,<span class="hljs-number">557 INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">5L,)
<span class="hljs-number">5L
>>>
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

添加多个数据

<code class="language-python hljs">session.add_all([
    Person(name=<span class="hljs-string">‘jack‘),
    Person(name=<span class="hljs-string">‘mike‘)
])
session.commit()
</span></span></code>

回滚

<code class="language-python hljs"><span class="hljs-meta">>>> person = Person(name=<span class="hljs-string">‘test‘)
<span class="hljs-meta">>>> session.add(person)
<span class="hljs-meta">>>> session.query(person).filter(name==<span class="hljs-string">‘test‘)
<span class="hljs-meta">>>> session.query(Person).filter(Person.name==<span class="hljs-string">‘test‘).all()
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">13:<span class="hljs-number">23,<span class="hljs-number">265 INFO sqlalchemy.engine.base.Engine INSERT INTO person (name) VALUES (%s)
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">13:<span class="hljs-number">23,<span class="hljs-number">265 INFO sqlalchemy.engine.base.Engine (<span class="hljs-string">‘test‘,)
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">13:<span class="hljs-number">23,<span class="hljs-number">267 INFO sqlalchemy.engine.base.Engine SELECT person.id AS person_id, person.name AS person_name
FROM person
WHERE person.name = %s
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">13:<span class="hljs-number">23,<span class="hljs-number">267 INFO sqlalchemy.engine.base.Engine (<span class="hljs-string">‘test‘,)
[<demo.Person object at <span class="hljs-number">0x7f4e37730510>]
<span class="hljs-meta">>>> session.rollback()
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">13:<span class="hljs-number">37,<span class="hljs-number">496 INFO sqlalchemy.engine.base.Engine ROLLBACK
<span class="hljs-meta">>>> session.query(Person).filter(Person.name==<span class="hljs-string">‘test‘).all()
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">13:<span class="hljs-number">38,<span class="hljs-number">690 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">13:<span class="hljs-number">38,<span class="hljs-number">691 INFO sqlalchemy.engine.base.Engine SELECT person.id AS person_id, person.name AS person_name
FROM person
WHERE person.name = %s
<span class="hljs-number">2015<span class="hljs-number">-08<span class="hljs-number">-18 <span class="hljs-number">23:<span class="hljs-number">13:<span class="hljs-number">38,<span class="hljs-number">692 INFO sqlalchemy.engine.base.Engine (<span class="hljs-string">‘test‘,)
[]
>>>
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

数据查询

使用Session的query()方法。

<code class="language-python hljs"><span class="hljs-comment">#获取所有数据
session.query(Person).all()
<span class="hljs-comment">#获取某一列数据,类似于django的get,如果返回数据为多个则报错
session.query(Person).filter(Person.name==<span class="hljs-string">‘jack‘).one()
<span class="hljs-comment">#获取返回数据的第一行
session.query(Person).first()
<span class="hljs-comment">#过滤数据
session.query(Person.name).filter(Person.id><span class="hljs-number">1).all()
<span class="hljs-comment">#limit
session.query(Person).all()[<span class="hljs-number">1:<span class="hljs-number">3]
<span class="hljs-comment">#order by
session.query(Person).ordre_by(-Person.id)
<span class="hljs-comment">#equal/like/in
query = session.query(Person)
query.filter(Person.id==<span class="hljs-number">1).all()
query.filter(Person.id!=<span class="hljs-number">1).all()
query.filter(Person.name.like(<span class="hljs-string">‘%ac%‘)).all()
query.filter(Person.id.in_([<span class="hljs-number">1,<span class="hljs-number">2,<span class="hljs-number">3])).all()
query.filter(~Person.id.in_([<span class="hljs-number">1,<span class="hljs-number">2,<span class="hljs-number">3])).all()
query.filter(Person.name==<span class="hljs-keyword">None).all()
<span class="hljs-comment">#and or
<span class="hljs-keyword">from sqlalchemy <span class="hljs-keyword">import and_
query.filter(and_(Person.id==<span class="hljs-number">1, Person.name==<span class="hljs-string">‘jack‘)).all()
query.filter(Person.id==<span class="hljs-number">1, Person.name==<span class="hljs-string">‘jack‘).all()
query.filter(Person.id==<span class="hljs-number">1).filter(Person.name==<span class="hljs-string">‘jack‘).all()
<span class="hljs-keyword">from sqlalchemy <span class="hljs-keyword">import or_
query.filter(or_(Person.id==<span class="hljs-number">1, Person.id==<span class="hljs-number">2)).all()
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

使用text

<code class="language-python hljs"><span class="hljs-keyword">from sqlalchemy <span class="hljs-keyword">import text
query.filter(text(<span class="hljs-string">"id>1")).all()
query.filter(Person.id><span class="hljs-number">1).all() <span class="hljs-comment">#同上
query.filter(text(<span class="hljs-string">"id>:id")).params(id=<span class="hljs-number">1).all() <span class="hljs-comment">#使用:，params来传参
query.from_statement(
    text(<span class="hljs-string">"select * from person where name=:name")).    params(name=<span class="hljs-string">‘jack‘).all()
</span></span></span></span></span></span></span></span></span></span></code>

计数

Query使用count()函数来实现查询计数。

<code class="language-python hljs">query.filter(Person.id><span class="hljs-number">1).count()
</span></code>

group by的用法

<code class="language-python hljs"><span class="hljs-keyword">from sqlalchemy <span class="hljs-keyword">import func
session.query(func.count(Person.name), Person.name),group_by(Person.name).all()
</span></span></code>

实现count(*)来查询表内行数

<code class="language-python hljs">session.query(func.count(<span class="hljs-string">‘*‘)).select_from(Person).scalar()
session.query(func.count(Person.id)).scalar()</span></code>

sqlalchemy(一)基本操作

标签：elf   查询   ssi   基本   create   first   values   %s   exe