Python-Dataframe SQL教程

发表于 分类于 阅读次数： Changyan： 本文字数： 1.4k 阅读时长 ≈ 5 分钟

在 Python 中，对pandas.Dataframe进行 SQL 查询主要有两种操作方式，分别为pandas的基本操作语句，以及pandasql库提供的直接对Dataframe进行 SQL 查询的操作。

虽然 Python 内置了sqlite数据库模块，但是其与Dataframe交互不方便，需要将原始数据插入sqlite后才可以使用 SQL 语句进行查询操作，因此一般还是用上述两种方法来完成 SQL 查询。

sqlite的操作流程：

1
2
3
4
5
6
7
8
9
10
11
12
import sqlite3
conn = sqlite3.connect('example.db')
# 连接后创建一个 Cursor 对象并调用其 execute() 方法来执行 SQL 命令
c = conn.cursor()
# 创建表
c.execute('''CREATE TABLE stocks(date text, trans text, symbol text, qty real, price real)''')
# 添加一行数据
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
# 保存修改
conn.commit()
# 关闭连接
conn.close()

两种方式的优缺点： Pandas 基本操作语句 - 优点：不需要额外安装第三方库，大多数场景内置函数都可以满足需求 - 缺点：在有些数据处理计算中不如 SQL 语言简洁易读（如大量的.groupby()、.sum()或者.count()之类），且无法实现部分复杂功能

Pandasql 库 - 优点：直接使用 SQL 语句完成查询操作，易读性高且可移植性好 - 缺点：

Pandasql 库

pandasql允许您使用 SQL 语法查询pandas.DataFrame。它的工作原理与R.pandasql中的sqldf类似。pandasql试图为刚接触 Python 或pandas的人提供一种更熟悉的操作和清理数据的方法。

安装方式

1
pip install pandasql

相关文档：https://pypi.org/project/pandasql/

使用方法

pandasql中使用的主要函数是sqldf。sqldf接受 2 个参数： - 一个 SQL 查询字符串 - 一组会话/环境变量（locals()或globals()）

一般使用时我们会定义一个简短的 lambda 表达式来使用这个函数： 

1
2
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())

查询实例

pandasql使用 SQLite 语法，将自动检测所有的pandas.DataFrame名称。可以像查询任何常规 SQL 表一样查询它们。

1
2
3
4
from pandasql import sqldf, load_meat, load_births
pysqldf = lambda q: sqldf(q, globals())
meat, births = load_meat(), load_births()
print pysqldf("SELECT * FROM meat LIMIT 10;").head()
1
2
3
4
5
6
                   date  beef  veal pork  lamb_and_mutton  broilers  other_chicken  turkey
0   1944-01-01 00:00:00   751    85 1280               89      None           None    None
1   1944-02-01 00:00:00   713    77 1169               72      None           None    None
2   1944-03-01 00:00:00   741    90 1128               75      None           None    None
3   1944-04-01 00:00:00   650    89  978               66      None           None    None
4   1944-05-01 00:00:00   681   106 1029               78      None           None    None

Where 条件语句： 

1
2
3
4
5
q = """SELECT date, veal, lamb_and_mutton
       FROM meat
       WHERE lamb_and_mutton >= veal
       ORDER BY date DESC;"""
print pysqldf(q).head()
1
2
3
4
5
                  date  veal  lamb_and_mutton
0  2012-11-01 00:00:00  10.1             12.4
1  2012-10-01 00:00:00  10.3             14.2
2  2012-09-01 00:00:00   8.8             12.5
3  2012-08-01 00:00:00  10.1             14.2

Join 连接语句： 

1
2
3
4
5
q = """SELECT m.date, m.beef, b.births
       FROM meats m
       INNER JOIN births b
       ON m.date = b.date;"""
print pysqldf(q).head()
1
2
3
4
5
6
                    date    beef  births
403  2012-07-01 00:00:00  2200.8  368450
404  2012-08-01 00:00:00  2367.5  359554
405  2012-09-01 00:00:00  2016.0  361922
406  2012-10-01 00:00:00  2343.7  347625
407  2012-11-01 00:00:00  2206.6  320195

Aggregation 聚合语句： 

1
2
3
4
q = """SELECT strftime('%Y', date) AS year, SUM(beef) AS beef_total
       FROM meat
       GROUP BY year;"""
print pysqldf(q).head()
1
2
3
4
5
6
   year  beef_total
0  1944        8801
1  1945        9936
2  1946        9010
3  1947       10096
4  1948        8766

注意事项

no such table报错

pandasql在执行时是读取在内存中的dataframe名称，因此执行的 SQL 语句一定要放在主程序中，或者将dataframe设为全局变量。

处理不规范的字段名

当表的名字或列的名字中，含有空格或关键字等一些特殊字符时，我们需要用[]将表名引起来，告诉语法分析器[]号内的才是一个完整的名称。比如SELECT * FROM [Order Details]

Pandas 基本操作语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
import pandas as pd
import numpy as np

df=pd.read_csv("") #默认输入csv文件路径
df.head() #读取前5行数据（输入数字就取对应开始的行数）

# 1.select操作：select 列名 from 表A limit 5
df[["","",""]].head(5)

# 2.where条件：select 列名 from 表名 where 条件=''
# 使用括号的方式进行 连接多个条件
condition=(df["列名"]=="")&(df["列名"]=="")&(df["列名"]=="")
condition.value_count()
df[condition].head(5)

# 3.in和not in：select * from 表A where 列名 in('1','2')
df["列名"].unique()   # 去重
# in
df[df["列名"].isin((1,2))].head()
# not in:df取反符号为~
df[~df["列名"].isin((1,2))].head()

# 4.1group by：select sum(),maen() from person group by sex
df.groupby("sex").agg({列名：np.sum,"列名":np.mean,"列名":np.mean})

# 4.2.多个列的聚合：select sum（）,mean() from person group by sex,department
df.groupby(["sex","department"]).agg({列名：np.sum,"列名":np.mean,"列名":np.mean})

# 5.join数据关联：select * from table1 a1 join table2 a2 on a1.列名=a2.列名 limit 5
df2=pd.read_csv()
df2.head(5)  # 查看前5行数据
df_merged=pd.merge(left=df1,right=df2,on="列名")
df_merged.head(5)

# 6.Union数据合并，当两个表的数据列一致时使用union。
# SQL:selet city，rank from 表1 union all select city，，rank from 表2
# 同样的df也需要有相同的列名，假设df1和df2的列名是一致的
pd.concat([df1,df2])

# 7.order limnit先排序后分页
# sql：select * from 表1 order by 列名 limit 5
df.sort_values("列名",ascending=False).head(5)

# 8.取每个分组group的top n：mysql不支持，oracle使用rownum
# 根据列名1，列名2分组，取列名3的top2
df.groupby(["列名1","列名2"]).apply(lambda df:df.sort_values("列名3",ascending=False).head(2))

# 9.update数据更新：update 表1 set 列名1=value where 条件
df.info() #查看df的信息
condition=df["列名"].isna()
condition.values_counts()
df[condition]=value
df["列名"].isna().values_counts()

# 10.delete删除数据：delete from 表1 where 列名=0
# df取反条件的值付给新的df
df_new=df[df["列名"]!=0]
df_new[df_new["列名"]=0] #可以得到0条数据

参考：https://blog.csdn.net/qq_42692386/article/details/113886419