python sql 图片库_PyPika：Python语法风格的SQL处理库

PyPika

PyPika是一个用于构建SQL查询的Python API， PyPika库作者的动机是为构建SQL查询提供一个简单的界面。

在python中写sql语句体验不好，我们都是用字符串写的，写的过程中编辑器不会给咱们自动补全。

SELECT "id","fname","lname","phone" FROM "customers" ORDER BY "id" DESC

安装

pypika支持2.7和3.3+.python pip3 install pypika

Select Data

"""

#从customers表中提取相关信息

SELECT id,fname,lname,phone FROM customers

"""from pypika import Query,Table,Field

#创建用Table对象customers = Table('customers')#customers是对象，有相关的属性值q = Query.from_(customers).select(customers.id, customers.fname, customers.lname, customers.phone)#q = Query.from_('customers').select('id', 'fname', 'lname', 'phone')

增-Insert

"""

#单条数据插入

INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')

"""customers = Table('customers')q = Query.into(customers).insert(1, 'Jane', 'Doe', 'jane@example.com')"""

#查入多条数据

INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com'),(2,'John','Doe','john@example.com')

"""customers = Table('customers')q = Query.into(customers).insert((1, 'Jane', 'Doe', 'jane@example.com'),

(2, 'John', 'Doe', 'john@example.com'))

更新-Update

"""

UPDATE "customers" SET "last_login"='2017-01-01 10:00:00'

UPDATE "customers" SET "lname"='smith' WHERE "id"=10

"""customers = Table('customers')Query.update(customers).set('last_login', '2017-01-01 10:00:00')Query.update(customers).set('lname', 'smith').where(customers.id == 10)

排序

"""

SELECT "id","fname","lname","phone" FROM "customers" ORDER BY "id" DESC

"""from pypika import Order

Query.from_('customers').select('id', 'fname', 'lname', 'phone').orderby('id', order=Order.desc)

四则算法

两个变量相减

"""

SELECT revenue-cost FROM accounts

"""from pypika import Field

q = Query.from_('account').select(

Field('revenue') - Field('cost'))#accounts = Table('accounts')#q = Query.from_(accounts).select( accounts.revenue - accounts.cost)

"""

#将两个变量相减得到的结果起个名字

SELECT revenue-cost profit FROM accounts

"""q = Query.from_(accounts).select(

(accounts.revenue - accounts.cost).as_('profit'))

更多的四则运算例子

"""

SELECT foo+bar,foo-bar,foo*bar,foo/bar,(foo+bar)/fiz FROM table

"""table = Table('table')q = Query.from_(table).select(

table.foo + table.bar,

table.foo - table.bar,

table.foo * table.bar,

table.foo / table.bar,

(table.foo+table.bar) / table.fiz)

1.4 IN/Between

"""

#where条件语句

SELECT id,fname,lname,phone FROM customers WHERE lname='Mustermann'

"""customers = Table('customers')q = Query.from_(customers).select(

customers.id, customers.fname, customers.lname, customers.phone).where(

customers.fname == 'Max').where(

customers.lname == 'Mustermann')

AND

"""

SELECT id,fname FROM customers WHERE age BETWEEN 18 AND 65 AND status IN ('new','active')

"""customers = Table('customers')q = Query.from_(customers).select(

customers.id,customers.fname).where(

customers.age[18:65] & customers.status.isin(['new', 'active']))

OR

"""

SELECT id,fname,lname,phone FROM customers WHERE age>=18 OR lname='Mustermann'

"""customers = Table('customers')q = Query.from_(customers).select(

customers.id, customers.fname, customers.lname, customers.phone).where(

(customers.age >= 18) | (customers.lname == 'Mustermann'))

Grouping &Aggregating

"""

SELECT id,SUM(revenue) FROM customers WHERE age>=18 GROUP BY id ORDER BY id ASC

"""from pypika import functions as fn

customers = Table('customers')q = Query.from_(customers).where(

customers.age >= 18).groupby(

customers.id).select(

customers.id, fn.Sum(customers.revenue))"""

SELECT customer_id,SUM(total) FROM payments

WHERE transacted BETWEEN '2015-01-01' AND '2016-01-01'

GROUP BY customer_id HAVING SUM(total)>=1000

"""from pypika import functions as fn

payments = Table('payments')q = Query.from_(payments).where(

payments.transacted[date(2015, 1, 1):date(2016, 1, 1)]).groupby(

payments.customer_id).having(

fn.Sum(payments.total) >= 1000).select(

payments.customer_id, fn.Sum(payments.total))

觉得有收获可以送支鹅毛信哦~(^_^)

历史文章：

数据采集

文本处理分析

图片数据处理

其他