Articles in this section

See more

MySQL Worked Example

  • Updated
Follow

This article 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 by leveraging public MySQL database. Apart from the technicalities, working with SQL data in Q is 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

Requirements

MySQL driver installation

The first step is downloading and installing 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 installation (nothing special needs to be done here).

ODBC configuration

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

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

    SQL.png

  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)

    Odbc config.png
  6. Click the Test button to test the configuration. You will get a Connection Successful message if everything has been entered correctly.
  7. Once the connection has been made, the Database dropdown box will display several databases. The first database in the list should be aedes_aegypti_core_48_1b (or you can copy and paste the database name from here).
  8. Click OK to complete the ODBC configuration.

Method

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 9.0 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 Q will automatically refresh it. The dashboard will slow down while refreshing data, so don't set this lower than necessary.

Db connection.png

Example SQL statements

These example SQL statements can be used with the above database connection. The following SQL statement will return 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 set up using normal Q data setup procedures).

Next

How to Import Data from SQL

How to Connect to a MySQL Database in Q

 

Related articles