Correct and easy method to copy tables from MySQL to Oracle

[Alex Hill]

Hello everyone,

I apologize if this question has been answered before, i've done some of my of searching and solution testing before coming here.

I've even created a thread on stackoverflow https://stackoverflow.com/questions/60008289/copy-or-migrate-tables-from-mysql-database-to-oracle-11g-and-vice-versa-using-py

I am using Python 3.6 & sqlalchemy 1.3.13, mysql workbench 8.0 and sql developer vers.19

Just like the title I would like to copy all the tables and their properties from MySQL to Oracle. You can see the solutions I have used in the link above after edit 1.

Up till now none of them worked except pandas read_mysql and to_sql but is it the right way to do it, does it really copy everything? is there anyway to "automate" the process without having to specify the table I want to copy?

I wanted to use etlalchemy which builds upon sqlalchemy but it's not available for windows (which i'm using)

Thank you for your help, this project is related to school.

[Gord Thompson]

If you're running Windows 10 then you might be able to use etlalchemy under WSL. If you're running Windows 8.1 then VirtualBox + your_Linux_distro_of_choice + etlalchemy would be another option.

The etlalchemy repo shows that the project is 100% Python so it should be possible to get it running natively on Windows. I suspect that the original author just didn't bother.

Note: I have no experience with etlalchemy, it just seems a shame for you to re-invent the wheel when it is just what you are looking for.

[Alex Hill]

The answers provided by Gord Thompson are good however i've stumbled upon a few issues:

• Working with Windows Subsystem Linux can be quite the hassle if you have multiple partitions on your hard-drive, for example, I use the Anaconda Distribution which is installed in my D:/ drive. So unless you have some familiarity with WSL I would somewhat avoid it. 
• If you insist on working with WSL, depending on the linux distribution you have chosen you're going to need to update your python inside your WSL and then install and IDE. If you will be using google search, you'll eventually be lead to a tutorial that recommends installing Pycharm, pycharm is a good IDE but you're going to have set the interpreter in its settings, which again depends on how familiar you are with these things. There is a WSL version interpreter available with the professional version of pycharm.
• Overall trying to work with WSL means looking up a lot of stuff on google and is a process which is kind of resource intensive.
• Working with virtual box is probably the best and easiest solution, I personally don't have a laptop strong enough to make it work as fast and responsive as I would like it to be.

Now, if you don't want install WSL or virtual box. Then the solution I used is this: https://www.slideshare.net/Stiivi/python-business-intelligence-pydata-2012-talk

Starting from slide 35 it provides an easy solution for copying a single table, I used it and I am satisfied. Just be careful when copying from Oracle to MySQL as their isn't a MySQL equivalent to the NUMBER data type. This solution requires sqlalchemy.