MySQL Worked Example

This page explains how to load data into Q from a MySQL database. If you want to see how Q works with SQL data sets, but don't have one readily on hand, then this page will help you get a better understanding. Apart from the technicalities, working with SQL Data in Q the same as working with other data formats that do not contain metadata (like CSV or Excel files).

This worked example utilizes a public MySQL database and details the steps to configure the Windows drivers and connect to the database. The basic process involves 3 specific steps:

1. Installing the MySQL database driver
2. Configuring the ODBC connection
3. Creating the database connection in Q

MySQL driver installation

The first step is to download and install the MySQL ODBC database connection driver.

1. Go to https://dev.mysql.com/downloads/connector/odbc/5.3.html
2. Download Windows (x86, 64-bit), MSI Installer (click No thanks, just start my download on the next page).
3. Run the downloaded MSI file to install the driver.
4. Accept all default prompts during the installation process (nothing special needs to be done here).

ODBC configuration

Once the driver installation is complete, you next need to create and configure an ODBC connection on your machine.

1. Open your Windows Control Panel and go to Administrative Tools > ODBC Data Sources (64-bit).
2. Select the System DSN tab and click Add.
3. Select MySQL ODBC 5.3 ANSI Driver.
4. Click Finish.
   
   
   
   
5. Enter the following values:
   
   

   Data Source Name	This is a reference name and can be anything you want.
   Description	Optional (can be left blank)
   TCP/IP Server	ensembldb.ensembl.org (however, this can be any of the Ensembl public MySQL database servers listed here: https://www.ensembl.org/info/data/mysql.html)
   Port	3306
   User	anonymous
   Password	None (leave blank)
   Database	None (leave blank for now - this will be added below)

   
   
   
   
6. Click the Test button to test the configuration. If everything has been entered correctly, you will get a Connection Successful message.
7. Once the connection has been made, there will be several databases available to select from in the Database dropdown box. Select the first database in the list which should be aedes_aegypti_core_48_1b (or copy and paste the database name from here).
8. Click OK to complete the ODBC configuration.

Connection to the database from Q

Open Q and select File > Data Sets > Add to Project > From Database (SQL) and enter the following values:

Name	This is a reference name and can be anything you want.
Data provider	System.Data.Odbc
Connection string	Driver={MySQL ODBC 5.3 ANSI Driver};Server=ensembldb.ensembl.org; Database=aedes_aegypti_core_48_1b; UID=anonymous;Port=3306
SQL command	Enter your SQL statement here (see below for sample SQL statements that can be used with this database).
Maximum cases	Limits the number of rows of data that will be fetched from the database. This helps protect you from queries that explode into enormous numbers of rows, and clog up both Q and your database.
Automatically refresh every	The number of hours old that your data may be before Displayr will automatically refresh it. The dashboard will slow down while refreshing data, so don't set this lower than necessary.

Example SQL statements

These example SQL statements can be used with the above database connection. The following SQL statement will returns all records (list of tables) in the specified schema:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'aedes_aegypti_core_48_1b'

This SQL statement will return all records from the table object_xref in the aedes_aegypti_core_48_1b schema.

SELECT * FROM aedes_aegypti_core_48_1b.object_xref

Click OK to save the SQL configuration, run the query and import the data into Q (data will be setup using normal Q data setup procedures).

See also

• SQL Data

External references

• SQL tutorial
• MySQL website