sqlalchemy compile 打印sql

转载

IT独行侠客 2024-09-13 07:10:20

文章标签 sql sqlalchemy html 数据 文章分类 机器学习人工智能

插入数据insert()。新建一个py，insert.py

from manage import cookies

ins = cookies.insert().values(
    cookie_name = 'chocolate chip',
    cookie_recipe_url = 'http://some.aweso.me/cookie/recipe.html',
    cookie_sku = 'cc01',
    quantity='12',
    unit_cost='0.50',
    )
print(str(ins))    #看 实际执行的SQL语句，用str()

输出
INSERT INTO cookies (cookie_name, cookie_recipe_url, cookie_sku, quantity, unit_cost) 
VALUES (:cookie_name, :cookie_recipe_url, :cookie_sku, :quantity, :unit_cost)

在编译后的 SQL语句中，values都被替换为 :column_name 的形式，这样可以帮助确保数据被正确转义，从而减少SQL注入攻击等安全问题。

也有方法可以看到编译后的语句的实际参数。compile()返回一个SQLCompiler object，.params 属性访问实际参数。

print(ins.compile().params)

{'cookie_name': 'chocolate chip', 'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe.html', 'cookie_sku': 'cc01', 'quantity': '12', 'unit_cost': '0.50'}

这时的数据还没进入数据库，要用 执行语句execute()。insert.py。

from manage import connection

result=connection.execute(ins)    #由于cookies的每个列都没有唯一性约束，所以重复执行能重复插入
print(result.inserted_primary_key)    #获得刚才插入的数据的ID

execute()将传入的 sql表达式语句编译为 sql语句，然后发给数据库执行。数据库执行完返回操作结果。
除了Table.insert()外，还可以直接使用insert()。
from sqlalchemy import insert ins = insert(cookies).values( cookie_name='chocolate chip', cookie_recipe_url = 'http://some.aweso.me/cookie/recipe.html', cookie_sku = 'cc01', quantity='12', unit_cost='0.50', ) #这种用法更接近常见的sql语句 result=connection.execute(ins)
insert()时可以不加value，execute()时再加。
ins = cookies.insert() result = connection.execute( ins, cookie_name = 'chocolate chip', cookie_recipe_url = 'http://some.aweso.me/cookie/recipe.html', cookie_sku = 'cc01', quantity='12', unit_cost='0.50', ) print(result.inserted_primary_key)
同时插入多个数据。用 字典列表。所有字典必须有相同的 key，否则报错。
ins = cookies.insert() inventory_list = [ { 'cookie_name':'peanut butter', 'cookie_recipe_url':'http://some.aweso.me/cookie/recipe.html', 'cookie_sku':'cc01', 'quantity':'12', 'unit_cost':'0.50', }, { 'cookie_name':'peanut butter2', 'cookie_recipe_url':'http://some.aweso.me/cookie/recipe2.html', 'cookie_sku':'cc02', 'quantity':'14', 'unit_cost':'0.80', } ] result = connection.execute(ins, inventory_lst)

查询数据select()。insert.py

from sqlalchemy.sql import select
s = select([cookies])    #选择cookies表，和insert不同，必须要加[]
#也可以用 s = cookies.select()
rp = connection.execute(s)    #执行
results = rp.fetchall()    #fetchall()返回所有行，是一个元组列表
print(str(s))    #查看SQL语句
print(rp)    #<sqlalchemy.engine.result.ResultProxy object at 0x00000165F5D7BFD0> 一个ResultProxy 对象
print(results)
'''
[(1, 'chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'cc01', 12, Decimal('0.50')), 
(2, 'chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'cc01', 12, Decimal('0.50')), 
(3, 'chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'cc01', 12, Decimal('0.50')), 
(4, 'chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'cc01', 12, Decimal('0.50')), 
(5, 'chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'cc01', 12, Decimal('0.50'))]
'''

ResultProxy对象，是DBAPI游标对象的包装器(给每个语句返回的结果加一层包装)，作用是让语句返回的结果更容易使用和操作，对每个返回的结果都能通过索引result[0],result[1]、属性名称result.cookie_name、列对象result[cookies.c.cookie_name] 来访问其中的元素。(使这三种方式都可以用，并导向同样的元素)令访问更加灵活。

print(results[0][1])
print(results[0].cookie_name)
print(results[0][cookies.c.cookie_name])    #column对象

ResultProxy是可迭代对象。(推荐)

#不使用fetchall()
s = select([cookies])
rp = connection.execute(s)
for record in rp:    #不用fetchall()，直接for遍历
    print(record.cookie_name)

其他查询方法：first(), fetchone(), scalar()

rp = connection.execute(s)
print(rp.first())    #若有记录，则返回第一个记录并 关闭连接。

rp = connection.execute(s)
print(rp.fetchone())    #返回一行，并保持光标为打开状态
for record in rp:    #这里的迭代 从fetchone后面开始
    print(record.cookie_name)

rp = connection.execute(s)
print(rp.scalar())    #如果查询结果是 包含一个列的单条记录，则返回单个值。

尽量使用first() 和迭代。避免使用fetchone()，因为连接会一直打开。当使用scalar()时，如果查询返回多行多列，则会报错。

查询指定的列，避免查完整的行，减少开销。

s = select([cookies.c.cookie_name, cookies.c.quantity])
rp = connection.execute(s)
print(rp.keys())    #没有.values()、.items()
result = rp.first()
print(result)

['cookie_name', 'quantity']
('chocolate chip', 12)

排序order_by()。

s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)    #升序
#或s = select([cookies.c.cookie_name, cookies.c.quantity]).order_by(cookies.c.quantity)    #太长了，不符合PEP8标准
#from sqlalchemy import desc
#s = s.order_by(desc(cookies.c.quantity))    #降序
rp = connection.execute(s)
for cookie in rp:
    print(cookie.quantity, cookie.cookie_name)
    #print('{} - {}'.format(cookie.quantity, cookie.cookie_name))    #带格式

limit()限制返回结果的行数。first() 和 fetchone()虽然在结果上仅提供了我们请求的一行，但实际查询运行时会访问所有结果，浪费时间和内存。limit()则不会

s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)
s = s.limit(2)    #限制只查询两条
rp = connection.execute(s)
for cookie in rp:
    print(cookie.cookie_name)

内置SQL函数。例：sum(), count()

许多数据库内置SQL函数的目的：让某些操作可以直接在数据库服务器上使用，如SUM和COUNT

from sqlalchemy.sql import func
s = select([func.sum(cookies.c.quantity)])    #这些函数对 列 使用
#import func避免直接import sum和python的sum冲突
rp = connection.execute(s)
print(rp.scalar())

from sqlalchemy.sql import func
s = select([func.count(cookies.c.cookie_name)])
rp = connection.execute(s)
record = rp.first()
print(record)
print(record.keys())    #keys一般是<func_name>_<position>
print(record.count_1)    #count_1    count源自count(),1是因为上述查询只用了一次count()
#如果查询中有多个count(),则第四个count()的统计结果对应的keys会是count_4

标签label()。 像count_1234这样的keys命名，多了以后就不方便了，可以用label自定义keys

from sqlalchemy.sql import func
s = select([func.count(cookies.c.cookie_name).label('inventory_count')])
rp = connection.execute(s)
record = rp.first()
print(record.keys())    #count_1被改为inventory_count
print(record.inventory_count)

过滤（筛选）。where()

s = select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')    #找cookie_name列名是chocolate chip的
rp = connection.execute(s)
record = rp.first()
print(record.quantity)

在where的字句里可以把列作为ClauseElement, ClauseElement有许多方法。P22

s = select([cookies]).where(cookies.c.cookie_name.like('%chocolate%'))    #cookie_name列中包含chocolate的
rp = connection.execute(s)
for record in rp.fetchall():
    print(record.cookie_name)

运算符。可以用于对数据中的字符串作连接处理，按我们想要的方式输出。（当然，在print()里实现也一样。）

s = select([cookies.c.cookie_name, 'SKU-' + cookies.c.cookie_sku])
for row in connection.execute(s):
    print(row)

布尔运算符 & | ~ 尽量不用。多用连接词

连接词 and_(), or_(), not_()

from sqlalchemy import and_, or_, not_
s = select([cookies]).where(
    and_(
        cookies.c.quantity > 10,
        cookies.c.unit_cost < 0.6,
        )
    )
for row in connection.execute(s):
    print(row)

from sqlalchemy import and_, or_, not_
s = select([cookies]).where(
    or_(
        cookies.c.quantity.between(10,50),        #ClauseElement的方法
        cookies.c.cookie_name.contains('chip')    #ClauseElement的方法
        )
    )
for row in connection.execute(s):
    print(row)

更新数据update()。和insert()的语法几乎一样,但可以用where指定要更新的行。

from sqlalchemy import update
u = update(cookies).where(cookies.c.cookie_id==2)    #update里不需要加[]
u = u.values(quantity=24,cookie_name='black chocolate chip')
result = connection.execute(u)
print(result.rowcount)    #打印更新的行数

s = select([cookies]).where(cookies.c.cookie_id==2)
rp = connection.execute(s)        #ResultProxy object
print(rp.first())

result = rp.first()
for key in result.keys():
    print('{:>20}: {}',format(key, result[key]))    #20格，右对齐

删除数据delete()。不加where()则表示删除表中所有行。

from sqlalchemy import delete
u = delete(cookies).where(cookies.c.cookie_id==5)
result = connection.execute(u)
print(result.rowcount)    #打印删除的行数

s = select([cookies]).where(cookies.c.cookie_id==5)
rp = connection.execute(s).fetchall()    #元组列表
print(len(rp))

加点数据。

from manage import users
customer_list = [
    {
        'username': 'cookiemon',
        'email_address': 'mon@cookie.com',
        'phone': '111-111-1111',
        'password': 'password'
    },
    {
        'username': 'cakeeater',
        'email_address': 'cakeeater@cake.com',
        'phone': '222-222-2222',
        'password': 'password'
    },
    {
        'username': 'pieguy',
        'email_address': 'guy@pie.com',
        'phone': '333-333-3333',
        'password': 'password'
    },
]
ins = users.insert()
result = connection.execute(ins, customer_list)

from manage import orders, line_items
from sqlalchemy.sql import insert

ins = insert(orders).values(user_id=1, order_id=1)
result = connection.execute(ins)

ins = insert(line_items)
order_items = [
    {
        'order_id': 1,
        'cookie_id': 1,
        'quantity': 2,
        'extended_cost': 1.00,
    },
    {
        'order_id': 1,
        'cookie_id': 3,
        'quantity': 12,
        'extended_cost': 3.00,
    },
]
result = connection.execute(ins, order_items)

ins = insert(orders).values(user_id=2, order_id=2)
result = connection.execute(ins)

ins = insert(line_items)
order_items = [
    {
        'order_id': 2,
        'cookie_id': 1,
        'quantity': 24,
        'extended_cost': 12.00,
    },
    {
        'order_id': 2,
        'cookie_id': 4,
        'quantity': 6,
        'extended_cost': 6.00,
    },
]
result = connection.execute(ins, order_items)

连接join()。关联与该数据有关的其他表中的数据。

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_orders = select(columns)
cookiemon_orders = cookiemon_orders.select_from(orders.join(    #如果没有这行，则会按照columns里的顺序查询所有结果
                users).join(line_items).join(cookies)).where(
                users.c.username == 'cookiemon')        #print(str())看sql会很清楚
result = connection.execute(cookiemon_orders)
for row in result:
    print(row)

join按远近顺序，与是否直接相连没有关系。例，第三行可以改为...orders.join(line_items).join(users).join(cookies)).where(.... ，users和cookies不直接相连，但line_items和users都比cookies离orders近；或者先完成一条链，...orders.join(line_items).join(cookies).join(users)).where(.... ，这样也行。
sqlalchemy通过外键知道了如何连接各个表

外连接outerjoin()。sql里叫全外连接，会把没有orders的users也算进来。

from sqlalchemy.sql import func
columns = [users.c.username, func.count(orders.c.order_id)]
all_orders = select(columns)
all_orders = all_orders.select_from(users.outerjoin(orders))
all_orders = all_orders.group_by(users.c.username)
result = connection.execute(all_orders).fetchall()
for row in result:
    print(row )

用outerjoin
('cakeeater', 1)
('cookiemon', 1)
('pieguy', 0)
用join
('cakeeater', 1)
('cookiemon', 1)

别名alias(). 一般在自引用的时候用。

分组 group_by()

from sqlalchemy.sql import func
columns = [users.c.username, func.count(orders.c.order_id)]
all_orders = select(columns)
all_orders = all_orders.select_from(users.outerjoin(orders))
all_orders = all_orders.group_by(users.c.username)    #按username分组
result = connection.execute(all_orders).fetchall()
for row in result:
    print(row )

链式调用。上述很多案例都是链式调用，下面把查询链放入函数中

def get_order_by_customer(cust_name):
    columns = [orders.c.order_id, users.c.username, users.c.phone,
                cookies.c.cookie_name, line_items.c.quantity,
                line_items.c.extended_cost]
    cust_orders = select(columns)
    cust_orders = cust_orders.select_from(users.join(orders).join(line_items).join(cookies))
    cust_orders = cust_orders.where(users.c.username == cust_name)
    result = connection.execute(cust_orders).fetchall()
    return print(result)

get_order_by_customer('cakeeater')

带条件的链式调用。添加条件语句，使函数功能更灵活

def get_order_by_customer(cust_name, shipped=None, details=False):
    columns = [orders.c.order_id, users.c.username, users.c.phone]
    joins = users.join(orders)

    if details:
        columns.extend([cookies.c.cookie_name, line_items.c.quantity,
                        line_items.c.extended_cost])
        joins = joins.join(line_items).join(cookies)
    cust_orders = select(columns)
    cust_orders = cust_orders.select_from(joins)
    cust_orders = cust_orders.where(users.c.username == cust_name)

    if shipped is not None:
        cust_orders = cust_orders.where(orders.c.shipped == shipped)
    result = connection.execute(cust_orders).fetchall()
    return print(result)

get_order_by_customer('cakeeater')
get_order_by_customer('cakeeater',shipped=True)
get_order_by_customer('cakeeater',details=True)