SQLAlchemy 使用总结
来源:互联网 发布:c templates 源码 编辑:程序博客网 时间:2024/05/21 05:55
使用 sqlalchemy 有3种方式:
方式1, 使用raw sql;
方式2, 使用SqlAlchemy的sql expression;
方式3, 使用ORM.
前两种方式可以统称为 core 方式. 本文讲解 core 方式访问数据库, 不涉及 ORM.
对于绝大多数应用, 推荐使用 SqlAlchemy. 即使是使用raw sql, SqlAlchemy 也可以带来如下好处:
1. 内建数据库连接池. [注意]如果是sqlalchemy+cx_oracle的话, 需要禁掉 connection pool, 否则会有异常. 方法是设置sqlalchemy.poolclass为sqlalchemy.pool.NullPool
2. 强大的log功能
3. 数据库中立的写法, 包括: sql参数写法, limit语法
4. 特别提一下, where()条件的==your_value, 如果your_value等于None, 真正的Sql会转为Is None
SqlAlchemy的sql expression和raw sql的比较:
1. sql expression 写法是纯python代码, 阅读性更好, 尤其是在使用insert()方法时, 字段名和取值成对出现.
2. raw sql 比 sql expression 更灵活, 如果SQL/DDL很复杂, raw sql就更有优势了.
具体案例如下
# coding: utf-8from sqlalchemy import * import tushare as tsimport pandas as pdfrom sqlalchemy.orm import sessionmaker,mapperfrom datetime import *engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/mystock?charset=utf8') #%% 1 hand-written SQL 方法result = engine.execute('select * from stock_basics where pe < %s', 2)# sqlalchemy推荐使用text()函数封装一下sql字符串,不同数据库, 可以使用统一的sql参数传递写法. 参数须以:号引出.result = engine.execute(text('select * from stock_basics where pe < :pe'), {'pe': 2})# 遍历result时, 得到的每一个行都是RowProxy对象, 获取字段的方法非常灵活, 下标和字段名甚至属性都行.# rowproxy[0] == rowproxy['id'] == rowproxy.idans = result.fetchall() # 获取所有数据ans1 = pd.DataFrame(ans) # 将数据转成 DataFrame格式# 事务处理conn = engine.connect()conn.begin()try: dosomething(connection) conn.commit()except: conn.rollback() conn.close() #%% SQL-expressions in Python 方法meta = MetaData(bind=engine, reflect=True)table = meta.tables['stock_basics']result2 = list(engine.execute(table.select(table.c.pe < 2))) # pe为stock_basics的一个列名#%% ORM 方法 表中要有主键engine.echo = True # We want to see the SQL we're creatingmetadata = MetaData(engine)# The stock_basics table already exists, so no need to redefine it. Just# load it from the database using the "autoload" feature.users = Table('stock_basics', metadata, autoload=True)def run(stmt): rs = stmt.execute() for row in rs: print(row)# Most WHERE clauses can be constructed via normal comparisonss = users.select(users.c.code == '000001')run(s)s = users.select(users.c.pe < 1) # pe为stock_basics的一个列名rs = s.execute().fetchall()ans2 = pd.DataFrame(rs) #将结果转换成 DataFrame格式# Python keywords like "and", "or", and "not" can't be overloaded, so# SQLAlchemy uses functions insteads = users.select(and_(users.c.age < 40, users.c.name != 'Mary'))s = users.select(or_(users.c.age < 40, users.c.name != 'Mary'))s = users.select(not_(users.c.name == 'Susan'))# Or you could use &, | and ~ -- but watch out for priority!s = users.select((users.c.age < 40) & (users.c.name != 'Mary')) #最好添加(),注意优先级s = users.select((users.c.age < 40) | (users.c.name != 'Mary'))s = users.select(~(users.c.name == 'Susan'))# There's other functions too, such as "like", "startswith", "endswith"s = users.select(users.c.name.startswith('M'))s = users.select(users.c.name.like('%a%'))s = users.select(users.c.name.endswith('n'))# The "in" and "between" operations are also availables = users.select(users.c.age.between(30,39))# Extra underscore after "in" to avoid conflict with Python keywords = users.select(users.c.name.in_('Mary', 'Susan'))# If you want to call an SQL function, use "func"s = users.select(func.substr(users.c.name, 2, 1) == 'a')# You don't have to call select() on a table; it's got a bare forms = select([users], users.c.name != 'Carl')s = select([users.c.name, users.c.age], users.c.name != 'Carl') # This can be handy for things like count()s = select([func.count(users.c.user_id)])# Here's how to do count(*)s = select([func.count("*")], from_obj=[users])#%%多表联查# 现在存在两个表users = Table('users', metadata, Column('user_id', Integer, primary_key=True), Column('name', String(40)), Column('age', Integer),)users.create()# emails = Table('emails', metadata,# Column('email_id', Integer, primary_key=True),# Column('address', String),# Column('user_id', Integer, ForeignKey('users.user_id')),)s = select([users, emails], emails.c.user_id == users.c.user_id)# 查询部分列s = select([users.c.name, emails.c.address], emails.c.user_id == users.c.user_id)#基于外键的李娜和查询s = join(users, emails).select()#使用 outerjoin 查询所有用户,不论是否有邮箱s = outerjoin(users, emails).select()#%% 将数据库中的对象映射到对象中users = Table('users', metadata, autoload=True)# These are the empty classes that will become our data classesclass User(object): passusermapper = mapper(User, users)session = DBSession()# 查询 -----------------query = session.query(User)print(query) # 显示SQL 语句print(query.statement) # 同上for user in query: # 遍历时查询 print(user.name)print(query.all()) # 返回的是一个类似列表的对象print(query.first().name) # 记录不存在时,first() 会返回 None# print(query.one().name) # 不存在,或有多行记录时会抛出异常print(query.filter(User.id == 2).first().name)print(query.get(2).name) # 以主键获取,等效于上句print(query.filter('id = 2').first().name) # 支持字符串query2 = session.query(User.name)print(query2.all()) # 每行是个元组print(query2.limit(1).all()) # 最多返回 1 条记录print(query2.offset(1).all()) # 从第 2 条记录开始返回print(query2.order_by(User.name).all())print(query2.order_by('name').all())print(query2.order_by(User.name.desc()).all())print(query2.order_by('name desc').all())print(session.query(User.id).order_by(User.name.desc(), User.id).all())print(query2.filter(User.id == 1).scalar()) # 如果有记录,返回第一条记录的第一个元素print(session.query('id').select_from(User).filter('id = 1').scalar())print(query2.filter(User.id > 1, User.name != 'a').scalar()) # andquery3 = query2.filter(User.id > 1) # 多次拼接的 filter 也是 andquery3 = query3.filter(User.name != 'a')print(query3.scalar())print(query2.filter(or_(User.id == 1, User.id == 2)).all()) # orprint(query2.filter(User.id.in_((1, 2))).all()) # inquery4 = session.query(User.id)print(query4.filter(User.name == None).scalar())print(query4.filter('name is null').scalar())print(query4.filter(not_(User.name == None)).all()) # notprint(query4.filter(User.name != None).all())print(query4.count())print(session.query(func.count('*')).select_from(User).scalar())print(session.query(func.count('1')).select_from(User).scalar())print(session.query(func.count(User.id)).scalar())print(session.query(func.count('*')).filter(User.id > 0).scalar()) # filter() 中包含 User,因此不需要指定表print(session.query(func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1) # 可以用 limit() 限制 count() 的返回数print(session.query(func.sum(User.id)).scalar())print(session.query(func.now()).scalar()) # func 后可以跟任意函数名,只要该数据库支持print(session.query(func.current_timestamp()).scalar())print(session.query(func.md5(User.name)).filter(User.id == 1).scalar())# 修删------query.filter(User.id == 1).update({User.name: 'c'})user = query.get(1)print(user.name)user.name = 'd'session.flush() # 写数据库,但并不提交print(query.get(1).name)session.delete(user)session.flush()session.rollback() # 回滚query.filter(User.id == 1).delete()session.commit() #提交,保存到数据库print query.get(1)session.close() # 关闭session
0 0
- SQLAlchemy 使用总结
- sqlalchemy ORM使用总结
- 使用 SQLAlchemy
- sqlalchemy使用
- 使用sqlalchemy
- 使用SQLAlchemy
- 使用SQLAlchemy
- 使用 sqlalchemy
- 使用SQLAlchemy
- sqlalchemy学习总结
- SQLAlchemy代码总结(1)
- Flask-SQLAlchemy 学习总结
- Flask-SQLAlchemy学习总结
- SQLAlchemy 使用经验
- SQLAlchemy 使用经验
- SQLAlchemy 使用经验
- SQLAlchemy 使用经验
- 关于python使用sqlalchemy
- 红帽 Red Hat Linux相关产品iso镜像下载【百度云】【更新7.2】
- ubuntu部署eclipse兼容性问题
- 自定义转换Json工具
- 解决eclipse资源文件名称不能含有特殊字符跟大写字母的问题
- Scala编程实例:使用List和Tuple
- SQLAlchemy 使用总结
- java中的抽象类和接口区别于联系
- 零基础制作物理引擎--创造世界
- 域安全篇:寻找SYSVOL里的密码和攻击GPP(组策略偏好)
- java.io.EOFException的解决办法
- oracle在SERIALIZABLE级别上事务的特殊性与ORA-08177
- React Native入门——组件构成及生命周期简介
- 安装mysql, 如何解决在centos上面用yum不能安装redis
- Metasploit 攻击winXP