Примеры работы с MySQL в Python при помощи модуля PyMySQL

В следующей инструкции будет показано, как использовать MySQL в Python при помощи PyMySQL модуля.

PyMySQL на примерах

PyMySQL представляет собой уникальную для Python клиентскую библиотеку, основанную на PEP 249. Большая часть общедоступных API совместимы с mysqlclient и MySQLdb. PyMySQL работает с MySQL 5.5+ и MariaDB 5.5+.

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

Установка PyMySQL

$ sudo pip3 install PyMySQL

1	$ sudo pip3 install PyMySQL

Для установки PyMySQL используется pip3.

Есть вопросы по Python?

На нашем форуме вы можете задать любой вопрос и получить ответ от всего нашего сообщества!

Python Форум Помощи

Telegram Чат & Канал

Вступите в наш дружный чат по Python и начните общение с единомышленниками! Станьте частью большого сообщества!

Чат

Канал

Паблик VK

Одно из самых больших сообществ по Python в социальной сети ВК. Видео уроки и книги для вас!

PyMySQL — узнаем версию MySQL

В следующем примере показано, как отобразить текущую версию MySQL.

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'mydb')

with con:
    
    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    version = cur.fetchone()
    
    print("Database version: {}".format(version[0]))

#!/usr/bin/python3

# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17',

's$cret', 'mydb')

with con:

cur = con.cursor()

cur.execute("SELECT VERSION()")

version = cur.fetchone()

print("Database version: {}".format(version[0]))

Для того чтобы узнать версию MySQL, можно использовать команду SELECT VERSION().

import pymysql

1	import pymysql

Для этого потребуется импортировать pymysql модуль.

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'mydb')

1 2	con = pymysql.connect('localhost', 'user17', 's$cret', 'mydb')

Затем производится подключение к базе данных при помощи connect(). Здесь требуется указать четыре параметра:

имя хоста;
имя пользователя;
пароль;
название базы данных.

with con:

with con:

При помощи with интерпретатор Python автоматически открывает доступные ресурсы. Он также обрабатывает возможные ошибки.

cur = con.cursor()

1	cur = con.cursor()

Из объекта подключения con создается курсор. Курсор используется для перемещения записей из набора результатов.

cur.execute("SELECT VERSION()")

1	cur.execute("SELECT VERSION()")

Для использования команды SQL вызывается метод курсора execute().

version = cur.fetchone()

1	version = cur.fetchone()

Метод fetchone() позволяет вызвать следующую строку из набора результатов запроса, показывая только одну запись. В том случае, если доступных данных нет, выводится None.

print("Database version: {}".format(version[0]))

1	print("Database version: {}".format(version[0]))

Таким образом, версия базы данных выводится на экран.

$ ./version.py 
Database version: 5.7.23-0ubuntu0.16.04.1

1 2	$ ./version.py Database version: 5.7.23-0ubuntu0.16.04.1

Это результат вывода.

PyMySQL fetchAll

Метод fetchAll() позволяет извлечь все (оставшиеся) строки результата запроса, возвращая их в виде последовательности последовательностей.

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'testdb')

with con: 

    cur = con.cursor()
    cur.execute("SELECT * FROM cities")

    rows = cur.fetchall()

    for row in rows:
        print("{0} {1} {2}".format(row[0], row[1], row[2]))

#!/usr/bin/python3

# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17',

's$cret', 'testdb')

with con:

cur = con.cursor()

cur.execute("SELECT * FROM cities")

rows = cur.fetchall()

for row in rows:

print("{0} {1} {2}".format(row[0], row[1], row[2]))

В данном примере из таблицы базы данных выводятся все города (cities).

cur.execute("SELECT * FROM cities")

1	cur.execute("SELECT * FROM cities")

Данный оператор SQL выбирает все данные из таблицы cities.

rows = cur.fetchall()

1	rows = cur.fetchall()

Метод fetchall() позволяет получить все записи. Он возвращает набор результатов. Технически, это кортеж из кортежей. Каждый из внутренних кортежей представляет собой строку в таблице.

for row in rows:
    print("{0} {1} {2}".format(row[0], row[1], row[2]))

1 2	for row in rows: print("{0} {1} {2}".format(row[0], row[1], row[2]))

Данные выводятся в консоль строка за строкой.

$ ./retrieve_all.py 
1 Bratislava 432000
2 Budapest 1759000
3 Prague 1280000
4 Warsaw 1748000
5 Los Angeles 3971000
6 New York 8550000
7 Edinburgh 464000
8 Berlin 3671000

$ ./retrieve_all.py

1 Bratislava 432000

2 Budapest 1759000

3 Prague 1280000

4 Warsaw 1748000

5 Los Angeles 3971000

6 New York 8550000

7 Edinburgh 464000

8 Berlin 3671000

Это результат вывода.

PyMySQL словарь курсора

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

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql
import pymysql.cursors

con = pymysql.connect(host='localhost',
        user='user17',
        password='s$cret',
        db='mydb',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM cities")

    rows = cur.fetchall()

    for row in rows:
        print(row["id"], row["name"])

#!/usr/bin/python3

# -*- coding: utf-8 -*-

import pymysql

import pymysql.cursors

con = pymysql.connect(host='localhost',

user='user17',

password='s$cret',

db='mydb',

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor)

with con:

cur = con.cursor()

cur.execute("SELECT * FROM cities")

rows = cur.fetchall()

for row in rows:

print(row["id"], row["name"])

В данном примере показано, как получить первые строки таблицы cities при помощи использования словаря курсора.

con = pymysql.connect(host='localhost',
        user='user17',
        password='s$cret',
        db='mydb',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)

con = pymysql.connect(host='localhost',

user='user17',

password='s$cret',

db='mydb',

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor)

Используя метод connect(), можно передать значение pymysql.cursors.DictCursor параметру cursorclass.

for row in rows:
    print(row["id"], row["name"])

1 2	for row in rows: print(row["id"], row["name"])

Обращение к данным происходит при помощи названий столбцов таблицы cities.

PyMySQL заголовки столбцов

Далее будет показано, как вывести названия столбцов с информацией из таблицы базы данных.

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM cities")

    rows = cur.fetchall()

    desc = cur.description

    print("{0:>3} {1:>10}".format(desc[0][0], desc[1][0]))

    for row in rows:    
        print("{0:3} {1:>10}".format(row[0], row[2]))

#!/usr/bin/python3

# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17',

's$cret', 'testdb')

with con:

cur = con.cursor()

cur.execute("SELECT * FROM cities")

rows = cur.fetchall()

desc = cur.description

print("{0:>3} {1:>10}".format(desc[0][0], desc[1][0]))

for row in rows:

print("{0:3} {1:>10}".format(row[0], row[2]))

Названия столбцов представляют собой метаданные. Они извлекаются из объекта курсора.

desc = cur.description

1	desc = cur.description

Атрибут курсора description возвращает информацию о каждом результативном столбце запроса.

print("{0:>3} {1:>10}".format(desc[0][0], desc[1][0]))

1	print("{0:>3} {1:>10}".format(desc[0][0], desc[1][0]))

Таким образом, выводятся и форматируются названия столбцов таблицы.

for row in rows:    
    print("{0:3} {1:>10}".format(row[0], row[2]))

1 2	for row in rows: print("{0:3} {1:>10}".format(row[0], row[2]))

Данные перебираются и выводятся на экран при помощи цикла for.

$ ./column_headers.py 
 id       name
  1     432000
  2    1759000
  3    1280000
  4    1748000
  5    3971000
  6    8550000
  7     464000
  8    3671000

$ ./column_headers.py

id name

1 432000

2 1759000

3 1280000

4 1748000

5 3971000

6 8550000

7 464000

8 3671000

Это результат вывода.

PyMySQL связываемые переменные

При написании связываемых переменных вместо прямого введения значений используется специальные плейсхолдеры (placeholders). Связываемые переменные позволяют повысить безопасность и производительность.

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17', 
    's$cret', 'testdb')

# user input
myid = 4

with con:    

    cur = con.cursor()
        
    cur.execute("SELECT * FROM cities WHERE id=%s", myid) 
    
    cid, name, population  = cur.fetchone()
    print(cid, name, population)

#!/usr/bin/python3

# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17',

's$cret', 'testdb')

# user input

myid = 4

with con:

cur = con.cursor()

cur.execute("SELECT * FROM cities WHERE id=%s", myid)

cid, name, population = cur.fetchone()

print(cid, name, population)

В данном примере показано, как получить строку с определенным Id.

cur.execute("SELECT * FROM cities WHERE id=%s", myid)

1	cur.execute("SELECT * FROM cities WHERE id=%s", myid)

Здесь используется плейсхолдер, который идентифицируется при помощи маркера %s. Перед тем, как определенная SQL переменная будет задействована, она должна быть привязана к ее плейсхолдеру.

$ ./prepared_statement.py 
4 Warsaw 1748000

1 2	$ ./prepared_statement.py 4 Warsaw 1748000

Это результат вывода.

PyMySQL задействованные строки

Предназначенный только для чтения атрибут курсор rowcount показывает количество строк, которые были получены в результате последнего использования одного из операторов SELECT, UPDATE или INSERT.

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17',
   's$cret', 'mydb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM cities WHERE id IN (1, 2, 3)")

    # rows = cur.fetchall()

    # for row in rows:
    #     print("{0} {1} {2}".format(row[0], row[1], row[2]))

    print("The query affected {} rows".format(cur.rowcount))

#!/usr/bin/python3

# -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect('localhost', 'user17',

's$cret', 'mydb')

with con:

cur = con.cursor()

cur.execute("SELECT * FROM cities WHERE id IN (1, 2, 3)")

# rows = cur.fetchall()

# for row in rows:

# print("{0} {1} {2}".format(row[0], row[1], row[2]))

print("The query affected {} rows".format(cur.rowcount))

В данном примере, показано, что используемый оператор SELECT выбирает три строки.

print("The query affected {} rows".format(cur.rowcount))

1	print("The query affected {} rows".format(cur.rowcount))

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

$ ./affected_rows.py 
The query affected 3 rows

1 2	$ ./affected_rows.py The query affected 3 rows

Это результат вывода.

В данной инструкции было показано, как использовать базу данных MySQL в Python при помощи модуля PyMySQL.

Vasile Buldumac

Являюсь администратором нескольких порталов по обучению языков программирования Python, Golang и Kotlin. В составе небольшой команды единомышленников, мы занимаемся популяризацией языков программирования на русскоязычную аудиторию. Большая часть статей была адаптирована нами на русский язык и распространяется бесплатно.

E-mail: vasile.buldumac@ati.utm.md

Образование
Universitatea Tehnică a Moldovei (utm.md)

2014 — 2018 Технический Университет Молдовы, ИТ-Инженер. Тема дипломной работы «Автоматизация покупки и продажи криптовалюты используя технический анализ»
2018 — 2020 Технический Университет Молдовы, Магистр, Магистерская диссертация «Идентификация человека в киберпространстве по фотографии лица»