阳光彩虹小白马

一、基本操作

连接数据库

from sqlalchemy import create_engine, MetaData, Table, and_, or_,insert,Column
from sqlalchemy.orm import sessionmaker

engine = create_engine(连接串)
fbing = engine.connect()

Session = sessionmaker(bind=fbing)
session = Session()

metadata = MetaData(bind=fbing, schema='yt_jkdpi_gd')

创建表

users_table = Table('test', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('email', String(50))
)
metadata.create_all(fbing)

与表建立连接

gd_table = Table('d_et_order_iptv_all', metadata, autoload_with=engine)

执行sql

session.query(查询条件)
session.commit()

执行原生sql

fbing.execute('select * from tableg')

展示dlist的前3行

stmt = session.query(users).limit(3)

取出users的name和age列

stmt = session.query(users.c.name, users.c.age)

取出users的name列更名为vname

stmt = session.query(users.c.name.label('vname'))

取出users第2到第5的行

stmt = session.query(users).limit(5).offset(2)

取出第2到第5的行的name和age列

stmt = session.query(users.c.name, users.c.age).limit(5).offset(2)

去重查询age列

stmt = session.query(users.c.age.distinct())

子查询

stmt1 = session.query(func.max(users.c.age)).subquery()
stmt2 = session.query(users).filter(users.c.age == stmt1)

查询age列去重后的行数

stmt = session.query(func.count(users.c.age.distinct()))

二、条件查询

取出age值缺失的行

stmt = session.query(users).filter(users.c.age.is_(None))

取出pop在400,800间的行

condition = and_(vsers.c.pop>400,vsers.c.pop<800)
stmt = session.query(vsers).filter(condition)

取出pop在400,800间，且country为’中国’的行

condition1 = and_(vsers.c.pop > 400,vsers.c.pop <= 1000)
condition2 = and_(condition1,vsers.c.country == '中国')
stmt = session.query(vsers).filter(condition2)

取出非空的行

condition1 = vsers.c.pop.isnot(None)
condition2 = vsers.c.country.isnot(None)
condition = and_(condition1,condition2)
stmt = session.query(vsers).filter(condition)

case when 查询

results = session.query(
    case([
            (MyTable.c.age < 18, '未成年'), 
            (MyTable.c.age >= 18, '成年')
         ], 
         else_='未知'
         ).label('age_category')

三、分组

计算每个age的name的数量

stmt = session.query(
    func.count(users.c.name),
    users.c.age
    ).group_by(users.c.age)

计算每个age的region等于日本的name数量

condition = users.c.region == '日本'
stmt = session.query(
    func.count(users.c.name),
    users.c.age
    ).filter(condition).group_by(users.c.age)

取出region无重复的行

select_query1 = session.query(
    func.count(users.c.name).label('name_count'),
    users.c.region
    ).group_by(users.c.region).subquery()

select_query2 = session.query(
    select_query1.c.region
    ).filter(select_query1.c.name_count < 2).subquery()

stmt = session.query(users).filter(users.c.region == select_query2)

数据透视表

stmt = session.query(
    users.c.age,
    users.c.sex,
    func.count(users.c.name)
    ).group_by(users.c.age,users.c.sex)

四、排序

按pop排列

stmt = session.query(vsers).order_by(vsers.c.pop)

五、添加数据

在vsers中添加country=‘John’, pop=25

new_record = insert(vsers).values(['John', 25])
session.execute(new_record)

六、更新数据

vsers的country为越南的记录，将country改为'南越'
upp = {"country": "南越"}
session.query(vsers).filter(vsers.c.country == '越南').update(upp)

类型转换查询

stmt = session.query(cast(users.c.age,Float))

类型转换

upp = {users.c.age: cast(users.c.age,String)}
session.query(users).update(upp)

region等于’中国’的age全部加33

condition = users.c.region == '中国'
upp = {users.c.age: users.c.age + 33}
session.query(users).filter(condition).update(upp)

七、模糊查找

name里包含‘佬’的行

stmt = session.query(users).filter(users.c.name.like('%佬%'))

name截取1~2位，更名为first_name

stmt = session.query(func.substring(users.c.name, 1, 2).label('first_name'))

八、删除数据

删除cname为yee的行

condition = users.c.name == '变态佬'
session.query(users).filter(condition).delete()

九、多表

查找vsers的pop>= 1000的记录，并获取该记录的country，并查找users中region在country的记录

stmt1 = session.query(vsers.c.country).filter(vsers.c.pop >= 1000)
condition = users.c.region.in_(stmt1)
stmt = session.query(users).filter(condition)

左关联

stmt = session.query(users).join(vsers, users.c.region == vsers.c.country, isouter = 'left')

挑选users的所有城市和vsers的所有城市，合并一起

stmt1 = session.query(users.c.region)
stmt2 = session.query(vsers.c.country)
stmt_join = stmt1.union_all(stmt2)