python sqlalchemy core

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_engineengine = 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,MetaDatametadata = 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 datetimefrom sqlalchemy import DateTimeusers = 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: 表示该字段是否允许为nulldefault：表示如果该字段没有设置值的时候的默认值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 datetimefrom 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,insertfrom datetime import datetimemetadata = 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 deleted1 = 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()# 返回第一个记录RowProxyfirst_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,descs = 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 funcs1 = 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 funcs = 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%'))# 范围查询 betweensbetween = 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 datetimefrom 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)