Python – How to connect to MySQL

By | 05/02/2020

In this post, we will see how to connect a Python application with the MySQL Db called “DbManagerUser”, created in the post: MySQL – How to create a DB.

First of all, in order to install the MySQL connector for Python, we run the command pip install mysql-connector-python.

Then, we open Visual Studio Code and we define an entity called “UserItem” and a Python script called “CoreForMySQL”:

[USERITEM.PY]

class UserItem:
    def __init__(self, userid, username, usertype):
        self.UserId = userid
        self.UserName = username
        self.UserType = usertype



[COREFORMYSQL.PY]

import mysql.connector
from UserItem import UserItem


def ExtractListUsers():
    # define list of Users
    lstUser = []
    
    try:
        # MySQL server connection string definition
        connect = mysql.connector.connect(user='test', password='$Test123',
                                 host='127.0.0.1',
                                 database='DbManageUser')
        
        # Query definition
        strSql = "select A.UserId, A.UserName, B.UserType from TabUser A inner join TabUserType B on A.UserType = B.IdUserType"
        
        # Cursor definition used to run the query
        cursor = connect.cursor()

        # getting records from the query
        cursor.execute(strSql)

        # fetching all records from the cursor
        records = cursor.fetchall()

        # Putting all data in the lstUser array
        for item in records:
            objUser = UserItem(item[0], item[1], item[2])
            lstUser.append(objUser)

        # connection close
        connect.close()

        return lstUser

    # define expects
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Access denied")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)
    
    
# users list
listUser = ExtractListUsers()

# read the list
for item in listUser:
    print(f"ID:{item.UserId} / UserName:{item.UserName} / Type:{item.UserType}")



Now, if we run the script, this will be the result:



Leave a Reply

Your email address will not be published.