Quick Sqlite cmd/script tools for Pythonista

[wolf71]

• 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 on Pythonista

• on pythonista , using stash, and then: pip3 install qsqlite , and then you can using it on stash by enter: qsqlite
• or just download Qsqlite.py to your pythonista file directory, and then run it.
• Demo on : https://github.com/wolf71/Qsqlite

[wolf71]

Add iPython Notebook demo.

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

check it on GitHub

[wolf71]

For Bioinformatics Support

• loadcsv also support tsv format file (\t split type), just using:
  • loadcsv test.tsv tb1 1
• loadcsv also support bioinformatics .maf/.vcf/.sam/.gtf/.gff/.gpd file, just using:
  • loadcsv test.maf tb1 or loadcsv test.vcf tb1
• Load GenBank Format Features Data.
  • using: loadgb gbfile tb01
  • load bioinformatics GenBank file (etc: .gb/.gbff/.gpff) features info to table.

Crawl website data, parse and write to database

• using: loop loadweb url=https://xxx.xxx.com/xxx re=(\d+), base on python regular module, Qsqlite loop nesting function, can achieve complex web data crawling and content extraction, and then write to the database.

All new function please check the Readme file

Qsqlite is one py file only, you can just copy the Qsqlite.py file on Github to Pythonista, and then run it on python3 mode.