_____ __ __ __ _ ___ __ __ _ __ __ _ ___
| _,\ `v' /' _/ /__\| | __ / _//__\| \| |/' _/ /__\| | | __|
| v_/`. .'`._`.| \/ | ||__| \_| \/ | | ' |`._`.| \/ | |_| _|
|_| !_! |___/ \_V_\___| \__/\__/|_|\__||___/ \__/|___|___|
v.0.3.0.2
Command line emulator written in Python 3.x
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
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.
- 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
- 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
pysqlconsole.py
- top layer py script handling user input and calling sub-routines fromcommands.py
drawInitBoard()
- renders the program's logo screenUserAuthentication()
- method responsible for performing user authentication. It keeps the main user identity connection opened during whole runtimeInputHandler()
- method processesing user input received fromMainActivity()
and executing calls tocommands.py
stored methodsMainActivity()
- method responsible for acquiring user input and callingInputHandler()
methodStartup()
- corepysql-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 bothpysqlconsole.py
andcommands.py
- Python 3.x
- non standard-issue
pyodbc
andtabulate
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 usingmaster
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 useServer=server_name\instance_name
orServer=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;')
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
exp: export command alias
quit: exit command alias
del: delete command alias
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 $
- 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 frompysqlconsole.py
to the nested dictionaries binded with command's keys. - all SQL-related commands binded with one specific command as optional parameters
(for examplesql 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
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 (throwsDataError
exception) - most likely caused bydatetime
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.fixedproblem 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 ofAdd()
method postponed until further notice
- 13/12/2019 changelog:
- Initial commit + main script's upload
- WrappingUserAuthentication()
database connection sub-routine in try...except clause
- ASCII Logo tweaks - 14/12/2019 changelog:
- Fixed bug inExit()
method - script is now finishing properly
-Introduced new bug in close proximity to- fixedUserAuthentication()
andInputLoop()
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
- Code refactoring inMainActivity()
- removed redundant nested execution loop and wrapped internal code in try...except clause
- Better formatted table's content aftershow
command's call. Still needs a bit of tinkering.
- Fixed theUserAuthentication()
/InputLoop()
bug causing user to land on login page after first command's execution.
- RefactoredUserAuthentication()
method - switchedpyodbc
driver toODBC 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
- Newsettings.py
global configuration file storing the globally-accessible DB connection data, user's active credentials, etc. - 15/12/2019 changelog:
- New exception handlers forpyodbc.Error
returning codes28000
and42S02
for incorrect table/database name.
- New commandstatus
displaying content ofglobal_config_array
, including non-set key-value pairs.
- Visual adjustments ofstatus
output
- New commandswitch
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 error08001
in case of connection failure to non-existing or not DNS-mapped SQL server.
- Major changes incommands
dictionary - migrated method calls frompysqlconsole.py
directly into nested command's dictionaries withexec
key. Added newquery
command which allows for writing and executing a personalized SQL select statement. Command's fallback is similar toexport
orshow
commands - in case of no active DB connection, user is redirected toConnect()
action. - 17/12/2019 changelog:
- Greatly improved table's output data format thanks to the application oftabulate
library. Now tables' output is much more readable and organized.
- Change in specific commands' fallback behaviour - upon filling the missing data inconnect
action, user is redirected back to the initial command while all previously obtained optional parameters were stored for later use and are fully accessible.
- AddedKeyboardInterrupt
exception handle forDelete()
method.
- Fixed bug inLogout()
method related toactive_sql_session
field not being set toNone
upon method's callback.
- Newlist
command added.
- Major upgrade ofInputHandler()
method's logic by applying mutable*args
parameter, allowing for handling multiple scenarios with optional arguments being passed into the function or not.
- Addedtest_branch
as a staging environment for testing unverified changes. - 20/12/2019 changelog:
- Addedtable_schema
column inList()
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
- addedConvertToXml()
method handling the above operation
- minor changes in exception handlers - 7/1/2020 changelog:
- adjustment ofInputHandler()
method for passing optional parameter toQuery()
method
- added support of optionalquery
parameter insideQuery()
method, allowing user to pass a query statement directly after command. - 9/1/2020 changelog:
- new testmetrics
command implemented, allowing for checking CPU and memory usage by MS SQL Server.
- new testdatabases
command implemented, which lists all databases within a specific instance of MS SQL Server. - 22/1/2020 changelog:
- fixed problem with AW2012 tables not being processed correctly during XML export job. - 8/2/2020 changelog:
- major changes in code:
- added newDataAnalysis()
method and commandda
related to it, for analysing data from CSV files (WIP).
- adjustment of user prompt - now instead ofuser $
it is displayed asuser@servername $
, as well as some display tweaks of monits.
- display of current date and time upon successful login.
- new field insettings.global_config_array
"sourceCsvFile" added in order to store file path previously given by the user inda
command - 21/4/2020 changelog:
- added newdb_drivers
dictionary insettings.py
for future app's development and enabling support for MySQL database engine
- adjusted motd's text and formatting