{ "info": { "author": "Catherine Devlin, Fabio Buso", "author_email": "catherine.devlin@gmail.com, fabio@logicalclocks.com", "bugtrack_url": null, "classifiers": [ "Development Status :: 3 - Alpha", "Environment :: Console", "License :: OSI Approved :: MIT License", "Programming Language :: Python :: 2", "Programming Language :: Python :: 3", "Topic :: Database", "Topic :: Database :: Front-Ends" ], "description": "===========\nipython-sql\n===========\n\n:Author: Catherine Devlin, http://catherinedevlin.blogspot.com\n\nIntroduces a %sql (or %%sql) magic.\n\nConnect to a database, using SQLAlchemy connect strings, then issue SQL\ncommands within IPython or IPython Notebook.\n\n.. image:: https://raw.github.com/catherinedevlin/ipython-sql/master/examples/writers.png\n :width: 600px\n :alt: screenshot of ipython-sql in the Notebook\n\nExamples\n--------\n\n.. code-block:: python\n\n In [1]: %load_ext sql\n\n In [2]: %%sql postgresql://will:longliveliz@localhost/shakes\n ...: select * from character\n ...: where abbrev = 'ALICE'\n ...:\n Out[2]: [(u'Alice', u'Alice', u'ALICE', u'a lady attending on Princess Katherine', 22)]\n\n In [3]: result = _\n\n In [4]: print(result)\n charid charname abbrev description speechcount\n =================================================================================\n Alice Alice ALICE a lady attending on Princess Katherine 22\n\n In [4]: result.keys\n Out[5]: [u'charid', u'charname', u'abbrev', u'description', u'speechcount']\n\n In [6]: result[0][0]\n Out[6]: u'Alice'\n\n In [7]: result[0].description\n Out[7]: u'a lady attending on Princess Katherine'\n\nAfter the first connection, connect info can be omitted::\n\n In [8]: %sql select count(*) from work\n Out[8]: [(43L,)]\n\nConnections to multiple databases can be maintained. You can refer to\nan existing connection by username@database\n\n.. code-block:: python\n\n In [9]: %%sql will@shakes\n ...: select charname, speechcount from character\n ...: where speechcount = (select max(speechcount)\n ...: from character);\n ...:\n Out[9]: [(u'Poet', 733)]\n\n In [10]: print(_)\n charname speechcount\n ======================\n Poet 733\n\nIf no connect string is supplied, ``%sql`` will provide a list of existing connections;\nhowever, if no connections have yet been made and the environment variable ``DATABASE_URL``\nis available, that will be used.\n\nFor secure access, you may dynamically access your credentials (e.g. from your system environment or `getpass.getpass`) to avoid storing your password in the notebook itself. Use the `$` before any variable to access it in your `%sql` command.\n\n.. code-block:: python\n\n In [11]: user = os.getenv('SOME_USER')\n ....: password = os.getenv('SOME_PASSWORD')\n ....: connection_string = \"postgresql://{user}:{password}@localhost/some_database\".format(user=user, password=password)\n ....: %sql $connection_string\n Out[11]: u'Connected: some_user@some_database'\n\nYou may use multiple SQL statements inside a single cell, but you will\nonly see any query results from the last of them, so this really only\nmakes sense for statements with no output\n\n.. code-block:: python\n\n In [11]: %%sql sqlite://\n ....: CREATE TABLE writer (first_name, last_name, year_of_death);\n ....: INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);\n ....: INSERT INTO writer VALUES ('Bertold', 'Brecht', 1956);\n ....:\n Out[11]: []\n\n\nBind variables (bind parameters) can be used in the \"named\" (:x) style.\nThe variable names used should be defined in the local namespace\n\n.. code-block:: python\n\n In [12]: name = 'Countess'\n\n In [13]: %sql select description from character where charname = :name\n Out[13]: [(u'mother to Bertram',)]\n\nAs a convenience, dict-style access for result sets is supported, with the\nleftmost column serving as key, for unique values.\n\n.. code-block:: python\n\n In [14]: result = %sql select * from work\n 43 rows affected.\n\n In [15]: result['richard2']\n Out[15]: (u'richard2', u'Richard II', u'History of Richard II', 1595, u'h', None, u'Moby', 22411, 628)\n\nResults can also be retrieved as an iterator of dictionaries (``result.dicts()``)\nor a single dictionary with a tuple of scalar values per key (``result.dict()``)\n\nAssignment\n----------\n\nOrdinary IPython assignment works for single-line `%sql` queries:\n\n.. code-block:: python\n\n In [16]: works = %sql SELECT title, year FROM work\n 43 rows affected.\n\nThe `<<` operator captures query results in a local variable, and\ncan be used in multi-line ``%%sql``:\n\n.. code-block:: python\n\n In [17]: %%sql works << SELECT title, year\n ...: FROM work\n ...:\n 43 rows affected.\n Returning data to local variable works\n\nConnecting\n----------\n\nConnection strings are `SQLAlchemy`_ standard.\n\nSome example connection strings::\n\n mysql+pymysql://scott:tiger@localhost/foo\n oracle://scott:tiger@127.0.0.1:1521/sidname\n sqlite://\n sqlite:///foo.db\n mssql+pyodbc://username:password@host/database?driver=SQL+Server+Native+Client+11.0\n\n.. _SQLAlchemy: http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls\n\nNote that ``mysql`` and ``mysql+pymysql`` connections (and perhaps others)\ndon't read your client character set information from .my.cnf. You need\nto specify it in the connection string::\n\n mysql+pymysql://scott:tiger@localhost/foo?charset=utf8\n\nNote that ``impala`` connecion with `impyla`_ for HiveServer2 requires to disable autocommit::\n\n %config SqlMagic.autocommit=False\n %sql impala://hserverhost:port/default?kerberos_service_name=hive&auth_mechanism=GSSAPI\n\n.. _impyla: https://github.com/cloudera/impyla\n\nConfiguration\n-------------\n\nQuery results are loaded as lists, so very large result sets may use up\nyour system's memory and/or hang your browser. There is no autolimit\nby default. However, `autolimit` (if set) limits the size of the result\nset (usually with a `LIMIT` clause in the SQL). `displaylimit` is similar,\nbut the entire result set is still pulled into memory (for later analysis);\nonly the screen display is truncated.\n\n.. code-block:: python\n\n In [2]: %config SqlMagic\n SqlMagic options\n --------------\n SqlMagic.autocommit=\n Current: True\n Set autocommit mode\n SqlMagic.autolimit=\n Current: 0\n Automatically limit the size of the returned result sets\n SqlMagic.autopandas=\n Current: False\n Return Pandas DataFrames instead of regular result sets\n SqlMagic.displaylimit=\n Current: 0\n Automatically limit the number of rows displayed (full result set is still\n stored)\n SqlMagic.feedback=\n Current: True\n Print number of rows affected by DML\n SqlMagic.short_errors=\n Current: True\n Don't display the full traceback on SQL Programming Error\n SqlMagic.style=\n Current: 'DEFAULT'\n Set the table printing style to any of prettytable's defined styles\n (currently DEFAULT, MSWORD_FRIENDLY, PLAIN_COLUMNS, RANDOM)\n\n In[3]: %config SqlMagic.feedback = False\n\nPlease note: if you have autopandas set to true, the displaylimit option will not apply. You can set the pandas display limit by using the pandas ``max_rows`` option as described in the `pandas documentation `_.\n\nPandas\n------\n\nIf you have installed ``pandas``, you can use a result set's\n``.DataFrame()`` method\n\n.. code-block:: python\n\n In [3]: result = %sql SELECT * FROM character WHERE speechcount > 25\n\n In [4]: dataframe = result.DataFrame()\n\nThe bogus non-standard pseudo-SQL command ``PERSIST`` will create a table name\nin the database from the named DataFrame.\n\n.. code-block:: python\n\n In [5]: %sql PERSIST dataframe\n\n In [6]: %sql SELECT * FROM dataframe;\n\n.. _Pandas: http://pandas.pydata.org/\n\nGraphing\n--------\n\nIf you have installed ``matplotlib``, you can use a result set's\n``.plot()``, ``.pie()``, and ``.bar()`` methods for quick plotting\n\n.. code-block:: python\n\n In[5]: result = %sql SELECT title, totalwords FROM work WHERE genretype = 'c'\n\n In[6]: %matplotlib inline\n\n In[7]: result.pie()\n\n.. image:: https://raw.github.com/catherinedevlin/ipython-sql/master/examples/wordcount.png\n :alt: pie chart of word count of Shakespeare's comedies\n\nDumping\n-------\n\nResult sets come with a ``.csv(filename=None)`` method. This generates\ncomma-separated text either as a return value (if ``filename`` is not\nspecified) or in a file of the given name.\n\n.. code-block:: python\n\n In[8]: result = %sql SELECT title, totalwords FROM work WHERE genretype = 'c'\n\n In[9]: result.csv(filename='work.csv')\n\nPostgreSQL features\n-------------------\n\n``psql``-style \"backslash\" `meta-commands`_ commands (``\\d``, ``\\dt``, etc.)\nare provided by `PGSpecial`_. Example:\n\n.. code-block:: python\n\n In[9]: %sql \\d\n\n.. _PGSpecial: https://pypi.python.org/pypi/pgspecial\n\n.. _meta-commands: https://www.postgresql.org/docs/9.6/static/app-psql.html#APP-PSQL-META-COMMANDS\n\nInstalling\n----------\n\nInstall the lastest release with::\n\n pip install ipython-sql\n\nor download from https://github.com/catherinedevlin/ipython-sql and::\n\n cd ipython-sql\n sudo python setup.py install\n\nDevelopment\n-----------\n\nhttps://github.com/catherinedevlin/ipython-sql\n\nCredits\n-------\n\n- Matthias Bussonnier for help with configuration\n- Olivier Le Thanh Duong for ``%config`` fixes and improvements\n- Distribute_\n- Buildout_\n- modern-package-template_\n- Mike Wilson for bind variable code\n- Thomas Kluyver and Steve Holden for debugging help\n- Berton Earnshaw for DSN connection syntax\n- Andr\u00e9s Celis for SQL Server bugfix\n- Michael Erasmus for DataFrame truth bugfix\n- Noam Finkelstein for README clarification\n- Xiaochuan Yu for `<<` operator, syntax colorization\n- Amjith Ramanujam for PGSpecial and incorporating it here\n\n.. _Distribute: http://pypi.python.org/pypi/distribute\n.. _Buildout: http://www.buildout.org/\n.. _modern-package-template: http://pypi.python.org/pypi/modern-package-template\n\n\nNews\n====\n\n0.1\n---\n\n*Release date: 21-Mar-2013*\n\n* Initial release\n\n0.1.1\n-----\n\n*Release date: 29-Mar-2013*\n\n* Release to PyPI\n\n* Results returned as lists\n\n* print(_) to get table form in text console\n\n* set autolimit and text wrap in configuration\n\n\n0.1.2\n-----\n\n*Release date: 29-Mar-2013*\n\n* Python 3 compatibility\n\n* use prettyprint package\n\n* allow multiple SQL per cell\n\n0.2.0\n-----\n\n*Release date: 30-May-2013*\n\n* Accept bind variables (Thanks Mike Wilson!)\n\n0.2.1\n-----\n\n*Release date: 15-June-2013*\n\n* Recognize socket connection strings\n\n* Bugfix - issue 4 (remember existing connections by case)\n\n0.2.2\n-----\n\n*Release date: 30-July-2013*\n\nConverted from an IPython Plugin to an Extension for 1.0 compatibility\n\n0.2.2.1\n-------\n\n*Release date: 01-Aug-2013*\n\nDeleted Plugin import left behind in 0.2.2\n\n0.2.3\n-----\n\n*Release date: 20-Sep-2013*\n\n* Contributions from Olivier Le Thanh Duong:\n\n - SQL errors reported without internal IPython error stack\n\n - Proper handling of configuration\n\n* Added .DataFrame(), .pie(), .plot(), and .bar() methods to\n result sets\n\n0.3.0\n-----\n\n*Release date: 13-Oct-2013*\n\n* displaylimit config parameter\n\n* reports number of rows affected by each query\n\n* test suite working again\n\n* dict-style access for result sets by primary key\n\n0.3.1\n-----\n\n* Reporting of number of rows affected configurable with ``feedback``\n\n* Local variables usable as SQL bind variables\n\n0.3.2\n-----\n\n* ``.csv(filename=None)`` method added to result sets\n\n0.3.3\n-----\n\n* Python 3 compatibility restored\n* DSN access supported (thanks Berton Earnshaw)\n\n0.3.4\n-----\n\n* PERSIST pseudo-SQL command added\n\n0.3.5\n-----\n\n* Indentations visible in HTML cells\n* COMMIT each SQL statement immediately - prevent locks\n\n0.3.6\n-----\n\n* Fixed issue #30, commit failures for sqlite (thanks stonebig, jandot)\n\n0.3.7\n-----\n\n* New `column_local_vars` config option submitted by darikg\n* Avoid contaminating user namespace from locals (thanks alope107)\n\n0.3.7.1\n-------\n\n* Avoid \"connection busy\" error for SQL Server (thanks Andr\u00e9s Celis)\n\n0.3.8\n-----\n\n* Stop warnings for deprecated use of IPython 3 traitlets in IPython 4 (thanks graphaelli; also stonebig, aebrahim, mccahill)\n* README update for keeping connection info private, from eshilts\n\n0.3.9\n-----\n\n* Fix truth value of DataFrame error (thanks michael-erasmus)\n* `<<` operator (thanks xiaochuanyu)\n* added README example (thanks tanhuil)\n* bugfix in executing column_local_vars (thanks tebeka)\n* pgspecial installation optional (thanks jstoebel and arjoe)\n* conceal passwords in connection strings (thanks jstoebel)\n\n0.3.9\n-----\n\n* Restored Python 2 compatibility (thanks tokenmathguy)", "description_content_type": "", "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "https://github.com/logicalclocks/ipython-sql", "keywords": "database ipython postgresql mysql", "license": "MIT", "maintainer": "", "maintainer_email": "", "name": "hops-ipython-sql", "package_url": "https://pypi.org/project/hops-ipython-sql/", "platform": "", "project_url": "https://pypi.org/project/hops-ipython-sql/", "project_urls": { "Homepage": "https://github.com/logicalclocks/ipython-sql" }, "release_url": "https://pypi.org/project/hops-ipython-sql/0.3.9/", "requires_dist": null, "requires_python": "", "summary": "RDBMS access via IPython", "version": "0.3.9" }, "last_serial": 4879490, "releases": { "0.3.9": [ { "comment_text": "", "digests": { "md5": "5dbdd58aaa76abf40f3a09b362f9a020", "sha256": "71dfdce95f202e0432e5aaba7cdafca50ee301b419bfd7d701183660d962131c" }, "downloads": -1, "filename": "hops-ipython-sql-0.3.9.tar.gz", "has_sig": false, "md5_digest": "5dbdd58aaa76abf40f3a09b362f9a020", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 18190, "upload_time": "2019-02-28T14:18:04", "url": "https://files.pythonhosted.org/packages/e4/6e/aa0304fbaf32d2297bc416a0f72eb17a959b261e3bf396d8de956c9e861a/hops-ipython-sql-0.3.9.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "5dbdd58aaa76abf40f3a09b362f9a020", "sha256": "71dfdce95f202e0432e5aaba7cdafca50ee301b419bfd7d701183660d962131c" }, "downloads": -1, "filename": "hops-ipython-sql-0.3.9.tar.gz", "has_sig": false, "md5_digest": "5dbdd58aaa76abf40f3a09b362f9a020", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 18190, "upload_time": "2019-02-28T14:18:04", "url": "https://files.pythonhosted.org/packages/e4/6e/aa0304fbaf32d2297bc416a0f72eb17a959b261e3bf396d8de956c9e861a/hops-ipython-sql-0.3.9.tar.gz" } ] }