3 minutes
Databases MySQL: Installation
Introduction to Databases: MySQL
During the following days I will write about databases SQL, how to install, debug and use them via Python and C++.
Installation
To install a MySQL I will use Docker, as it is the most easiest way in my opinion to quickly deploy a test database.
In a Jupyter Notebook, let’s run:
!!docker run --name mysql-test -e MYSQL_ROOT_PASSWORD=secret -d -p 3306:3306 mysql
['b2946635401f23ee223861da235cc309af878dd0305d03285673b39962ca1311']
We can check that the database is running with docker ps
!!docker ps | grep mysql-test
['b2946635401f mysql "docker-entrypoint.s…" 14 minutes ago Up 14 minutes 0.0.0.0:3306->3306/tcp, 33060/tcp mysql-test']
We can create an user using the root credentials (password secret). For that let’s run in a terminal
luis@BARE:~/projects/How to use MySQL$ docker exec -it mysql-test mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.33 sec)
mysql> GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> exit
Bye
luis@BARE:~/projects/How to use MySQL$
We can reconnect (in a terminal) to our database and create a database, for that we run:
luis@BARE:~/projects/How to use MySQL$ docker exec -it mysql-test mysql -u user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE datab;
Query OK, 1 row affected (0.02 sec)
mysql> exit
Bye
luis@BARE:~/projects/How to use MySQL$
Now we can configure our Jupyter Notebook to connect to the database and execute SQL commands, for that we need to install the SQL extension for iPython
!pip3 install ipython-sql sqlalchemy
Requirement already satisfied: ipython-sql in /home/luis/.local/lib/python3.8/site-packages (0.4.0)
Requirement already satisfied: sqlalchemy in /home/luis/.local/lib/python3.8/site-packages (1.3.20)
Requirement already satisfied: sqlparse in /home/luis/.local/lib/python3.8/site-packages (from ipython-sql) (0.4.1)
Requirement already satisfied: ipython-genutils>=0.1.0 in /home/luis/.local/lib/python3.8/site-packages (from ipython-sql) (0.2.0)
Requirement already satisfied: six in /usr/lib/python3/dist-packages (from ipython-sql) (1.14.0)
Requirement already satisfied: ipython>=1.0 in /home/luis/.local/lib/python3.8/site-packages (from ipython-sql) (7.18.1)
Requirement already satisfied: prettytable<1 in /home/luis/.local/lib/python3.8/site-packages (from ipython-sql) (0.7.2)
Requirement already satisfied: jedi>=0.10 in /home/luis/.local/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.17.2)
Requirement already satisfied: backcall in /home/luis/.local/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /home/luis/.local/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (3.0.8)
Requirement already satisfied: traitlets>=4.2 in /home/luis/.local/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.0.5)
Requirement already satisfied: setuptools>=18.5 in /usr/lib/python3/dist-packages (from ipython>=1.0->ipython-sql) (45.2.0)
Requirement already satisfied: decorator in /home/luis/.local/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (4.4.2)
Requirement already satisfied: pickleshare in /home/luis/.local/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)
Requirement already satisfied: pygments in /home/luis/.local/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (2.7.1)
Requirement already satisfied: pexpect>4.3; sys_platform != "win32" in /home/luis/.local/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)
Requirement already satisfied: parso<0.8.0,>=0.7.0 in /home/luis/.local/lib/python3.8/site-packages (from jedi>=0.10->ipython>=1.0->ipython-sql) (0.7.1)
Requirement already satisfied: wcwidth in /home/luis/.local/lib/python3.8/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)
Requirement already satisfied: ptyprocess>=0.5 in /home/luis/.local/lib/python3.8/site-packages (from pexpect>4.3; sys_platform != "win32"->ipython>=1.0->ipython-sql) (0.6.0)
And finally let’s load the extension
%reload_ext sql
Before we can connect to our database, we need to install the mysql driver
!pip3 install pymysql mysql-connector
Requirement already satisfied: pymysql in /home/luis/.local/lib/python3.8/site-packages (1.0.2)
Requirement already satisfied: mysql-connector in /home/luis/.local/lib/python3.8/site-packages (2.2.9)
Now we can connect to our database in our localhost
%sql mysql+pymysql://user:password@localhost/datab
If everything went OK, no output is produced with the above command
In the next post, we will create a table and explore how to add, read and delete entries in our database