tmthyjames / sqlcell

SQLCell is a magic function for the Jupyter Notebook that executes raw, parallel, parameterized SQL queries with the ability to accept Python values as parameters and assign output data to Python variables while concurrently running Python code. And *much* more.

SQLCell

Bringing together the power of SQL, Python, and Javascript. Run raw, multi-threaded SQL in an IPython Notebook while concurrently executing python cells, and a ton of other features.

*** New Version 2.0 ***

Find a tutorial here.

To Install:

pip install sqlcell

sqlcell's Issues

testing workflow

modularize all js strings

stop watch for timing queries

using this same technique

import sys
for i in range(1, 1100):
    sys.stdout.write('\r')
    sys.stdout.write("Progress: %s%% | Timer: %s | %-s" % (100*2.0, float(2)*i, '#'))
    sys.stdout.flush()

create table via UI

Enable editing rows inline, without any SQL

display the table, if pulled from one table show button to enable editing.

Allow user to define multiple connection strings

For example, in my config I have four connection string LOCAL, DEV, PROD AND QA.

User should have ability to easily add connection strings.

fix issue where pg_dump DB > file wasn't working

Produce graph(s) for EXPLAIN ANALYZE queries

Show dashboard/graphs for EXPLAIN ANALYZE queries.

Maybe use dc.js/crossfilter, or a similar basic javascript graphing library.

MySQL ANALYZE EXPLAIN graph (parse format to build d3)

Enable Vacuum Analyze

http://stackoverflow.com/questions/30206715/psycopg2-how-to-execute-vacuum-postgresql-query-in-python-script

Engine connection string persistence

After setting the engine, if engine argument is used on subsequent queries instead of using same connection, the current connection is closed and a new one is open which takes significant time.

SQLCell should do a check to see if the current connection string is the same as new one being passed. If so, use same connection, else open new one and persist that one until new engine passed.

Use threading to halt_query instead of $.get call to server

replace all print statements with JQuery.append so they'll track the correct cell

escape backslashes in notification

If \d <table> is executed, the body of the notification will read: d <table>

EXPLAIN button errors out, then --pg_dump doesn't register it

0. run public.table database
1. Press Explain Graph button
2. try to run public.table database

The SQLVARS.EXPLAIN_GRAPH doesn't get updated

general clean up

convert spaghetti into lasagna.

modularize display(Javascript(*args, **kwargs)) call

this is used a lot, so i need to modularize

testing in progress

bugs. seperate later

converting table to editabletableWidget only works occasionally, randomly

when it doesn't work, the tab-index attr doesn't get set.

Percent sign throwing "dict object does not support indexing error" error

SELECT * FROM table WHERE column LIKE '%''%' LIMIT 10

does not work. Searching for column that contains an apostrophe.

Add columns via the UI

Add ability to add columns to tables through the UI rather than raw SQL.

TESTING zube workflow

set_isolation_level method isn't language agnostic

get MySQL commands (metacommands in psql) working

Trying to return the output of --pg_dump as DATA using the button returns errors

retrieve create statement

add bootstrap-notify notifications for query alerts

add column via UI

option to Turn Notifications off

Accept psql meta-commands

Using subprocess, we can execute psql commands (with the -H flag to convert output to HTML tables) from the CLI via python and get the output using subprocess.check_ouput. Then we convert the HTML to a pandas dataframe with pd.read_html.

import subprocess

psql_command = False
if cell.startswith('\\'):
    psql_command = True
    pg = ['psql', 'phTeam-allpayer', '-c', cell.strip(), '-H']

    output = subprocess.check_output(pg)
    data = pd.read_html(output, header=0)[0]

Testing...

Pandas doesn't show leading whitespace which is important when running EXPLAIN ANALYZE

Tell pandas to show leading whitespace or, if that's not a viable option, do a find/replace and replace leading whitespace with some other character, like a dash.

check types before creating UPDATE statement

Check for types for quote injection into UPDATE statement.

(ProgrammingError) column "phteam" does not exist
LINE 1: ...E branding SET host_name = 'local' WHERE brand_name = PHTEAM
^
"UPDATE branding SET host_name = 'local' WHERE brand_name = PHTEAM" {}

PHTEAM here should be in quotes.

find equivalent of pg_dump for MySQL

create informative table from EXPLAIN (FORMAT JSON)

use this as reference.

add background color to "actual time" in Query Plan to indicate problem areas of query

Optimize for MySQL

postgres's application_name conn str param isn't language agnostic

Modify column info using \d syntax

Modify column info using \d by making HTML table editable.

in progress test

Use threading to execute queries on separate threads

make sample site to test SQLCell features

With > 1 SQLAlchemy named paras on same line, Python raises SyntaxError

Line 48

Tech Debt

pg_dump compatibility

test zenhub transitions

sqlcell workflow test

some psql commands, such as \COPY, aren't working.

MAKE_GLOBAL param does not overwrite existing global variable

EXPLAIN table for MySQL output fo EXPLAIN ANALYZE