Python中使用SQLAlchemy连接Mysql数据库（单表操作）-mysql教程-PHP中文網

首頁

資料庫

mysql教程

Python中使用SQLAlchemy连接Mysql数据库（单表操作）

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:50 PM

mysqlpythonsqlalchemy使用連接

一，SQLAlchemy的安装使用 $ easy_install sqlalchemy或 $ pip install sqlalchemy 如果出现什么错，就进去root用户下进行安装试试，或者网上查查 import sqlalchemy 这样说明成功了，切记是小写哦二，使用理论我也不懂，自己查查资料，现在用一个小的案

一，SQLAlchemy的安装
使用

<code class=" hljs ruby"><span class="hljs-variable">$ </span>easy_install sqlalchemy
或
<span class="hljs-variable">$ </span>pip install sqlalchemy</code>

如果出现什么错，就进去root用户下进行安装试试，或者网上查查

<code class=" hljs python"><span class="hljs-prompt">>>> </span><span class="hljs-keyword">import</span> sqlalchemy
<span class="hljs-prompt">>>> </span></code>

这样说明成功了，切记是小写哦
二，使用
理论我也不懂，自己查查资料，现在用一个小的案例说一下使用步骤
1，在进行数据操作之前要先连上数据库。

<code class=" hljs python"><span class="hljs-prompt">>>> </span><span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> create_engine
<span class="hljs-prompt">>>> </span><span class="hljs-keyword">from</span> sqlalchemy.orm <span class="hljs-keyword">import</span> sessionmaker  
<span class="hljs-prompt">>>> </span>DB_CONNECT = <span class="hljs-string">'mysql+mysqldb://root:102@localhost/mydb'</span>
<span class="hljs-prompt">>>> </span>engine = create_engine(DB_CONNECT, echo=<span class="hljs-keyword">True</span>)
<span class="hljs-prompt">>>> </span>DB_Session = sessionmaker(bind=engine)
<span class="hljs-prompt">>>> </span>session = DB_Session()
</code>

from 是从sqlalchemy中插入必须的模板，DB_CONNECT 是构造数据库的路径，mysql+mysqldb是说明使用MySQL-Python 来连接，root是数据库用户名，102是密码，localhost表示是数据库在本机上，mydb是要连接的数据库名字，设置字符集的charset可以省了
create_engine() 会返回一个数据库引擎，echo 参数为 True 时，会显示每条执行的 SQL 语句，生产环境下可关闭。
sessionmaker(bind=engine)会生成一个数据库会话类。这个类的实例可以当成一个数据库连接，它同时还记录了一些查询的数据，并决定什么时候执行 SQL 语句。由于 SQLAlchemy 自己维护了一个数据库连接池（默认 5 个连接），也可以自己设置。
得到session 后，就可以执行 SQL 了：
2，在进行操作前先把表给建立了，由于SQLAlchemy 可以和变进行建立连接并且可以通过语言进行见表

<code class=" hljs avrasm">mysql> show tables<span class="hljs-comment">;</span>
Empty <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> <span class="hljs-keyword">sec</span>)
mysql> </code>

此时是没有表的，现在我们建立一个学生便stu，一个课程表cla和一个成绩表grade

<code class=" hljs r">>>> from sqlalchemy import Column
>>> from sqlalchemy.types import CHAR, Integer, String
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from random import randint
>>> from sqlalchemy import ForeignKey
>>> BaseModel = declarative_base()
>>> def init_db():
<span class="hljs-keyword">...</span>     BaseModel.metadata.create_all(engine)
<span class="hljs-keyword">...</span> 
>>> def drop_db():
<span class="hljs-keyword">...</span>     BaseModel.metadata.drop_all()
<span class="hljs-keyword">...</span> 
>>> class Stu(BaseModel):
<span class="hljs-keyword">...</span>     __tablename__=<span class="hljs-string">'stu'</span>
<span class="hljs-keyword">...</span>     id = Column(Integer,primary_key = True)
<span class="hljs-keyword">...</span>     name = Column(CHAR(<span class="hljs-number">30</span>))
<span class="hljs-keyword">...</span> 
>>> class Cla(BaseModel):
<span class="hljs-keyword">...</span>     __tablename__=<span class="hljs-string">'cla'</span>
<span class="hljs-keyword">...</span>     id = Column(Integer,primary_key = True)设置主键
<span class="hljs-keyword">...</span>     cname = Column(CHAR(<span class="hljs-number">30</span>))
<span class="hljs-keyword">...</span> 
>>> class Grade(BaseModel):
<span class="hljs-keyword">...</span>     __tablename__ = <span class="hljs-string">'grade'</span>
<span class="hljs-keyword">...</span>     uid = Column(Integer,ForeignKey(<span class="hljs-string">'stu.id'</span>))设置外键
<span class="hljs-keyword">...</span>     cid = Column(Integer,ForeignKey(<span class="hljs-string">'cla.id'</span>))
<span class="hljs-keyword">...</span>     id = Column(Integer,primary_key=True)
<span class="hljs-keyword">...</span>     gre=Column(Integer)
<span class="hljs-keyword">...</span> </code>

declarative_base() 创建了一个 BaseModel 类，这个类的子类可以自动与一个表关联。以 Stu 类为例，它的 tablename 属性就是数据库中该表的名称，它有 id 和 name 这两个字段，分别为整型和 30 个定长字符。Column 还有一些其他的参数，我就不解释了。
最后，BaseModel.metadata.create_all(engine) 会找到 BaseModel 的所有子类，并在数据库中建立这些表；drop_all() 则是删除这些表。
现在执行init_db()进行建立表，对应语句如下

<code class=" hljs vbnet">>>> init_db()
CREATE TABLE stu (
    id <span class="hljs-built_in">INTEGER</span> <span class="hljs-keyword">NOT</span> NULL AUTO_INCREMENT, 
    name <span class="hljs-built_in">CHAR</span>(<span class="hljs-number">30</span>), 
    PRIMARY <span class="hljs-keyword">KEY</span> (id)
)

CREATE TABLE cla (
    id <span class="hljs-built_in">INTEGER</span> <span class="hljs-keyword">NOT</span> NULL AUTO_INCREMENT, 
    cname <span class="hljs-built_in">CHAR</span>(<span class="hljs-number">30</span>), 
    PRIMARY <span class="hljs-keyword">KEY</span> (id)
)
CREATE TABLE grade (
    id <span class="hljs-built_in">INTEGER</span> <span class="hljs-keyword">NOT</span> NULL AUTO_INCREMENT, 
    uid <span class="hljs-built_in">INTEGER</span>, 
    cid <span class="hljs-built_in">INTEGER</span>, 
    gre <span class="hljs-built_in">INTEGER</span>, 
    PRIMARY <span class="hljs-keyword">KEY</span> (id), 
    FOREIGN <span class="hljs-keyword">KEY</span>(uid) REFERENCES stu (id), 
    FOREIGN <span class="hljs-keyword">KEY</span>(cid) REFERENCES cla (id)
)
COMMIT
>>> </code>

以上就是执行时对应的建表语句，现在去数据库看看表是否存在，并查看一个表结构

<code class=" hljs asciidoc"><span class="hljs-header">mysql> show tables;
+----------------+</span>
<span class="hljs-header">| Tables_in_mydb |
+----------------+</span>
| cla            |
| grade          |
<span class="hljs-header">| stu            |
+----------------+</span>
3 rows in set (0.00 sec)
</code>

表已经建立成功了，现在看一下表结构

<code class=" hljs asciidoc"><span class="hljs-header">mysql> desc grade;
+-------+---------+------+-----+---------+----------------+</span>
<span class="hljs-header">| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+</span>
| id    | int(11) | NO   | PRI | NULL    | auto<span class="hljs-emphasis">_increment |
| uid   | int(11) | YES  | MUL | NULL    |                |
| cid   | int(11) | YES  | MUL | NULL    |                |
| gre   | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)</span></code>

可以看出使用SQLAlchemy中的语句和使用SQL语句的结果一样。接下来就可以插入数据了

<code class=" hljs python"><span class="hljs-prompt">>>> </span>stu = Stu(name=<span class="hljs-string">'a'</span>)
<span class="hljs-prompt">>>> </span>session.add(stu)
<span class="hljs-prompt">>>> </span>stu = Stu(name=<span class="hljs-string">'b'</span>)
<span class="hljs-prompt">>>> </span>session.add(stu)
<span class="hljs-prompt">>>> </span>stu = Stu(name=<span class="hljs-string">'c'</span>)
<span class="hljs-prompt">>>> </span>session.add(stu)
<span class="hljs-prompt">>>> </span>stu = Stu(name=<span class="hljs-string">'d'</span>)
<span class="hljs-prompt">>>> </span>session.add(stu)
<span class="hljs-prompt">>>> </span>stu = Stu(name=<span class="hljs-string">'e'</span>)
<span class="hljs-prompt">>>> </span>session.add(stu)
<span class="hljs-prompt">>>> </span>
</code>

手动插入了五条记录，但此时还没有提交，没有真正的写入数据库
或者使用非ORM方式进行插入

<code class=" hljs ruby">>>>session.execute(<span class="hljs-constant">Stu</span>.__table_<span class="hljs-number">_</span>.insert(),[{<span class="hljs-string">'name'</span><span class="hljs-symbol">:randint</span>(<span class="hljs-number">1</span>,<span class="hljs-number">100</span>)} <span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> xrange(<span class="hljs-number">10000</span>)])
>>>session.commit()
<span class="hljs-comment">#可以速度更快的插入更多的数据</span></code>

<code class=" hljs avrasm">>>> session<span class="hljs-preprocessor">.commit</span>()
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">839</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> BEGIN (implicit)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">840</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> INSERT INTO stu (name) VALUES (%s)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">840</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'a'</span>,)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">841</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> INSERT INTO stu (name) VALUES (%s)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">841</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'b'</span>,)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">841</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> INSERT INTO stu (name) VALUES (%s)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">841</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'c'</span>,)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">842</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> INSERT INTO stu (name) VALUES (%s)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">842</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'d'</span>,)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">842</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> INSERT INTO stu (name) VALUES (%s)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">842</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'e'</span>,)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">22</span>:<span class="hljs-number">16</span>,<span class="hljs-number">843</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> COMMIT
>>> </code>

此时真的写入数据库了哦。向课程表插入五条

<code class=" hljs python"><span class="hljs-prompt">>>> </span>cla = Cla(cname=<span class="hljs-string">'yuwen'</span>)
<span class="hljs-prompt">>>> </span>session.add(cla)
<span class="hljs-prompt">>>> </span>cla = Cla(cname=<span class="hljs-string">'shuxue'</span>)
<span class="hljs-prompt">>>> </span>session.add(cla)
<span class="hljs-prompt">>>> </span>cla = Cla(cname=<span class="hljs-string">'yingyu'</span>)
<span class="hljs-prompt">>>> </span>session.add(cla)
<span class="hljs-prompt">>>> </span>cla = Cla(cname=<span class="hljs-string">'wuli'</span>)
<span class="hljs-prompt">>>> </span>session.add(cla)
<span class="hljs-prompt">>>> </span>cla = Cla(cname=<span class="hljs-string">'huaxue'</span>)
<span class="hljs-prompt">>>> </span>session.add(cla)
<span class="hljs-prompt">>>> </span>session.commit()</code>

3,现在开始操作数据

<code class=" hljs vbnet">>>> query = session.query(Stu)
>>> <span class="hljs-keyword">for</span> st <span class="hljs-keyword">in</span> query:
...     print st.name
... 
对应的SQL语句
<span class="hljs-keyword">SELECT</span> stu.id <span class="hljs-keyword">AS</span> stu_id, stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">56</span>:<span class="hljs-number">07</span>,<span class="hljs-number">084</span> INFO sqlalchemy.engine.base.Engine ()
a
b
c
d
e
>>> print query.all()<span class="hljs-preprocessor"># # 返回的是一个类似列表的对象</span>
<span class="hljs-keyword">SELECT</span> stu.id <span class="hljs-keyword">AS</span> stu_id, stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">58</span>:<span class="hljs-number">16</span>,<span class="hljs-number">085</span> INFO sqlalchemy.engine.base.Engine ()
[<__main__.Stu <span class="hljs-built_in">object</span> at <span class="hljs-number">0xb66b3f4c</span>>, <__main__.Stu <span class="hljs-built_in">object</span> at <span class="hljs-number">0xb5e4202c</span>>, <__main__.Stu <span class="hljs-built_in">object</span> at <span class="hljs-number">0xb66b3f8c</span>>, <__main__.Stu <span class="hljs-built_in">object</span> at <span class="hljs-number">0xb5e4206c</span>>, <__main__.Stu <span class="hljs-built_in">object</span> at <span class="hljs-number">0xb6688c0c</span>>]

>>> print query.first().name<span class="hljs-preprocessor"># 有数据时返回第一条记录，没有数据时会返回 None</span>
<span class="hljs-keyword">SELECT</span> stu.id <span class="hljs-keyword">AS</span> stu_id, stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
 LIMIT %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">18</span>:<span class="hljs-number">59</span>:<span class="hljs-number">43</span>,<span class="hljs-number">149</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">1</span>,)
a
<span class="hljs-preprocessor"># print query.one().name# 不存在，或有多行记录时会抛出异常</span>

>>> print query.filter(Stu.id == <span class="hljs-number">2</span>).first().name
<span class="hljs-keyword">SELECT</span> stu.id <span class="hljs-keyword">AS</span> stu_id, stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> stu.id = %s 
 LIMIT %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">04</span>:<span class="hljs-number">54</span>,<span class="hljs-number">363</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">2</span>, <span class="hljs-number">1</span>)
b
>>> print query.filter(<span class="hljs-comment">'id = 2').first().name # 支持字符串</span>
<span class="hljs-keyword">SELECT</span> stu.id <span class="hljs-keyword">AS</span> stu_id, stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> id = <span class="hljs-number">2</span> 
 LIMIT %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">07</span>:<span class="hljs-number">02</span>,<span class="hljs-number">016</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">1</span>,)
b
>>> print query.<span class="hljs-keyword">get</span>(<span class="hljs-number">2</span>).name <span class="hljs-preprocessor"># 以主键获取，等效于上句</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">07</span>:<span class="hljs-number">40</span>,<span class="hljs-number">007</span> INFO sqlalchemy.engine.base.Engine <span class="hljs-keyword">SELECT</span> stu.id <span class="hljs-keyword">AS</span> stu_id, stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> stu.id = %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">07</span>:<span class="hljs-number">40</span>,<span class="hljs-number">007</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">2</span>,)
b
>>> print query.<span class="hljs-keyword">get</span>(<span class="hljs-number">2</span>).id
<span class="hljs-keyword">SELECT</span> stu.id <span class="hljs-keyword">AS</span> stu_id, stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> stu.id = %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">08</span>:<span class="hljs-number">46</span>,<span class="hljs-number">009</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">2</span>,)
<span class="hljs-number">2</span></code>

<code class=" hljs avrasm">>>> quer2 = session<span class="hljs-preprocessor">.query</span>(Stu<span class="hljs-preprocessor">.name</span>)
>>> print quer2<span class="hljs-preprocessor">.all</span>() 
SELECT stu<span class="hljs-preprocessor">.name</span> AS stu_name 
FROM stu
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">09</span>:<span class="hljs-number">46</span>,<span class="hljs-number">259</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> ()
[(<span class="hljs-string">'a'</span>,), (<span class="hljs-string">'b'</span>,), (<span class="hljs-string">'c'</span>,), (<span class="hljs-string">'d'</span>,), (<span class="hljs-string">'e'</span>,)]</code>

<code class=" hljs avrasm">>>> print quer2<span class="hljs-preprocessor">.limit</span>(<span class="hljs-number">1</span>)<span class="hljs-preprocessor">.all</span>() <span class="hljs-preprocessor">#只返回一条</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">11</span>:<span class="hljs-number">23</span>,<span class="hljs-number">383</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> SELECT stu<span class="hljs-preprocessor">.name</span> AS stu_name 
FROM stu 
 LIMIT %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">11</span>:<span class="hljs-number">23</span>,<span class="hljs-number">383</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-number">1</span>,)
[(<span class="hljs-string">'a'</span>,)]

>>> print quer2<span class="hljs-preprocessor">.limit</span>(<span class="hljs-number">2</span>)<span class="hljs-preprocessor">.all</span>()<span class="hljs-preprocessor">#只返回两条</span>
SELECT stu<span class="hljs-preprocessor">.name</span> AS stu_name 
FROM stu 
 LIMIT %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">11</span>:<span class="hljs-number">29</span>,<span class="hljs-number">480</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-number">2</span>,)
[(<span class="hljs-string">'a'</span>,), (<span class="hljs-string">'b'</span>,)]</code>

<code class=" hljs avrasm">>>> print quer2<span class="hljs-preprocessor">.offset</span>(<span class="hljs-number">1</span>)<span class="hljs-preprocessor">.all</span>() <span class="hljs-preprocessor">#跳过一条，从第二条数据开始查询</span>
SELECT stu<span class="hljs-preprocessor">.name</span> AS stu_name 
FROM stu 
 LIMIT %s, <span class="hljs-number">18446744073709551615</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">13</span>:<span class="hljs-number">25</span>,<span class="hljs-number">734</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-number">1</span>,)
[(<span class="hljs-string">'b'</span>,), (<span class="hljs-string">'c'</span>,), (<span class="hljs-string">'d'</span>,), (<span class="hljs-string">'e'</span>,)]
>>> print quer2<span class="hljs-preprocessor">.offset</span>(<span class="hljs-number">3</span>)<span class="hljs-preprocessor">.all</span>() <span class="hljs-preprocessor">#从第四条数据开始</span>
SELECT stu<span class="hljs-preprocessor">.name</span> AS stu_name 
FROM stu 
 LIMIT %s, <span class="hljs-number">18446744073709551615</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">13</span>:<span class="hljs-number">39</span>,<span class="hljs-number">629</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-number">3</span>,)
[(<span class="hljs-string">'d'</span>,), (<span class="hljs-string">'e'</span>,)]</code>

<code class=" hljs oxygene">#按name降序排序
>>> print quer2.order_by(Stu.name.<span class="hljs-keyword">desc</span>()).all()
<span class="hljs-keyword">SELECT</span> stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> stu.name <span class="hljs-keyword">DESC</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">16</span>:<span class="hljs-number">56</span>,<span class="hljs-number">022</span> INFO sqlalchemy.engine.base.Engine ()
[(<span class="hljs-string">'e'</span>,), (<span class="hljs-string">'d'</span>,), (<span class="hljs-string">'c'</span>,), (<span class="hljs-string">'b'</span>,), (<span class="hljs-string">'a'</span>,)]

>>> print quer2.order_by(<span class="hljs-string">'name desc'</span>).all()
<span class="hljs-keyword">SELECT</span> stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> name <span class="hljs-keyword">desc</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">17</span>:<span class="hljs-number">09</span>,<span class="hljs-number">851</span> INFO sqlalchemy.engine.base.Engine ()
[(<span class="hljs-string">'e'</span>,), (<span class="hljs-string">'d'</span>,), (<span class="hljs-string">'c'</span>,), (<span class="hljs-string">'b'</span>,), (<span class="hljs-string">'a'</span>,)]
#按name降序，有重复的按id升序排序
>>> print session.query(Stu.id).order_by(<span class="hljs-string">'name desc'</span>,<span class="hljs-string">'id'</span>).all()
<span class="hljs-keyword">SELECT</span> stu.id <span class="hljs-keyword">AS</span> stu_id 
<span class="hljs-keyword">FROM</span> stu <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> name <span class="hljs-keyword">desc</span>, stu.id
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">20</span>:<span class="hljs-number">34</span>,<span class="hljs-number">818</span> INFO sqlalchemy.engine.base.Engine ()
[(<span class="hljs-number">5</span>L,), (<span class="hljs-number">4</span>L,), (<span class="hljs-number">3</span>L,), (<span class="hljs-number">2</span>L,), (<span class="hljs-number">1</span>L,)]</code>

<code class=" hljs vbnet"><span class="hljs-preprocessor">#scalar()在有多条数据时使用报出异常,all()可以使用多条也可以使用一条</span>
<span class="hljs-preprocessor">#>>> print quer2.filter(Stu.id>2).scalar()</span>
>>> print quer2.filter(Stu.id><span class="hljs-number">2</span>).all()
<span class="hljs-keyword">SELECT</span> stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> stu.id > %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">56</span>:<span class="hljs-number">47</span>,<span class="hljs-number">760</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">2</span>,)
[(<span class="hljs-comment">'c',), ('d',), ('e',)]</span>

>>> print quer2.filter(Stu.id==<span class="hljs-number">2</span>).all()
<span class="hljs-keyword">SELECT</span> stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> stu.id = %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">57</span>:<span class="hljs-number">47</span>,<span class="hljs-number">901</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">2</span>,)
[(<span class="hljs-comment">'b',)]</span>


>>> print quer2.filter(Stu.id==<span class="hljs-number">2</span>).scalar()
<span class="hljs-keyword">SELECT</span> stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> stu.id = %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">23</span>:<span class="hljs-number">38</span>,<span class="hljs-number">761</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">2</span>,)
b

>>> print quer2.filter(<span class="hljs-comment">'id=2').scalar()</span>
<span class="hljs-keyword">SELECT</span> stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> id=<span class="hljs-number">2</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">43</span>:<span class="hljs-number">47</span>,<span class="hljs-number">797</span> INFO sqlalchemy.engine.base.Engine ()
b

<span class="hljs-preprocessor">#在此中‘，’等价于and</span>
>>> print query2.filter(Stu.id><span class="hljs-number">1</span>,Stu.name !=<span class="hljs-comment">'a').first()</span>
<span class="hljs-keyword">SELECT</span> stu.name <span class="hljs-keyword">AS</span> stu_name 
<span class="hljs-keyword">FROM</span> stu 
<span class="hljs-keyword">WHERE</span> stu.id > %s <span class="hljs-keyword">AND</span> stu.name != %s 
 LIMIT %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">51</span>:<span class="hljs-number">14</span>,<span class="hljs-number">571</span> INFO sqlalchemy.engine.base.Engine (<span class="hljs-number">1</span>, <span class="hljs-comment">'a', 1)</span>
(<span class="hljs-comment">'b',)</span>
>>> 
</code>

<code class=" hljs avrasm"><span class="hljs-preprocessor">#此种迭代也类似与and</span>
>>> query3 = query2<span class="hljs-preprocessor">.filter</span>(Stu<span class="hljs-preprocessor">.id</span>><span class="hljs-number">1</span>)
>>> query3 = query3<span class="hljs-preprocessor">.filter</span>(Stu<span class="hljs-preprocessor">.name</span> != <span class="hljs-string">'a'</span>)
>>> query3<span class="hljs-preprocessor">.first</span>()
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">53</span>:<span class="hljs-number">50</span>,<span class="hljs-number">150</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> SELECT stu<span class="hljs-preprocessor">.name</span> AS stu_name 
FROM stu 
WHERE stu<span class="hljs-preprocessor">.id</span> > %s <span class="hljs-keyword">AND</span> stu<span class="hljs-preprocessor">.name</span> != %s 
 LIMIT %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">53</span>:<span class="hljs-number">50</span>,<span class="hljs-number">151</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-number">1</span>, <span class="hljs-string">'a'</span>, <span class="hljs-number">1</span>)
(<span class="hljs-string">'b'</span>,)
</code>

<code class=" hljs avrasm"><span class="hljs-preprocessor">#or_就是类似or</span>
>>> print query2<span class="hljs-preprocessor">.filter</span>(or_(Stu<span class="hljs-preprocessor">.id</span> == <span class="hljs-number">1</span>,Stu<span class="hljs-preprocessor">.id</span>==<span class="hljs-number">2</span>))<span class="hljs-preprocessor">.all</span>()
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">55</span>:<span class="hljs-number">59</span>,<span class="hljs-number">383</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> SELECT stu<span class="hljs-preprocessor">.name</span> AS stu_name 
FROM stu 
WHERE stu<span class="hljs-preprocessor">.id</span> = %s <span class="hljs-keyword">OR</span> stu<span class="hljs-preprocessor">.id</span> = %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">19</span>:<span class="hljs-number">55</span>:<span class="hljs-number">59</span>,<span class="hljs-number">383</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-number">1</span>, <span class="hljs-number">2</span>)
[(<span class="hljs-string">'a'</span>,), (<span class="hljs-string">'b'</span>,)]
</code>

<code class=" hljs avrasm"><span class="hljs-preprocessor"># in的用法</span>
>>> print query2<span class="hljs-preprocessor">.filter</span>(Stu<span class="hljs-preprocessor">.id</span><span class="hljs-preprocessor">.in</span>_((<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>)))<span class="hljs-preprocessor">.all</span>()
SELECT stu<span class="hljs-preprocessor">.name</span> AS stu_name 
FROM stu 
WHERE stu<span class="hljs-preprocessor">.id</span> <span class="hljs-keyword">IN</span> (%s, %s, %s)
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">01</span>:<span class="hljs-number">01</span>,<span class="hljs-number">729</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-number">1</span>, <span class="hljs-number">2</span>, <span class="hljs-number">3</span>)
[(<span class="hljs-string">'a'</span>,), (<span class="hljs-string">'b'</span>,), (<span class="hljs-string">'c'</span>,)]
>>> 
</code>

<code class=" hljs python"><span class="hljs-comment">#为null的一些用法</span>
<span class="hljs-prompt">>>> </span>query4 = session.query(Stu.id)
<span class="hljs-prompt">>>> </span><span class="hljs-keyword">print</span> query4.filter(Stu.name==<span class="hljs-keyword">None</span>).scalar()
SELECT stu.id AS stu_id 
FROM stu 
WHERE stu.name IS NULL
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">02</span>:<span class="hljs-number">59</span>,<span class="hljs-number">821</span> INFO sqlalchemy.engine.base.Engine ()
<span class="hljs-keyword">None</span>
<span class="hljs-prompt">>>> </span>
<span class="hljs-prompt">>>> </span><span class="hljs-keyword">print</span> query4.filter(<span class="hljs-string">'name is null'</span>).scalar()
SELECT stu.id AS stu_id 
FROM stu 
WHERE name <span class="hljs-keyword">is</span> null
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">03</span>:<span class="hljs-number">40</span>,<span class="hljs-number">312</span> INFO sqlalchemy.engine.base.Engine ()
<span class="hljs-keyword">None</span>
<span class="hljs-prompt">>>> </span>
</code>

<code class=" hljs avrasm"><span class="hljs-preprocessor">#不为null的一些用法</span>
>>> print query4<span class="hljs-preprocessor">.filter</span>(not_(Stu<span class="hljs-preprocessor">.name</span> == None))<span class="hljs-preprocessor">.all</span>()
SELECT stu<span class="hljs-preprocessor">.id</span> AS stu_id 
FROM stu 
WHERE stu<span class="hljs-preprocessor">.name</span> IS NOT NULL
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">04</span>:<span class="hljs-number">49</span>,<span class="hljs-number">888</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> ()
[(<span class="hljs-number">1</span>L,), (<span class="hljs-number">2</span>L,), (<span class="hljs-number">3</span>L,), (<span class="hljs-number">4</span>L,), (<span class="hljs-number">5</span>L,)]
>>> 

>>> print query4<span class="hljs-preprocessor">.filter</span>(Stu<span class="hljs-preprocessor">.name</span> != None)<span class="hljs-preprocessor">.all</span>()
SELECT stu<span class="hljs-preprocessor">.id</span> AS stu_id 
FROM stu 
WHERE stu<span class="hljs-preprocessor">.name</span> IS NOT NULL
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">05</span>:<span class="hljs-number">42</span>,<span class="hljs-number">724</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> ()
[(<span class="hljs-number">1</span>L,), (<span class="hljs-number">2</span>L,), (<span class="hljs-number">3</span>L,), (<span class="hljs-number">4</span>L,), (<span class="hljs-number">5</span>L,)]
>>> </code>

<code class=" hljs avrasm"><span class="hljs-preprocessor">#func条用各种函数的用法</span>

>>> print query4<span class="hljs-preprocessor">.count</span>()
SELECT count(*) AS count_1 
FROM (SELECT stu<span class="hljs-preprocessor">.id</span> AS stu_id 
FROM stu) AS anon_1
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">352</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> ()
<span class="hljs-number">5</span>

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.count</span>(<span class="hljs-string">'*'</span>))<span class="hljs-preprocessor">.select</span>_from(Stu)<span class="hljs-preprocessor">.scalar</span>()
 SELECT count(%s) AS count_1 
FROM stu
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">356</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'*'</span>,)
<span class="hljs-number">5</span>

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.count</span>(<span class="hljs-string">'1'</span>))<span class="hljs-preprocessor">.select</span>_from(Stu)<span class="hljs-preprocessor">.scalar</span>()
SELECT count(%s) AS count_1 
FROM stu
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">362</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'1'</span>,)
<span class="hljs-number">5</span>

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.count</span>(Stu<span class="hljs-preprocessor">.id</span>))<span class="hljs-preprocessor">.scalar</span>()
SELECT count(stu<span class="hljs-preprocessor">.id</span>) AS count_1 
FROM stu
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">369</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> ()
<span class="hljs-number">5</span>

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.count</span>(<span class="hljs-string">'*'</span>))<span class="hljs-preprocessor">.filter</span>(Stu<span class="hljs-preprocessor">.id</span> > <span class="hljs-number">0</span>)<span class="hljs-preprocessor">.scalar</span>() <span class="hljs-preprocessor"># filter()中包含Stu，因此不需要指定表</span>
SELECT count(%s) AS count_1 
FROM stu 
WHERE stu<span class="hljs-preprocessor">.id</span> > %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">377</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'*'</span>, <span class="hljs-number">0</span>)
<span class="hljs-number">5</span>

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.count</span>(<span class="hljs-string">'*'</span>))<span class="hljs-preprocessor">.filter</span>(Stu<span class="hljs-preprocessor">.name</span> == <span class="hljs-string">'a'</span>)<span class="hljs-preprocessor">.limit</span>(<span class="hljs-number">1</span>)<span class="hljs-preprocessor">.scal</span>() == <span class="hljs-number">1</span> <span class="hljs-preprocessor"># 可以用 limit() 限制 count() 的返回数</span>
SELECT count(%s) AS count_1 
FROM stu 
WHERE stu<span class="hljs-preprocessor">.name</span> = %s 
 LIMIT %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">394</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-string">'*'</span>, <span class="hljs-string">'a'</span>, <span class="hljs-number">1</span>)
True

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.sum</span>(Stu<span class="hljs-preprocessor">.id</span>))<span class="hljs-preprocessor">.scalar</span>()
SELECT sum(stu<span class="hljs-preprocessor">.id</span>) AS sum_1 
FROM stu
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">401</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> ()
<span class="hljs-number">15</span>

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.now</span>())<span class="hljs-preprocessor">.scalar</span>() <span class="hljs-preprocessor"># func 后可以跟任意函数名，只要该据库支持</span>
SELECT now() AS now_1
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">406</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> ()
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.current</span>_timestamp())<span class="hljs-preprocessor">.scalar</span>()
SELECT CURRENT_TIMESTAMP AS current_timestamp_1
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>,<span class="hljs-number">411</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> ()
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">43</span>

>>> print session<span class="hljs-preprocessor">.query</span>(func<span class="hljs-preprocessor">.md</span>5(Stu<span class="hljs-preprocessor">.name</span>))<span class="hljs-preprocessor">.filter</span>(Stu<span class="hljs-preprocessor">.id</span> == <span class="hljs-number">1</span>)<span class="hljs-preprocessor">.scalar</span>()
SELECT md5(stu<span class="hljs-preprocessor">.name</span>) AS md5_1 
FROM stu 
WHERE stu<span class="hljs-preprocessor">.id</span> = %s
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">09</span> <span class="hljs-number">20</span>:<span class="hljs-number">08</span>:<span class="hljs-number">44</span>,<span class="hljs-number">841</span> INFO sqlalchemy<span class="hljs-preprocessor">.engine</span><span class="hljs-preprocessor">.base</span><span class="hljs-preprocessor">.Engine</span> (<span class="hljs-number">1</span>,)
<span class="hljs-number">0</span>cc175b9c0f1b6a831c399e269772661
>>> 
</code>

<code class=" hljs asciidoc">#修改数据
>>> query.filter(Stu.id==1).update({Stu.name:<span class="hljs-emphasis">'li'</span>})
UPDATE stu SET name=%s WHERE stu.id = %s
2016-05-09 20:12:57,027 INFO sqlalchemy.engine.base.Engine (<span class="hljs-emphasis">'li'</span>, 1)
1L

#删除数据
>>> query = session.query(Grade)
>>> query.filter(Grade.id == 1).delete()
DELETE FROM grade WHERE grade.id = %s
2016-05-09 20:28:18,638 INFO sqlalchemy.engine.base.Engine (1,)
1L
>>> 
此时没有提交，在数据库中环视存在的
<span class="hljs-header">mysql> select * from grade;
+----+------+------+------+</span>
<span class="hljs-header">| id | uid  | cid  | gre  |
+----+------+------+------+</span>
|  1 |    1 |    1 |   60 |
|  2 |    2 |    1 |   66 |
|  3 |    5 |    1 |   66 |
|  4 |    5 |    5 |   96 |
|  5 |    5 |    3 |   96 |
|  6 |    3 |    2 |   96 |
|  7 |    3 |    4 |   76 |
|  8 |    4 |    4 |   76 |
|  9 |    4 |    3 |   76 |
| 10 |    4 |    5 |   76 |
| 11 |    1 |    4 |   76 |
| 12 |    1 |    5 |   76 |
| 13 |    2 |    5 |   76 |
| 14 |    3 |    3 |   60 |
<span class="hljs-header">| 15 |    2 |    3 |   50 |
+----+------+------+------+</span>
15 rows in set (0.00 sec)
#开始提交
>>> session.commit()
2016-05-09 20:31:02,461 INFO sqlalchemy.engine.base.Engine COMMIT
>>> 
<span class="hljs-header">mysql> select * from grade;
+----+------+------+------+</span>
<span class="hljs-header">| id | uid  | cid  | gre  |
+----+------+------+------+</span>
|  2 |    2 |    1 |   66 |
|  3 |    5 |    1 |   66 |
|  4 |    5 |    5 |   96 |
|  5 |    5 |    3 |   96 |
|  6 |    3 |    2 |   96 |
|  7 |    3 |    4 |   76 |
|  8 |    4 |    4 |   76 |
|  9 |    4 |    3 |   76 |
| 10 |    4 |    5 |   76 |
| 11 |    1 |    4 |   76 |
| 12 |    1 |    5 |   76 |
| 13 |    2 |    5 |   76 |
| 14 |    3 |    3 |   60 |
<span class="hljs-header">| 15 |    2 |    3 |   50 |
+----+------+------+------+</span>
14 rows in set (0.00 sec)

也获取不到对象了
>>> print query.get(1)
SELECT grade.id AS grade<span class="hljs-emphasis">_id, grade.uid AS grade_</span>uid, grade.cid AS grade<span class="hljs-emphasis">_cid, grade.gre AS grade_</span>gre 
FROM grade 
WHERE grade.id = %s
2016-05-09 20:32:20,742 INFO sqlalchemy.engine.base.Engine (1,)
None
>>> </code>

单表的增删改查完事了，下面来看看多表连接操作
http://blog.csdn.net/u011573853/article/details/51363780
一些细节会在下面进行说明（事务，加锁，编码等）
http://blog.csdn.net/u011573853/article/details/51366124

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

您如何處理MySQL中的數據庫升級？Apr 30, 2025 am 12:28 AM

MySQL數據庫升級的步驟包括：1.備份數據庫，2.停止當前MySQL服務，3.安裝新版本MySQL，4.啟動新版本MySQL服務，5.恢復數據庫。升級過程需注意兼容性問題，並可使用高級工具如PerconaToolkit進行測試和優化。

您可以使用MySQL的不同備份策略是什麼？Apr 30, 2025 am 12:28 AM

MySQL備份策略包括邏輯備份、物理備份、增量備份、基於復制的備份和雲備份。 1.邏輯備份使用mysqldump導出數據庫結構和數據，適合小型數據庫和版本遷移。 2.物理備份通過複製數據文件，速度快且全面，但需數據庫一致性。 3.增量備份利用二進制日誌記錄變化，適用於大型數據庫。 4.基於復制的備份通過從服務器備份，減少對生產系統的影響。 5.雲備份如AmazonRDS提供自動化解決方案，但成本和控制需考慮。選擇策略時應考慮數據庫大小、停機容忍度、恢復時間和恢復點目標。

什麼是mySQL聚類？Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

如何優化數據庫架構設計以在MySQL中的性能？Apr 30, 2025 am 12:27 AM

在MySQL中優化數據庫模式設計可通過以下步驟提升性能：1.索引優化：在常用查詢列上創建索引，平衡查詢和插入更新的開銷。 2.表結構優化：通過規範化或反規範化減少數據冗餘，提高訪問效率。 3.數據類型選擇：使用合適的數據類型，如INT替代VARCHAR，減少存儲空間。 4.分區和分錶：對於大數據量，使用分區和分錶分散數據，提升查詢和維護效率。

您如何優化MySQL性能？Apr 30, 2025 am 12:26 AM

tooptimizemysqlperformance，lofterTheSeSteps：1）inasemproperIndexingTospeedUpqueries，2）使用ExplaintplaintoAnalyzeandoptimizequeryPerformance，3）ActiveServerConfigurationStersLikeTlikeTlikeTlikeIkeLikeIkeIkeLikeIkeLikeIkeLikeIkeLikeNodb_buffer_pool_sizizeandmax_connections，4）

如何使用MySQL的函數進行數據處理和計算Apr 29, 2025 pm 04:21 PM

MySQL函數可用於數據處理和計算。 1.基本用法包括字符串處理、日期計算和數學運算。 2.高級用法涉及結合多個函數實現複雜操作。 3.性能優化需避免在WHERE子句中使用函數，並使用GROUPBY和臨時表。

MySQL批量插入數據的高效方法Apr 29, 2025 pm 04:18 PM

MySQL批量插入数据的高效方法包括：1.使用INSERTINTO...VALUES语法，2.利用LOADDATAINFILE命令，3.使用事务处理，4.调整批量大小，5.禁用索引，6.使用INSERTIGNORE或INSERT...ONDUPLICATEKEYUPDATE，这些方法能显著提升数据库操作效率。

給MySQL表添加和刪除字段的操作步驟Apr 29, 2025 pm 04:15 PM

在MySQL中，添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column，刪除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段時，需指定位置以優化查詢性能和數據結構；刪除字段前需確認操作不可逆；使用在線DDL、備份數據、測試環境和低負載時間段修改表結構是性能優化和最佳實踐。

See all articles