Connecting your MySQL (pymysl) db to your python application.

Hello minna-san!
How are you? | Como vocês estão?

Today I brought you a tutorial of how to connect your MySQL database to your Python application. I've been working on this issue since yesterday and now that I have the answer to it, I would be more than glad to share it with you!

First, you will need to run some commands on your Terminal, into your Pycharm IDE.
So open it up and let's go.

PS: I used a video on Youtube to achieve with success all those steps and also some other references I will give the credits to.

Running a command using pip3

Make sure to always use the following command core to run your code in a smooth way.

pip3 install --trusted-host=pypi.org --trusted-host=files.pythonhosted.org --user

Follow with the example:

Run the following code on your MacOS terminal to install MySQL.

brew install mysql

mysql.server start - to start your DB

Onto your Python terminal on Pycharm type the following to make sure MySQL is installed in your machine

pip3 install pymysql --trusted-host pypi.org --trusted-host files.pythonhosted.org 

 pip3 install Certificates --trusted-host pypi.org --trusted-host files.pythonhosted.org

pip3 install --trusted-host=pypi.org --trusted-host=files.pythonhosted.org --user mysql-connector-python

pip3 install --trusted-host=pypi.org --trusted-host=files.pythonhosted.org --user pymysql

CRUDE Example:

into your db_run.py copy the following code and change it as necessary.

import pymysql.cursors

print('Conectando...')
conn = pymysql.connect(
      user='root',      passwd='pass',
      db='db',      host='127.0.0.1',      port=3306)

conn.commit()

try:

      with conn.cursor() as cursor:

            criar_tabelas = '''                  

                CREATE TABLE jogo (

                    id int(11) NOT NULL AUTO_INCREMENT,

                    nome varchar(50) COLLATE utf8_bin NOT NULL,

                    categoria varchar(40) COLLATE utf8_bin NOT NULL,

                    console varchar(20) NOT NULL,

                    PRIMARY KEY (id)) 

                    ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;'''
            criar_tabelas2 = '''CREATE TABLE usuario ( 

                    id varchar(8) COLLATE utf8_bin NOT NULL,

                    nome varchar(20) COLLATE utf8_bin NOT NULL,

                    senha varchar(8) COLLATE utf8_bin NOT NULL,

                    PRIMARY KEY (id)) 

                    ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;'''
          
      conn.commit()

      with conn.cursor() as cursor:

            sql = "INSERT INTO usuario (id, nome, senha) VALUES (%s, %s, %s)"

            # cursor.execute(sql,('luan', 'Luan Marques', 'flask'))            

            # cursor.execute(sql,('nico', 'Nico', '7a1'))            

            # cursor.execute(sql,('danilo', 'Danilo', 'vegas'))
            cursor.executemany(
                        'INSERT INTO usuario (id, nome, senha) VALUES (%s, %s, %s)',                        [
                              ('tai', 'Tailane', 'tata'),                              

                              ('midori', 'Mido', 'midori'),                              

                              ('tati', 'Tatiane', 'tati')
                        ]
            )


      conn.commit()

      with conn.cursor() as cursor:
            sql = 'select * from jogoteca.usuario'            cursor.execute(sql)
            result = cursor.fetchall()
            print(' -------------  Usuários:  -------------')
            print(result)
            for user in cursor.fetchall():
                  print(user[1])

      with conn.cursor() as cursor:
            
            sql = 'INSERT INTO jogoteca.jogo (nome, categoria, console) VALUES (%s, %s, %s)'
            cursor.execute(sql, ('God of War 4', 'Ação', 'PS4'))
            cursor.execute(sql, ('NBA 2k18', 'Esporte', 'Xbox One'))
            cursor.execute(sql, ('Rayman Legends', 'Indie', 'PS4'))
            cursor.execute(sql, ('Super Mario RPG', 'RPG', 'SNES'))
            cursor.execute(sql, ('Super Mario Kart', 'Corrida', 'SNES'))
            cursor.execute(sql, ('Fire Emblem Echoes', 'Estratégia', '3DS'))

            sql_select = 'SELECT * from jogoteca.jogo'            cursor.execute(sql_select)
            print(' -------------  Jogos:  -------------')
            for jogo in cursor.fetchall():
                  print(jogo[1])


      conn.commit()
finally:
      
      conn.close()

In this tutorial, you were able to Insert data into your DB using pymysl module.