The following script can be used to run a select statement against a MySQL server and export the output to a CSV file. Save as say MySQLcollector.py and run like:
python MySQLcollector.py -host HOSTNAME -username USERNAME -password PASSWORD -port PORT -schema SCHEMA -sqlQuery QUERY -csvName CSVNAME
Note: All parameters need to be specified.
The Script
# MySQL Collector
# ===============
# A Python Tool to run an SQL Select Statement against a MySQL Box and save to CSV:
# python MySQLcollector.py -host HOSTNAME -username USERNAME -password PASSWORD -port PORT -schema SCHEMA -sqlQuery QUERY -csvName CSVNAME
# Use "" to encapsulate QUERY.
import argparse
import csv
import sys
import mysql.connector
''' PARAMETERS '''
parser = argparse.ArgumentParser(description="Sync data from CI DWH to MySQL.", formatter_class=argparse.ArgumentDefaultsHelpFormatter)
parser.add_argument("-host", help="Source MySQL Server")
parser.add_argument("-username", help="Source MySQL User")
parser.add_argument("-password", help="Source MySQL P/W")
parser.add_argument("-port", help="Source MySQL Port")
parser.add_argument("-schema", help="Default Schema/Database")
parser.add_argument("-sqlQuery", help="SELECT statement")
parser.add_argument("-csvName", help="Filename for CSV")
args = parser.parse_args()
config = vars(args)
for c in config:
if not config[c]: sys.exit("Missing argument " + c + "!")
print("Basic argument checks completed!")
''' CONNECT TO MYSQL SERVER '''
myDstDb = mysql.connector.connect(
host = config['host'],
user = config['username'],
password = config['password'],
port = config['port'],
database = config['schema']
)
try: myDstDb
except NameError: sys.exit("Unable to connect to MySQL!")
''' RUN THE SQL QUERY '''
sql = config['sqlQuery']
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(config['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()
Comments
Post a Comment