pysqldf 1.2.3

安装

$ pip install pysqldf

基本知识

pysqldf中的主类是SQLDF。SQLDF接受1个环境 构造函数中的变量集或多个参数。-一套 会话/环境变量（有效值字典，locals()或 globals()）-临时文件类型-用户定义函数-用户 定义的聚合函数

pysqldf使用SQLite syntax。 任何可转换为pandasdataframes的数据都将自动 由pysqldf检测。您可以像查询任何常规sql一样查询它们 桌子。

$python>>>frompysqldfimportSQLDF,load_meat,load_births>>>sqldf=SQLDF(globals())>>>meat=load_meat()>>>births=load_births()>>>printsqldf.execute("SELECT * FROM meat LIMIT 10;").head()datebeefvealporklamb_and_muttonbroilersother_chickenturkey01944-01-0100:00:0075185128089NoneNoneNone11944-02-0100:00:0071377116972NoneNoneNone21944-03-0100:00:0074190112875NoneNoneNone31944-04-0100:00:006508997866NoneNoneNone41944-05-0100:00:00681106102978NoneNoneNone>>>q="SELECT m.date, m.beef, b.births FROM meat m INNER JOIN births b ON m.date = b.date;">>>printsqldf.execute(q).head()datebeefbirths4032012-07-0100:00:002200.83684504042012-08-0100:00:002367.53595544052012-09-0100:00:002016.03619224062012-10-0100:00:002343.73476254072012-11-0100:00:002206.6320195>>>q="SELECT strftime('%Y', date) AS year, SUM(beef) AS beef_total FROM meat GROUP BY year;">>>printsqldf.execute(q).head()yearbeef_total0194488011194599362194690103194710096419488766

用户定义函数和用户定义聚合函数 支持。

$python>>>frompysqldfimportSQLDF,load_iris>>>importmath>>>importnumpy>>>ceil=lambdax:math.ceil(x)>>>udfs={"ceil":lambdax:math.ceil(x)}>>>udafs={"variance":lambdavalues:numpy.var(values)}>>># or you can also define aggregation function as class>>># class variance(object):...#     def __init__(self):...#         self.a = []...#     def step(self, x):...#         self.a.append(x)...#     def finalize(self):...#         return numpy.var(self.a)...>>># udafs={ "variance": variance }>>>iris=load_iris()>>>sqldf=SQLDF(globals(),udfs=udfs,udafs=udafs)>>>sqldf.execute("""
    SELECT
        ceil(sepal_length) AS sepal_length,
        ceil(sepal_width) AS sepal_width,
        ceil(petal_length) AS petal_length,
        ceil(petal_width) AS petal_width,
        species
    FROM iris;
    """).head()sepal_lengthsepal_widthpetal_lengthpetal_widthspecies06421Iris-setosa15321Iris-setosa25421Iris-setosa35421Iris-setosa45421Iris-setosa>>>sqldf.execute("SELECT species, variance(sepal_width) AS var FROM iris GROUP BY species;")speciesvar0Iris-setosa0.1422761Iris-versicolor0.0965002Iris-virginica0.101924

文档