Migrating from Excel to MySQL

I have not written about MySQL and Python for a long time, so today I tell you how to migrate a table that you have in Excel to MySQL, all this using Python. And all, in less than 50 lines. This entry today is a totally different approach to using MySQL for Excel, which I already told you about a while ago.

Previous knowledge

For the solution I propose, I used SqlAlchemy, and pandas. Obviously, you need to have a MySQL server. .

SqlAlchemy is an Object Relational Model library, this is ORM, which facilitates the writing of Python code that will be the ORM that is responsible for translating it into the language of the database. I have not used it in many projects, and the truth is, it is a little uncomfortable for me to write Python code that will be executed in the database, as well as not to use SQL, and to do it in a Python language, adapted to the imposed rules by SqlAlchemy. It’s not that it’s a bad thing, but it’s more of a personal mania, and in fact, I have not used it on almost any project.

However, today is one of those moments where you can get good performance from SqlAlchemy!

Pandas is the library par excellence to work with data in Python. Although with this library I have worked on multiple projects, the truth is that I have named the blog little. But hey, it should ever be the first!

Requirements

Before continuing, I assume that you have installed Anaconda, the best distribution of Python (to me, is the one I like and I use). In case you do not have it, I recommend that you install the python MySQL client with the command:

pip install mysqlclient

The fact of installing this MySQL client waas due to an error I was receiving, because I don’t have installed the module:

Error loading MySQLdb module: No module named 'MySQLdb'

So, if you receive this error, you now know what do you need in order to solve it: install the MySQL client.

The second thing to consider is that you need to have installed SqlAlchemy, (it is included in Anaconda !!) and you can check it with:

import sqlalchemy

The third consideration is that, to work with UTF-8, it is necessary to have the correct connection string, and in fact, it was good for me to read this answer: https://stackoverflow.com/questions/16394332/unicodeencodeerror-latin-1-codec-cant-encode-characters-in-position-0-1-ordi.

The code for migrating from Excel to MySQL

We have all the requirements ready, now you only need to modify the variables to locate the route, the Excel file, the tab that will become a table, and for MySQL, in addition to the connection data, how you want your new table to be called.

The code is this:

 # coding: utf-8 <br /># -------------------------------------------------------------------- 
# Name:       Excel 2 MySQL # Copyright:   (c) ManejandoDatos.es 2019 
# --------------------------------------------------------------------
 __APP__ = "Excel 2 MySQL - Version %s (%s)" 
__VERSION__ = "1.0.0" 
__FECHA__ = "7 Marzo 2018" 
nl = "\n" 
print((__APP__ + nl) % (__VERSION__, __FECHA__)) 
ruta = r"C:\" fichero = "file.xlsx"
solapa = "sheet_name"
tabla = "table_name"
host = "localhost" 
user = "user" 
passwd = "***" 
database = "your_db" 
try:     
    import sys     
    import pandas as pd     
    from sqlalchemy import create_engine
except Exception as e:     
    print(" *** ERROR FATAL *** Error inicial al cargar librerias!! ")     
    print(e)     
    sys.exit(0) 
def excel2mysql():     
   try:         
    try:            
        xls = pd.ExcelFile(ruta + "\\" + fichero)             
        x1 = xls.parse(solapa)             
        x1.columns = map(str.lower, x1.columns)  
        # Columnas a lower             
        engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8&use_unicode=1' % (user, passwd, host, database), encoding='utf-8')             
        with engine.connect() as conn, conn.begin():                         
               x1.to_sql(tabla, conn, if_exists='replace')             
        print ("Migración terminada!")         
except Exception as f:             
    print('Imposible conectar con Base de datos %s!! Revise datos de conexión!!' % (host))             
    print(f)     
except Exception as e:         
    print('ERROR!! Imposible abrir fichero')         
    print(e) 

if __name__ == '__main__':
     excel2mysql() 

Easy, isn’t it? If you like it, you can help me for mainining this blog with a beer!

As additional information, I recommend that you use the echo = True option in create_engine so that you can see all the SQL statements that are taking place in the process, although when you have become accustomed to this little script, it will be dispensable!

Also it is a good idea to include that you will be using UTF-8 in the connection string and in the encoding option of create_engine.

Happy coding!