Python中使用SQLAlchemy连接Mysql数据库（单表操作）
mysql
python
sqlalchemy
使用
連接
一，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
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
如何修復KB5055612無法在Windows 10中安裝？
1 個月前
By DDD
<🎜>：泡泡膠模擬器無窮大 - 如何獲取和使用皇家鑰匙
4 週前
By 尊渡假赌尊渡假赌尊渡假赌
<🎜>：種植花園 - 完整的突變指南
3 週前
By DDD
北端：融合系統，解釋
4 週前
By 尊渡假赌尊渡假赌尊渡假赌
Mandragora：巫婆樹的耳語 - 如何解鎖抓鉤
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
Python vs. JavaScript：開發環境和工具
Apr 26, 2025 am 12:09 AM
Python和JavaScript在開發環境上的選擇都很重要。 1)Python的開發環境包括PyCharm、JupyterNotebook和Anaconda，適合數據科學和快速原型開發。 2)JavaScript的開發環境包括Node.js、VSCode和Webpack，適用於前端和後端開發。根據項目需求選擇合適的工具可以提高開發效率和項目成功率。
在MySQL中解釋外鍵的目的。
Apr 25, 2025 am 12:17 AM
在MySQL中，外鍵的作用是建立表與表之間的關係，確保數據的一致性和完整性。外鍵通過引用完整性檢查和級聯操作維護數據的有效性，使用時需注意性能優化和避免常見錯誤。
比較和對比Mysql和Mariadb。
Apr 26, 2025 am 12:08 AM
MySQL和MariaDB的主要區別在於性能、功能和許可證：1.MySQL由Oracle開發，MariaDB是其分支。 2.MariaDB在高負載環境中性能可能更好。 3.MariaDB提供了更多的存儲引擎和功能。 4.MySQL採用雙重許可證，MariaDB完全開源。選擇時應考慮現有基礎設施、性能需求、功能需求和許可證成本。
SQL與MySQL：澄清兩者之間的關係
Apr 24, 2025 am 12:02 AM
SQL是一種用於管理關係數據庫的標準語言，而MySQL是一個使用SQL的數據庫管理系統。 SQL定義了與數據庫交互的方式，包括CRUD操作，而MySQL實現了SQL標準並提供了額外的功能，如存儲過程和触發器。
MySQL：數據庫，PHPMYADMIN：管理接口
Apr 29, 2025 am 12:44 AM
MySQL和phpMyAdmin可以通過以下步驟進行有效管理：1.創建和刪除數據庫：在phpMyAdmin中點擊幾下即可完成。 2.管理表：可以創建表、修改結構、添加索引。 3.數據操作：支持插入、更新、刪除數據和執行SQL查詢。 4.導入導出數據：支持SQL、CSV、XML等格式。 5.優化和監控：使用OPTIMIZETABLE命令優化表，並利用查詢分析器和監控工具解決性能問題。
Python和JavaScript的未來：趨勢和預測
Apr 27, 2025 am 12:21 AM
Python和JavaScript的未來趨勢包括：1.Python將鞏固在科學計算和AI領域的地位，2.JavaScript將推動Web技術發展，3.跨平台開發將成為熱門，4.性能優化將是重點。兩者都將繼續在各自領域擴展應用場景，並在性能上有更多突破。
給MySQL表添加和刪除字段的操作步驟
Apr 29, 2025 pm 04:15 PM
在MySQL中，添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column，刪除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段時，需指定位置以優化查詢性能和數據結構；刪除字段前需確認操作不可逆；使用在線DDL、備份數據、測試環境和低負載時間段修改表結構是性能優化和最佳實踐。
MySQL在macOS系統的安裝步驟詳解
Apr 29, 2025 pm 03:36 PM
在macOS上安裝MySQL可以通過以下步驟實現：1.安裝Homebrew，使用命令/bin/bash-c"$(curl-fsSLhttps://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"。 2.更新Homebrew，使用brewupdate。 3.安裝MySQL，使用brewinstallmysql。 4.啟動MySQL服務，使用brewservicesstartmysql。安裝後，可通過mysql-u
