[Python][MySQL] MySQL Collector V2 (Supports Complex MySQL Queries via .sql file)

 A slight update to the python script I posted here:

[Python][MySQL] Collect Output of a Select Statement and Export to CSV

I wanted to add support for complex MySQL queries via a .SQL file (i.e. queries that might be over 100 lines long, with comments etcetera, that cannot be readily converted to a simple string.) And it turned out to be a quite simple upgrade.

And example of running it:

MySQLcollector.py MYSQLSERVER PORT USER PASSWORD SCHEMA -sqlFile "Complex MySQL Query.sql" -csvName "Special Report.csv"

The option to have a simple MySQL query is still there via -sqlQuery.

Ths Script

# MySQL Collector
# ===============
# A Python Tool to run an SQL Select Statement / SQL File against a MySQL Box and save to CSV:
# python MySQLcollector.py HOSTNAME PORT USERNAME PASSWORD SCHEMA -sqlQuery QUERY -sqlFile FILE -csvName CSVNAME
# Use "" to encapsulate QUERY.
# Note: You must have either a sqlQuery or sqlFile.

import argparse
import csv
import sys
import mysql.connector

''' PARAMETERS '''
parser = argparse.ArgumentParser(description="MySQLcollector.py", formatter_class=argparse.ArgumentDefaultsHelpFormatter)
parser.add_argument("host", help="Source MySQL Server")
parser.add_argument("port", help="Source MySQL Port")
parser.add_argument("username", help="Source MySQL User")
parser.add_argument("password", help="Source MySQL P/W")
parser.add_argument("schema", help="Default Schema/Database")
parser.add_argument("-sqlQuery", help="SELECT statement")
parser.add_argument("-sqlFile", help="SQL Query File (for complex SELECT statements)")
parser.add_argument("-csvName", default="output.csv", help="Filename for CSV")

args = parser.parse_args()
config   = vars(args)
host     = config['host']
port     = config['port']
username = config['username']
password = config['password']
schema   = config['schema']
sqlQuery = config['sqlQuery']
sqlFile  = config['sqlFile']
csvName  = config['csvName']

if sqlQuery is None and sqlFile is None:
  sys.exit("Need to specify either -sqlQuery or -sqlFile!")
if sqlQuery is non None and sqlFile is not None:
  sys.exit("Specify either -sqlQuery or -sqlFile but not both!")

sql = ""
if sqlFile is not None:
  with open(sqlFile) as f: lines = f.readlines()
  for l in lines: sql += l
if sqlQuery is not None:
  sql = sqlQuery

''' CONNECT TO MYSQL SERVER '''
myDstDb = mysql.connector.connect(
  host = host,
  user = username,
  password = password,
  port = port,
  database = schema
)

try: myDstDb
except NameError: sys.exit("Unable to connect to MySQL!")

''' RUN THE SQL QUERY '''
mycursor = myDstDb.cursor()
mycursor.execute(sql)
myresult = mycursor.fetchall()

''' GET THE COLUMNS AND OUTPUT RESULT TO CSV '''
columns = [i[0] for i in mycursor.description]
output = open(csvName,mode='w',newline='')
writer = csv.writer(output, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
writer.writerow(columns)
for row in myresult: writer.writerow(list(row))
output.close()