ipython-sql 0.5.0

示例

In [1]: %load_ext sql

In [2]: %%sql postgresql://will:longliveliz@localhost/shakes
   ...: select * from character
   ...: where abbrev = 'ALICE'
   ...:
Out[2]: [(u'Alice', u'Alice', u'ALICE', u'a lady attending on Princess Katherine', 22)]

In [3]: result = _

In [4]: print(result)
charid   charname   abbrev                description                 speechcount
=================================================================================
Alice    Alice      ALICE    a lady attending on Princess Katherine   22

In [4]: result.keys
Out[5]: [u'charid', u'charname', u'abbrev', u'description', u'speechcount']

In [6]: result[0][0]
Out[6]: u'Alice'

In [7]: result[0].description
Out[7]: u'a lady attending on Princess Katherine'

第一次连接后，可以省略连接信息

In [8]: %sql select count(*) from work
Out[8]: [(43L,)]

可以维护多个数据库的连接。你可以通过username@database引用现有的连接

In [9]: %%sql will@shakes
   ...: select charname, speechcount from character
   ...: where  speechcount = (select max(speechcount)
   ...:                       from character);
   ...:
Out[9]: [(u'Poet', 733)]

In [10]: print(_)
charname   speechcount
======================
Poet       733

如果没有提供连接字符串，%sql 将提供一个现有连接的列表；但是，如果没有建立任何连接，并且环境变量DATABASE_URL可用，则将使用该变量。

为了安全访问，你可以动态地访问你的凭证（例如从系统环境或getpass.getpass），以避免在笔记本本身中存储密码。在%sql命令中使用$来访问任何变量。

In [11]: user = os.getenv('SOME_USER')
   ....: password = os.getenv('SOME_PASSWORD')
   ....: connection_string = "postgresql://{user}:{password}@localhost/some_database".format(user=user, password=password)
   ....: %sql $connection_string
Out[11]: u'Connected: some_user@some_database'

你可以在单个单元中执行多个SQL语句，但你只会看到最后一个语句的查询结果，所以这实际上只适用于没有输出的语句

In [11]: %%sql sqlite://
   ....: CREATE TABLE writer (first_name, last_name, year_of_death);
   ....: INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);
   ....: INSERT INTO writer VALUES ('Bertold', 'Brecht', 1956);
   ....:
Out[11]: []

作为便利，支持以字典样式访问结果集，其中最左边的列作为键，用于唯一值。

In [12]: result = %sql select * from work
43 rows affected.

In [13]: result['richard2']
Out[14]: (u'richard2', u'Richard II', u'History of Richard II', 1595, u'h', None, u'Moby', 22411, 628)

结果也可以作为字典迭代器（result.dicts()）或带有每个键的标量值元组的单个字典（result.dict()）检索。

变量替换

可以使用“命名”样式（:x）绑定变量（绑定参数）。所使用的变量名称应在本地命名空间中定义。

In [15]: name = 'Countess'

In [16]: %sql select description from character where charname = :name
Out[16]: [(u'mother to Bertram',)]

或者，可以使用$variable_name或{variable_name}在形成并传递给SQL引擎之前将本地命名空间中的变量注入SQL语句。（不支持同时使用$和{}，如${variable_name}。）

In [17]: %sql select description from character where charname = ‘{name}’ Out[17]: [(u’mother to Bertram’,)]

绑定变量会传递到SQL引擎，并且只能用于替换传递给SQL的字符串。$和{}在传递给SQL之前会被替换，并可用于动态形成SQL语句。

赋值

普通的IPython赋值对单行%sql查询有效。

In [18]: works = %sql SELECT title, year FROM work
43 rows affected.

<<操作符将查询结果捕获到局部变量中，并可用于多行%%sql。

In [19]: %%sql works << SELECT title, year
    ...: FROM work
    ...:
43 rows affected.
Returning data to local variable works

连接

连接字符串是SQLAlchemy URL标准。

一些示例连接字符串

mysql+pymysql://scott:tiger@localhost/foo
oracle://scott:tiger@127.0.0.1:1521/sidname
sqlite://
sqlite:///foo.db
mssql+pyodbc://username:password@host/database?driver=SQL+Server+Native+Client+11.0

请注意，mysql和mysql+pymysql连接（以及可能的其他连接）不会从.my.cnf读取客户端字符集信息。您需要在连接字符串中指定它。

mysql+pymysql://scott:tiger@localhost/foo?charset=utf8

请注意，使用impyla的impala连接需要禁用自动提交。

%config SqlMagic.autocommit=False
%sql impala://hserverhost:port/default?kerberos_service_name=hive&auth_mechanism=GSSAPI

SQLAlchemy未列入白名单的连接参数可以作为JSON字符串的标志提供，与连接字符串一起使用（-a|–connection_arguments）。请参阅SQLAlchemy Args。

%sql –connection_arguments {“timeout”:10,”mode”:”ro”} sqlite:// SELECT * FROM work;

%sql -a ‘{“timeout”:10, “mode”:”ro”}’ sqlite:// SELECT * from work;

配置

查询结果作为列表加载，因此非常大的结果集可能会耗尽系统内存和/或使浏览器挂起。默认情况下没有自动限制。然而，如果设置了autolimit，则限制结果集的大小（通常在SQL中使用LIMIT子句）。displaylimit类似，但整个结果集仍然被拉入内存（用于后续分析）；只有屏幕显示被截断。

In [2]: %config SqlMagic
SqlMagic options
--------------
SqlMagic.autocommit=<Bool>
    Current: True
    Set autocommit mode
SqlMagic.autolimit=<Int>
    Current: 0
    Automatically limit the size of the returned result sets
SqlMagic.autopandas=<Bool>
    Current: False
    Return Pandas DataFrames instead of regular result sets
SqlMagic.column_local_vars=<Bool>
    Current: False
    Return data into local variables from column names
SqlMagic.displaycon=<Bool>
    Current: False
    Show connection string after execute
SqlMagic.displaylimit=<Int>
    Current: None
    Automatically limit the number of rows displayed (full result set is still
    stored)
SqlMagic.dsn_filename=<Unicode>
    Current: 'odbc.ini'
    Path to DSN file. When the first argument is of the form [section], a
    sqlalchemy connection string is formed from the matching section in the DSN
    file.
SqlMagic.feedback=<Bool>
    Current: False
    Print number of rows affected by DML
SqlMagic.short_errors=<Bool>
    Current: True
    Don't display the full traceback on SQL Programming Error
SqlMagic.style=<Unicode>
    Current: 'DEFAULT'
    Set the table printing style to any of prettytable's defined styles
    (currently DEFAULT, MSWORD_FRIENDLY, PLAIN_COLUMNS, RANDOM)

In[3]: %config SqlMagic.feedback = False

请注意：如果您将autopandas设置为true，则displaylimit选项将不适用。您可以通过使用pandas max_rows选项来设置pandas显示限制，如pandas文档中所述。

Pandas

如果您已安装pandas，则可以使用结果集的.DataFrame()方法。

In [3]: result = %sql SELECT * FROM character WHERE speechcount > 25

In [4]: dataframe = result.DataFrame()

--persist参数，带有内存中DataFrame对象的名称，将在数据库中创建一个表名。或者使用--append通过该名称向现有表添加行。

In [5]: %sql --persist dataframe

In [6]: %sql SELECT * FROM dataframe;

绘图

如果您已安装 matplotlib，您可以使用结果集的 .plot()、.pie() 和 .bar() 方法进行快速绘图

In[5]: result = %sql SELECT title, totalwords FROM work WHERE genretype = 'c'

In[6]: %matplotlib inline

In[7]: result.pie()

转储

结果集包含一个 .csv(filename=None) 方法。这会生成逗号分隔的文本，要么作为返回值（如果未指定 filename），要么作为给定名称的文件。

In[8]: result = %sql SELECT title, totalwords FROM work WHERE genretype = 'c'

In[9]: result.csv(filename='work.csv')

PostgreSQL 特性

psql-风格的“反斜杠” 元命令（\d、\dt 等）由 PGSpecial 提供。示例

In[9]: %sql \d

选项

-l / --connections

列出所有活动连接

-x / --close <session-name>

关闭命名连接

-c / --creator <creator-function>

指定新连接的创建函数

-s / --section <section-name>

用于生成连接字符串的 dsn_file 的部分

-p / --persist

从命名 DataFrame 创建数据库中的表名

--append

类似于 --persist，但如果表已存在，则附加到表中

-a / --connection_arguments <"{connection arguments}">

指定要传递给 SQL 驱动程序的连接参数字典

-f / --file <path>

从此路径运行 SQL 文件

注意

注释

因为 ipyton-sql 接受如 --persist 这样的用短横线分隔的选项，但短横线也是表示 SQL 注释的语法，解析器需要做出一些假设。

• 如果您尝试传递一个不受支持的参数，例如 --lutefisk，它将被解释为 SQL 注释，而不会抛出不受支持的参数异常。

• 如果 SQL 语句以看起来像是接受的参数之一的第一行注释开始 - 例如 %sql --persist is great! - 它将被解析为参数，而不是注释。将注释移到第二行或之后可以避免这种情况。

安装

使用以下命令安装最新版本

pip install ipython-sql

或从 https://github.com/catherinedevlin/ipython-sql 下载并

cd ipython-sql
sudo python setup.py install

开发

https://github.com/catherinedevlin/ipython-sql

致谢

• Matthias Bussonnier 帮助配置

• Olivier Le Thanh Duong 对 %config 修复和改进

• 分发

• Buildout

• modern-package-template

• Mike Wilson 为绑定变量代码

• Thomas Kluyver 和 Steve Holden 为调试帮助

• Berton Earnshaw 为 DSN 连接语法

• Bruno Harbulot 为 DSN 示例

• Andrés Celis 为 SQL Server 错误修复

• Michael Erasmus 为 DataFrame 真实性错误修复

• Noam Finkelstein 为 README 清晰度

• Xiaochuan Yu 为 << 运算符、语法着色

• Amjith Ramanujam 为 PGSpecial 以及将其集成在这里

• Alexander Maznev 为更好的参数解析、接受指定创建者的连接

• Jonathan Larkin 为可配置的 displaycon

• Jared Moore 为 connection-arguments 支持

• 吉尔伯特·布拉特为 --append

• 卢卡斯·泽尔为变量替换的多行错误修复，<<

• vvk800为--file

• 延斯·阿尔布雷希特为MySQL DatabaseError错误修复

• meihkv为连接关闭错误修复

• Abhinav C为SQLAlchemy 2.0兼容性

新闻