Migrar de SQLite a MySQL.

I introduce myself, I am Alfredo Riveros and I have been learning about programming for some years, I am currently studying Higher Technician in Software Development at the Higher School of Commerce - Río Tercero, and below I will describe a challenge that I came across.

As the title says, my goal was to migrate a SQLite database to MySQL, something that arose from an assignment in the database subject that I am studying.

The database I selected belongs to the game SQL Murder Mystery. This game, created to teach SQL skills in a playful way, is available at this link, where you can download the database provided by its developers.

I chose this database for its pedagogical orientation, given that although it is a game in itself, it constitutes a valuable resource for both teaching and learning concepts related to databases.

My first step in this challenge was to investigate if I could use DB Browser for SQLite to export the database in a format compatible with MySQL Workbench. Although I managed to generate a SQL script from DB Browser, importing it into Workbench presented me with numerous problems, especially syntax and data integrity, in addition to the complexity of managing such a large file.

I studied this file and tried to solve the syntax problems, and finally came to the conclusion that I should look for another approach.

My next step was to use the sqlite3 function to export a sql script through the terminal (linux).

This time the script improved a lot in syntax, but nevertheless the big problem is that one or another new problem always appeared.

With both approaches exhausted, I took a moment to reflect and evaluate other alternatives. I considered that Python could be an effective tool for this migration, given its support for both SQLite and MySQL, and began to design an algorithm to automate the process.

Then I looked for information on the topic, first verifying that it was a possible approach and collecting information to be able to design an algorithm that would allow me to meet my goal.

Now I will briefly describe the new approach with which I achieved success in my goal.
The first thing I did was document my research step by step, which led me to learn about something called object-relational mapping (ORM).

object-relational mapping (ORM) is a technique used in programming to convert data between incompatible type systems in object-oriented programming languages. In the context of databases, ORM allows you to interact with relational databases through objects instead of using SQL queries directly. This provides a more intuitive and efficient way of working with data.

In my case I used SQLAlchemy to carry out the development of the algorithm in python, and analyzing the results I found the following key points.

• Classes like Tables:
  • Each class you define (such as crime_scene_report, drivers_license, etc.) corresponds to a table in the database. The class attributes represent the columns of the table.
• Instances as Rows:
  • Each instance of a class represents a row in the corresponding table. When you query data from SQLite, you get instances of these classes, which makes working with data more intuitive and object-oriented.
• Insertion and Update Operations:
  • Using methods like merge() allows you to operate on these instances directly. You can insert new records or update existing ones without having to write SQL queries manually, which simplifies the code and makes it more readable.
• Relationship Management:
  • SQLAlchemy automatically handles relationships between tables (for example, through foreign keys) through attributes in classes, making it easier to navigate between related records.

An important thing to note during the process, after several trials and errors, is that understanding the approach and evaluating the written code is crucial, as it helps you identify places where problems can arise. After some reflection and pause, I came to the conclusion that the issue was probably related to the database structure. However, one question persisted in my mind: how is it possible for this database to work in SQLite despite having integrity problems and the various errors that appeared? The answer is simple: unlike MySQL, SQLite allows you to have tables without primary keys, which contributes to large differences in data management between both systems. This flexibility in SQLite can mask problems that, in a more restrictive environment like MySQL, would result in immediate errors.

Another difference is that MySQL has a more rigorous approach to data structure and types. For example, if you define a field as INTEGER, you will not be able to insert a value that is not a number.

The differences continue, the result of understanding them was to realize that for the approach to work there would have to be a change in the database, for this I decided to modify the tables and make sure they complied with MySQL standards , the first thing is that each one has its primary key, and make sure that both have the same data types.

I add... If you want to do the same, keep in mind that SQLite does not allow you to directly alter tables, another big difference with MySQL.

Finally having made the adaptations in the script, and in the algorithm written in python, I proceeded to execute it. The result: The game database was migrated to MySQL.

This challenge not only improved my technical skills, but also taught me the importance of understanding the differences between database management systems and how these can affect the integrity of the database.

I hope my experience migrating database from SQLite to MySQL was helpful and inspiring. Each challenge presents an opportunity to learn and grow in the world of programming.
Thanks for reading and until next time!

The above is the detailed content of Migrar de SQLite a MySQL.. For more information, please follow other related articles on the PHP Chinese website!