angle-up arrow-clockwise arrow-counterclockwise arrow-down-up arrow-left at calendar card-list chat check envelope folder house info-circle pencil people person person-plus phone plus question-circle search tag trash x

Преобразование базы данных SQLAlchemy MariaDb / MySQL с интегрированным Primary Keys в UUID Primary Keys для веб-сайта Flask

26 апреля 2020 возле Peter

Создание скриптов для автоматизации преобразования. Они сэкономят ваше время и предотвратят ненужные ошибки.

post main image
https://unsplash.com/@enginakyurt

Этот вебсайт использует Flask, (обычный) SQLAlchemy с Alembic для миграций и MariaDb. Если вы их не используете, то этот пост, скорее всего, не то, что вы ищете.

В предыдущем посте я написал, почему многие сайты должны использовать UUID Primary Keys вместо Integer Primary Keys. Даже если у вас есть высокопроизводительный сайт, то, вероятно, только несколько таблиц отвечают за эту производительность. Высокопроизводительные веб-сайты используют кэширование, что означает, что (в большинстве случаев) SELECT разница в производительности не приведет к разнице во времени отклика страницы.

Основной проблемой при использовании UUID Primary Keys является производительность INSERT с таблицами со многими записями. Пересчет ключей после INSERT занимает намного больше времени, чем при использовании Integer Primary Keys. Для этого сайта я записываю каждый запрос в таблицу базы данных page_request, также в таблицу content_item_view записывается представление записи блога. Обе таблицы должны иметь максимальную производительность INSERT . В других таблицах не так много записей. Эти две таблицы, а также некоторые демонстрационные таблицы, являются единственными таблицами, которые я не конвертировал. Общее количество таблиц в БД - 80. Все таблицы имеют идентификационный столбец, который должен быть преобразован, может быть половина (?) не использует Foreign Keys.

Должны ли вы конвертировать базу данных вашего сайта? Наверное, нет, если она работает нормально, и вы счастливы. Я хотел преобразовать в UUID, так что продолжайте читать этот пост, если вы тоже этого хотите. Работает ли описанный метод? Да. Этот пост был написан в новой (преобразованной) базе данных.

Автоматизация преобразования существующей базы данных

Есть две важные причины для автоматизации преобразования:

  • Минимальное время простоя производственного сайта
  • Избегая ошибок

Процесс переноса преобразованной базы данных в производство выглядит примерно так:

  • Удалить веб-сайт
  • Сделать копию существующей базы данных
  • Скопировать базу данных в другую (локальную) систему
  • Преобразовать базу данных
  • Копировать преобразованную базу данных в производственную систему
  • Включить новую версию программного обеспечения
  • Поднять сайт

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

Я также разработал два других сценария, см. также ниже:

  • сравнивать таблицы и столбцы
  • копирую данные из одной БД в другую

Преобразование базы данных в четыре этапа

Прежде чем что-либо делать, сделайте резервные копии своей базы данных и проверьте, что эта резервная база данных корректна, восстановив и протестировав! Вы же не хотите потерять данные. Четыре шага:

  1. Преобразование существующей базы данных в новую базу данных DB_DATA с UUIDs
    Эта база данных имеет данные. Нас не волнуют ограничения, на самом деле мы удаляем их все. Это значит, что в новой базе данных Foreign Keys - это всего лишь значения. Главное, что мы здесь делаем: конвертируем Integer Primary Keys в UUID Primary Keys, и конвертируем Integer Foreign Keys в UUID Foreign Keys
  2. Сгенерируйте базу данных DB_FINAL с Alembic с UUIDs
    Эта база данных имеет UUID Primary Keys, UUID Foreign Keys и связи. Но она не имеет данных ... пока что. Я создал новое дерево или версию программы. Здесь я вношу все изменения для работы с UUID Primary Keys вместо Integer Primary Keys. В этой новой версии я внес изменения в файл models.py: Integer Primary Keys в UUID Primary Keys, Integer Foreign Keys в UUID Foreign Keys
  3. Сравниваем таблицы БД и столбцы БД DB_DATA с таблицами БД и столбцами БД DB_FINAL
    Мы не сравниваем ограничения. Они ДОЛЖНЫ быть идентичными, иначе мы не сможем скопировать данные на следующем этапе!
  4. Скопируем данные таблицы базы данных DB_DATA в таблицы базы данных DB_FINAL
    Я делаю это, используя SELECT INTO OUTFILE и LOAD DATA INFILE.

Несколько слов о коде

Самая трудоемкая часть, которую мне пришлось разработать, это класс базы данных. В основном он содержит административные методы MariaDb / MySQL . Я не собираюсь показывать весь свой код, но чтобы дать вам представление, вот класс базы данных с некоторыми методами:

import pymysql.cursors
import uuid


class Database:

    def __init__(self,  user=None, passwd=None, host=None, db=None, charset=None, autocommit=None, connect_timeout=None):
        ...

    def set_foreign_key_checks(self, off_on):
        
    def table_get_constraints(self, table_name):

    def table_has_integer_primary_key_id(self, table_name):

    def table_create_column(self, table_name, column_definition):

    def table_column_add_key(self, table_name, column_name):

    def table_drop_column(self, table_name, column_name):

    def table_drop_constraint(self, table_name, constraint_name):

    def load_column_with_uuids(self, table_name, column_name, force=False):

    def get_all_foreign_keys_to_table(self, table_name, column_name=None):

    def select_into_outfile(self, outfile, table_name, column_names):

    def load_from_infile(self, infile, table_name, column_names):

    def copy_uuid_from_referenced_table_to_foreign_key_table(self, 
                referenced_table_name, referenced_table_column_name, 
                foreign_key_table_name, foreign_key_table_column_name, 
                from_referenced_table_column_name, to_foreign_key_table_column_name):

1. Преобразовать существующую БД в новую БД DB_DATA с UUIDs

Мы должны создать новую базу данных с UUID в нужных местах. Это означает, что мы должны преобразовать Integer Primary Keys и Integer Foreign Keys в UUID. После создания UUID Foreign Keys мы можем снять ограничения.
Это позволяет легко переименовывать колонки.

Создание столбца UUID Primary Key не является большой проблемой. Я создаю колонку CHAR(32) 'id_upk_copy' и загружаю ее UUID. Для работы с Foreign Keys мы можем использовать таблицу INFORMATION_SCHEMA, чтобы получить все Foreign Keys в таблицу. Ниже приведен пример Foreign Keys со ссылкой на столбец id таблицы user . Запрос есть:

SELECT
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
  FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
   WHERE  
    REFERENCED_TABLE_SCHEMA = 'peterspython_dev_existing' 
    AND
    REFERENCED_TABLE_NAME = 'user';

И в результате:

+---------------------+-------------+----------------------------+-----------------------+------------------------+
| TABLE_NAME          | COLUMN_NAME | CONSTRAINT_NAME            | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+---------------------+-------------+----------------------------+-----------------------+------------------------+
| ...                 | ...         | ...                        |  user                   | id                     |
|  user_mtm_user_group |  user_id     |  user_mtm_user_group_ibfk_1 |  user                   | id                     |
|  user_mtm_user_role  |  user_id     |  user_mtm_user_role_ibfk_1  |  user                   | id                     |
|  user_login          |  user_id     |  user_login_ibfk_1          |  user                   | id                     |
| comment             |  user_id     | comment_ibfk_3             |  user                   | id                     |
| contact_form        |  user_id     | contact_form_ibfk_2        |  user                   | id                     |
| ...                 | ...         | ...                        |  user                   | id                     |
+---------------------+-------------+----------------------------+-----------------------+------------------------+

Результат включает в себя все необходимое для преобразования Foreign Keys. Таблицы 'user_mtm_user_group' и 'user_mtm_user_role' являются таблицами отношений many-to-many . Что я делаю, так это для каждого ограничения:

  • создайте новую колонку user_id_ufk_copy
  • скопировать UUID из колонки REFERENCED_TABLE id_upk_copy в TABLE_NAME user_id_ufk_copy

Последнее сделано с помощью метода в моем классе Database:

    def copy_uuid_from_referenced_table_to_foreign_key_table(self, 
                referenced_table_name, referenced_table_column_name, 
                foreign_key_table_name, foreign_key_table_column_name, 
                from_referenced_table_column_name, to_foreign_key_table_column_name):

        # we alias the referenced_table because the key can be in the same table
        query = '''
            UPDATE {} 
              INNER JOIN {} abcde
                ON abcde.{} = {}.{}
              SET {}.{} = abcde.{}'''\
                .format(
                    foreign_key_table_name, 
                    referenced_table_name, 
                    referenced_table_column_name, 
                    foreign_key_table_name, foreign_key_table_column_name,
                    foreign_key_table_name, to_foreign_key_table_column_name,
                    from_referenced_table_column_name)

        cursor = self.conn.cursor()
        self.execute_query(cursor, query)

        return True

Скрипт для преобразования базы данных выглядит следующим образом:

# convert integer primary keys and integer foreign keys to uuids
def to_uuid():

    db = Database(
         user=DB_USER, 
        passwd=DB_PASSWORD, 
        host=DB_HOST,
        db=DB_DATABASE_NAME)

    # connect to database

    # get all tables

    # get all tables with integer primary key id

    # create and load new columns: id_ipk_copy, INT(11), and id_upk_copy, CHAR(32)
    for table_name in table_names_ipkid:

        # create new column id_ipk_copy and copy id column into it

        # create new column id_upk_copy and fill with uuids

    # create new foreign key uuid columns in tables
    for table_name_ipkid in table_names_ipkid:
        for row in db.get_all_foreign_keys_to_table(table_name_ipkid, column_name='id'):

            foreign_key_table_name = row['FOREIGN_KEY_TABLE_NAME']
            foreign_key_table_column_name = row['FOREIGN_KEY_TABLE_COLUMN_NAME']

            if not db.table_column_is_integer(foreign_key_table_name, foreign_key_table_column_name):
                # already uuid?
                continue

            referenced_table_name = row['REFERENCED_TABLE_NAME']
            referenced_table_column_name = row['REFERENCED_TABLE_COLUMN_NAME']

            foreign_key_table_new_column_name = foreign_key_table_column_name  +  '_ufk_copy'
            foreign_key_table_new_column_definition = {
                'column_name': foreign_key_table_new_column_name,
                'definition': foreign_key_table_new_column_name  +  ' CHAR(32)',
            }
            db.table_create_column(foreign_key_table_name, foreign_key_table_new_column_definition)

            # copy uuids (id_upk_copy) from referenced_table to new column of foreign_key_table
            db.copy_uuid_from_referenced_table_to_foreign_key_table(
                referenced_table_name, referenced_table_column_name, 
                foreign_key_table_name, foreign_key_table_column_name, 
                'id_upk_copy', foreign_key_table_new_column_name)

    # at this point we have:
    # 
    # - tables with an integer primary key column id:
    #   - have a new column id_ipk_copy with a copy of id
    #   - have a new column id_upk_copy with uuids values 
    # - all tables with foreign keys:
    #   - have an extra foreign key column foreign_key_table_column_name  +  '_uuid'
    # 

    # now we must rearrange

    # first we drop all constraints
    ...

    # 1. Rearrange primary key
    # - drop integer primary key column id
    # - rename column id_upk_copy to id
    ...

    # 2. Rearrange foreign keys
    # for all tables, using column names ending with '_ufk_copy':
    # - drop integer foreign key column 
    # - rename uuid foreign key column to dropped integer foreign key column name
    ...

    # 3. Remove temporary columns
    ...

    # at this point we have:
    # a database that has all the data in the correct columns

2. Сгенерировать базу данных DB_FINAL с Alembic с UUID Primary Keys и Foreign Keys.

В этой базе данных есть UUID Primary Keys, UUID Foreign Keys и связи. Но в ней нет данных. Я создал новое дерево или версию программы. Здесь я делаю все изменения для работы с UUID Primary Keys вместо Integer Primary Keys. В этой новой версии я внес следующие изменения в файл models.py:

  • Заменить Integer Primary Keys на UUID Primary Keys
  • Заменено Integer Foreign Keys на UUID Foreign Keys

Первый шаг - создание пустой базы данных. В IQL я это сделал:

drop database  peterspython_dev;
create database  peterspython_dev;

Далее мы опустошаем каталог alembic/versions. Затем запускаем Alembic для создания свежей версии:

alembic revision -m "v2.20 first" 
alembic upgrade head

И сделайте это во второй раз, чтобы сгенерировать все таблицы и отношения:

alembic revision --autogenerate -m "v2.20 2e time"
alembic upgrade head

Импорт GUID() в процесс Alembic я пока не рассматривал. Перед запуском 'alembic upgrade head' я второй раз редактировал файл версий и заменил строку:

shared.models.GUID()

к:

sa.CHAR(32)

3. Сравните таблицы и столбцы базы данных DB_DATA с таблицами и столбцами базы данных DB_FINAL

Я также создал сценарий для этого. Это не очень сложно, но должно быть сделано, наши базы данных должны быть идентичны! Я не только сравниваю имена таблиц и колонок, но и проверяю, совпадают ли DATA_TYPE колонок. Скрипт сравнения выдал мне (ошибочное) сообщение типа:

db1 column content_item_translation.intro data_type text not same as db2 data_type longtext
db1 column content_item_translation.summary data_type text not same as db2 data_type longtext
db1 column mail_smtp_message.body_text data_type text not same as db2 data_type mediumtext
db1 column contact_form.message data_type text not same as db2 data_type mediumtext

Наверное, я поменял их вручную, так как только начинал с SQLAlchemy. Я использовал тип данных SQLAlchemy Text и нуждался в большем пространстве. В любом случае, время сделать это правильно.

К сожалению, единственный способ - это использовать типы данных диалектов MySQL MEDIUMTEXT и LONGTEXT, что уводит меня в очередной шаг от поддержки обоих диалектов MariaDb / MySQL и PostgreSQL. Но по крайней мере теперь обе базы данных идентичны!

4. Скопируйте данные из таблиц БД DB_DATA в таблицы БД DB_FINAL.

Конечно, я столкнулся с проблемой. Значения NULL в базе данных DB_DATA не были значениями NULL в базе данных DB_FINAL. Причина в том, что я определил свой собственный разделитель полей, разделитель строк. Читая об этом в интернете, решение оказалось простым: используйте значения по умолчанию MariaDb / MySQL . Это означает экспорт по:

SELECT  <column_names_list> FROM <table_name> INTO OUTFILE <outfile_infile>

А потом импортировать:

LOAD DATA LOCAL INFILE <outfile_infile> INTO TABLE <table_name> <column_names_list>

Теперь значения NULL передаются корректно. Другая проблема, с которой вы можете столкнуться, это OUTFILE. Если этот файл существует, вы получите ошибку, поэтому убедитесь, что эти файлы удалены перед новым запуском.

То, что пошло не так

База данных больше не отвечает

Может быть, все это добавление и удаление столбцов что-то путает. Но, возможно, в моем коде что-то не так. В первой версии я забыл закрыть соединение. Когда я зашел в mysql и захотел сбросить базу данных, я получил это сообщение:

MySQL: Error dropping database (errno 13; errno 17; errno 39)

Это означает, что есть один или несколько файлов, которые заблокированы или что-то в этом роде сервером базы данных. Решение заключается в том, чтобы зайти в этот каталог базы данных на вашей файловой системе и удалить эти файлы, например, вручную:

sudo rm /var/lib/mysql/your_database_name/category_translation.ibd

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

Объект UUID не имеет атрибута 'translate'.

Во время тестирования контактной формы я получил сообщение:

AttributeError: 'UUID' object has no attribute 'translate' when using backend-agnostic  GUID type

Зачем? Это произошло, когда я впервые зафиксировал объект mail_message, а затем использовал данный mail_message.id в качестве Foreign Key при добавлении записей в таблицу mail_message_to.После фиксации тип(item.id) = <класс 'uuid.UUID'>, item.id = dcd0fd6b-cd0f-44b0-92c6-a5f08c0d784f и его нельзя записать в поле ForeignKey.

Проблема: я преобразовал Integer Primary Keys в UUID Primary Keys , но не сделал этого для Foreign Keys ... ой ... дурацкий я .... Решением было заменить CHAR(32) в models.py на GUID():

    ... = Column(CHAR(32), ForeignKey(...

Должен быть изменен:

    ... = Column(GUID(), ForeignKey(...

После этого изменения не было необходимости запускать Alembic .

Добавлена отсутствующая Foreign Keys

По крайней мере (по крайней мере, пока) в трех местах я только что добавил Integer в таблицу, в которую я должен был добавить Foreign Key. Два из них были в ContentItem и ContentItemTranslation Classess. Я использовал их для пометки родительского элемента. После того, как я отредактировал объект ContentItem или ContentItemTranslation, я нажимаю кнопку Save. Затем делается копия текущей записи с родительским элементом оригинальной записи. Чтобы исправить это, я вернулся к исходной базе данных, добавил Foreign Keys, и запустил процесс снова.

Raw SQL

Хотя я использую большинство запросов SLQAlchemy ORM , я также использую SQLAlchemy Raw SQL. Оказалось, что некоторые из этих запросов были неудачными, так как после преобразования идентификаторы были не кавычками.

Flask routes

Конечно, есть проблема со многими маршрутами. В Flask можно указать тип данных для параметров функции представления, причем большую часть времени они составляли Integer. Теперь они должны быть строками. Пример:

@pages_blueprint.route('/category', defaults={'category_id': 0})
@pages_blueprint.route('/category/<int:category_id>')
def category(category_id):
    ...

Должен быть изменен:

@pages_blueprint.route('/category', defaults={'category_id': ''})
@pages_blueprint.route('/category/<category_id>')
def category(category_id):
    ...

Integer проверка

В некоторых местах я явно проверяю, является ли значение Integer. Это только я в параноидальном режиме. Например, на этом сайте используется Flask-Login, а в create_app() у нас есть user_loader:

    @login_manager.user_loader
    def load_user(user_id):
        ...
        return g.db_session.query(User).get(user_id)

Мне пришлось убрать проверку, что user_id на самом деле был Integer.

Сортировка

Да, конечно, я использовал идентификатор Integer Primary Key в ряде случаев для сортировки записей. С UUID эта сортировка запуталась. Решили это с помощью временной метки (записи), созданной_на временной метке. У меня уже был этот столбец create_on в каждой таблице.

WTForms SelectField

В большинстве форм с выбором я использую (id, name) Tuples. Но id больше не Integer. Решение заключается в удалении параметра coerce=int . Пример:

class ContentItemAssignAuthorsForm(FlaskForm):

    author_ids = MultiCheckboxField(_('Select authors for content item'),  coerce=int)
    submit = SubmitField(_l('Assign'))

Должна быть заменена на:

class ContentItemAssignAuthorsForm(FlaskForm):

    author_ids = MultiCheckboxField(_('Select authors for content item'))
    submit = SubmitField(_l('Assign'))

Резюме

Это был первый раз, когда я использовал Python pymysql с преимущественно командами обслуживания MariaDb / MySQL , такими как получение информации о столбцах, контрапункты, добавление и удаление столбцов, хранение и загрузка данных. Где-то я ожидал серьезных трудностей во время конвертации, но этого не произошло.

Использование UUID может сбить с толку. Это в определенный момент объект UUID или строка? Я использую рецепт Backend-agnostic GUID Type , смотрите ссылки ниже. Думаю, я бы предпочел, чтобы UUID были везде строками, в конце концов, нам нужен только объект UUID при вставке объекта Primary Key или Foreign Key.

Ссылки / кредиты

AttributeError: 'UUID' object has no attribute 'replace' when using backend-agnostic GUID type
https://stackoverflow.com/questions/47429929/attributeerror-uuid-object-has-no-attribute-replace-when-using-backend-agno

Backend-agnostic GUID Type
https://docs.sqlalchemy.org/en/13/core/custom_types.html?highlight=guid#backend-agnostic-guid-type

How can I use UUIDs in SQLAlchemy?
https://stackoverflow.com/questions/183042/how-can-i-use-uuids-in-sqlalchemy

How do I see all foreign keys to a table or column?
https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column

MySQL: Error dropping database (errno 13; errno 17; errno 39)
https://stackoverflow.com/questions/12196996/mysql-error-dropping-database-errno-13-errno-17-errno-39

Replace integer id field with uuid
https://stackoverflow.com/questions/42674931/replace-integer-id-field-with-uuid

SQLAlchemy one to one relationship get parent and add child
https://stackoverflow.com/questions/59227673/sqlalchemy-one-to-one-relationship-get-parent-and-add-child

Using custom UUID Type from SQLAlchemy
https://stackoverflow.com/questions/47275130/using-custom-uuid-type-from-sqlalchemy

What column type does SQLAlchemy use for “Text” on MySQL?
https://stackoverflow.com/questions/47644739/what-column-type-does-sqlalchemy-use-for-text-on-mysql

Оставить комментарий

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

Комментарии

Оставьте ответ

Ответьте анонимно или войдите в систему, чтобы ответить.