ipython-sql introduces the
%%sql magics for iPython and Jupyter notebooks.
You can easily install ipython-sql in your Jupyter kernel with:
$ pipenv install ipython-sql
First, ipython-sql is activated in your notebook with
In : %load_ext sql
The SQLAlchemy URL is used to connect to the database:
In : %sql postgresql://
Then you can create a table, for example:
In : %%sql postgresql:// ....: CREATE TABLE accounts (login, name, email) ....: INSERT INTO accounts VALUES ('veit', 'Veit Schiele', email@example.com);
You can query the contents of the
In : result = %sql select * from accounts
Query results are loaded as a list, so very large amounts of data can occupy
memory. Usually there is no automatic limit, but with
Autolimit you can
limit the amount of results.
displaylimit only limits the amount of results displayed, but not the
amount of memory required.
%config SqlMagic you can display the current configuration:
In : %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 ...
autopandas is set to
displaylimit is not applied. In
this case, the
max_rows option of pandas can be used as described in the
If pandas is installed, the
DataFrame method can be used:
In : result = %sql SELECT * FROM accounts In : dataframe = result.DataFrame() In : %sql --persist dataframe In : %sql SELECT * FROM dataframe;
Argument with the name of a DataFrame object, creates a table name in the database from this.
Argument to add rows with this name to an existing table.
psql can also be used in ipython-sql:
lists all active connections
close named connection
specifies the creator function for a new connection
specifies section of
dsn_fileto be used in a connection
creates a table in the database from a named DataFrame
--persist, but the contents are appended to the table
specifies a dict of connection arguments to be passed to the SQL driver
executes SQL from the file under this path
Since ipython-sql processes
-- options such as
-persist, and at the
same time accepts
-- as a SQL comment, the parser has to make some
assumptions: for example,
--persist is great in the first line is
processed as an argument and not as a comment.