Python and SQLs: How I Create Universal SQL CLI Client using PEP 249 standards

摘要

The SQL client is ubiquitous and impossible for developers not to have one in their toolbag. However potentially there is a unforeseen situation that the developers are forbid to use SQL client to access the cloud/on-premise SQL DB. This proposal primarily addresses the issue of such scenario and provides a solution based on the hacker's mindset. In this proposal, the author will show how he tackles this pain point in his workplace, by creating a universal SQL client CLI that allows him to connect to the SQL DB in his DB cluster, hence extends this project to other SQL DB using the API standardization in PEP 249. Apart from integrating all the SQL connectors, the author also implemented standardize hotkey for all the SQL DB, by studying the internal core of the SQL standards, and retrieving SQL metadata using pure SQL queries through the hotkeys.

說明

* what is my project about My project is how I design a universal SQL client CLI that allows me to do SQL operations via my universal client. It has few features which can be summarized using the inset below ``` Help: help/h -- list out the command db -- list out all the registered db table -- list out the table within the db quit/q -- quit exit/x -- same as quit column <TABLE_NAME> -- show the attribute of the table connect <DB_NAME> -- connect to the designated db switch <DB_NAME> -- switch from current db to designated db p -- show previous command e -- exeucte the previous command save <FILENAME.csv> -- save query result to CSV file Funky mode: | -- This is similar to unix/linux "more" or "less" command: e.g. select * from table_123; | This will display the first 10 entries from the select/table/column query To display remaining queries, press z To quit from Funky mode, press x ``` * Purpose/Philosophy of the design: The purpose of the design has no means to replace any kind of tools/libraries that are currently available. The purpose of this design is try to understand how a command prompt of SQL client works and mimic one by creating one. Hence in order for the author to connect to different DBs easily, he decided to build a SQL client prompt to utilize the dynamicity of Python module to create the universal connector, and the standardize of SQL library using PEP 249. *Use what you need* is the core philosophy of building this tool. This tool does not include every single DB connector library. Rather it will only download/use the library that you need. For e.g. if your DB is MySQL DB, then you will need to download MySQLdb and run MySQLdb only with the tool. * how is the design? This SQL client has a rather simple and lightweight command prompt. It allows the user to type the SQL command, together with the hotkeys that is written in the help above. The standardization of the hotkeys or shortcut will help the user to have better productivity since they do not need to memorize different commands for different SQL client such as {"MySQL": "show tables", "PostgreSQL": "\dt", "SQLite": ".table"}. * What is the future roadmap? * 1. ~~This SQL client is projected to have a web-based version which is similar to PHPMyAdmin.~~ 2. Generate a ER digram 3. Generate data visualization interface from the SQL query 4. Compile it to a *binary* so that it can be available to those who has no Python installed on their possesion. 5. Scale the interface so that anyone can build and embed any kind of command they want. * how my project can be beneficial to the community? This project will always remain in the domain of GNUGPLv3. It will serve as an alternative client for those who is potentially having limited resource/access to the current available SQL client. This SQL client will always work as long as Python is allowed to run in user's possesion, and this client does not require any propietary software to run it.

講者

Ing Wei Tang

Ing Wei is the chair for PyCon MY 2019, the co-chair of PyCon MY 2018, and vice president of MyPOP. He has spoken in various PyCons, particularly in PyCon APAC 2018, as well as involving in PyCon communities actively in Malaysia.

He uses python a lot in his daily work, especially coding the automation process and flow. During his past time, he likes to experience and perform hacking different things on operating system level.

Apart from programming language, he can also speak 5 different types of languages concurrently in one sentence. Please ask for demo if time permits.