MySQL
=====

Connecting to MySQL databases from the command line
---------------------------------------------------

You can connect to a MySQL database with the `mysql client
<https://dev.mysql.com/doc/refman/5.7/en/mysql.html>`_. This is preinstalled on
Faculty servers.

To connect to a MySQL database, you need to know:

 - its `hostname`: this is usually a string like ``customers.mydomain.com``;
 - its `port`: the default port for MySQL databases is 3306;
 - the name of the database that you want to connect to. If this is a new
   server, the only database on it is likely to be named ``mysql``;
 - your username and password for the database server (note that this is
   different to your Faculty username). If you are unsure of these, you
   should ask your database administrator.

You can then connect to the database with:

.. code-block:: bash

   $ mysql --host HOSTNAME --port PORT --user USERNAME --database DATABASE_NAME --password

You should then be prompted for a password. If the port is the default 3306,
you may omit the ``--port PORT`` argument.

For instance, I can connect as user ``funny_bohr`` to database ``example`` on
host ``exampledb.cqx0gge7x5bc.eu-west-1.r ds.amazonaws.com`` with the following
command:

.. thumbnail:: images/mysql_example.png

If you get stuck, run ``mysql --help | less`` in a terminal to view a list of
all available options.

Connecting to MySQL databases from Python
-----------------------------------------

Unfortunately, there seems to be some confusion as to what the best Python MySQL
client is. The `official` client, `MySQLdb
<https://pypi.python.org/pypi/MySQL-python/1.2.5>`_, does not support Python 3
yet. We recommend using a fork, `mysqlclient-python
<https://github.com/PyMySQL/mysqlclient-python>`_, which exposes the same
interface as the official client, and does support Python 3.

You can install `mysqlclient-python` on a Faculty server with:

.. code-block:: bash

   $ sudo apt install libmysqlclient-dev
   $ pip install mysqlclient

You can now connect to your MySQL database:

.. code-block:: python

   import MySQLdb as mysql

   connection = mysql.connect(
       host='customers.mydomain.com',  # host on which the database is running
       database='database_name',  # name of the database to connect to
       user='username',  # username to connect with
       passwd='password'  # password associated with your username; note
   )                      # the keyword is `passwd`, not `password`

.. note:: We close the connection to allow the database server to reclaim
          resources. This can be critical in a Jupyter notebook, since the
          kernel remains alive for a long time.

The mysql connection can also be used in a ``with`` statement to ensure it gets
closed automatically:

.. code-block:: python

    import mysql

    with mysql.connect(
        host='customers.mydomain.com', database='database_name',
        user='username', password='password'
    ) as connection:
        cursor = connection.cursor()
        cursor.execute('SELECT * FROM customers')
        customers = list(cursor.fetchall())
        print('We have {} customers'.format(len(customers)))

You can also use the ``read_sql()`` function in pandas to read the result of a
query directly into a DataFrame:

.. code-block:: python

    import mysql
    import pandas

    with mysql.connect(
        host='customers.mydomain.com', database='database_name',
        user='username', password='password'
    ) as connection:
        df = pandas.read_sql('SELECT * FROM customers', connection)

    print(df)

.. note:: We recommend avoiding pasting database passwords and other connection
          details in many notebooks in a project. Have a look at
          :ref:`package_connection_details` for a recommended strategy for
          managing database connection details.