pysql-console

Command line emulator written in Python

---

Project maintained by jsarnowski96 Hosted on GitHub Pages — Theme by mattgraham

                         _____   __ __   __  _      ___ __  __  _   __   __  _   ___  
                        | _,\ `v' /' _/ /__\| | __ / _//__\|  \| |/' _/ /__\| | | __| 
                        | v_/`. .'`._`.| \/ | ||__| \_| \/ | | ' |`._`.| \/ | |_| _|  
                        |_|   !_! |___/ \_V_\___|  \__/\__/|_|\__||___/ \__/|___|___|
                                                                           v.0.3.0.2

PySql-Console (under development)

Command line emulator written in Python 3.x

Table of contents

Introduction
Current features
Planned features
File structure and dependant methods listing
Requirements
List of available commands
List of commands’ aliases
CLI’s interactive mode
To do
Known issues
Release notes

Introduction

PySql-Console is a simple program emulating the command line interface, designed to interact with MS SQL environment. Due to Python’s limitations related to internal command execution, some of the features introduced in this program are a sort of workarounds (for example user authentication system or multiple input() parameters treated as a separate strings kept in list() object). Thus the code might look a bit groggy and unsophisticated in some places, but during the development process I’m going to polish some things up.

PySql-Console allows user to interact with MS SQL database and its content. In future I’m going to implement other features like SQL-XML converter, text editor or even system-wide operations’ support.

Current features:

• create a connection with local or external MS SQL database
• display contents of the selected table (currently in raw, unformatted form)
• export selected table to .csv file
• import data from .csv file into the selected table
• list tables within the selected database, including different schemas
• execute a customized select query directly from the command line
• drop selected table
• list databases in the selected MS SQL Server instance
• implementation of user authentication mechanism based on Windows SQL Server Authentication
• executing commands directly or via aliases
  

Planned features:

• full CRUD integration
• read data from .csv file and display it on the screen
• importing data from .csv file into the selected database/table - done
• SQL-XML converter and vice-versa
• integration with other database engines, such as MariaDB, MySQL, PostgreSQL etc.
• system-wide commands
  

File structure and dependant methods listing:

• pysqlconsole.py - top layer py script handling user input and calling sub-routines from commands.py
  • drawInitBoard() - renders the program’s logo screen
    
  • UserAuthentication() - method responsible for performing user authentication. It keeps the main user identity connection opened during whole runtime
    
  • InputHandler() - method processesing user input received from MainActivity() and executing calls to commands.py stored methods
    
  • MainActivity() - method responsible for acquiring user input and calling InputHandler() method
    
  • Startup() - core pysql-console method invoking all dependant methods in a strict, predetermined order
    
• commands.py - contains implementations of all internal commands used by the program
  
  • Exit()
    
  • Connect()
    
  • Close()
    
  • Logout()
    
  • List()
    
  • Delete()
    
  • Drop()
    
  • Import()
    
  • Metrics()
    
  • Databases()
    
  • ConvertToXml()
    
  • Show()
    
  • Query()
    
  • Export()
    
  • Clear()
    
  • Status()
    
  • Switch()
    
  • Help()
    
• settings.py - global configuration file feeding requested data to both pysqlconsole.py and commands.py
  

Requirements:

• Python 3.x
• non standard-issue pyodbc and tabulate libraries
• configured account on local/remote MS SQL server with SQL Server and Windows Authentication mode enabled
• before you can use the program, you have to adjust server’s name/IP and default database in UserAuthentication() method to your personal needs. Although using master as a default database might be sufficient, I highly recommend testing some of these settings beforehand. If you wish to connect with a certain server instance or you are using a non-standard port, you can use Server=server_name\instance_name
  or Server=server_name,port_number, respectively. Use below template as a general reference point:

  dbConnection = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                              'Server=;'
                              'Database=;'
                              'uid='+username+';'
                              'pwd='+password+';'
                              'Trusted_Connection=no;')  
  

  
  

List of available commands:

add <table>: Add new record to the selected table
clear: This command clears the console window
close: Close active connection to the database
connect <server> <database>: Open new connection to the target database
da <file_name> <param>: Execute a Data Analysis over the given CSV file - optional parameter determines the exact method - if it is not provided, the default method describe() weill be executed.
databases: Display list of all databases within the selected MS SQL Server instance
delete <table> <rowId>: Remove the existing record from the selected table
drop <table>: Drop the selected table
edit <table> <rowId>: Modify the existing record in the selected table
exit - Exit the program
export <table>: Exports currently selected table to .csv file
help: Displays the list of available commands
import <destination_table> <file_name>: Import existing CSV file into the selected database
list: Display list of tables in the selected database
logout: Return to splash screen
metrics: Display CPU/Memory usage of MS SQL Server
query: Run a specific query in the database
show: List all rows in the selected table
status: Displays current session's data
switch <table>: If no new table name is provided, remove focus from the current table, otherwise switch to the another table
xml <table>: Export selected table into the pre-formatted XML file

List of commands’ aliases:

exp: export command alias
quit: exit command alias
del: delete command alias

CLI’s interactive mode:

Commands with implemented interactive mode allows user to provide the additional parameters “on the run” - in case of not providing any of the specified parameters or just a part of them. For example:

js $ export users
There is no connection established. Redirecting to connect action...

Server name: localhost
Database: test_db
Successfully connected to the localhost->test_db

js $ export users
users.csv export task finished successfully.

js $

Result:

To do:

• implementation of commands’ functionalities:
  • close
  • add - partially implemented
  • delete
  • edit - partially implemented
  • drop
  • status
  • list
  • query
  • read
  • help
  • xml
  • export
• migration of commands.py method calls coming from pysqlconsole.py to the nested dictionaries binded with command’s keys.
  
• all SQL-related commands binded with one specific command as optional parameters
  (for example sql connect, sql show, sql export, sql edit, etc.) - postponed
• fix bug in Exit() method
• fix bug in Export() method
• better format of table’s listed content
• implementation of basic MS SQL Server monitoring features
  

Known issues:

• problem with exiting the app due to credential variables not being removed from the memory. Ctrl+c forced exit required. <- problem applied exclusively to Spyder environment and IPython console.
• pyodbc library’s limitations prevents some of the tables from being processed (throws DataError exception) - most likely caused by datetime type fields. The problem lies mostly in Microsoft’s way of data types’ implementation, so unfortunately there is no easy fix for it.
  
• export does its job only partially since it replicates a single row N times instead of processing next rows. fixed
• problem with using aliases - fixed
  
• problem with multiline cells’ formatting which breaks whole table’s view
  
• MS SQL Server throws error 23000 upon inserting a foreign key ID into the selected table. Implementation of Add() method postponed until further notice
  

Release notes:

• 13/12/2019 changelog:
  - Initial commit + main script’s upload
  - Wrapping UserAuthentication() database connection sub-routine in try…except clause
  - ASCII Logo tweaks
  
• 14/12/2019 changelog:
  - Fixed bug in Exit() method - script is now finishing properly
  - Introduced new bug in close proximity to UserAuthentication() and InputLoop() methods - first execution of any command leads to login screen. After inserting credentials for the second time, all commands seem to work correctly. Currently under investigation - fixed
  - Code refactoring in MainActivity() - removed redundant nested execution loop and wrapped internal code in try…except clause
  - Better formatted table’s content after show command’s call. Still needs a bit of tinkering.
  - Fixed the UserAuthentication()/InputLoop() bug causing user to land on login page after first command’s execution.
  - Refactored UserAuthentication() method - switched pyodbc driver to ODBC Driver 17 for SQL Server which allows for more reliable server-side user validation. The same driver change applies to all other methods establishing active connection with the database
  - New settings.py global configuration file storing the globally-accessible DB connection data, user’s active credentials, etc.
  
• 15/12/2019 changelog:
  - New exception handlers for pyodbc.Error returning codes 28000 and 42S02 for incorrect table/database name.
  - New command status displaying content of global_config_array, including non-set key-value pairs.
  - Visual adjustments of status output
  - New command switch allows users to lift their focus off the currently selected table or move it to another table.
  - Export() method: fix of issue related with file validation sub-routine.
  
• 16/12/2019 changelog:
  - Changes in exception handling mechanism - now it is more accurate, specific and covers a wider range of errors.
  - Added new exception handle for error 08001 in case of connection failure to non-existing or not DNS-mapped SQL server.
  - Major changes in commands dictionary - migrated method calls from pysqlconsole.py directly into nested command’s dictionaries with exec key. Added new query command which allows for writing and executing a personalized SQL select statement. Command’s fallback is similar to export or show commands - in case of no active DB connection, user is redirected to Connect() action.
  
• 17/12/2019 changelog:
  - Greatly improved table’s output data format thanks to the application of tabulate library. Now tables’ output is much more readable and organized.
  - Change in specific commands’ fallback behaviour - upon filling the missing data in connect action, user is redirected back to the initial command while all previously obtained optional parameters were stored for later use and are fully accessible.
  - Added KeyboardInterrupt exception handle for Delete() method.
  - Fixed bug in Logout() method related to active_sql_session field not being set to None upon method’s callback.
  - New list command added.
  - Major upgrade of InputHandler() method’s logic by applying mutable *args parameter, allowing for handling multiple scenarios with optional arguments being passed into the function or not.
  - Added test_branch as a staging environment for testing unverified changes.
  
• 20/12/2019 changelog:
  - Added table_schema column in List() for better tables’ identification.
  
• 1/1/2020 changelog:
  - Adjusted commands’ behaviour on Ctrl+C event.
  - import command, allowing user to import data from CSV file directly into the selected table/database.
  - drop command, allowing user to drop selected table from the database.
  
• 6/1/2020 changelog:
  - xml command allowing user to export a selected table into pre-formatted XML file
  - added ConvertToXml() method handling the above operation
  - minor changes in exception handlers
  
• 7/1/2020 changelog:
  - adjustment of InputHandler() method for passing optional parameter to Query() method
  - added support of optional query parameter inside Query() method, allowing user to pass a query statement directly after command.
  
• 9/1/2020 changelog:
  - new test metrics command implemented, allowing for checking CPU and memory usage by MS SQL Server.
  - new test databases command implemented, which lists all databases within a specific instance of MS SQL Server.
  
• 22/1/2020 changelog:</br> - fixed problem with AW2012 tables not being processed correctly during XML export job.
  
• 8/2/2020 changelog:
  - major changes in code:
  - added new DataAnalysis() method and command da related to it, for analysing data from CSV files (WIP).
  - adjustment of user prompt - now instead of user $ it is displayed as user@servername $, as well as some display tweaks of monits.
  - display of current date and time upon successful login.
  - new field in settings.global_config_array “sourceCsvFile” added in order to store file path previously given by the user in da command
  
• 21/4/2020 changelog:
  - added new db_drivers dictionary in settings.py for future app’s development and enabling support for MySQL database engine
  - adjusted motd’s text and formatting