SQLAlchemy是和很多数据库进行交互的一个库,他可以让你创建model,让你可以以一种Python中面向对象的方式进行查询。使得你的代码和数据库可以分开,也就是减轻他们之间的依赖。让你进行数据库的切换或者迁移是很方便的。
首先,你需要考虑的是使用SQLAlchemy Core还是SQLAlchemy ORM,这两种方式在解析方面稍有不同。但是最大的不同是访问是基于schema还是业务对象:
SQLAlchemy Core: 基于schema的,就有点类似于传统的SQL,在数据仓库,报表分析等方面能够对查询控制的更好是很有用的。
SQLAlchemy ORM: 但是如果在考虑领域模型的设计时,ORM封装了大量底层的schema和元数据结构,这种封装使得开发人员和数据库的交互变得更加简单
一 安装python数据库驱动和连接数据库
1.1 安装数据库驱动
PostgreSQL: pip install psycopg2
MySQL: pip install pymysql
其他的省略
1.2 连接数据库
我们可以根据一个给定的字符串创建引擎,然后通过引擎和数据库交互:
字符串格式:<数据库类型>+<数据库驱动类型>://<用户名>:<密码>@<主机名或者IP>:<端口>/数据库名称
# 数据库类型(mysqlpostgresql)
# 驱动类型(pymysqlpsycopg2)
# 授权 (用户和密码)
# 数据库端口 (3306)
# 数据库的名字
比如:
"mysql+pymysql://root:123456@localhost:3306/employee"
"postgresql+psycopg2://root:123456@localhost:5432/mydb"
其他的一些参数:
echo:它会记录引擎出现的一些行为,默认是false
encoding: 默认是UTF-8
isolation_level:指定隔离级别:READ_COMMIT,READ_UNCOMIT
TED,REPEATABLE READ,SERIALIZABLE,AUTOCOMMIT
默认就是提交读
pool_recycle: 数据库连接超时时间,就回收连接,默认是-1.
例子:
from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root:123456@localhost:3306/employee") conn = engine.connect()
二Schema 和 类型
2.1 数据类型
我们在SQLAlchemy可以使用四种类型的Type:
# Generic
# SQL Standard
# Vendor Specific
# User Defined
SQLAlchemy定义了很多普通类型:
2.2Metadata
它经常适合数据库结构绑定在一起的,以便在SQLAlchemy能迅速的访问它。可以把它看做很多Tables 对象的集合,定义表对象之前,需要先实例化它
2.3Tables
Table对象可以通过表名,metadata和额外Cloumn参数进行构建,Column代表着数据库中每一个字段。
举个例子:
from sqlalchemy import Table,Column,Integer,
Numeric,String,ForeignKey,MetaData metadata = MetaData() cookies = Table('cookie',metadata, Column('cookie_id',Integer(),primary_key=True), Column('cookie_name',String(),index=True), Column('cookie_recipe_url',String(255)), Column('cookie_sku',String(55)), Column('quantity',Integer()), Column('unit_cost',Numeric(12,2)) ) ''' primary_key: 表示这个字段或者这个列是主键 index:表示该列是索引列 '''
2.3 列定义了表中的字段,并且可以设置数据类型以及是否是主键,是否允许为空等,还具有设置默认值;不同的数据类型,可能有不同参数,比如字符串类型,可以设置长度,比如浮点类型可以设置精度等
from datetime import datetime from sqlalchemy import DateTime users = Table('users', metadata, Column('user_id', Integer(), primary_key=True), Column('username', String(15), nullable=False, unique=True), Column('email_address', String(255), nullable=False), Column('phone', String(20), nullable=False), Column('password', String(25), nullable=False), Column('created_on', DateTime(), default=datetime.now), Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)) ''' nullable: 表示该字段是否允许为null default:表示如果该字段没有设置值的时候的默认值 onupdate:每次更新时都会调用该方法或函数 这里default,onupdate属性是一个callable对象而不是直接值,比如datetime.now(),因为这样的话,就永远是这个值,而不是每个实例实例化、更新时的时间了。 '''
2.4Keys and Constraints键和约束
键和约束是一种可以使得我们的数据满足特定的需求,键和约束既可以像上面那样通过kwargs定义在Column中,也可以在之后通过对象添加。
from sqlalchemy import PrimaryKeyConstraint,Unique
Constraint,CheckConstraint
最常用的key可能是主键primary key了,他表示该字段必须为是唯一的,你也可以定义一个复合主键通过多个列
PrimaryKeyConstraint('user_id',name='user_pk')
还有比较常用的是唯一约束和检查约束
UniqueConstraint('user_name',name="uix_username") CheckConstraint('unit_cost',name='unit_cost_positive')
2.5Indexes 索引
索引可以加速我们寻找字段的值
from sqlalchemy import Index
Index('ix_cookies_cookie_name', 'cookie_name')
这个定义需要放置在Table构造器中。也可以在之后定义,比如
Index('ix_test', mytable.c.cookie_sku,mytable.c.cookie_name))
2.6Relationships and ForeignKeyConstraints
关联关系和外键约束:
orders = Table('orders', metadata, Column('order_id',
Integer(), primary_key=True), Column('user_id', ForeignKey('users.user_id')), Column('shipped', Boolean(), default=False) ) line_items = Table('line_items', metadata, Column('line_items_id', Integer(), primary_key=True), Column('order_id', ForeignKey('orders.order_id')), Column('cookie_id', ForeignKey('cookies.cookie_id')), Column('quantity', Integer()), Column('extended_cost', Numeric(12, 2)) )
2.7 表的持久化
所有的表和额外的schema定义都是和metadata实例相关联的,通过调用metadata.create_all(engine)方法,就可以持久化schema到数据库了,默认情况下,该方法别接受已经存在的表重建
一下一是一个完整的例子:
from datetime import datetime from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String, DateTime, ForeignKey, create_engine) metadata = MetaData() cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ) users = Table('users', metadata, Column('user_id', Integer(), primary_key=True), Column('customer_number', Integer(), autoincrement=True), Column('username', String(15), nullable=False, unique=True), Column('email_address', String(255), nullable=False), Column('phone', String(20), nullable=False), Column('password', String(25), nullable=False), Column('created_on', DateTime(), default=datetime.now), Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now) ) orders = Table('orders', metadata, Column('order_id', Integer(), primary_key=True), Column('user_id', ForeignKey('users.user_id')) ) line_items = Table('line_items', metadata, Column('line_items_id', Integer(), primary_key=True), Column('order_id', ForeignKey('orders.order_id')), Column('cookie_id', ForeignKey('cookies.cookie_id')), Column('quantity', Integer()), Column('extended_cost', Numeric(12, 2)) ) engine = create_engine('sqlite:///:memory:') metadata.create_all(engine)
三 通过SQLAlchemyCore操作数据
3.1 插入数据
构建一个INSERT 语句将数据插入到表中:
from sqlalchemy import Table,Column,MetaData,BigInteger,String,Integer,DateTime,create_engine,insert from datetime import datetime metadata = MetaData() items = Table('items',metadata, Column('id',BigInteger(),primary_key=True), Column('title',String(100),index=True), Column('sell_point',String(500),nullable=False), Column('price', BigInteger()), Column('num', Integer()), Column('barcode',String(30),nullable=False), Column('image',String(500),nullable=False), Column('cid',BigInteger()), Column('status',Integer()), Column('created',DateTime(), default=datetime.now), Column('updated',DateTime(),default=datetime.now,onupdate=datetime.now) ) # 创建引擎 engine = create_engine("mysql+pymysql://root:123456@localhost:3306/ecommerce",encoding='utf8') # 如果表不存在则创建 metadata.create_all(engine) # 通过引擎打开连接 conn = engine.connect() ''' 创建插入语句有以下两种方式: ''' # 第一种 ins1 = items.insert().values( title = "Aldssd dsdfdf ert sunshine ", sell_point = "clearance!!!", price = "3450", num = "999", barcode = "#1234565656#", image = "http://image.taotao.com/jd/4ef8861cf6854de9889f3db9b24dc371.jpg", cid = "560", status = "1" ) # 通过连接执行先前创建的插入语句 conn.execute(ins1) # 第二种 ins2 = insert(items).values( title="Thinking In Java ", sell_point="clearance!!!", price="68", num="78", barcode="#1234565656#", image="http://image.taotao.com/jd/4ef8861cf6854de9889f3db9b24dc371.jpg", cid="560", status="2" ) # 通过连接执行先前创建的插入语句 conn.execute(ins2) # 第三种 ins3 = items.insert() # 通过连接执行先前创建的插入语句 conn.execute( ins3, # 第一参数是一个需要执行的insert声明语句的函数的引用 title="Scala In Action", sell_point="clearance!!!", price="168", num="56", barcode="#1234565656#", image="http://image.taotao.com/jd/1118861cf6854de9129f3db9b24dc371.jpg", cid="560", status="3" ) # 第四种:同时挿入多条 ins4 = items.insert() data_list = [ { "title": "Scala In Action", "sell_point": "clearance!!!", "price": "168", "num": "56", "barcode" : "#233456124", "image" : "http://image.taotao.com/jd/1118861cf6854de9129f3db9b24dc371.jpg", "cid" : "560", "status" : "4" }, { "title": "Hadoop In Action", "sell_point": "clearance!!!", "price": "868", "num": "77", "barcode" : "#233456112", "image" : "http://image.taotao.com/jd/1118861cf6854de9129f3db9b24dc371.jpg", "cid" : "561", "status" : "5" } ] # 通过连接执行先前创建的插入语句 conn.execute(ins4,data_list)
3.2 更新数据
更新数据和插入数据其实差不多:
第一:需要使用update函数构造一个更新语句
第二:需要用where从句指定需要修改的条件
表对象.c : 表示表的列对象
表对象.c.quantity : 表示该表的哪一列
upt = update(cookies).where(cookies.c.cookie_name == 'chocolate chip') upt = upt.values(quantity=(cookies.c.quantity + 120)) result = conn.execute(upt) print("结果数量 => %s" %result.rowcount)
3.3 删除数据
第一: 需要使用delete函数构造一个删除语句
第二:使用where条件去过滤哪些数据需要被删粗
第三:如果没有指定where从句,那么会删除表中所有数据
# 删除数据 from sqlalchemy import delete d1 = delete(cookies).where(cookies.c.cookie_name == 'dark chocolate chip') d2 = delete(cookies) result1 = conn.execute(d1) result1 = conn.execute(d2)
3.4 查询数据
我们需要使用select函数构造一个查询语句构造一个类似于标准SELECT语句
3.4.1ResultProxy
是对cursor对象的一个包装类,它的主要目标是使得使用和操作结果集更加容易,比如索引,名字或者Column对象
s = select([items]) rs = conn.execute(s) # 返回一个ResultProxy对象 results = rs.fetchall() # 返回第一个记录RowProxy first_row = results[0] # 通过下标访问列的值 idx = first_row[0] # 通过列名访问列的值 title = first_row.title # 通过Column对象访问列的值 imgURL = first_row[items.c.image] # 便利ResultProxy对象 # for record in results: # print(record.title) ''' 通过fecthone也可以回去一个ResultProxy对象 fetchone: 因为是游标操作,所以取出一个少一个 调用一次fetchone,那么取出的是第一个数据 在调用一次,则是取出的第二个数据 ''' rs = conn.execute(s) record1 = rs.fetchone() record2= rs.fetchone() ''' 通过first也可以返回一个ResultProxy对象 first: 只会取第一个数据,如果已经取了,在调用这个方法就会报错 ''' rs = conn.execute(s) record1 = rs.first()
3.4.2 在查询中控制列
''' 我们可以限制查询中返回多少字段,我们需要把这些字段传递给select方法 ''' s = select([items.c.id,items.c.title,items.c.price,items.c.num]) proxy = conn.execute(s) # proxy.keys(): 可以取出我需要访问哪些列 record = proxy.first() print(record) # (1, 'Aldssd dsdfdf ert sunshine ', 3450, 999)
3.4.3 结果集排序
''' 对结果集排序,如果不指定排序规则,默认是升序排序 我们可以通过asc或者desc对象进行包装,然后进行圣湖或者降序排序 ''' from sqlalchemy import asc,desc s = select([items.c.id,items.c.title,items.c.price,items.c.num]) # 根据price列进行排序 s = s.order_by(items.c.price) proxy = conn.execute(s) records = proxy.fetchall() for r in records: print(r) ''' 或者你也可以你这么写 s = select([items.c.id,items.c.title,items.c.price,items.c.num]).order_by(items.c.price) ''' # 根据price列进行降序排序 s = select([items.c.id,items.c.title,items.c.price,items.c.num]).order_by(desc(items.c.price)) proxy = conn.execute(s) records = proxy.fetchall() for r in records: print(r)
3.4.4 限制取出记录
''' limit:对结果集的数量进行限制 ''' from sqlalchemy import asc,desc # 根据price列进行降序排序 s = select([items.c.id,items.c.title,items.c.price,items.c.num]).order_by(desc(items.c.price)).limit(2) proxy = conn.execute(s) records = proxy.fetchall() for r in records: print(r)
3.4.5 内置函数和别名
''' 使用sqlalchemy的内置函数,比如sum(),avg() 还可以对结算结果取别名 ''' from sqlalchemy import func s1 = select([func.sum(items.c.price).label('total_price')]) s2 = select([func.count(items.c.title).label('count')]) proxy1 = conn.execute(s1) proxy2 = conn.execute(s2) record1 = proxy1.first() record2 = proxy2.first() print(record1.total_price,record2.count)
3.4.6 过滤
''' where语句进行结果集过滤 ''' from sqlalchemy import func s = select([items.c.title,items.c.price,items.c.sell_point]).where(items.c.price == 868) proxy = conn.execute(s) records = proxy.fetchall() for record in records: # 返回一个元组列表,每一个元组都是列名和该列的值 print(record.items()) # [('title', 'Hadoop In Action'), ('price', 868), ('sell_point', 'clearance!!!')]
3.4.7CluaseElements
从句元素时我们只能在从句中使用的那些实体元素,比如like()进行模糊匹配等,以下举几个例子:
''' where语句进行结果集过滤 ''' s = select([items.c.title,items.c.price,items.c.sell_point]) # 模糊查询 like() 大小写敏感 ilike()大小写不敏感 slike = s.where(items.c.title.like('%_in action%')) # 范围查询 between sbetween = s.where(items.c.price.between(100,200)) # in([list]) sin = s.where(items.c.price.in_([168,868])) # is_(None):取出为空的数据 sisnone = s.where(items.c.sell_point.is_(None)) # startswith字符串以什么开始 sstart = s.where(items.c.price.title.startswith('clearance')) # endswith字符串以什么结束 send = s.where(items.c.price.title.endswith('clearance'))
3.4.8 操作符
+,-,*,/,%
==,!=,<,>,<=,>=
AND,OR,NOT,由于python关键字的原因,使用and_(),or_(),not_()来代替
+号还可以用于字符串拼接:
s = select([cookies.c.cookie_name, 'SKU-' + cookies.c.cookie_sku])
for row in connection.execute(s):
print(row)
from sqlalchemy import cast
s = select([cookies.c.cookie_name,
cast((cookies.c.quantity * cookies.c.unit_cost),
Numeric(12,2)).label('inv_cost')])
for row in connection.execute(s):
print('{} - {}'.format(row.cookie_name, row.inv_cost))
3.4.9 连词
from sqlalchemy import and_,or_,not_
s = select([items.c.title,items.c.price,items.c.sell_point])
# 模糊查询 like() 大小写敏感 ilike()大小写不敏感
s_and = s.where(
and_(
items.c.title.ilike('%_inaction%'),
items.c.price < 200
)
)
s_or = s.where(
or_(
items.c.title.contains('Scala'),
items.c.price > 800
)
)
s_not = s.where(
not_(items.c.price < 3000)
)
四 join 操作
conn = engine.connect() # 指定腰查询的列
columns = [orders.c.order_id,users.c.username,users.c.phone, cookies.c.cookie_name,line_items.c.quantity,line_items.c.extended_cost] cookiemon_order = select(columns) # 通过select_from查询数据
cookiemon_order = cookiemon_order.select_from(orders.join(users).join(line_items).join(cookies)).where(users.c.username == 'cookiemon') results = conn(cookiemon_order).fetchall() for row in results: print(row)
五 别名
''' alias 给表起别名 ''' employee = Table('employee',metadata, Column('eid',Integer(),primary_key=True,autoincrement=True), Column('manager_id',Integer()), Column('ename',String(255)) ) manager = employee.alias("mgr") select([employee.c.name]).where( and_( manager.c.manager_id == manager.c.eid, manager.c.name == 'Fred' ) )
六 分组
首先你要确定你需要对什么进行分组,然后分组的目的是什么
from datetime import datetime from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String, create_engine,insert,select,func,and_) metadata = MetaData() emp = Table( 'employee',metadata, Column('empno',Integer(),primary_key=True,nullable=False), Column('ename',String(30),nullable=False), Column('job',String(20),nullable=False), Column('mgr',Integer(),nullable=True,default=None), Column('hiredate',String(20),nullable=True,default=None), Column('sal',Numeric(5,2),nullable=True,default=None), Column('comm',Numeric(5,2),nullable=True,default=None), Column('deptno',Integer()), Column('dname',String(30)), Column('loc',String(30)) ) engine = create_engine("mysql+pymysql://root:123456@localhost:3306/sqlalchemy",encoding='utf8') metadata.create_all(engine) conn = engine.connect() columns = [emp.c.dname,func.sum(emp.c.sal)] all_emp = select(columns) all_emp = all_emp.group_by(emp.c.dname,emp.c.sal) proxy = conn.execute(all_emp) results = proxy.fetchall() for row in results: print(row)
七 利用原始的查询语句进行查询和text查询
# 利用原始的查询语句进行查询 results = conn.execute("SELECT * FROM employee").fetchall() for row in results: print(row) # 利用text查询 stmt = select([emp]).where(text("dname='DELIVERY'")) results = conn.execute(stmt).fetchall() for row in results: print(row)