Convert a SQL Dump to Excel

The attached script can be used to migrate data out of a sqldump and into csv and xls for further analysis or ETL’ing. The name of each table is used to create a separate csv file (e.g. table1.csv) and an xls representation of each table is put into a workbook in a spreadsheet.

The script requires Python 3.7. So if you’re on a Mac you might need to install that. Here, we use Homebrew to do so:

brew install python3

Then you’d need to install the specific mods:

python3 -m pip install pandas

python3 -m pip install openpyxl

The command is then run as follows:

python3 sqlcsvxlsexport.py <sql dump file> <target directory>

The script is accessible here:

Python Script To Move A SQL Database To .csv Files

You have a database, such as a mysql dump of a Jamf Pro server, or a sql dump of a WordPress site. You want to bring it into another tool or clean the data using a csv as an intermediary. Or you’re using an Amazon Glue job to ETL the data. The following script will take that sql dump and convert it into a bunch of csv files.

To use the script, simply run it with $1 as the path to the sql file and $2 as the path to the export directory, as follows:

python sqlcsvexport.py /sql_file_path /target_dir

To download the script:

mysql command switches

Mysql command options: –auto-rehash Enable or disable automatic rehashing –auto-vertical-output Automatically display output of commands vertically rather than horizontally –batch Do not write to the history file –binary I use this when I have large results timing out due to blobs being in there –bind-address Specify a network interface when connecting to the server –character-sets-dir Indicate a custom directory where character sets are installed –column-names Output column names when running queries –column-type-info Show metadata –comments Enable or disable commenting when sending statements to the server –compress Compresses data sent to/from client and server –connect_timeout Seconds of inactivity before a timeout –database The database to connect to –debug Write debugging log; supported only if MySQL was built with debugging support –debug-check Print debugging information when program exits –debug-info Print debugging information, memory, and CPU statistics when program exits –default-auth Authentication plugin to use –default-character-set Specify default character set –defaults-extra-file Read named option file in addition to usual option files –defaults-file Read only named option file –defaults-group-suffix Option group suffix value –delimiter Set the statement delimiter –enable-cleartext-plugin Enable cleartext authentication plugin –execute Execute the statement and quit –force Continue even if an SQL error occurs –help Display help message and exit –histignore Patterns specifying which statements to ignore for logging –host Connect to MySQL server on given host –html Produce HTML output –ignore-spaces Ignore spaces after function names –init-command SQL statement to execute after connecting –line-numbers Write line numbers for errors –local-infile Enable or disable for LOCAL capability for LOAD DATA INFILE –login-path Read login path options from .mylogin.cnf –max_allowed_packet Maximum packet length to send to or receive from server –max_join_size The automatic limit for rows in a join when using –safe-updates –named-commands Enable named mysql commands –net_buffer_length Buffer size for TCP/IP and socket communication –no-auto-rehash Disable automatic rehashing –no-beep Do not beep when errors occur –no-defaults Read no option files –one-database Ignore statements except those for the default database named on the command line –pager Use the given command for paging query output –password Password to use when connecting to server –pipe On Windows, connect to server using named pipe –plugin-dir Directory where plugins are installed –port TCP/IP port number to use for connection –print-defaults Print default options –prompt Set the prompt to the specified format –protocol Connection protocol to use –quick Do not cache each query result –raw Write column values without escape conversion –reconnect If the connection to the server is lost, automatically try to reconnect –safe-updates Only process UPDATE and DELETE statements when they have values –secure-auth Do not send passwords to server in old (pre-4.1) format 5.7.5 –select_limit The automatic limit for SELECT statements when using –safe-updates –server-public-key-path Path name to file containing RSA public key –shared-memory-base-name The name of shared memory to use for shared-memory connections –show-warnings Show warnings after each statement if there are any –sigint-ignore Ignore SIGINT signals (typically the result of typing Control+C) –silent Run silently (necessary when daemonizing) –skip-auto-rehash Disable automatic rehashing –skip-column-names Do not write column names in results –skip-line-numbers Skip line numbers for errors –skip-named-commands Disable named mysql commands –skip-pager Disable paging –skip-reconnect Disable reconnecting –socket For connections to localhost, the Unix socket file to use –ssl Enable secure connection –ssl-ca Path of file that contains list of trusted SSL CAs –ssl-capath Path of directory that contains trusted SSL CA certificates in PEM format –ssl-cert Path of file that contains X509 certificate in PEM format –ssl-cipher List of permitted ciphers to use for connection encryption –ssl-crl Path of file that contains certificate revocation lists –ssl-crlpath Path of directory that contains certificate revocation list files –ssl-key Path of file that contains X509 key in PEM format –ssl-mode Security state of connection to server 5.7.11 –ssl-verify-server-cert Verify server certificate Common Name value against host name used when connecting to server –syslog Log interactive statements to syslog 5.7.1 –table Display output in tabular format –tee Append a copy of output to named file –tls-version TLS versions Protocols permitted for secure connections –unbuffered Constantly flush buffers –user The user name to use when connecting to server (SQL users are not the same as users on an OS) –verbose As with most other commands, output verbosely –version Show the MySQL version number –vertical Show one line per column in query outputs –wait Retry when a connection fails –xml Output data into an XML format

Use the Slow Query Log in MySQL

Slow queries can cause poor database performance. MySQL has a slow query log, which lets you log queries that take too long (with too long a user-configurable setting). This allows you to quickly find craptastic queries in your logs. To enable the slow query log, use SET GLOBAL and then set the slow_query_log option to ON: SET GLOBAL slow_query_log = 'ON'; By default, the slow query log identifies any query that takes 10 seconds or longer. To change long_query_time using SET GLOBAL. Here, we’ll set it to 7 seconds: SET GLOBAL long_query_time = 7; You can also set the log file to something other than the default location of /var/lib/mysql/hostname-slow.log. Again, use SET GLOBAL and then provide a path for slow_query_log_file: SET GLOBAL slow_query_log_file = '/var/logs/slowquery.log'; To disable the slow query log: SET GLOBAL slow_query_log = 'OFF';

Use SQL Views to Grant Access and Constrain Output

You can grant access to certain columns to view in SQL without providing access to specific users to see the whole database. This is pretty useful when delegating reporting to users, without giving them access to all of the data in your database. For example, a user might be able to see a column with an address, but not a column with a credit card number, increasing database security while allowing you to delegate certain tasks when appropriate. In this article, we’ll use the same “Customers” table from our first articles, signupdate: ID Site Contact Address City Zip Country SignupDate 1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01 2 Apple Tim Cook spaceship Cupertino 95014 US 2015-12-05 3 Microsoft Satya Nadella campus Redmond 98053 US 2014-11-01 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 2010-03-10 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 2016-01-01 Next, we’ll create a view called SignupDate that only has customers that signed up on January 1st of 2005. This view returns the data set of contacts and signup dates: CREATE VIEW signupdate AS SELECT * FROM Customers WHERE OrderDate='2005-01-01'; The syntax is similar to a SELECT, but with CREATE VIEW followed by the name of the view and then AS followed by the SELECT statement. The view is a virtual table containing the output of the query rather than data. Once created, use use the signupdate view in a query: SELECT * FROM signupdate; This SQL statement returns the following results: 1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01 You can also use the view to contain a query with just the columns you want, according to how you structure your query, thus granting access to specific columns, without granting access to all of the columns in a table.

Index Those SQL Tables

If you have growing sets of data, one of the best ways to speed up database performance is to make sure each column in larger tables is indexed. You can easily index a column following this syntax (using the name of your table in the place of tablename and the name of your column in the place of columnname): ALTER TABLE tablename ADD INDEX (columnname); So if you have a table called Customers and the following columns (as in the case of my Customers database from the previous exercises): ID Site Contact Address City Zip Country You would index them all as follows: ALTER TABLE Customers ADD INDEX (ID); ALTER TABLE Customers ADD INDEX (Site); ALTER TABLE Customers ADD INDEX (Contact); ALTER TABLE Customers ADD INDEX (Address); ALTER TABLE Customers ADD INDEX (City); ALTER TABLE Customers ADD INDEX (Zip); ALTER TABLE Customers ADD INDEX (Country);

Manage SQL Schemas with Flyway

Flyway is a tool that allows you to perform version-controlled management of SQL schemas. You can download flyway at http://flywaydb.org. It doesn’t require a build, so once downloaded, drop it where you want it to live and you can just summon the binary in scripts. Once installed, the following is the basic structure of commands for Flyway: flyway [options] command By default, the configuration will be read from conf/flyway.conf. Options passed from the command-line override the configuration. Commands ======== migrate : Migrates the database clean : Drops all objects in the configured schemas info : Prints the information about applied, current and pending migrations validate : Validates the applied migrations against the ones on the classpath baseline : Baselines an existing database at the baselineVersion repair : Repairs the metadata table Options (Format: -key=value) ======= driver : Fully qualified classname of the jdbc driver url : Jdbc url to use to connect to the database user : User to use to connect to the database password : Password to use to connect to the database schemas : Comma-separated list of the schemas managed by Flyway table : Name of Flyway’s metadata table locations : Classpath locations to scan recursively for migrations resolvers : Comma-separated list of custom MigrationResolvers sqlMigrationPrefix : File name prefix for Sql migrations sqlMigrationSeparator : File name separator for Sql migrations sqlMigrationSuffix : File name suffix for Sql migrations encoding : Encoding of Sql migrations placeholderReplacement : Whether placeholders should be replaced placeholders : Placeholders to replace in Sql migrations placeholderPrefix : Prefix of every placeholder placeholderSuffix : Suffix of every placeholder target : Target version up to which Flyway should use migrations outOfOrder : Allows migrations to be run “out of order” callbacks : Comma-separated list of FlywayCallback classes validateOnMigrate : Validate when running migrate cleanOnValidationError : Automatically clean on a validation error baselineVersion : Version to tag schema with when executing baseline baselineDescription : Description to tag schema with when executing baseline baselineOnMigrate : Baseline on migrate against uninitialized non-empty schema configFile : Config file to use (default: conf/flyway.properties) configFileEncoding : Encoding of the config file (default: UTF-8) jarDirs : Dirs for Jdbc drivers & Java migrations (default: jars) Add -X to print debug output Add -q to suppress all output, except for errors and warnings Example ======= flyway -target=1.5 -placeholders.user=my_user info More info at http://flywaydb.org/documentation/commandline

Install MySQL on Linux

Installing MySQL on Linux is pretty easy. You can use yum (or your favorite package manager for most installs. Here, we’ll pull a list of packages from yum using repolist: yum repolist enabled | grep "mysql.*-community.*" You’ll then get a list of community edition MySQL packages that are available. Then let’s say you’re installing on RHEL 6, we’ll pull a string from the repolist of an appropriate package and then do a localinstall of it: sudo yum localinstall mysql57-community-release-el6-157.noarch.rpm We could also grab mysql and all the other stuffs we want to have with it: yum install mysql mysql-server mysql-libs mysql-server And then start it up: service mysql start

Using Functions in SQL

SQL has a number of built-in functions that can be used to find common query results, such as averaging data, summing up a column of data, rounding information off, formatting data, etc. SQL also has a number of options for building your own custom functions (and triggering them to run). I usually like to use functions when I’m looking for data and reporting. I don’t like using them in code, as the language I’m performing a task in is typically better suited to manage data than is SQL, comparably. SQL functions come in three types. Aggregate, Scalar, and Custom. Aggregate functions (those that take multiple objects as an input):
  • AVG() – Show the average value in a defined set of data
  • COUNT() – Output the number of rows that match a pattern
  • FIRST() – Output the first value in a set of objects
  • LAST() – Returns the last value in a set of objects
  • MAX() – Show the largest value given a set of objects
  • MIN() – Output the smallest value of a set of objects
  • SUM() – Output the sum defined objects
Scalar functions, which return a value (such as a number or new text):
  • FORMAT() – Formats data in a field
  • LEN() – Shows the length of text
  • LCASE() – Converts the specified data into lower case
  • MID() – Pulls specified characters from text
  • NOW() – Shows the system date and time
  • ROUND() – Rounds a decimal number to the decimals indicated
  • UCASE() – Converts specified data into upper case
Now let’s look at actually using a SQL function. We’ve covered running a SELECT Statement in SQL. But what if you want to count the number of objects returned with that statement. You could dump the results into a csv and view them in Excel. Or, you could save yourself a little time by using the SQL COUNT() function, which shows the number of rows that match a given criteria. In its most basic form, the COUNT function is used as follows (where column is a column in a database and table is the table the column is in: SELECT COUNT(column) FROM table; To put this in action, you can look at all records in the Customers table using the following: SELECT COUNT(*) FROM Customers; In this article, we’ll use the same “Customers” table from our first articles to add and edit columns: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US So the output of the first COUNT would be 5. To constrain, you can use WHERE  following the initial COUNT line. For example, the following SQL statement counts the number of customers from “City”=Minneapolis from the “Customers” table: SELECT COUNT(City) AS InMinneapolis FROM Customers WHERE City="Minneapolis"; You can also use the DISTINCT option to look at how many unique customer cities we have: SELECT COUNT(DISTINCT City) AS CitieswithCustomers FROM Customers;

Reset A Lost MySQL Password

The first step to reset a password is to stop the MySQL daemon. This will cause mysqld to accept no new connections and terminate existing connections. But this can all be done in a matter of seconds, usually. To stop MySQL on Mac, use the System Preference pane or launchctl. To stop on Linux, use init.d: sudo /etc/init.d/mysql stop Or if it’s mysqld instead: sudo /etc/init.d/mysqld stop Then start the SQL daemon using the –skip-grant-tables option: sudo mysqld_safe --skip-grant-tables & Next, login to mysql, which won’t require a password running in this mode: mysql -u root And use the UPDATE USER statement to set a new password: UPDATE USER set password=PASSWORD("mysecretpassword") WHERE USER='root'; Then flush the privileges: flush privileges; Viola, start things back up normally and you’re off to the races.