Introduction to Qsqlite

• A command line tool to interactively manipulate sqlite or mysql databases for fast data processing, analysis, statistics, and graphical presentation;
  • General sqlite operations (just like the sqlite cmd tools); can be easily manipulated using sql statements.
  • Support sqlite and sqlite memory database (:memory:), support mysql database, copy whole mysql database to sqlite, copy tables between different sqlite databases.
  • Load csv or json data, or export select results to csv file.
  • Draw graphs with the data obtained by select statement, such as: scatter, line, histogram (distribution), violin graph (based on data distribution).
  • Provide a series of extended sqlite functions, supporting regular operations, text-based sum operations, Chinese ID recognition, and other extended functions.
• A script interpreter that can write script files to batch and automate a series of operations to achieve data processing, analysis, and report output;
  • Provides sql statement-based scripting capabilities, allowing you to automate batch operations and output the results to an html file.
  • Support extended loop/lend with nested support for loop statements, which is convenient for some operations that can't be handled by sql statements.
  • Support for formatting the output syntax of select results, to facilitate the generation of formatted reports.
  • The graphs are saved inline in the output html file, so that only a single html file can be sent to show all the graphs in their entirety.
• A Web Server and Job Server that can provide web or scheduled task processing backend services based on a database;
  • The web server can be scripted to enable data query and data insertion operations to facilitate the interaction of data statistics and analysis results in a browser-based.
  • Job server can be defined by scripts to achieve regular data cleaning, data aggregation, analysis, report generation, and output as local files or send emails to share the results.
• A python libray Can be imported using: from Qsqlite import Qexec , for use in python or jupyter/ipython ipynb notebooks using Qexec(cmds) calls, cmds can be a string with newlines,including a series of commands or a command.
• Summary: With sqlite's powerful sql syntax and high performance, Qsqlite hopes to enable you to efficiently use the power of sqlite and sql syntax to quickly organize, analyze, aggregation, and show result; and collaborate with Excel by exporting/importing csv files when needed to achieve greater efficiency.

Quickly start

1. install
   • pip3 install Qsqlite , and then open cmd/Term enter: Qsqlite
   • Copy Qsqlite.py to local directory, and then open cmd/Term python Qsqlite.py (please using python3)
2. Now you can enter sql cmd or ? get help or copy below code to try it.

   # open memory database 
   open :memory:
   # create demo table
   create table demo(ID text, Name text, Age integer)
   # using loop/lend insert data to table
   loop [ [i, i**2] for i in range(1,100) ]
   insert into demo values ('_^1_', 'User_^1_', _^2_)
   lend
   # select data
   info
   select count(*) from demo 
   select * from demo limit 10 >[- ID: _@1_, Name: _@2_, Age: _@3_]
   # draw data 
   draw l select age from demo; draw ll select id,age from demo
   

3. Try running the demo script file in the demo directory, using the python3 Qsqlite.py script filename to run.
   • qloop.txt loop/lend function demo
   • qdraw.txt html output and drawing demo (requires two csv files in the demo directory)
   • qweb.txt Web server demo
   • qjob.txt Job task server demo

Add iPython Notebook demo.

You can using iPython Notebook on iPad with JUNO or Carnets.

check it on GitHub

Nice Job.

About the iPython Notebook Demo being supported on the iPad via Carnets: Can you re-confirm? It would be great.

I am asking, because as far as I remember, Carnets supports pure Python libraries/packages only, when installed by the user during runtime, while the sqlite3 is a CPython library?

Thank you for your reply!

Maybe, I mixed up something, anyway I used this to check the packages installed in my Carnets Plus from the Apple AppStore:

%pip list

Did not find the sqlite3 package, though. As far as I know, cannot install sqlite3 as user, because it is not pure Python.

In the source code of Qsqlite.py in line 89:

import sqlite3

Maybe sqlite3 is pre-installed in Juno or the other alternatives you listed?

Cheers!

sqlite3 is native python lib.

1. %pip install qsqlite
2. try demo notebook.

I already try it on Carnets. It’s ok.

You are right: while the Sqlite3 package is not shown with ‘%pip list’, it is part of the CPython integration of Carnets already

… and the ‘ import Sqlite3’ in the Python code works right away

import Sqlite3’ in the Python code works right away