python SQLAlchemy
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了python SQLAlchemy,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6646字,纯文字阅读大概需要10分钟。
内容图文
![python SQLAlchemy](/upload/InfoBanner/zyjiaocheng/466/737ffd8d611445e7b18bba7dc4a4b05c.jpg)
cloumn
对象,例如>>> user.c # 表 user 的字段column对象<sqlalchemy.sql.base.ImmutableColumnCollection object at 0x0000000002E804A8>>>> print user.c [‘user.id‘, ‘user.name‘, ‘user.fullname‘]>>> s = select([user.c.name,user.c.fullname])>>> r = conn.execute(s)>>> r <sqlalchemy.engine.result.ResultProxy object at 0x00000000025A7748>>>> r.rowcount # 影响的行数5L>>> ru = r.fetchall() >>> ru [(u‘hello‘, u‘hello world‘), (u‘Jack‘, u‘Jack Jone‘), (u‘Jack‘, u‘Jack Jone‘), (u‘jack‘, u‘jack Jone‘), (u‘tom‘, u‘tom Jim‘)]>>> r <sqlalchemy.engine.result.ResultProxy object at 0x00000000025A7748>>>> r.closed # 只要 r.fetchall() 之后,就会自动关闭 ResultProxy 对象True
同时查询两个表
>>> s = select([user.c.name, address.c.user_id]).where(user.c.id==address.c.user_id) # 使用了字段和字段比较的条件>>> s <sqlalchemy.sql.selectable.Select at 0x2f03390; Select object>>>> print sSELECT "user".name, address.user_id FROM "user", address WHERE "user".id = address.user_id
操作符
>>> print user.c.id == address.c.user_id # 返回一个编译的字符串"user".id = address.user_id>>> print user.c.id == 7"user".id = :id_1 # 编译成为带参数的sql 语句片段字符串>>> print user.c.id != 7"user".id != :id_1>>> print user.c.id > 7"user".id > :id_1>>> print user.c.id == None"user".id IS NULL>>> print user.c.id + address.c.id # 使用两个整形的变成 +"user".id + address.id>>> print user.c.name + address.c.email # 使用两个字符串 变成 ||"user".name || address.email
操作连接
这里的连接指条件查询的时候,逻辑运算符的连接,即 and
or
和 not
print and_( user.c.name.like(‘j%‘), user.c.id == address.c.user_id, or_( address.c.email == ‘wendy@aol.com‘, address.c.email == ‘jack@yahoo.com‘ ), not_(user.c.id>5))"user".name LIKE :name_1 AND "user".id = address.user_id AND (address.email = :email_1 OR address.email = :email_2) AND "user".id <= :id_1
得到的结果为 编译的sql语句片段,下面看一个完整的例子
>>> se_sql = [(user.c.fullname +", " + address.c.email).label(‘title‘)]>>> wh_sql = and_( user.c.id == address.c.user_id, user.c.name.between(‘m‘, ‘z‘), or_( address.c.email.like(‘%@aol.com‘), address.c.email.like(‘%@msn.com‘) ) )>>> print wh_sql"user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email LIKE :email_1 OR address.email LIKE :email_2)>>> s = select(se_sql).where(wh_sql)>>> print sSELECT "user".fullname || :fullname_1 || address.email AS title FROM "user", address WHERE "user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email LIKE :email_1 OR address.email LIKE :email_2)>>> r = conn.execute(s)>>> r.fetchall()
使用 raw sql 方式
遇到负责的sql语句的时候,可以使用 sqlalchemy.sql 下面的 text 函数。将字符串的sql语句包装编译成为 execute执行需要的sql对象。例如:、
>>> text_sql = "SELECT id, name, fullname FROM user WHERE id=:id" # 原始sql语句,参数用( :value)表示>>> s = text(text_sql)>>> print s SELECT id, name, fullname FROM user WHERE id=:id>>> s <sqlalchemy.sql.elements.TextClause object at 0x0000000002587668>>>> conn.execute(s, id=3).fetchall() # id=3 传递:id参数[(3L, u‘Jack‘, u‘Jack Jone‘)]
连接 join
连接有join
和 outejoin
两个方法,join 有两个参数,第一个是join 的表,第二个是on 的条件,joing之后必须要配合select_from
方法
>>> print user.join(address)"user" JOIN address ON "user".id = address.user_id # 因为开启了外键 ,所以join 能只能识别 on 条件>>> print user.join(address, address.c.user_id==user.c.id) # 手动指定 on 条件"user" JOIN address ON address.user_id = "user".id>>> s = select([user.c.name, address.c.email]).select_from(user.join(address, user.c.id==address.c.user_id)) # 被jion的sql语句需要用 select_from方法配合>>> s <sqlalchemy.sql.selectable.Select at 0x2eb63c8; Select object>>>> print s SELECT "user".name, address.email FROM "user" JOIN address ON "user".id = address.user_id>>> conn.execute(s).fetchall() [(u‘hello‘, u‘jack@yahoo.com‘), (u‘hello‘, u‘jack@msn.com‘), (u‘hello‘, u‘jack@yahoo.com‘), (u‘hello‘, u‘jack@msn.com‘), (u‘Jack‘, u‘www@www.org‘), (u‘Jack‘, u‘wendy@aol.com‘), (u‘Jack‘, u‘www@www.org‘), (u‘Jack‘, u‘wendy@aol.com‘)]
排序 分组 分页
排序使用 order_by
方法,分组是 group_by
,分页自然就是limit 和 offset
两个方法配合
>>> s = select([user.c.name]).order_by(user.c.name) # order_by>>> print s SELECT "user".name FROM "user" ORDER BY "user".name>>> s = select([user]).order_by(user.c.name.desc())>>> print s SELECT "user".id, "user".name, "user".fullname FROM "user" ORDER BY "user".name DESC>>> s = select([user]).group_by(user.c.name) # group_by>>> print s SELECT "user".id, "user".name, "user".fullname FROM "user" GROUP BY "user".name>>> s = select([user]).order_by(user.c.name.desc()).limit(1).offset(3) # limit(1).offset(3)>>> print s SELECT "user".id, "user".name, "user".fullname FROM "user" ORDER BY "user".name DESC LIMIT :param_1 OFFSET :param_2 [(4L, u‘jack‘, u‘jack Jone‘)]
更新 update
前面都是一些查询,更新和插入的方法很像,都是 表下面的方法,不同的是,update
多了一个 where
方法 用来选择过滤
>>> s = user.update()>>> print sUPDATE "user" SET id=:id, name=:name, fullname=:fullname>>> s = user.update().values(fullname=user.c.name) # values 指定了更新的字段>>> print sUPDATE "user" SET fullname="user".name>>> s = user.update().where(user.c.name == ‘jack‘).values(name=‘ed‘) # where 进行选择过滤>>> print s UPDATE "user" SET name=:name WHERE "user".name = :name_1>>> r = conn.execute(s)>>> print r.rowcount # 影响行数3
还有一个高级用法,就是一次命令执行多个记录的更新,需要用到 bindparam
方法
from sqlalchemy import bindparam
>>> s = user.update().where(user.c.name==bindparam(‘oldname‘)).values(name=bindparam(‘newname‘)) # oldname 与下面的传入的从拿书进行绑定,newname也一样>>> print sUPDATE "user" SET name=:newname WHERE "user".name = :oldname>>> u = [{‘oldname‘:‘hello‘, ‘newname‘:‘edd‘}, {‘oldname‘:‘ed‘, ‘newname‘:‘mary‘}, {‘oldname‘:‘tom‘, ‘newname‘:‘jake‘}]>>> r = conn.execute(s, u)>>> r.rowcount5L
删除 delete
删除比较容易,调用 delete
方法即可,不加 where 过滤,则删除所有数据,但是不会drop掉表,等于清空了数据表
>>> r = conn.execute(address.delete()) # 清空表>>> print r <sqlalchemy.engine.result.ResultProxy object at 0x0000000002EAF550>>>> r.rowcount8L>>> r = conn.execute(users.delete().where(users.c.name > ‘m‘)) # 删除记录>>> r.rowcount3L
python SQLAlchemy
标签:python mysql
本文系统来源:http://9272317.blog.51cto.com/9262317/1790100
内容总结
以上是互联网集市为您收集整理的python SQLAlchemy全部内容,希望文章能够帮你解决python SQLAlchemy所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。