β

SQLAlchemy的SQL引擎学习笔记

zhoulin的IT工作之旅 1220 阅读

昨天分享了SQLAlchemy的ORM模型来操作数据库,今天分享一下它的SQL引擎的学习笔记。

#coding:utf-8
__author__ = 'itzhoulin'
from sqlalchemy import Integer, String, BIGINT, DateTime, VARCHAR, Table, ForeignKey
from sqlalchemy import create_engine, MetaData, Column, Sequence, select, update
from sqlalchemy import and_, or_, not_
from sqlalchemy.orm import sessionmaker
import json
#首先设置数据库连接,更多数据库连接设置方式参考官网手册:
#http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html#database-urls
db_url = 'postgresql://testdb:123456@10.1.14.74/testdbdb'
#定义一个Metadata,它是一个容器对象,能够描述一个数据库或者多个数据库的很多不同特性。
#Table对象是被连接到Metadata的,Table是由诸多Column组成的,
metadata = MetaData()
#这里设置两个Table,设置Table时前两个参数必须是Table name和绑定的Metadata
# 关于Table object的详细参数参见官方文档:
#  http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.Table
host = Table('server', metadata,
    Column('id', Integer, primary_key = True),
    Column('hostname', String(30), nullable = False),
    Column('ip', String(16), nullable = False),
    Column('fsid', String(60), nullable = False),
    Column('type', String(10),nullable = False),
)
#这里的Sequence是为了实现postgresql有起始值的自增,以及自增间隔,这种方法仅仅针对postgresql有效
# 其他数据库要寻找其他解决办法。
fsuser = Table('fsusers', metadata,
    Column('id',BIGINT, Sequence('fsuser_aid_seq', start=5000, increment=1), primary_key=True),
    Column('user_name',VARCHAR(50),primary_key=True, unique=True,nullable=False),
    Column('user_pwd',VARCHAR(500),nullable=False),
    Column('fsid',String(60),nullable=False),
    )
engine = create_engine(db_url)
#根据前面设置的Table建立数据表,create_all是建立所有绑定到metadata的所有Table
#要清除所有Table,试用metadata.drop_all(engine)
#create_all函数能够很好的处理Table之间的依赖关系,如Table中有ForeignKey外链时。
metadata.create_all(engine)
# metadata.drop_all(engine)
# #如果仅仅建立单个Table,试用下面的语句,清除则用host.drop(engine,checkfirst=True)
# #启用“check first for the table existing” 逻辑检查,增加参数 checkfirst=True 给create() 和 drop():
# host.create(engine,checkfirst=True)
#插入几条数据
ins1 = host.insert().values(hostname='test1',ip='10.1.35.101',fsid='abcdefghijklmnopqresuvmxyz',type='samba')
# ##也可以分两句写,一句生成一个insert,一句附加values
# ## ins1 = host.insert()
# ## ins1.values(******)
#
# #这里新建的Engine只是一个数据库连接对象,有连接数据库的能力,但是并没有实际连接,因此,首先建立连接
conn = engine.connect()
# # #
# # # #连接好数据库之后,可以执行sql语句了
result = conn.execute(ins1)
conn.execute(fsuser.insert(),user_name='user4',user_pwd='123456',fsid='zhoulindefghijklmnopqresuvmxyz')
conn.execute(host.insert(),hostname='test3',ip='10.1.35.101',fsid='zhoulindefghijklmnopqresuvmxyz',type='samba')
####################
#select method
#注意,这里fsuser必须添加[],因为select的参数只接受python的list或者其他iterable类型
s = select([fsuser])
##获取指定Column的数据
s = select([fsuser.c.id,fsuser.c.user_name])
result = conn.execute(s)
#输出结果有几种方法,因为本身返回的就是iiterable类型,因此,可以试用for循环或者内置的fetchone等函数
for row in result:
    print(row)
#获取第一条数据,执行完即将result的cursor指向结尾。
result.first()
#每一次获取一条数据直到获取完
row = result.fetchone()
#获取单条记录里面的某一个字段,方法有如下几种:
print row[0]   ##当做列表
print row['id']  ##当做dict
print row[fsuser.c.id]   #当做类
#返回制定数目的结果,cursor
print result.fetchmany(4)
print result.fetchall()
# ##获取完需要的数据之后,最好的方法是关闭当前ResultProxy(result的类型)
result.close()
#注意,上述获取方法有可能报错是因为result已经遍历完,要测试请重新执行select
# ##更高级的查询:带条件的查询,这里可以试用and_,or_,not_实现条件查询以及+来实现格式化输出
s = select([fsuser.c.id, fsuser.c.user_name, host.c.hostname, host.c.ip]).\
    where(
        and_(fsuser.c.fsid==host.c.fsid,
             fsuser.c.id>5002,
             or_(
                 host.c.hostname.like('test%'),
                 host.c.hostname.like('host%')
             )
        )
)
result=conn.execute(s).fetchall()
for row in result:
    print row
##当然更多的参数比如order_by(),选择的排序对象还可以添加asc() and desc() 两个函数制定升序降序
s1 = select([fsuser]).order_by(fsuser.c.user_name)
s1 = select([fsuser]).order_by(fsuser.c.user_name.desc())
###update方法
u1 = fsuser.update().where(fsuser.c.id==5001).values(user_name='updatename')
conn.execute(u1)
###delete方法
conn.execute(server.delete()) 
conn.execute(fsuser.delete().where(fsuser.c.user_name == 'test1')) 
##删除和更新操作还支持返回匹配行,即更新了多少行/删除了多少行
result = conn.execute(fsuser.delete()) 
#>>> result.rowcount
# 1
作者:zhoulin的IT工作之旅
云计算、对象存储、备份、S3、python、shell
原文地址:SQLAlchemy的SQL引擎学习笔记, 感谢原作者分享。

发表评论