Для работы с MySQL в Python используется библиотека pymysql. Но чтобы подключиться с локальной машины к базе данных, которая расположена на сервере, используем библиотеку sshtunnel.

По сути – мы сначала подключаемся к хостингу через защищённое соединение, а уже через это соединение подключаемся к базе данных.

Перед подключением убедитесь, что на хостинге разрешен доступ по SSH.

Как получить данные MySQL:

• в виде списка
• в виде словаря

Как получить данные MySQL в виде списка

Рассмотрим на примере получения информации о товарах из базы данных MySQL интернет-магазина на WooCommerce.

Результат:

(878, 'Рассеиватель шар ПММА 160 мм дымчатый (резьба А 85) TDM', 'SQ0321-0203')

Чтобы получить больше информации о товарах, усложним запрос. Допустим мы хотим получить ID, Наименование, Категорию, Артикул, Цену и Остаток:

cur.execute("""
    SELECT p.ID,
    p.post_title,
    cat.name,
    MAX(CASE WHEN meta.meta_key = '_sku' THEN meta.meta_value END),
    MAX(CASE WHEN meta.meta_key = '_price' THEN meta.meta_value END),
    MAX(CASE WHEN meta.meta_key = '_stock' THEN meta.meta_value END)
    FROM wp_posts AS p
    JOIN wp_postmeta AS meta ON p.ID = meta.post_ID
    LEFT JOIN
    (
        SELECT pp.id,
        GROUP_CONCAT(t.name SEPARATOR ' > ') AS name
        FROM wp_posts AS pp
        JOIN wp_term_relationships tr ON pp.id = tr.object_id
        JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
        JOIN wp_terms t ON tt.term_id = t.term_id
        || tt.parent = t.term_id
        WHERE tt.taxonomy = 'product_cat'
        GROUP BY pp.id, tt.term_id
    ) cat ON p.id = cat.id
    WHERE (p.post_type = 'product' OR p.post_type = 'product_variation')
    AND meta.meta_key IN ('_sku', '_price', '_stock')
    AND meta.meta_value is not null
    GROUP BY p.ID
    LIMIT 1
""")

Результат:

(878, 'Рассеиватель шар ПММА 160 мм дымчатый (резьба А 85) TDM', 'Садово-парковые светильники > Опоры и аксессуары для садово-парковых светильников', 'SQ0321-0203', '198', '5')

Как получить данные MySQL в виде словаря

Если мы изменим тип курсора на DictCursor и немного доработаем SQL-запрос, то данные можно получать в более удобном виде.

Пример ответа:

[
  {
    "id": 878,
    "name": "Рассеиватель шар ПММА 160 мм дымчатый (резьба А 85) TDM",
    "status": "publish",
    "sku": "SQ0321-0203",
    "price": "198",
    "regular_price": "198",
    "sale_price": ""
  }
]

Пример запроса:

import json
import pymysql
from sshtunnel import SSHTunnelForwarder
import app.config as config


def get_connection():
    server = SSHTunnelForwarder(
        ssh_address_or_host=config.host,
        ssh_username=config.host_login,
        ssh_password=config.host_pass,
        remote_bind_address=('127.0.0.1', 3306)
    )
    server.start()

    con = pymysql.connect(
        host='127.0.0.1',
        port=server.local_bind_port,
        user=config.user_db,
        password=config.pass_db,
        db=config.name_db,
    )
    return con


def get_products():
    con = get_connection()
    with con:
        cur = pymysql.cursors.DictCursor(con)
        cur.execute("""
                SELECT p.ID as id,
                p.post_title as name,
                p.post_status as status,
                MAX(CASE WHEN meta.meta_key = '_sku' THEN meta.meta_value END) as sku,
                MAX(CASE WHEN meta.meta_key = '_price' THEN meta.meta_value END) as price,
                MAX(CASE WHEN meta.meta_key = '_regular_price' THEN meta.meta_value END) as regular_price,
                MAX(CASE WHEN meta.meta_key = '_sale_price' THEN meta.meta_value END) as sale_price
                FROM wp_posts AS p
                JOIN wp_postmeta AS meta ON p.ID = meta.post_ID
                WHERE (p.post_type = 'product' OR p.post_type = 'product_variation')
                AND meta.meta_key IN 
                    ('_sku', '_price', '_regular_price', '_sale_price')
                AND meta.meta_value is not null
                GROUP BY p.ID
                LIMIT 1
            """)
        rows = cur.fetchall()
        print(json.dumps(rows, ensure_ascii=False, indent=2))


def main():
    get_products()


if __name__ == '__main__':
    main()

Обратите внимание, мы определяем курсор, как cur = pymysql.cursors.DictCursor(con). А в SQL-запросе для каждого поля задаём своё название столбца p.post_title as name.