API Ideas

The session

A session is the main object you are working with to connect with the database. It is not global as it should be possible to query multiple databases in a single script.

session = noteql.Session('schema_name')  # using env_var for the db uri
session = noteql.Session('schema_name', db_uri= 'postgresql://sqlachamydatabaseurlstring')
session = noteql.Session('schema_name', search_path= ['other', 'schemas'])
session = noteql.Session('schema_name', sql_file_location='/sql')

session.make_magic() # Allow %(%)sql magics to use same connection parameters as this session.

Basic session usage would be to run a query on the the data.

session.run_sql('select * from table', params=[]) # resulting table will display in notbook
results = session.get_results('select * from table') # results contain JSON style structured data of all data
 
for row in session.async_results('select * from table'):
    do_something_with(row) # get each row in result one at a time

Create intermediate view of the data. This is done with simple select statements.

session.create_view('view_name', sql='select * from table', params=[]) # resulting view created
session.create_table('table_name', sql='select * from table') # resulting table created
session.create_table('table_name', file='mysqlfile.sql', params=[]) # get sql file form sql directory specified
session.drop_view('view_name') # resulting view created

session.add_table_indexes('table_name', [id, (id, name')]) # list of indexes to be created a tuple is a joint index.
session.add_unique_indexes('table_name', [id, (id, name')]) # list of indexes to be created.

Load local or remote data

session.load_json('example.json', path_to_list='people', table_name='import', field_name='person')
session.load_xml('example.xml', base_tag='people', table_name='import', field_name='person')
session.load_csv('example.csv', base_tag='people', table_name='import', field_name='person')
session.load_dataframe(my_pandas_dataframe, table_name='import;)