How to use MySQL with Python: A beginners guide
SQL and Python are two of the most valuable tools for data analytics. The real-world data used in Machine Learning applications are usually not available in CSV formats like they
are in Kaggle competitions but rather in databases. The structured data usually are stored in relational databases. And to pull data from these Relational Database Systems(RDBMS) SQL (Structured Query Language) is used. But we can only do so much with SQL but with the help of Python, we can do many more things from analysis to visualization of these data. So, in this article, we are going to discuss how we can connect and use Mysql databases from Python. But before that let’s get to know the advantages of using SQL in Python.
Why SQL with Python?
At this point, it’s trivial to discuss the impact of Python on Machine Learning. Vast library support with a relatively easy learning curve makes it ideal for Machine Learning. As it lets us spend less time juggling with codes and more time optimizing algorithms. Libraries like Numpy, and pandas make data analysis so much easier, while Matplotlib, Seaborn, and Plotly can make attractive plots with few lines of code. For any kind of statistical analysis there are Scipy and Statsmodels and for machine learning TensorFlow, Pytorch, and Scikit Learn. On top of all these things a vast ever-growing community. SQL on the other hand is imperative if you are working with structured data. Like I said before real-world data usually gets stored in databases so retrieving those data requires a specific scripting language and for relational databases it is SQL. Combining Python with SQL makes it even more flexible to work with data. For example, with Python, we can do any statistical analysis such as Hypothesis testing quite easily and also can fetch data for different Machine Learning applications directly from databases. So, let’s get started.
Getting Started
Before proceeding to the code part let’s set up our system. In this tutorial, we will need MySQL and MySQL connectors. If you haven’t already installed MySQL in your system then download the MySQL installer and take care of the entire setup. For MySQL connector type the following code in your Jupyter Notebook or you can do it within the shell as well by just skipping ‘!’.
`!pip install mysql-connector-python` `import mysql.connector import pandas as pd` Here, the MySQL connector will help us connect to our database and Pandas as usual will be used for some data modifications.
Connecting to MySQL
The next thing is to connect to the MySQL server. This will help us communicate with the MySQL server to work with relational databases.
`mydb = mysql.connector.connect( host="localhost", user="root", password="******", ) print(mydb)`
Be sure to use the correct username and password. To make it more elegant you can always use it inside a function to make the code reusable. Now, we will initialize a cursor object. It can be thought of as a bridge between queries and MySQL databases. They are bound to the current connection for a lifetime until the connection is terminated. This help executes MySQL queries. mycursor = mydb.cursor(buffered=True)
The buffering argument is by default set to False but we set it to true as it will return the outcome rows after being executed. SQL Queries
Now, let’s get to the main thing. In this section, we will be executing SQL queries to create, alter and delete database elements. We can simply create our database by executing the following code.
`mycursor.execute("create database testdb")` `mycursor.execute("SHOW DATABASES") `for x in mycursor:` ` print(x)`` `mycursor.execute("use mydatabase")` `mycursor.execute("show tables") `for x in mycursor: `print(x)` Creating a Table
`mycursor.execute("create table student (SId int(10), Name varchar(20), City varchar(20) )")` If you are not already familiar with SQL, refer to this article for an introduction to different types of queries. The above code creates a table named student in your selected database with field or column names with a specified data type and length.
Adding Data to Table
Data into the table can easily be fed through the following code snippet
`mycursor.execute("""insert into student values ("1","Alex", "Patna"), ("2","Alexa", "Georgia") ,("3","Frieza", "Pearl Harbour"), ("4","Siri", "Pretoria")""")`` Here, we used triple quotation marks for multi-line string commands. Let’s now see how our data table looks like
`mycursor.execute("select * from student") st = mycursor.fetchall() for i in st: print(i) ('1', 'Alex', 'Patna') ('2', 'Alexa', 'Georgia') ('3', 'Frieza', 'Pearl Harbour') ('4', 'Siri', 'Pretoria')` Python work with mysql
У библиотеки есть весьма простая, понятная документация и большое комьюнити .
Создадим файл main.py, в котором будет происходить вся магия и импортируем в него ранее установленный модуль:
Подключение к БД
Первым делом нам нужно подключиться к базе. Создадим объект класса pymysql, вызовем у него метод connect и передадим в него параметры для подключения к нашей базе данных (БД):
import pymysql connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) - host: если ваша БД находится на локальной машине, то его значение будет localhost , либо 127.0.0.1, либо IP адрес хостинга, на котором вы развернули СУБД
- port: стандартный 3306
- user: это логин пользователя
- password: пароль
- db_name: имя нашей базы данных
Обернем код в блок try/except, в блоке try будем подключаться к БД, а в блоке except будем выводить в терминал возможные ошибки:
import pymysql try: connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) print("successfully connected. ") print("#" * 20) except Exception as ex: print("Connection refused. ") print(ex) После работы с базой рекомендуется закрывать соединение. Создадим ещё один блок try/finally, внутри блока try мы будем писать наши запросы к БД, а внутри блока finally будем закрывать наше соединение:
import pymysql try: connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) print("successfully connected. ") print("#" * 20) try: pass finally: connection.close() except Exception as ex: print("Connection refused. ") print(ex) Чтобы начать работать с MySQL, нам нужно создать объект cursor. Это объект, который содержит в себе различные методы для проведения SQL команд. М ы можем как просто положить его значение в переменную cursor = connection.cursor() , так и воспользоваться контекстным менеджером with. Мне второй вариант нравится больше, так как выглядит лаконичней, да и документация подсказывает нам, как правильно работать с библиотекой:
import pymysql try: connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) print("successfully connected. ") print("#" * 20) try: # create table with connection.cursor() as cursor: pass finally: connection.close() except Exception as ex: print("Connection refused. ") print(ex) Теперь давайте создадим простую таблицу, на которой сегодня потренируемся. Создаем переменную create_table_query и пишем запрос.
Создать таблицу users со следующими строками:
# create table with connection.cursor() as cursor: create_table_query = "CREATE TABLE `users`(id int AUTO_INCREMENT," \ " name varchar(32)," \ " password varchar(32)," \ " email varchar(32), PRIMARY KEY (id));" Для того чтобы выполнить запрос на создание таблицы, вызываем у cursor метод execute, и передаем в него наш запрос. Выведем в print сообщение об успешном исполнении:
# create table with connection.cursor() as cursor: create_table_query = "CREATE TABLE `users`(id int AUTO_INCREMENT," \ " name varchar(32)," \ " password varchar(32)," \ " email varchar(32), PRIMARY KEY (id));" cursor.execute(create_table_query) print("Table created successfully") Добавление данных в таблицу
Таблицу мы создали, теперь давайте заполним её данными. За добавление данных в таблицу в SQL отвечает метод INSERT. Пишем запрос. Дословно говорим:
Вставить в таблицу users, перечисляем поля, которые хотим заполнить, а затем данные, которыми мы хотим наполнить запись в таблице.
Например, у нас будет пользователь Анна, с паролем qwerty и почтой от gmail:
# insert data with connection.cursor() as cursor: insert_query = "INSERT INTO `users` (name, password, email) VALUES ('Anna', 'qwerty', 'anna@gmail.com');" Вызываем метод execute у cursor и передаем в него наш запрос. Для того чтобы наши данные занеслись в таблицу и сохранились там, нам нужно закоммитить или зафиксировать наш запрос. Вызываем метод commit у объекта connection:
# insert data with connection.cursor() as cursor: insert_query = "INSERT INTO `users` (name, password, email) VALUES ('Anna', 'qwerty', 'anna@gmail.com');" cursor.execute(insert_query) connection.commit() Теперь в нашей таблице создалась одна запись с пользователем Анна.
Извлечение данных из таблицы
Напишем запрос на извлечение всех данных из таблицы. Звездочка в данном запросе забирает все, что есть в таблице:
# select all data from table with connection.cursor() as cursor: select_all_rows = "SELECT * FROM `users`" cursor.execute(select_all_rows) У cursor есть замечательный метод fetchall, который извлекает из таблицы все строки. Нам лишь остается пробежаться по ним циклом и распечатать.
# select all data from table with connection.cursor() as cursor: select_all_rows = "SELECT * FROM `users`" cursor.execute(select_all_rows) rows = cursor.fetchall() for row in rows: print(row) print("#" * 20) Удаление данных из таблицы
Напишем запрос на удаление. Говорим: Удалить запись из таблицы users, где id равен 1. У нас ведь пока только одна запись:
# delete data with connection.cursor() as cursor: delete_query = "DELETE FROM `users` WHERE cursor.execute(delete_query) connection.commit() Удаление таблицы
Последний запрос, который мы выполним. Давайте дропним нашу таблицу. Под словом дропнут подразумевается удаление всей таблицы целиком. Будьте аккуратны: данному запросу, как и запросу на создание таблицы, коммит не требуется:
# drop table with connection.cursor() as cursor: drop_table_query = "DROP TABLE `users`;" cursor.execute(drop_table_query) Полный код файла main.py:
import pymysql try: connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) print("successfully connected. ") print("#" * 20) try: # create table with connection.cursor() as cursor: create_table_query = "CREATE TABLE `users`(id int AUTO_INCREMENT," \ " name varchar(32)," \ " password varchar(32)," \ " email varchar(32), PRIMARY KEY (id));" cursor.execute(create_table_query) print("Table created successfully") insert data with connection.cursor() as cursor: insert_query = "INSERT INTO `users` (name, password, email) VALUES ('Anna', 'qwerty', 'anna@gmail.com');" cursor.execute(insert_query) connection.commit() # delete data with connection.cursor() as cursor: delete_query = "DELETE FROM `users` WHERE cursor.execute(delete_query) connection.commit() # drop table with connection.cursor() as cursor: drop_table_query = "DROP TABLE `users`;" cursor.execute(drop_table_query) # select all data from table with connection.cursor() as cursor: select_all_rows = "SELECT * FROM `users`" cursor.execute(select_all_rows) rows = cursor.fetchall() for row in rows: print(row) print("#" * 20) finally: connection.close() except Exception as ex: print("Connection refused. ") print(ex) Подведение итогов
Теперь вы знаете как можно подключаться к СУБД MySQL с помощью Python, а также выполнять любые запросы, включая создание таблиц, занесение в них данных, а также удаление строк и самих таблиц.
Надеюсь, статья вам помогла и вы узнали что-то новое. 👍