Как использовать MySQL с помощью Python: Руководство для начинающих


Введение

SQL и Python — два наиболее ценных инструмента для анализа данных. Реальные данные, используемые в приложениях машинного обучения, обычно доступны не в формате CSV, как в соревнованиях Kaggle, а в базах данных.
как на соревнованиях Kaggle, а скорее в базах данных. Структурированные данные обычно хранятся в реляционных базах данных. Для извлечения данных из этих реляционных баз данных используется язык SQL (Structured Query Language). Но с помощью SQL мы можем сделать лишь очень многое, а вот с помощью Python мы можем сделать гораздо больше — от анализа до визуализации этих данных.

Итак, в этой статье мы обсудим, как можно подключаться и использовать базы данных Mysql из Python. Но перед этим давайте познакомимся с преимуществами использования SQL в Python.

Почему именно SQL в Python?

На данном этапе тривиально обсуждать влияние Python на Machine Learning. Обширная поддержка библиотек и относительно легкая кривая обучения делают его идеальным для машинного обучения. Он позволяет нам тратить меньше времени на жонглирование кодами и больше времени на оптимизацию алгоритмов. Такие библиотеки, как Numpy и pandas, значительно упрощают анализ данных, а Matplotlib, Seaborn и Plotly позволяют создавать привлекательные графики с помощью нескольких строк кода. Для любого вида статистического анализа существуют Scipy и Statsmodels, а для машинного обучения — TensorFlow, Pytorch и Scikit Learn. Вдобавок ко всему этому огромное постоянно растущее сообщество.

SQL, с другой стороны, необходим, если вы работаете со структурированными данными. Как я уже говорил, реальные данные обычно хранятся в базах данных, поэтому для извлечения этих данных требуется специальный язык сценариев, а для реляционных баз данных это SQL. Сочетание Python и SQL делает работу с данными еще более гибкой. Например, с помощью Python мы можем легко проводить любой статистический анализ, такой как проверка гипотез, а также получать данные для различных приложений машинного обучения непосредственно из баз данных. Итак, давайте приступим.

Начало работы

Прежде чем перейти к кодовой части, давайте настроим нашу систему. В этом руководстве нам понадобится MySQL и коннекторы MySQL. Если вы еще не установили MySQL в своей системе, то скачайте программу установки MySQL и займитесь всей установкой. Для коннектора MySQL введите следующий код в Jupyter Notebook или можете сделать это в оболочке, пропустив ‘!’.

`!pip install mysql-connector-python`
Войти в полноэкранный режим Выйти из полноэкранного режима

Импорт библиотек
Как обычно, в самом начале мы импортируем необходимые библиотеки.

`import mysql.connector
import pandas as pd`
Войти в полноэкранный режим Выйти из полноэкранного режима

Здесь коннектор MySQL поможет нам подключиться к нашей базе данных, а Pandas, как обычно, будет использоваться для некоторых модификаций данных.

Подключение к MySQL

Следующим шагом будет подключение к серверу MySQL. Это поможет нам общаться с сервером MySQL для работы с реляционными базами данных.

`mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="******",
)
print(mydb)`
<mysql.connector.connection_cext.CMySQLConnection object at 0x0000029B2907FBB0>
Вход в полноэкранный режим Выход из полноэкранного режима

Обязательно используйте правильное имя пользователя и пароль. Чтобы сделать его более элегантным, вы всегда можете использовать его внутри функции, чтобы сделать код многоразовым.

Теперь мы инициализируем объект курсора. Его можно рассматривать как мост между запросами и базами данных MySQL. Они привязываются к текущему соединению на время жизни, пока соединение не будет разорвано. С его помощью выполняются запросы к MySQL.

mycursor = mydb.cursor(buffered=True)
Аргумент buffering по умолчанию имеет значение False, но мы установим его в true, так как после выполнения запроса он будет возвращать итоговые строки.

SQL-запросы
Теперь перейдем к главному. В этом разделе мы будем выполнять SQL-запросы для создания, изменения и удаления элементов базы данных.

Мы можем просто создать нашу базу данных, выполнив следующий код.

`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)`
Войти в полноэкранный режим Выйти из полноэкранного режима

Создание таблицы

Мы можем создать таблицу с помощью следующего кода

`mycursor.execute("create table student (SId int(10), Name varchar(20), City varchar(20) )")`
Войти в полноэкранный режим Выйти из полноэкранного режима

Если вы еще не знакомы с SQL, обратитесь к этой статье для ознакомления с различными типами запросов. Приведенный выше код создает таблицу с именем student в выбранной вами базе данных с именами полей или столбцов с указанным типом данных и длиной.
Добавление данных в таблицу
Данные в таблицу могут быть легко добавлены с помощью следующего фрагмента кода

`mycursor.execute("""insert into student values ("1","Alex", 
"Patna"), ("2","Alexa", "Georgia") ,("3","Frieza", "Pearl Harbour"), ("4","Siri", "Pretoria")""")``
Вход в полноэкранный режим Выйти из полноэкранного режима

Здесь мы использовали тройные кавычки для многострочных строковых команд. Теперь давайте посмотрим, как выглядит наша таблица данных

`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')`
Войти в полноэкранный режим Выйти из полноэкранного режима

Чтобы отменить изменения, внесенные в таблицу, мы можем использовать команду SQL rollback.

` mydb.rollback()`
Войти в полноэкранный режим Выйти из полноэкранного режима

Операторы Select и Where

В этом разделе мы будем использовать примерный набор данных о городах в разных странах.

`mycursor.execute("select * from city")
cities = mycursor.fetchmall()
for city in cities:
    print(city)`
Вход в полноэкранный режим Выход из полноэкранного режима

В приведенном выше коде функция fetchall() извлекает все строки таблицы. Существуют и другие подобные функции, например

fetchmany(size): Извлекает количество строк, заданное размером. При многократном вызове этот метод извлекает следующий набор строк результата запроса
fetchone(): Захватывает только один ряд или ни одного.

`columns = [i[0] for i in mycursor.description]
print(columns)
output: ['ID', 'Name', 'CountryCode', 'District', 'Population']`
Войти в полноэкранный режим Выйти из полноэкранного режима

Давайте выполним несколько запросов

`query = '''select Name from City where population>500000 limit 10 
mycursor.execute(query)
for city in mycursor:
    print(city)

output:('Kabul',)
('Amsterdam',)
('Rotterdam',)
('Alger',)
('Oran',)
('Luanda',)
('Dubai',)
('Buenos Aires',)
('La Matanza',)
('Córdoba',)`
Войдите в полноэкранный режим Выйти из полноэкранного режима

Запрос городов Японии с населением более 5 000 000 человек.

`query = '''select Name from City where population>500000 and countrycode = 'JPN' limit 5 '''
mycursor.execute(query)
s = mycursor.fetchmany(5)
for city in s:
    print(city)
output: ('Tokyo',)
('Jokohama [Yokohama]',)
('Osaka',)
('Nagoya',)
('Sapporo',)`
Войти в полноэкранный режим Выйти из полноэкранного режима

Группировать по и упорядочивать по
Group by — это предложение SQL, которое используется для группировки строк с одинаковыми значениями. В то время как order by используется для упорядочивания строк, выбранных с помощью оператора select.

`query = '''select countrycode,sum(population) from City group by countrycode'''
mycursor.execute(query)
s = mycursor.fetchmany(5)
for city in s:
    print(city)
output: ('ABW', Decimal('29034'))
('AFG', Decimal('2332100'))
('AGO', Decimal('2561600'))
('AIA', Decimal('1556'))
('ALB', Decimal('270000'))`
Войти в полноэкранный режим Выход из полноэкранного режима

Упорядочить коды стран по убыванию численности населения.

`query = '''select countrycode,sum(population) as s from City group by countrycode order by s desc'''
mycursor.execute(query)
s = mycursor.fetchmany(5)
for city in s:
    print(city)
output: ('CHN', Decimal('175953614'))
('IND', Decimal('123298526'))
('BRA', Decimal('85876862'))
('USA', Decimal('78625774'))
('JPN', Decimal('77965107'))`
Войти в полноэкранный режим Выход из полноэкранного режима

Объединения SQL

Объединения в SQL — одна из ключевых концепций SQL. Обычно нам приходится работать с несколькими таблицами, и для запроса данных из них требуется объединение. Существует несколько методов объединения, но чаще всего мы будем использовать внутреннее объединение. Различные виды присоединений показаны на следующей диаграмме.

источник: Dofactory.com

`query = '''select co.continent,sum(ci.population) as TotalPop from country co inner join city ci on ci.countrycode = co.code group by co.continent 
order by TotalPop desc'''
mycursor.execute(query)
res = mycursor.fetchall()
for i in res:
    print(i)

output:('Asia', Decimal('697604103'))
('Europe', Decimal('241942813'))
('South America', Decimal('172037859'))
('North America', Decimal('168250381'))
('Africa', Decimal('135838579'))
('Oceania', Decimal('13886149'))`
Вход в полноэкранный режим Выход из полноэкранного режима

В приведенном выше коде мы выполнили внутреннее объединение двух таблиц city и country по общему атрибуту country code, поскольку объединение возможно только при наличии общего поля или атрибута. Затем мы упорядочили результаты в порядке убывания.

Pandas и SQL

В Pandas есть встроенные функции для SQL-запросов. Но в Pandas мы получаем на выходе не кортежи, а фреймы данных. Все, что нам нужно сделать, это передать в функцию запрос и соединение с базой данных.

`query ='''select * from city limit 10
pd.read_sql(query, con = mydb)`
Вход в полноэкранный режим Выход из полноэкранного режима

Или вы можете просто создать набор данных, как обычно, передав запрашиваемые данные и столбцы.

`mycursor.execute("select * from city")
cities = mycursor.fetchmany(10)
df = pd.DataFrame(cities,columns=columns)
df.head()`
Войти в полноэкранный режим Выход из полноэкранного режима

Самое лучшее в Pandas — это то, что он значительно упрощает анализ. Если мы хотим визуализировать страны с наибольшей численностью населения, мы можем легко сделать это с помощью функции Pandas plot.

`query = '''select countrycode,sum(population) as TotalPop 
from City 
group by countrycode 
order by TotalPop desc 
limit 10'''
pd.read_sql_query(query,con=mydb).plot(kind='bar', x = 'countrycode', y='TotalPop' )`
Войти в полноэкранный режим Выход из полноэкранного режима

import matplotlib.pyplot as plt
query = '''select language,percentage from countrylanguage where countrycode='AFG' '''
data = pd.read_sql_query(query,con=mydb, )
data.plot(kind='pie',x='language',y='percentage', )
plt.legend(data.language)
Войти в полноэкранный режим Выход из полноэкранного режима

После преобразования данных в объект фрейма данных мы можем использовать любую библиотеку для получения желаемых результатов.

После завершения работы соединение можно прервать, выполнив следующую команду.

Заключение

MySQL и Python — два самых популярных и эффективных в своей работе языка. MySQL значительно облегчает эффективное извлечение данных из баз данных, в то время как Python позволяет проводить сложный анализ данных. Оба языка имеют свои уникальные возможности, и, комбинируя их, мы можем использовать лучшее из того и другого.
Прежде чем расстаться, давайте подведем краткий итог вышеупомянутой статьи.

  • Прежде всего, мы научились устанавливать соединение с сервером MySQL из Python.
  • Мы научились выполнять SQL-запросы из среды Python с помощью объекта курсора.
  • Научились реализовывать несколько ключевых концепций SQL, таких как Select, Where, Group by, Order By, Join и т.д.
  • Конвертировали данные запросов во фрейм данных Pandas для анализа и визуализации.

Вот и все. Надеюсь, вам понравилась статья.

Оцените статью
devanswers.ru
Добавить комментарий