[Python][MySQL] Standalone Def to Run a Select Statement and Export to CSV / List of Dicts

Another standalone def (Python function) or two. Probably because my Python is not so good, I like bits of code I can just pick up and re-use elsewhere without anything being missing - i.e. no need to import this from this custom file, and that from that custom file (I don't mind importing stuff that is Python standard or easily obtainable via pip install.) This is partly for supportability and portability (one py file.)

Two standalone defs here:

  • mysqlQueryCsv: Run a MySQL select statement and get the output as a CSV
  • mysqlQueryList: Run a MySQL select statement and get the output as a list of dicts
Note: If you don't have it already you'll need to run:

python -m pip install mysql-connector-python

Usage

First specify your core variables:

host     = "MYSQL_HOST"
port     = "MYSQL_PORT"
username = "MYSQL_USER"
password = "MYSQL_PASS"
schema   = "SCHEMA"

Running mysqlQueryCsv:

sqlQuery = "select * from SCHEMA.TABLE"
csvName  = "TABLE.csv"
mysqlQueryCsv(host,port,username,password,schema,sqlQuery,csvName)

Running mysqlQueryList:
sqlQuery = "select * from SCHEMA.TABLE"
output = mysqlQueryList(host,port,username,password,schema,sqlQuery)

Definition: mysqlQueryCsv

def mysqlQueryCsv(*arg):
  # Usage:
  # mysqlQuery(host,port,username,password,schema,sqlQuery,csvName)
  def pwd(string):
    import datetime
    print(str(datetime.datetime.now()).split('.',1)[0] + ": " + string)
  # Check we have enough arguments:
  if len(arg) < 7:
    pwd("Syntax error: Not enough arguments!")
    return
  # Import required packages:
  import csv
  import mysql.connector
  # Connect to MySQL server:
  myDb = mysql.connector.connect(
    host = arg[0],
    user = arg[2],
    password = arg[3],
    port = arg[1],
    database = arg[4]
  )
  try: myDb
  except NameError:
    pwd("Unable to connect to {0}!".format(arg[0]))
    return
  pwd("Successfully connected to {0}!".format(arg[0]))
  # Run MySQL query:
  pwd("Running SQL query: {0}!".format(arg[5]))
  mycursor = myDb.cursor()
  mycursor.execute(arg[5])
  myresult = mycursor.fetchall()
  pwd("Completed running SQL query.")
  # Get the colums and export to CSV
  pwd("Exporting to CSV: {0}!".format(arg[6]))
  columns = [i[0] for i in mycursor.description]
  output = open(arg[6],mode='w',newline='', encoding="utf=8")
  writer = csv.writer(output, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
  writer.writerow(columns)
  for row in myresult: writer.writerow(list(row))
  output.close()
  pwd("Exporting complete!")


Definition: mysqlQueryList

def mysqlQueryList(*arg):
  # Usage:
  # mysqlQuery(host,port,username,password,schema,sqlQuery)
 def pwd(string):
    import datetime
    print(str(datetime.datetime.now()).split('.',1)[0] + ": " + string)
  # Check we have enough arguments:
  if len(arg) < 6:
    pwd("Syntax error: Not enough arguments!")
    return
  # Import required packages:
  import mysql.connector
  # Connect to MySQL server:
  myDb = mysql.connector.connect(
    host = arg[0],
    user = arg[2],
    password = arg[3],
    port = arg[1],
    database = arg[4]
  )
  try: myDb
  except NameError:
    pwd("Unable to connect to {0}!".format(arg[0]))
    return
  pwd("Successfully connected to {0}!".format(arg[0]))
  # Run MySQL query:
  pwd("Running SQL query: {0}!".format(arg[5]))
  mycursor = myDb.cursor()
  mycursor.execute(arg[5])
  myresult = mycursor.fetchall()
  pwd("Completed running SQL query.")
  columns = [i[0] for i in mycursor.description]
  # Return the output as a list of dicts
  pwd("Returning as list of dicts.")
  results = []
  for row in myresult:
    d = {}
    for label in columns:
      d[label] = row[columns.index(label)]
    results += [d]
  return results


Comments