Python库积累之pandasql：在 Python 中对Dataframe使用 SQL_python dataframe sqldf

Pandasql简介

Pandasql是一个可以让我们直接在Python中对Dataframe进行SQL查询的库。

Python中虽然内置有sqlite数据库，但是如果我们使用sqlite进行查询的话我们需要将原始数据插入sqlite后才可以使用SQL语句。同时Python中的Pandas在有些数据处理计算中不如SQL简洁易读（具体的用pandas实现sql常用操作的代码对照可参照文末的补充部分，通过这个对照你可以更好的了解两者的区别），这时候Pandasql可以很好的解决这个问题

在R中也有一个类似的库sqldf，同样可以直接在R中对数据集进行SQL查询。

安装

pip install -U pandasql
1

其他安装方法请查看Python包/库的安装方法

用法

pandasql中使用的主要函数是sqldf。sqldf有2个参数

• sql查询字符串
• 一组会话/环境变量（locals（）或globals（））

一般使用时我们会定义一个lambda表达式来使用这个函数。

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

这样可以使代码更可读同时在多次使用时也更加方便

实例

pandasql使用SQLite语法。我们使用Pandasql的内置数据集：

from pandasql import sqldf, load_meat, load_births
pysqldf = lambda q: sqldf(q, globals())
meat = load_meat()
print(pysqldf("SELECT * FROM meat LIMIT 10;"))
1
2
3
4

Join语句：

from pandasql import sqldf, load_meat, load_births
pysqldf = lambda q: sqldf(q, globals())
meat = load_meat()
births = load_births()
 q = """SELECT
        m.date, m.beef, b.births
     FROM
        meats m
     INNER JOIN
        births b
           ON m.date = b.date;"""
 joined = pyqldf(q)
 print(joined.head())
1
2
3
4
5
6
7
8
9
10
11
12
13

注意事项

1.no such table报错
pandasql在执行时是读取在内存中的dataframe名，因此执行的SQL语句一定要放在主程序中。举个例子：

import pandas as pd
from pandasql import sqldf

def read_file(file_path):
    load_info=pd.read_excel(file_path)
    pysqldf = lambda q: sqldf(q, globals())
    q = "SELECT * FROM load_info"
    dataframe=pysqldf(q)
    return dataframe
    
if __name__=='__main__':
    file_path=r"C:\Users\load_info.xlsx"
    read_file(file_path)
1
2
3
4
5
6
7
8
9
10
11
12
13

运行后会报错：no such table: load_info，解决办法为将pandasql部分放到主程序中，或者将dataframe设为全局变量

def read_file(file_path):
    global load_info
    load_info = pd.read_excel(file_path)
    pysqldf = lambda q: sqldf(q, globals())
    q = "SELECT * FROM load_info"
    dataframe = pysqldf(q)
    return dataframe
1
2
3
4
5
6
7

2.处理不规范的字段名
当表的名字或列的名字中，含有空格等一些特殊字符时，我们需要用[] 将表名引起来，告诉语法分析器，[]号内的才是一个完整的名称。比如
Select * from [Order Details]

补充部分：使用pandas实现sql常用操作

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条数据

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
59

参考：https://blog.csdn.net/t13698859293/article/details/104325438
pandasql项目地址：https://github.com/yhat/pandasql

↓↓↓欢迎关注我的公众号，在这里有数据相关技术经验的优质原创文章↓↓↓