sqlexec

Execute SQL queries and generate csv exports in all SQL dialects from command line.

Why to use this tool

One of the most common tasks i have at work is to run an SQL query vs some databases and email the results or troubleshoot an issue based on them, using GUI tools like dbeaver does the job but needs a lot of human interaction (cannot schedule SQL jobs in this tools), plus that you cannot do those queries in parallel

How it works

This is a very simple python script that uses the SQLAlchemy library to connect to the database and pandas to execute the query and convert it in csv format, it has a number of parameters that makes the tool very flexible.

Examples

Print results on console sqlexec.py -d <database_config_file> -q <query_file>

-d: a text file with the database connection options
-q: the query to be executed

running this will produce output on the console with the output of the executed query

dbserver\database_name\executed_query.sql\4.91Sec\11-Feb-2022T14:19:42
----------------------------------------------------------------------

              col1          col2            col3    col4
0          2234267  11.15.186.11         2234267  229362
1          2234662    11.0.98.11         2234662  400830

Export results in csv file sqlexec.py -d <database_config_file> -q <query_file> -e

-d: a text file with the database connection options
-q: the query to be executed
-e: instructs the tool export data to a csv file, the filename will be autogenerated by the database server name the query and timestamp
-n: if you dont want an autogenerated filename you can define your own using the -n parameter
-t: will not print query results on screen
-c: will zip the csv export

Export to influxdb sqlexec.py -d <database_config_file> -q <query_file> -i -f <influxdb_config_file>

-f: a text file with influxdb configuration options

Database configuration file example

engine:mysqldb            <-- or any other sqlalchemy database engine
db_user:username
db_pass:password
db_name:dabatase_name
db_host:database_server
db_port:database_port
ssh_host:gwserver1
ssh_port:22
ssh_key: ~/.ssh/id_rsa
ssh_user:username
ssh_pass:
use_tunnel:True || False

if use_tunnel is false then ssh parameters are optional

Influxdb configuration file example

host:influxdb_host
port:influxdb_port
measurement:measurement_name
database:database_name
ts: <-- optional, if used will be the name of the column you want to use as timestamp, must be in influxdb format
drop: <-- optional, columns to drop.
tags:column1,column2 <-- columns used as tags
token: 
org: